Howto SQL-Query with quotes

General
22

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:

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:

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

23

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

E.g.

`d_result` varchar(10) COLLATE latin1_german1_ci NOT NULL

But you have...

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.

24

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

25

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