Writing Insert query inside node-red-contrib-oracledb-mod

General
1

Hello, I am trying to make use of node-red-contrib-oracledb-mod to connect to the Oracle db and access datas. I am able to connect it successfully and write select queries and getting the proper results. But, not able to write the insert queries by giving the values that I get from the payload. Please do help me out in this.

INSERT INTO Schema1.tableABC (GOOGLEPLUS, MIDDLENAME, NOTES, DEPARTMENT, BIRTHDAY, PROFILEPHOTO, LINKEDIN, MAIL_ID, SKYPEID, FACEBOOK, TWITTER, STATUS, SALUTAION, FIRSTNAME, LASTNAME, WORKPHONE, MOBILE, TITLEDESIGNATION ) 
VALUES( +msg.payload.ContactInformation[0].Googleplus+ , +msg.payload.ContactInformation[0].MiddleName+ , +msg.payload.ContactInformation[0].Notes+ , +msg.payload.ContactInformation[0].Department+ TO_DATE( +msg.payload.ContactInformation[0].Birthday+ ,'YYYY-MM-DD'), +msg.payload.ContactInformation[0].ProfilePhoto+ , +msg.payload.ContactInformation[0].Linkedin+ , +msg.payload.ContactInformation[0].Email+ , +msg.payload.ContactInformation[0].SKYPEID+ , +msg.payload.ContactInformation[0].Facebook+ , +msg.payload.ContactInformation[0].Twitter+ , +msg.payload.ContactInformation[0].Status+ , +msg.payload.ContactInformation[0].Salutation+ , +msg.payload.ContactInformation[0].FirstName+ , +msg.payload.ContactInformation[0].LastName+ , +msg.payload.ContactInformation[0].WorkPhone+ , +msg.payload.ContactInformation[0].Mobile+ , +msg.payload.ContactInformation[0].TitleDesignation+ );

This is how I am trying to write the query in node-red-contrib-oracledb-mod.
Using this node for the first time. If there's any material that explains in detail about this node, please share.

Thanks in Advance.

2

Feed that into a debug node and check the values are being inserted into the query correctly. Also I don't know about oracle but on mysql the column names would be in backticks.

1 Like
3

Yeah Colin. Tried it. I am getting the following error.

"Oracle query error: NJS-019: ResultSet cannot be returned for non-query statements"

4

+msg.payload.ContactInformation[0].Googleplus+
Is this correct ? (concatenating strings+variables).

5

I am not sure. Need help in that only.

6

Your payload should look something like:

sql = "INSERT INTO Schema1.tableABC ( ... ) VALUES ('"+msg.payload.ContactInformation[0].Googleplus+"', '"+msg.payload.ContactInformation[0].MiddleName+"')"

return {payload:sql}

To make it readable for yourself, assign variables like:

googleplus = msg.payload.ContactInformation[0].Googleplus
middlename = msg.payload.ContactInformation[0].MiddleName

sql = "INSERT INTO Schema1.tableABC ( ... ) VALUES ('" + googleplus + "', '"+ middlename +"')"

return {payload:sql}
7

I will try this and get back.
Thank you

8

Should I write this in the node-red-contrib-oracledb-mod or in a separate function node? I made changes in node-red-contrib-oracledb-mod node as follows. Still it is giving the same error as before.

query = "INSERT INTO Schema1.tableABC (GOOGLEPLUS, MIDDLENAME, NOTES, DEPARTMENT, BIRTHDAY, PROFILEPHOTO, LINKEDIN, MAIL_ID, SKYPEID, FACEBOOK, TWITTER, STATUS, SALUTAION, FIRSTNAME, LASTNAME, WORKPHONE, MOBILE, TITLEDESIGNATION ) VALUES('"+msg.payload.ContactInformation[0].Googleplus+ "', '" +msg.payload.ContactInformation[0].MiddleName+ "', '" +msg.payload.ContactInformation[0].Notes"' , '" +msg.payload.ContactInformation[0].Department+ "', TO_DATE( '" +msg.payload.ContactInformation[0].Birthday+ "' ,'YYYY-MM-DD'), '" +msg.payload.ContactInformation[0].ProfilePhoto+ "' , '" +msg.payload.ContactInformation[0].Linkedin+ "' , '" +msg.payload.ContactInformation[0].Email+ "' , '" +msg.payload.ContactInformation[0].SKYPEID+ "' , '" +msg.payload.ContactInformation[0].Facebook+ "' , '" +msg.payload.ContactInformation[0].Twitter+ "' , '" +msg.payload.ContactInformation[0].Status+ "' , '" +msg.payload.ContactInformation[0].Salutation+ "' , '" +msg.payload.ContactInformation[0].FirstName+ "' , '" +msg.payload.ContactInformation[0].LastName+ "' , '" +msg.payload.ContactInformation[0].WorkPhone+ "' , '" +msg.payload.ContactInformation[0].Mobile+ "' , '" +msg.payload.ContactInformation[0].TitleDesignation+ "' )";

return {payload:query}
9

Did you read the info panel of the node ?

Oracle database storage node. Connects to a server and inserts rows into the database or retrieves rows from the database with a SQL query.

Expects an object called msg containing msg.payload and optionally msg.query and msg.mappings.

