How To Pass Unknown Number Of Parameters to SQLUpdate

I wasn’t sure if this should be in Databases or iOS, so feel free to move to the correct channel as needed.

I’ve written my own database class that I prefer to use as the conduit to my sqlite database. It includes a generic insert, update, and delete method as well as a bunch of other helper utility methods to do various things with the database. Before you tell me not to do it this way, I have good reasons, such as being able to automate auditing what happens with the database by hooking into my own custom methods.

In the case of the Insert method, it takes 2 parameters: TableName as Text and data as Dictionary. On the old framework, this worked great because I could build up the string of SQL required by using the table name and just walking through the dictionary, then using a prepared statement to bind all the values and ultimately run the insert. Then I could record the success or failure, do whatever auditing was required, etc. and then return an indication of whether the insert was successful or not to the caller.

Here is where I’ve run into an issue: In the new framework, I need to run the insert with a call to SQLExecute, which takes an SQLStatement as Text and Paramarray values() as Auto. Param arrays don’t seem to play nice with me just passing an array, and so I can’t do the following:

[code] //Create a text to hold our SQL as we build it
dim sql as Text
//start the sql statement, including the table name
sql = “insert into " + table + " (”

//walk through the dictionary of data (keys are column names, values are the data to insert) and build up more of the sql statement.
dim i as integer = 1
for each entry as DictionaryEntry in data
sql = sql + entry.Key
if i < data.Count then
sql = sql + “,”
end if
i = i + 1
sql = sql + “) values (”
i = 1
//extract the values we will be inserting into an array for later use with sqlExecute
dim values() as Text
for each entry as DictionaryEntry in data
//put in binding placeholders such as ?1, ?2, etc in the sql, based on how many items are in the data dictionary.
sql = sql + “?” + i.ToText
if entry = nil then
end if
if i < data.Count then
sql = sql + “,”
end if
i = i + 1
//close off our sql insert statement.
sql = sql + “)”

mydb.SQLExecute(sql, values) //THIS FAILS because I cannot pass an array in place of a paramarray.
catch e as RuntimeException
app.logger.log("Error on database query : " + sql + “. Values:”)
for i = 0 to ubound(values)
return false
end try


It would appear (based on my reading of the documentation for SQLExecute) that I have to instead pass the values like this:

mydb.sqlExecute(sql, values(0), values(1), values(2), values(3), values(4))

Which works great if I know that I’ll always have 5 values to pass… but I want to make this as generic as possible. How can I use this with dictionaries of different sizes from different places in my app?

Am I missing something obvious?

The docs say “ParamArray”:

SQLExecute(sqlstatement As Text, ParamArray values() As Auto)

So you can’t hand over an array to SQLExecute. See Feedback 40319.

This means you have to construct the sql with all the values inserted yourself so you then can call it like that:


Found the bug report and signed on to it, and added it as my #1 most needed. Bumped it to #22. So hopefully it will get some love in the next 2 years.


I guess this means I have to try to sanitize my own sql? Has this nut already been cracked? What do I need to consider when taking user input and putting it straight into sql statements? Obviously I should escape quotes, semicolons, etc. What else should I consider?

I think you should remove the “Answered” sign (so others open this thread) or start a new thread with this question.

Personally I would consider doing the ugly approach of a select case block that switches on the number of items in the array and invokes it like your last snippet of code. It definitely is a kludge and there would need to be some limit on how many parameters you can have…

@Kimball Larsen: use prepared statements, don’t try to sanitise. You’ll forget something - everyone (including me) always does. I think it was Kem who said that he had three pieces of advice when taking user data and putting it into a database: 1: use prepared statements, 2: see point 1; 3: don’t think about doing it any other way. (I paraphrase!)

Hamish, there is currently no possibility to pass a variable amount of values to SQLiteDatabase in iOS – that was his initial question. If one wants to pass a variable amount of values, one has to “sanitize” the values oneself.

Then I’d go with @Joe Ranieri 's idea of having a really ugly select case statement which worked for one, two, three, four, five, values, and used prepared statements with them. The ugliness of the case statement would be more than compensated for by using prepared statements.

Already submitted <>. Feel free to add it to your Top Cases list to give it some points.

Yeah, I was afraid of this. The unfortunate part is that I have a table with upwards of 20 fields, so I’ll just have to have a large select block. Ugly.

For anyone interested, I’ve knocked up a quick method that extends the sqliteDatabase class to allow for an array of Text up to 50 elements large to be passed to sqlExecute. I’ve tested it lightly and it seems to work for my specific use-case.

I’d be happy to know if there are improvements that could be made to this approach, as I hate this method. A lot.

It’s apparently too large to paste here, so here’s a pastebin.



db.SQLExecute(sql, split(values,",")) wont work?

I don’t believe so, because each element of the array has to be passed to the method as a separate argument. Using split will convert the array to a single string, which will be passed as only 1 argument.

I have no idea. But if this works:

db.SQLExecute(sql, “first name”, “last name”, “etc”)

…then I guess using split should work.

I think you mean Join. Split only works on a text object. Join is how you take an array and slap all the elements together into a delimited string.

And, to clarify, if your array looks like this:

arr(0) = "first name" arr(1) = "last name" arr(2) = "address"

The result of join as you describe will be a single string that looks like this:

"first name, last name, address"

Unfortunately, I have to pass all the elements of the array as separate strings.

[quote=234572:@Marco Hof]I have no idea. But if this works:

db.SQLExecute(sql, “first name”, “last name”, “etc”)

…then I guess using split should work.[/quote]

Actually, it won’t work. Parameter Array, which is what you are demonstrating, is not the same as a regular array. If it was, this problem would be solved by now.

Ah, I didn’t know that.