Passing Array Parameter to SQL command javascript

Question

I'm trying to pass a dynamic array to a executeSql. I'm passing the SQL to the IN as array.

let lor_text_Array = [];
if (lor_text == "TEST 1, 2, 3") {
    lor_text_Array = ["TEST 1", "TEST 2", "TEST 3"];
}
else {
    lor_text_Array.push("TEST 4")
    lor_text_Array.push("TEST 5")
}

let sql_query = 'SELECT * FROM SUBMISSION WHERE REV IN (' + lor_text_Array + ')ORDER BY ID DESC'; 
app.db.transaction(function (tx) { tx.executeSql(sql_query, [], loadItemsSYNCED, app.onError);});

however an error occured while executing sql Error object Object

You should format your array as a `string` value manually. Because it's formatted by JS as `object Object`. — Vlad DX, Dec 03 '19 at 23:38

score 3 · Accepted Answer · answered Dec 03 '19 at 23:40

3

Assuming that your string values do not contain embedded single quotes, you use map and join to generate the IN list, as follows:

let lor_in_list = lor_text_Array.map(function (a) { return "'" + a + "'"; }).join(",");
let sql_query = 'SELECT * FROM SUBMISSION WHERE REV IN (' + lor_in_list + ')ORDER BY ID DESC';

To handle possible embedded single quotes:

let lor_in_list = lor_text_Array.map(function (a) { 
    return "'" + a.replace("'", "''") + "'"; 
}).join(",");

answered Dec 03 '19 at 23:40

GMB

216,147
25
84
135

ok you mean when i create the array like let lor_text_Array = ["TEST 1", "TEST 2", "TEST 3"]; it is not a comma seperated string? – Dec 04 '19 at 00:04
can i avoid single quotes when creating the array? – Dec 04 '19 at 00:19

Passing Array Parameter to SQL command javascript

1 Answers1