Using "$VVV" in a SQLitePreparedStatement

Databases
1

According to the docs, you can use the form $VVV as a named parameter within a SQLite prepared statement. You can also use “:” or “@” as the prefix. I have no trouble with the latter two, but “$” keeps giving me an error. Am I missing something?

For example:

  sql = "SELECT * FROM person WHERE last_name = $abc"
  ps = db.Prepare(sql)
  // Error: Unable to prepare statement
2

That’s weird. I normally just use “?”

 sql = "SELECT * FROM person WHERE last_name = ?"
3

As do I, but SQLite is supposed to be able to take named indexes with “$”. It just doesn’t.

Anyway, for my purposes, it doesn’t much matter. In fact, I just added some normalization code to our database adapters so you can write it in any form and the code will change the SQL (if needed) so it can be prepared by that engine. If you want to use “:name” style placeholders for Postgres or MySQL and provide a Dictionary or array of Pairs, go ahead. The adapter will fix it for you.

I’ll make this public at some point.

4

https://www.sqlite.org/lang_expr.html#varparam
they should all work as far as I know
they’re part of sqlite itself

5

Then perhaps a bug in the plugin. I’ll create a small project and submit it to feedback.

6

MBS SQL plugin uses : prefixes for named parameters. See

http://www.mbsplugins.net/archive/2014-03-12/SQLPreparedStatementMBS_improv/monkeybreadsoftware_blog_archive

I think it’s a great improvement over indexes.

7

That is nice.

8

[quote=230450:@Norman Palardy]https://www.sqlite.org/lang_expr.html#varparam
they should all work as far as I know
they’re part of sqlite itself[/quote]
But, the Xojo database plug-in provides no option to bind to named parameters. Binding appears to only be by ? and the order you bind the parameters. I would love to be proved wrong on this. I far prefer the named approach.

9

The classes that we are making public, OrmRecord, contain database adapters that will allow you to use named parameters with any database. While OrmRecord itself is still a work in progress and subject to change, the adapters are complete and can be used independently.

So for example, you could do something like this:

dim adapter as new OrmMySQLDbAdapter( mySqlDb )

sql = "SELECT * FROM my_table WHERE a = :a OR b = :b"
rs = adapter.SQLSelect( sql, "a" : "data1", "b" : 3.0 )

Or

sql = "SELECT * FROM my_table WHERE a = :a OR b = :b"

dim params() as pair
params.Append "a" : "data1"
params.Append "b" : 3.0

rs = adapter.SQLSelect( sql, params )

Or

sql = "SELECT * FROM my_table WHERE a = :a OR b = :b"

dim params as new Dictionary
params.Value( "a" ) = "data1"
params.Value( "b" ) = 3.0

rs = adapter.SQLSelect( sql, params )

Note, no binding or bind-type required. The adapter figures this out and handles this internally. You can also use ordered ("?") or indexed ("?1", “$1”) parameters, or “@” instead of “:” for named parameters.

If you’re interested, see the develop branch at:

https://github.com/advancedpricing/OrmRecord/tree/develop

Look at the “Database Adapters” folder within the project.

10

To show something that is readable:

dim adapter as new OrmMySQLDbAdapter(mySqlDb)

sql = "SELECT * FROM my_table WHERE a = :a OR b = :b"
rs = adapter.SQLSelect(sql, "a":"data1", "b":3.0)

Ha!

Edit: Sorry, inside joke about white space…