# Howto SQL-Query with quotes

**URL:** <https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155>\
**Category:** General\
**Created:** [6 March 2021 13:57 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155 "2021-03-06T13:57:46Z")\
**Posts on this page:** 4\
**Page:** 2

<div class="post-metadata">

**Author:** ![Muschelpuster](https://avatars.discourse-cdn.com/v4/letter/m/b3f665/32.png) [@Muschelpuster](https://discourse.nodered.org/u/Muschelpuster)\
**Post date:** [15 March 2021 21:07 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/22 "2021-03-15T21:07:03Z")

</div>

Damned! I corrected it but I tooke an old version at my external editor for solving the null-challenge. So I was hunting a ghost. Now I rebuild it and checked it twice:

```auto
var value01 = typeof(msg.cdr.event) != "undefined" ? msg.cdr.event : null;
var value02 = typeof(msg.cdr.time) != "undefined" ? msg.cdr.time : null;
var value03 = typeof(msg.cdr.ts) != "undefined" ? msg.cdr.ts : null;
var value04 = typeof(msg.cdr.ref) != "undefined" ? msg.cdr.ref : null;
var value05 = typeof(msg.cdr.dir) != "undefined" ? msg.cdr.dir : null;
var value06 = msg.cdr.GWmark + msg.cdr.src_if;
var value07 = msg.cdr.GWmark + msg.cdr.dst_if;
var value08 = typeof(msg.cdr.src_cgpn) != "undefined" ? msg.cdr.src_cgpn : null;
var value09 = typeof(msg.cdr.src_dgpn) != "undefined" ? msg.cdr.src_dgpn : null;
var value10 = typeof(msg.cdr.src_cdpn) != "undefined" ? msg.cdr.src_cdpn : null;
var value11 = typeof(msg.cdr.dst_cgpn) != "undefined" ? msg.cdr.dst_cgpn : null;
var value12 = typeof(msg.cdr.dst_dgpn) != "undefined" ? msg.cdr.dst_dgpn : null;
var value13 = typeof(msg.cdr.dst_cdpn) != "undefined" ? msg.cdr.dst_cdpn : null;
var value14 = typeof(msg.cdr.bcaps) != "undefined" ? msg.cdr.bcaps : null;
var value15 = typeof(msg.cdr.cause) != "undefined" ? msg.cdr.cause : null;
var value16 = typeof(msg.cdr.xcoder) != "undefined" ? msg.cdr.xcoder : null;
var value17 = typeof(msg.cdr.rcoder) != "undefined" ? msg.cdr.rcoder : null;
var value18 = typeof(msg.cdr.xstats) != "undefined" ? msg.cdr.xstats : null;
var value19 = typeof(msg.cdr.rstats) != "undefined" ? msg.cdr.rstats : null;
var value20 = typeof(msg.cdr.alert_time) != "undefined" ? msg.cdr.alert_time : null;
var value21 = typeof(msg.cdr.connect_time) != "undefined" ? msg.cdr.connect_time : null;
var value22 = typeof(msg.cdr.disc_time) != "undefined" ? msg.cdr.disc_time : null;
var value23 = typeof(msg.cdr.srv_id) != "undefined" ? msg.cdr.srv_id : null;
var value24 = typeof(msg.cdr.start_time) != "undefined" ? msg.cdr.start_time : null;
var value25 = typeof(msg.cdr.result) != "undefined" ? msg.cdr.result : null;

// 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25                      
msg.topic = "INSERT INTO `test-daten` (`d_event`, `d_time`, `d_ts`, `ref`, `d_dir`, `d_src_if`, `d_dst_if`, `d_src_cgpn`, `d_src_dgpn`, `d_src_cdpn`, `d_dst_cgpn`, `d_dst_dgpn`, `d_dst_cdpn`, `d_bcaps`, `d_cause`, `d_xcoder`, `d_rcoder`, `d_xstats`, `d_rstats`, `d_alert_time`, `d_connect_time`, `d_disc_time`, `d_srvid`,`d_start_time`,`d_result`) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
msg.payload = [value01, value02, value03, value05, value06, value07,value08, value09, value10, value11, value12, value13, value14, value15, value16, value17, value18, value19, value20, value21, value22, value23, value24, value25];
return msg;

```

But Maria is not kindly to me:

```auto
Error: 
ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?)' at line 1
msg.topic:
INSERT INTO `test-daten` (`d_event`, `d_time`, `d_ts`, `ref`, `d_dir`, `d_src_if`, `d_dst_if`, `d_src_cgpn`, `d_src_dgpn`, `d_src_cdpn`, `d_dst_cgpn`, `d_dst_dgpn`, `d_dst_cdpn`, `d_bcaps`, `d_cause`, `d_xcoder`, `d_rcoder`, `d_xstats`, `d_rstats`, `d_alert_time`, `d_connect_time`, `d_disc_time`, `d_srvid`,`d_start_time`,`d_result`) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

```

Niels

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [15 March 2021 23:12 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/23 "2021-03-15T23:12:06Z")

</div>

Some of your columns are set for NOT NULL, check your Payload values are suitable for the column types.

E.g.

```auto
`d_result` varchar(10) COLLATE latin1_german1_ci NOT NULL

```

But you have...

```auto
var value25 = typeof(msg.cdr.result) != "undefined" ? msg.cdr.result : null;

```

And yes I realise the error message might not match what I am saying but check everything/check all values, use hard coded values as a test if you have to - just to prove the SQL works before you start digging.

---

<div class="post-metadata">

**Author:** ![Muschelpuster](https://avatars.discourse-cdn.com/v4/letter/m/b3f665/32.png) [@Muschelpuster](https://discourse.nodered.org/u/Muschelpuster)\
**Post date:** [17 March 2021 20:55 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/24 "2021-03-17T20:55:53Z")

</div>

Thanks Steve,

I never thought that the values can be the problem at this error message. Additionally the same data had no problems with the template-solution.  
But it was - there was a offset in column-order.

Niels

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [31 March 2021 20:56 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/25 "2021-03-31T20:56:23Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.

[Previous page](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155.md?page=1)