Node-oracledb: Cant insert date with executeMany

Created on 20 Jun 2019  路  3Comments  路  Source: oracle/node-oracledb

queryString = `INSERT INTO TABLENAME ( DATEBORN ) VALUES ( to_date(:dtb, 'DD.MM.YYYY') )`;

optionsObj = {  
    autoCommit: true,
        bindDefs: {    
        dtb:    { type: oracledb.DATE }     
    } 
}   

bindsArr =[ { dtb: '11.05.2019' }, { dtb: '20.06.2019' } ]

connection = await oracledb.getConnection(
    { 
        user: dbConfig.user, 
        password: dbConfig.password, 
        connectString: dbConfig.connectString
    });                

 // There error: [Error: NJS-011: encountered bind value and type mismatch]
result = await connection.executeMany(     
        queryString
       ,bindsArr
       ,optionsObj
);   

I can't insert the date into the database using executeMany. There is no problem with other types of data.
Also, the date is successfully added using execute.
What am I doing wrong?

question

Most helpful comment

The minimal change is to bind as oracledb.STRINGsince your dates are actually stored as strings like '20.06.2019' in your example.

    let queryString = `INSERT INTO TABLENAME ( DATEBORN ) VALUES ( to_date(:dtb, 'DD.MM.YYYY') )`;

    let optionsObj = {
      autoCommit: true,
      bindDefs: {
        dtb:    { type: oracledb.STRING, maxSize: 10 }
      }
    };

    let bindsArr = [ { dtb: '11.05.2019' }, { dtb: '20.06.2019' } ];

    result = await connection.executeMany(queryString, bindsArr, optionsObj);

All 3 comments

The minimal change is to bind as oracledb.STRINGsince your dates are actually stored as strings like '20.06.2019' in your example.

    let queryString = `INSERT INTO TABLENAME ( DATEBORN ) VALUES ( to_date(:dtb, 'DD.MM.YYYY') )`;

    let optionsObj = {
      autoCommit: true,
      bindDefs: {
        dtb:    { type: oracledb.STRING, maxSize: 10 }
      }
    };

    let bindsArr = [ { dtb: '11.05.2019' }, { dtb: '20.06.2019' } ];

    result = await connection.executeMany(queryString, bindsArr, optionsObj);

Thnx, it works!
But i can't understand why.
In my database type of field is
image

In this case, what is { type: oracledb.DATE } used for?
According to my example, there is no use at all.

You are passing a string in:

    let s = '11.05.2019';
    console.log(typeof s);  // string
Was this page helpful?
0 / 5 - 0 ratings

Related issues

nicholas-ochoa picture nicholas-ochoa  路  3Comments

ainthek picture ainthek  路  3Comments

cristian-programmer picture cristian-programmer  路  4Comments

annjawn picture annjawn  路  4Comments

satodu picture satodu  路  3Comments