Using a parameter more than once in an SQL statement

Databases
1

Suppose I have:

[code]Var absid as Integer, mystr, sql as String, reg as RowSet, dbh as SQLiteDatabase

absid = 25
mystr = “some text”
[/code]

and without using a prepared statement I might do this:

sql = "select * from mytable where id1=" + absid.ToString + " and dbstr='" + mystr + "' and id=" + absid.ToString reg = db.SelectSQL (sql)

Note the use of absid in two places. Now, in recasting this to use the prepared statement facility built into the API2 database methods, can I do the following:

sql = "select * from mytable where id1=?1 and dbstr=?2 and id=?1" reg = db.SelectSQL (sql, absid, mystr)

That is, only pass a variable in once, but refer to it more than once in the sql.

2

seems to work and i got a result with sqlite.
SelectSQL

3

I assume this is with PostgreSQL? I suppose it works because the PostgreSQL driver uses numbered parameters, whereas the others don’t so you’d have to repeat the parameters in those.

4

No, SQLite as it says in my OP (although I see there’s a type in the selects in that I use db rather than dbh).

SQLite certainly allows numbered parameters.

5

Sorry, missed that as well as Markus’ response.

Interesting. This is not indicated in the docs. I wonder if it’s a “bug” that may change in the future?

6

it is mentioned in the online doku but not if it works as we think / expect.

7

[quote=489437:@Jay Madren]Sorry, missed that as well as Markus’ response.

Interesting. This is not indicated in the docs. I wonder if it’s a “bug” that may change in the future?[/quote]
No. I checked on the SQLite User Forum and Richard Hipp himself (author & lead developer of SQLite) said it was intended behaviour. So, not only can one do it but we won’t find that the ability suddenly vanishes.