jeudi 18 février 2016

execute query breaking due to wrong string concatenation

I am trying to execute an INSERT query using db.execute using the Appcelerator APIs. As per its docs it should be ideally used as :

var thisName = 'Arthur';
var thisPhoneNo = '1-617-000-0000';
var thisCity = 'Mountain View';
db.execute('INSERT INTO people (name, phone_number, city) VALUES (?, ?, ?)', thisName, thisPhoneNo, thisCity);

Database execute API doc for Appcelerator

So here is my problematic code which is almost blinding me:

    var _tbl = "g_work_docket";
    var records = [{
        "id" : "2134",
        "accession_no" : "20043446",
        "opened" : "2014-07-25",
        "job_origin" : "TRAMS Inspection",
        "deadline" : "2014-09-30",
        "completed_by" : "naren",
        "completed" : "2016-02-18",
        "mitigation_option" : "",
        "location_no" : "186:03:",
        "notes" : "",
        "comments" : "",
        "priority" : null,
        "status" : "closed",
        "is_approved" : "0",
        "reviewer_comments" : "",
        "updated_at" : "2016-02-18 12:58:50",
        "is_deleted" : "0",
        "site" : "K"
    }, {
        "id" : "3016",
        "accession_no" : "196920850",
        "opened" : "2000-01-19",
        "job_origin" : "TRAMS Inspection",
        "deadline" : "2001-01-01",
        "completed_by" : "naren",
        "completed" : "2016-02-18",
        "mitigation_option" : "",
        "location_no" : "770:01:",
        "notes" : "Further inspection :\n\nDecay assesment : microdrill trunk base",
        "comments" : "",
        "priority" : null,
        "status" : "closed",
        "is_approved" : "0",
        "reviewer_comments" : "",
        "updated_at" : "2016-02-18 13:26:14",
        "is_deleted" : "0",
        "site" : "W"
    }];

    _.each(records, function(record) {
        var qry = "INSERT OR REPLACE INTO " + _tbl + " (";
        _.each(record, function(value, key, list) {
            qry += '' + key + ',';
        });
        qry = qry.slice(0, -1);
        qry += ") VALUES (";
        _.each(record, function(value, key, list) {
            qry += '?,';
        });
        qry = qry.slice(0, -1);
        qry += "),";
        _.each(record, function(value, key, list) {
            qry += "'" + value + "',";
        });
        qry = qry.slice(0, -1);
        db.execute(qry);
    });

This always errors saying:

[ERROR] :  Error: near "'2134'": syntax error (code 1): , while compiling: INSERT OR REPLACE INTO g_work_docket (id,accession_no,opened,job_origin,deadline,completed_by,completed,mitigation_option,location_no,notes,comments,priority,status,is_approved,reviewer_comments,updated_at,is_deleted,site) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),'2134','20043446','2014-07-25','TRAMS Inspection','2014-09-30','naren','2016-02-18','','186:03:','','','null','closed','0','','2016-02-18 12:58:50','0','K'

Aucun commentaire:

Enregistrer un commentaire