Prepared statement with multiple use of same variable

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?

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)

or this instead?

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

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.

the ? is basically positional

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

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.

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

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 )


Return returnString
End Function

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 ) )

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

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.