Error: NJS-005: invalid value for parameter 2

[kleyton032]

1. What versions are you using? Node - v12.18.2 node-oracledb#v5.1.0

Give your database version. oracle 11g

2. Describe the problem

I have a problem trying to make an insert, compile error - Error: NJS-005: invalid value for parameter 2

3. Include a runnable Node.js script that shows the problem.

const registerContato = async () => { const options = { autoCommit: true, bindDefs: { s: { type: oracledb.NUMBER }, p: { type: oracledb.NUMBER}, t: { type: oracledb.STRING, maxSize: 200 }, dt: { type: oracledb.DATE }, u: { type: oracledb.STRING, maxSize: 200 }, obs: { type: oracledb.STRING, maxSize: 200 }, con: { type: oracledb.STRING, maxSize: 200 }, loc: { type: oracledb.STRING, maxSize: 200 } } } const binds = { s: 123456, p: 1113567, t: "FALTA", dt: new Date(), u: "KLEYTON", obs: "TESTE", con: "PACIENTE", loc: "EXAMES" };

const sql = `INSERT INTO dbamv.fav_registro_contato_marc(CD_SEQ, CD_PACIENTE, TP_SITUACAO, DT_REGISTRO, CD_USUARIO, DS_OBSERVACAO, CONTATO, LOC) 
                                                        VALUES(:s, :p, :t, :dt, :u, :obs, :con :loc)`;

console.log(binds)
const conn = await oracledb.getConnection(credentials).catch(err => console.log('ERRO', err));
console.log("Conexão sucesso")
//oracledb.fetchAsString = [oracledb.DATE];
//let result = await conn.execute(`SELECT current_date, current_timestamp FROM DUAL`);
//console.log(result);
const result = await conn.executeMany(sql, binds, options).catch(err => console.log('ERRO', err));
console.log(sql);
console.log('Query: ' , result) //result.rowsAffected;

}

---

Include all SQL needed to create the database schema.

[cjbj]

Review the executeMany() example em_insert1.js and see how the binds parameter is constructed. It looks like you are using the non-array format that you might pass to execute().

If this isn't the problem, then:

• debug the problem by reducing the number of columns you are passing
• update your question with the SQL files that will create the table
• update your question with a runnable Node.js script that reproduces the problem

[kleyton032]

I'm getting another error "ORA-01036: Invalid variable name/number"

I took the instruction and ran it directly in the database through query insert and it worked normally, as follows in the print below and passing all the same parameters as binds, I executed it as explained with fewer parameters and without success...

Another question, the first column of my table is a sequence, if you can see in the print of the direct insertion in the database you can see that I use it, I would like to know how I do the passage inside binds to be mapped with this sequence that is created, already very grateful.

` const registerContato = async () => { const options = { autoCommit: true, bindDefs: { s: { type: oracledb.NUMBER }, p: { type: oracledb.NUMBER }, t: { type: oracledb.STRING, maxSize: 200 }, dt: { type: oracledb.DATE }, u: { type: oracledb.STRING, maxSize: 200 }, obs: { type: oracledb.STRING, maxSize: 200 }, con: { type: oracledb.STRING, maxSize: 200 }, loc: { type: oracledb.STRING, maxSize: 200 } } } const binds = [ { s: 12345, p: 1113567, t: "FALTA", dt: new Date(), u: "KLEYTONBOMFIM", obs: "TESTE", con: "PACIENTE", loc: "EXAMES" }, ];

const sql = `INSERT INTO dbamv.fav_registro_contato_marc(CD_SEQ, CD_PACIENTE, TP_SITUACAO, DT_REGISTRO, CD_USUARIO, DS_OBSERVACAO, CONTATO, LOC) 
                                                        VALUES(:s, :p, :t, :dt, :u, :obs, :con :loc);`;

let conn;
try {
    conn = await oracledb.getConnection(credentials);
    console.log(binds)
    const result = await conn.executeMany(sql, binds, options);
    console.log('Query: ', result)
} catch (error) {
    console.error(error);
} finally {
    if (conn) {
        try {
            await conn.close();
        } catch (error) {
            console.error(error);
        }
    }
}

} `

[cjbj]

If you could provide the info previously requested we can more easily help:

• update your question with the SQL files that will create the table
• update your question with a runnable Node.js script that reproduces the problem

PS avoid screen shots: not everyone can read them, and they can't be cut/pasted from when replying.

[kleyton032]

• update your question with the SQL files that will create the table:

---this question generated a question it was not clear, do you need to see the structure of the table in which the data will be inserted?

• update your question with a runnable Node.js script that reproduces the problem:

----And at this point, do you say executable code, for example, inside codeSandbox, or something like that?

[cjbj]

