Prepared Statement - Ok to Set Bind Type Outside of Loop?

Is it ok to set the bind type outside of a loop? I don’t see many examples like this.

[code]Dim ps As SQLitePreparedStatement

ps.BindType(0, SQLitePreparedStatement.SQLITE_INT64)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)

For Each location in locations
sql = “INSERT INTO MyTable( field_1, field_2 ) Values (?,?)”
ps = App.db.Prepare( sql )

ps.Bind(0, id )
ps.Bind(1, location )

If App.db.Error Then Exit

I don’t see why not. You might even be able to move the Prepare out of the loop too.

As with all things, a little bit of testing will prove it one way or the other.

I’d be very surprised if you could set the bind type before preparing. ps is Nil until it’s created by the prepare.

True. Didn’t think of that. But if you put the prepare outside of the loop it should work. Right?

I guess we should ask the OP, why do you want to do this? Setting the BindType shouldn’t be a huge time waste.

Yes you are correct I’ve done it before. Prepare & set bind types before l just binding data in the loop. With some engines this adds to efficiency due to retaining the execution plan.

Some engines require you to recreate it each time
So it will vary from engine to engine

SQLite recommends preparing once to, as Wayne said, retain the execution plan. It will be reused. So prepare, then bind types, then execute in the loop.

You can also skip the bind entirely, and use:

ps.SQLExecute(id, location)

There is also the little known syntax where you bind the type and value at the same time, though it would not be appropriate here:

ps.Bind(0, id, SQLitePreparedStatement.SQLITE_INT64

[quote=167003:@Bob Keeney]
I guess we should ask the OP, why do you want to do this? Setting the BindType shouldn’t be a huge time waste.[/quote]

I thought saving even a little execution time would be good.

Only if you are experiencing a slow down in the code you have is it really worth it. Premature optimization is a common problem. Most people try to optimize too early in the lifespan of a project.