abreits
commented
8 years ago I have no working oracle database at the moment to test against, so I cannot test it, but I suspect that the problem originates in the value binding.
Does the following code work in node.js? (example code from the node-oracledb library)
This should work (with the correct credentials):
oracledb.getConnection(
{
user : "hr",
password : "welcome",
connectString : "localhost/XE"
},
function(err, connection)
{
if (err) { console.error(err.message); return; }
connection.execute(
"INSERT INTO DEMO (devid) VALUES (:id)",
["3333"], // bind value for :id
function(err, result)
{
if (err) { console.error(err.message); return; }
console.log(result.rows);
});
});This should also should work (with the correct credentials):
oracledb.getConnection(
{
user : "hr",
password : "welcome",
connectString : "localhost/XE"
},
function(err, connection)
{
if (err) { console.error(err.message); return; }
connection.execute(
"INSERT INTO DEMO (devid) VALUES ('3333')",
// ["3333"], // bind value for :id <-- this line commented out
function(err, result)
{
if (err) { console.error(err.message); return; }
console.log(result.rows);
});
});If my suspicion is correct this could create the error you are experiencing (with the correct credentials):
oracledb.getConnection(
{
user : "hr",
password : "welcome",
connectString : "localhost/XE"
},
function(err, connection)
{
if (err) { console.error(err.message); return; }
connection.execute(
"INSERT INTO DEMO (devid) VALUES ('3333')",
[], // bind value for :id <-- empty value array
function(err, result)
{
if (err) { console.error(err.message); return; }
console.log(result.rows);
});
});If this is indeed the case, the workaround is to pass the value in the payload to the oracledb node (and for me to fix this bug).
I hope this helps, otherwise we will need to wait until next week, when our oracle DB admin is available for help.
mu45
phatao
We are trying to get node-red-contrib-oracledb running, yet to no (satisfying) avail. We've setup the Oracle schema 'testdb' on a remote machine. The device running node-red is equiped with the required Oracle libraries. We are actually able to insert a row into the table "DEMO" using SQLPLUS with the same credentials we configured the node with. Once we trigger the node to do an insert, we get the error "ORA-1036 - Illegal variable name/number". The query we fire is: "INSERT INTO DEMO (devid) VALUES ('3333');". Cases are correct, the column "devid" exists and is type varchar2(32). We have hacked the oracledb.js to output the query to the console log. It turns out that it is about to execute the query in the exact same way as posted above. Again, if I copy the query from the console log and paste it to SQLPLUS, it would just work fine. Could you give us any glue on what might be the cause for this behavior? Many thanks in advance!