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

**URL:** <https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390>\
**Category:** General\
**Created:** [17 July 2019 11:18 UTC](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390 "2019-07-17T11:18:51Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Divya](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/divya/32/6208_2.png) [@Divya](https://discourse.nodered.org/u/Divya)\
**Post date:** [17 July 2019 11:18 UTC](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390/1 "2019-07-17T11:18:51Z")

</div>

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.

```auto
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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [17 July 2019 11:43 UTC](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390/2 "2019-07-17T11:43:16Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Divya](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/divya/32/6208_2.png) [@Divya](https://discourse.nodered.org/u/Divya)\
**Post date:** [17 July 2019 11:48 UTC](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390/3 "2019-07-17T11:48:11Z")

</div>

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

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

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [17 July 2019 12:01 UTC](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390/4 "2019-07-17T12:01:32Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Divya](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/divya/32/6208_2.png) [@Divya](https://discourse.nodered.org/u/Divya)\
**Post date:** [17 July 2019 12:03 UTC](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390/5 "2019-07-17T12:03:40Z")

</div>

I am not sure. Need help in that only.

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [17 July 2019 12:09 UTC](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390/6 "2019-07-17T12:09:56Z")

</div>

Your payload should look something like:

```auto
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:

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

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

return {payload:sql}

```

---

<div class="post-metadata">

**Author:** ![Divya](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/divya/32/6208_2.png) [@Divya](https://discourse.nodered.org/u/Divya)\
**Post date:** [17 July 2019 12:11 UTC](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390/7 "2019-07-17T12:11:38Z")

</div>

I will try this and get back.  
Thank you

---

<div class="post-metadata">

**Author:** ![Divya](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/divya/32/6208_2.png) [@Divya](https://discourse.nodered.org/u/Divya)\
**Post date:** [17 July 2019 12:36 UTC](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390/8 "2019-07-17T12:36:54Z")

</div>

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.

```auto
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}

```

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [17 July 2019 13:08 UTC](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390/9 "2019-07-17T13:08:54Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Divya](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/divya/32/6208_2.png) [@Divya](https://discourse.nodered.org/u/Divya)\
**Post date:** [17 July 2019 13:12 UTC](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390/10 "2019-07-17T13:12:44Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [17 July 2019 13:31 UTC](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390/11 "2019-07-17T13:31:08Z")

</div>

@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.

---

<div class="post-metadata">

**Author:** ![Divya](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/divya/32/6208_2.png) [@Divya](https://discourse.nodered.org/u/Divya)\
**Post date:** [17 July 2019 13:35 UTC](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390/12 "2019-07-17T13:35:02Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Divya](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/divya/32/6208_2.png) [@Divya](https://discourse.nodered.org/u/Divya)\
**Post date:** [17 July 2019 13:40 UTC](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390/13 "2019-07-17T13:40:31Z")

</div>

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

```auto
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

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [17 July 2019 13:49 UTC](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390/14 "2019-07-17T13:49:30Z")

</div>

What does a debug node return (complete msg object)

---

<div class="post-metadata">

**Author:** ![ukmoose](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ukmoose/32/13_2.png) [@ukmoose](https://discourse.nodered.org/u/ukmoose)\
**Post date:** [17 July 2019 14:13 UTC](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390/15 "2019-07-17T14:13:51Z")

</div>

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

> [@bakman2](#):
>
> **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.

---

<div class="post-metadata">

**Author:** ![Divya](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/divya/32/6208_2.png) [@Divya](https://discourse.nodered.org/u/Divya)\
**Post date:** [17 July 2019 16:14 UTC](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390/16 "2019-07-17T16:14:15Z")

</div>

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.

```auto
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

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

```

---

<div class="post-metadata">

**Author:** ![Divya](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/divya/32/6208_2.png) [@Divya](https://discourse.nodered.org/u/Divya)\
**Post date:** [20 August 2019 09:01 UTC](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390/17 "2019-08-20T09:01:28Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ukmoose](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ukmoose/32/13_2.png) [@ukmoose](https://discourse.nodered.org/u/ukmoose)\
**Post date:** [20 August 2019 09:04 UTC](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390/18 "2019-08-20T09:04:52Z")

</div>

> [@Divya](#):
>
> . I think we need somemore documentation with some examples on the mapping payload values part

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

---

<div class="post-metadata">

**Author:** ![Divya](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/divya/32/6208_2.png) [@Divya](https://discourse.nodered.org/u/Divya)\
**Post date:** [20 August 2019 09:07 UTC](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390/19 "2019-08-20T09:07:38Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![shekhar](https://avatars.discourse-cdn.com/v4/letter/s/c37758/32.png) [@shekhar](https://discourse.nodered.org/u/shekhar)\
**Post date:** [10 March 2020 09:33 UTC](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390/20 "2020-03-10T09:33:54Z")

</div>

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

[Next page](https://discourse.nodered.org/t/writing-insert-query-inside-node-red-contrib-oracledb-mod/13390.md?page=2)