search

abreits / node-red-contrib-oracledb

Node-RED oracle database nodes
8 stars 15 forks source link

NJS-006: Invalid Type for parameter 2 #2

Open nowhereman404 opened 7 years ago

nowhereman404 commented 7 years ago

Hello,

i am using the Oracle node to connect to a Oracle 11g DB on Windows 7. When i try to send a query (any, insert, select...) by setting the msg.query property, it causes Node Red to crash and i get the following error:

Error: NJS-006: invalid type for parameter 2 at Object.assert (C:......\oracledb\lib\util.js:78:11) at Connection.execute (C:......\oracledb\lib\connection.js:87:16) at Connection.execute (C:......\oracledb\lib\util.js:97:19) at OracleServer.node.query (C:......\oracledb\lib\oracledb.js:186:33) at OracleServer.node.queryQueued (C:......\oracledb\lib\oracledb.js:255:22) at C:......\oracledb\lib\oracledb.js:146:30 at C:......\oracledb\lib\oracledb.js:215:7

If i enter the query string in SQL Developer, it runs without problems.

Do you have any idea what the problem could be?

Now if i try with the following data for example: msg.payload = [1]; msg.query = "INSERT INTO MACHINES (MACHINEPOSITION) VALUES (:mn)";

I get this error: NJS-019: ResultSet cannot be returned for non-query statements

At the time i havent been able to execute any query.

abreits commented 7 years ago

The crashing needs to be fixed :) I will look into that when I have time.

You can specify in the Query results tab how the results will be received, "send multiple result messages" is the default, try to change that to "send single query result message" or "ignore results".

nowhereman404 commented 7 years ago

Thank you for the fast answer. Changing the results to "send single query result message" fixes the NJS-019 error and now accepts the example query from before. But if i try a more complicated query with strings, numbers and a timestamp, it causes Node Red to crash with the error:

(cmd output) [red] Uncaught Exception: Error: ORA-00911: invalid character
at Error (native)

do you have to give quotes with the string? like: [...,"'"+ stringValue + "'",...] or simply [..., stringValue,....] ether way i get the error. I hope the informations are helpfull.

abreits commented 7 years ago

This looks like an Oracle input problem. I am not an Oracle specialist, I created this node for a colleague and he can use it. The node uses the https://github.com/oracle/node-oracledb library underneath, maybe you can look there for more information.

nowhereman404 commented 7 years ago

I think the "invalid character" error was caused by an ';' at the end of my query (I guess your node appends that at the end and is not required in the query). I think the documentation could use some more info but well, i understand that you are not getting paid for this so i thank you for the contribution anyway. Have a nice day :)