How do I programmatically create a paramarray to pass to SQLitePreparedStatement.sqlSelect?

I am creating a database search container control. It allows the user to specify any number of criteria which will all be “ANDed” together to form a query. Since the user will be entering text, among other data types, I’d like to use prepared statements for the queries. As designed, the container creates an SQL string (e.g., “SELECT * FROM myTable WHERE LIKE ? AND myTable.age > ?”) and an array of 1-n (2 in the this example) variants representing the values with which each of the “?” characters in the string needs to be substituted.

I have a method that takes these two parameters (the string and the variant array). It instantiates a SQLitePreparedStatement, iterates over each element of the passed-in array, and does the appropriate data type bindings. It then passes the variant array to the SQLitePreparedStatement.sqlSelect function.

That results in this error:

Database Error: 0 - Mismatched parameter and type at 1

and it returns an empty recordSet.

It fails even if I try this with a single element in the array, BUT if I do this:

myRecordSet = myPreparedStatement.SQLSelect(theValues(0))

instead of

myRecordSet = myPreparedStatement.SQLSelect(theValues)

it works as expected with the one criterion. So, passing a single property to the SQLselect function works, but passing a variant array does not.

What am I doing wrong, and how can I construct whatever the function is looking for in terms of multiple values rather than just one?


Use the Bind method instead of passing the values in SQLSelect. Bind each value individually, then call SQLSelect with no additional parameters.

Excellent! As you probably can tell, this is my first foray into using preparedStatements. The examples I looked at all did it with an array, and I missed the “bind” method in the docs.

Thanks, Tim!

You must check WHICH database you are using. PosgreSQL permits PreparedSQLStatement.SQLExecute ( Paramarray bindValues() as Variant ). Others needs some mix of .BindType() and .Bind() prior to .SQLExecute().

Check them here:

The Xojo Docs contain this line of code in one of the examples for SQLitePreparedStatement:

Dim rs As RecordSet = ps.SQLSelect("john", 20)

That looks to me like a Paramarray, which is why I was trying what I was trying.