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
next
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
values.append("")
else
values.append(entry.Value)
end if
if i < data.Count then
sql = sql + “,”
end if
i = i + 1
next
//close off our sql insert statement.
sql = sql + “)”
try
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)
app.logger.log(values(i))
next
return false
end try
[/code]
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?