Prepared statement with multiple use of same variable

Databases
1

Using values or variables more than once in stored procedures using MySQL.

In Xojo I find myself binding variables multiple times because in Xojo the binding is numbered rather than named.
Consequently, when using the same variable in a query more than once I need to bind it more than once.
This is what I do today all the time.
Am I overlooking something? Is there, for example, an internal notation documented somewhere like @1, @2 that can be used creating prepare the SQL?

2

Don’t use BIND, just list them in the EXECUTE statement

stmt = SQLitePreparedStatement(db.Prepare("SELECT * FROM Customers WHERE FirstName like ? "))
stmt.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
stmt.Bind(0, TextField1.Text)
rs=stmt.SQLSelect

or this instead?

stmt = SQLitePreparedStatement(db.Prepare("SELECT * FROM Customers WHERE FirstName like ? "))
stmt.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
rs=stmt.SQLSelect(textfield1.text)
rs=stmt.SQLSelect("someothertext")
3

Thanks. But I apparently have been unclear.

I mean the ‘?’ being used several times. I know how to do it with an INSERT ON DUPLICATE KEY UPDATE.

INSERT INTO some_table (example) VALUE(?) ON DUPLICATE KEY UPDATE example = VALUES(example). In this, value naming is linked via the column name. In multi-level queries that is not possible.

Some times in multi-level queries the same ‘?’ needs to be used. But there is no link to a named variable. In other languages, you can declare a sql variable and then use it more than it once in the query.

4

the ? is basically positional

EDIT : you’re not overlooking anything
that just IS how it works in Xojo

5

I think he wants something like this:

db.Prepare("SELECT * FROM tableName WHERE id LIKE ? OR parentID LIKE ? ")

With the ability to just bind once instead of setting up a bind statement for each position since those replacement values are expected to be identical (in this case, if row’s id field matches the value, or is a child of the row whose id field matches the value).

You could write a method to parse the SQL statement using predefined key/value pairs to setup the bindings for you. Would save you a lot of work in the end if you’re doing this often.

6

Positional binding is all Xojo exposes despite some C APIs to db’s supporting other mechanisms like named values

7

Here’s an example to help you along. Not necessarily the best method, but it will get you started.

Add this method to a module:

[code]Public Function KeyPrepare(extends db as Database, SQLStatement as String, Values as Dictionary) as String
dim returnString as String = SQLStatement

dim keyString as String
dim valueString as String
for intCycle as Integer = 0 to Values.Count - 1
keyString = values.Key(intCycle).StringValue
valueString = values.Value(keyString).StringValue

returnString = returnString.ReplaceAll( "{" + keyString + "}", valueString )

next

Return returnString
End Function
[/code]

Then you can call it something like this:

dim dValues as new Dictionary( "id" : 1 ) rs = db.SQLSelect( db.KeyPrepare( "SELECT * FROM tableName WHERE id LIKE {id} OR parentID LIKE {id}", dValues ) )

8

Just be aware that there are issues you can run into with Anthony’s method (like doubling up quotes)

9

Yes, there are certain issues you may need to handle as you encounter them. Hence the “Not necessarily the best method.” Such as escaping quotes. I welcome improvements, but this isn’t meant to be production code, just an example to show how to get started.