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

Databases
1

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 myTable.name 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?

Thanks…

2

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

3

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!

4

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: http://documentation.xojo.com/index.php/PreparedSQLStatement

5

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.