https://stackoverflow.com/help/minimal-reproducible-example

We need the SQL CREATE statement to create the table.

Also give us a single JS file that connects to a DB and performs the code that gives you a problem. We will change the credentials to our DB. Don't make us spend time recreating this file when you already have the code!!! You can use https://gist.github.com/ to host it.

[kleyton032]

We need the SQL CREATE statement to create the table:

**The table is already created, and inside a database in production, unfortunately I can't provide sensitive data due to Brazil's data protection law LGPD, I tried to reproduce the most faithful scenario I'm working on, I apologize in advance if I can't provide more information , I'm creating a restApi, to insert these data through a front in React.js, but I'm still running it in the test environment as you can see that the parameters passed in binds are "fake", just for testing, to check if the format and type are in accordance with what the table expects to receive, and as I said in the previous comment, I did the manual insertion with the same parameters and it was successfully inserted into the table.

obs: As mentioned by you, I believe that with the example given to you, it is possible to put your connection string and test where the possible error is and with that solve mine.

Grateful in advance!

example: https://codesandbox.io/s/affectionate-diffie-yp96c?file=/src/models/registerContato.js

[anthony-tuininga]

Since you appear to be doing a simple insert, can you simply provide the create table statement and adjust your example accordingly? The names of the table and columns shouldn't be important -- you can call the table issue_1401 and the columns col_1, col_2, col_3, etc. That way we will be running exactly the same code as you and can comment accordingly.

[kleyton032]

Ok, but in this part, where you are asked to create the table, I'm in doubt where I should create this..., in the link shown in the documentation there is no part where I do a "create table", so I would be immensely grateful if you could provide one example or guide on how to make this request of you using my example.

**can you simply provide the create table statement and adjust your example accordingly?

[anthony-tuininga]

Sure something like this:

create table issue_1401 (
    col_s number,
    col_p number,
    col_t varchar2(200),
    col_dt date,
    col_u varchar2(200),
    col_obs varchar2(200),
    col_con varchar2(200),
    col_loc varchar2(200)
);

Then adjust your SQL in your script and post your modified script here. That way we can simply run your script after only changing the database credentials.

[kleyton032]

Ahhhh, sorry, in fact I didn't understand, I run this script to create this table within my database creating this new table, I change my query within the code by placing the new table and in binds I put the new parameters of this table and then step for you here, correct?, but there is still a question, I do not need to pass connection strings from my bank, thank you!

[anthony-tuininga]

No! Please don't send credentials for your bank! We will run the create table statement in our own database, change your script to put in the credentials of our own database and then run the script to see if we get the same error you are getting. Hope that explains things clearly enough!

[kleyton032]

i hope i have reached the expected utulizeio the user hr of the database, follow the link with the code as requested, thanks!

example: https://codesandbox.io/s/hopeful-framework-1trh1?file=/src/index.js

[anthony-tuininga]

So what error did you get when you ran that code?

I found two issues: namely, a missing comma in your list of bind variables in your SQL (which yielded ORA-1036) and a trailing semicolon (which yielded ORA-933). Once I fixed those the code worked as expected.

[cjbj]

@kleyton032 thanks for persevering with this. I think @anthony-tuininga has fixed your immediate problems.

Thanks for using node-oracledb.

[kleyton032]

I still have the same error ORA-01036, but if you notice the columns in capital letters are going through my query, and I was also passing in the insert into the instance of the database, I changed these two forms and inserted them into the database normally, immensely grateful. and once again I apologize for any inconvenience, thank you very much indeed.

[kleyton032]

@anthony-tuininga @cjbj, just one more question that I had commenting in another comment, in this case I have a column that is filled by a sequence ("seq"), and automatically incremented, how do I pass this information into my binds with the name of the sequence?

[kleyton032]

Hi, you managed to check my last comment, how do I pass my Sequence inside binds?, I'm waiting thanks!

[anthony-tuininga]

Hi, my apologies for the delay in responding to you! You can't pass the name of a sequence inside a bind variable. You can only pass data there. So you have to do something like this:

conn.execute("insert into SomeTable (Col1, Col2, Col3) values (SomeSeq.nextval, :a, :b)", [a, b]);

If the column is one that is an identity column, such as in this table:

create table SomeTable (
    Col1 number generated always as identity,
    Col2 number,
    Col3 number
);

Then you want to do this instead:

conn.execute("insert into SomeTable (Col2, Col3) values (:a, :b)", [a, b]);

[kleyton032]

Hi, thank you very much @anthony-tuininga , the first option is my case, I will test it and let you know the result, my table is mapped with a sequence in the way that the 1st option I believe will be valid. thanks

[cjbj]

I'll close this since it has concluded. Open new issues if you have new questions. And let us know if you create something fun or useful.