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
– Vlad DX, Commented Dec 3, 2019 at 23:38

GMB · Accepted Answer · 2019-12-03 23:40:09Z

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 3, 2019 at 23:40

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user12448300 Over a year ago

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?

user12448300 Over a year ago

can i avoid single quotes when creating the array?

Collectives™ on Stack Overflow

Passing Array Parameter to SQL command javascript

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related