Can SQLiteDatabase.SelectSQL accept an array as parameter?

Databases
1

Hi,

I just ran into this runtime error:

parameters cannot be arrays

which was generated by this line of code:

rs = MainDatabase.SelectSQL(sql, values)

where values() is a string array. I thought I was being smart because the SQL select statement will vary in the number of parameters it needs and rather than writing multiple individual SelectSQL statements with ParamArray of different lengths, this looked much neater.

I thought this was ok, based on the Language Reference:

Database.SelectSQL (SQLStatement as String [,ParamArray values() as Variant]) As RowSet
Database.SelectSQL (SQLStatement as String [,values() as Variant]) As RowSet

My interpretation was that you could pass either an indefinite number of parameters separately or one array that contained all the parameters.

Then I thought maybe the problem was that my values() array was of type String, not Variant, so I changed it, but then got a different runtime error:

Expected 2 parameters to be bound but received 1

I guess it then treated my passed array as just the first parameter.

I can easily work around this by just writing out separate statements for different conditions, but could someone clarify for me what the actual situation is please? What does the Language Reference mean by the second version then?

2

I’ve used variant arrays to pass the values for an API 2.0 prepared sql statement. Are you sure your array length matches the number of placeholders in the SQL?

3

It has to be a Variant array, not a String array, is my best guess.

4

This.

The error:

Expected 2 parameters to be bound but received 1

sure sounds like you have a mismatch in the number of placeholders in your SQL string vs. the number of values in the passed array.

Sample code from the LR:

Var rowsFound As RowSet
rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE Age=? AND PostalCode=?", Age.Value, PostalCode.Value)

The Number of “?” symbols in the SQL needs to match the number of array values. If it does, then maybe what Kem suggested, otherwise it might be a bug?

5

Hi Tim,

You are spot on. This is embarrassing! I missed adding one of the arguments to my values array! Now corrected and works fine :kissing_closed_eyes:

Thanks guys

2 Likes
6

(and yes, the sample code isn’t using an array, it was just an easy way to show some SQL with the correct “?” “value identifiers”.)

LR entry is here: https://documentation.xojo.com/api/databases/database.html#database-selectsql

[edit: fixed link]