msg.payload: array containing the fields to be used inside the query, first element in the array corresponds with the first `:fieldname` parameter in the query etc.
msg.query: string containing the SQL query, if this is not available, the default SQL query will be used.
msg.fieldMappings: array containing the object to array field mappings. Will be used if the content of msg.payload is not an array. If this is not available, the default field mappings will be used.
msg.resultAction: string containing "single", "single-meta", "multi" or "none", if "single" a single result message containing the resulting rows will be sent, if "single-meta" a single result message containing the resulting rows and metadata will be sent, if "multi" the results can be spread over multiple messages, if "none" no messages will be sent. If this is not available the default defined in Query results will be used.
msg.resultSetSize: number, maximum number of rows in a result message. If this is not available the default defined in Query results will be used.

10

Sorry, I didn't read it. I will work on this now.
Thanks @bakman2

1 Like
11

@Divya in your first post you asked if there was any documentation on how to use the node. Every single node provides help in the info sidebar. Some may provide more help than others, but that should always be the first place to look when you want to learn more about the node.

12

Yes @knolleary I agree. But, sometimes I may need more information and some examples than what is provided in the info tab. Just now I went through the info tab thats provided for this oracle node. Still I am finding difficulties in inserting the query. Badly in need of help. I am also trying in whatever ways possible.

13

I tried in the following way. Still I am not able to solve this issue.

if (msg.payload) {
    
 var values = `('` +  msg.payload.ContactInformation[0].Googleplus + `','` +  msg.payload.ContactInformation[0].MiddleName + `','` +  msg.payload.ContactInformation[0].Notes + `','` +  msg.payload.ContactInformation[0].Department + `', TO_DATE('` + msg.payload.ContactInformation[0].Birthday + `', 'YYYY-MM-DD'),'` +  msg.payload.ContactInformation[0].ProfilePhoto + `','` +  msg.payload.ContactInformation[0].Linkedin + `','` +  msg.payload.ContactInformation[0].Email + `','` +  msg.payload.ContactInformation[0].SKYPEID + `','` +  msg.payload.ContactInformation[0].Facebook + `','` +  msg.payload.ContactInformation[0].Twitter + `','` +  msg.payload.ContactInformation[0].Status + `','` +  msg.payload.ContactInformation[0].Salutation + `','` +  msg.payload.ContactInformation[0].FirstName + `','` +  msg.payload.ContactInformation[0].LastName + `','` +  msg.payload.ContactInformation[0].WorkPhone + `','` +  msg.payload.ContactInformation[0].Mobile + `','` +  msg.payload.ContactInformation[0].TitleDesignation + `')`;

   var query = `INSERT INTO VAPL.SPOORSLEADCONTACT (GOOGLEPLUS, MIDDLENAME, NOTES, DEPARTMENT, BIRTHDAY, PROFILEPHOTO, LINKEDIN, MAIL_ID, SKYPEID, FACEBOOK, TWITTER, STATUS, SALUTAION, FIRSTNAME, LASTNAME, WORKPHONE, MOBILE, TITLEDESIGNATION ) VALUES ` + values;

    msg.query = query;

    return msg;

}

Storing it in msg.query only. Still oracle-db node isn't accepting it

14

What does a debug node return (complete msg object)

15

Are you sure the node takes the query as you are writing it
In that Info panel you have now read…

16

I'm getting the following query in the msg.query object from the debug message of the function node. I executed the same query manually in the oracle sql developer tool. It inserts the data. So, I don't think there's a problem in query. But, I just need to know how this msg.query will be accessed by the oracledb node.

INSERT INTO schema1.tableabc (GOOGLEPLUS, MIDDLENAME, NOTES, DEPARTMENT, BIRTHDAY, PROFILEPHOTO, LINKEDIN, MAIL_ID, SKYPEID, FACEBOOK, TWITTER, STATUS, SALUTAION, FIRSTNAME, LASTNAME, WORKPHONE, MOBILE, TITLEDESIGNATION ) VALUES ('Test', 'Test', 'Notes', 'Test', TO_DATE('2019-07-11', 'YYYY-MM-DD'), '103804', 'Test', 'test@gmail.com', 'Test', 'Test', 'Test', 'Active', '', 'ABC', 'Tech', '6875432234', '6875432234', 'Test')

I am getting the same error as before from the oracledb node

Oracle query error: NJS-019: ResultSet cannot be returned for non-query statements
17

Hello, Sorry for the late response. The node-red-contrib-oracledb-mod node wasnot accepting the variables in the query tab of it. We had to give the raw query that runs in the database and not any javascript form of it. So, I just wrote the query as required for y need in a function node and assigned the query to msg.query, which in turn goes as an input to the node-red-contrib-oracledb-mod node. Finally performs the task as expected.

Here, I failed in finding out a way to map the data I get from my payload to the query within the node-red-contrib-oracledb-mod node, where it says it has that feature. But, I was not able to find out the right way of doing it. I think we need somemore documentation with some examples on the mapping payload values part.

18

Have you logged that as an issue on the nodes GitHub page?

19

No. I didn't even thought of that. Thanks @ukmoose, I will raise this issue on that node's github page.

20

Hi @Divya, if you can share the steps, will help for me. I am facing same issue.