Prepared Statement BindType

SQLite Database:
I see in an old example of Prepared Statements (LR 2012) that the BindType is inside the loop that is inserting a record. Is this necessary or can it be before the loop?

Did you try?
I think it works if you use BindType once before the loop.

Not there yet :slight_smile: probably 2 days away. I just have a single Prepared Statement that will run multiple times throughout the project so I want to make it a property in a module and Prepare it when the software starts. Just wondering if I can put the bindType in the App Open event or I need to put it throughout the software. If nobody answers before I get to that point, I’ll find 5 mins to test it :slight_smile:

In my testing there was no significant different between creating new Prepared Statements or reusing them with SQLite. I believe internally the plugin is caching them.

I have not done enough testing with other databases to know if there is any significant different with them or not. I think I remember that one of the database servers they actually recommended you do a new Prepared Statement every time you use one. But, I might not remember correctly.

The BindType just sets an integer in some table.
(at least the MySQL and my plugin do so)

[quote=263932:@Bob Keeney]In my testing there was no significant different between creating new Prepared Statements or reusing them with SQLite. I believe internally the plugin is caching them.

I have not done enough testing with other databases to know if there is any significant different with them or not. I think I remember that one of the database servers they actually recommended you do a new Prepared Statement every time you use one. But, I might not remember correctly.[/quote]

I’d just rather code it once not 20 times. But the question is still do I have to code the bindtype before every Insert or is one time enough for all the inserts?

Well, if you do your code right, you’re only doing it once. If you find yourself doing anything in Xojo 20 times you might want to rethink how you’re doing it.

We use ActiveRecord which is using PreparedStatements behind the scenes but we never see it. And ActiveRecord does the BindTypes every single time we do an insert/update/delete. As I said, I believe there is some caching done by the plugin/database itself.

With ActiveRecord (or other data storage class), the advantage is that DB code tends to all be in one place rather than spread throughout the project and particularly in the UI. This ends up being a huge time saver in the long run because as things change (as they tend to do in large projects) you only have to go to one spot and that changes it for everything.

Example: In an accounting application it’s pretty common to get an account list. I worked on one application where that same bit of code was in 50+ places - the exact same code copy and pasted and slightly tweaked in each one. It took us forever to track down all the places it needed to change. If the original developer had just made one global method that brought back the account list all we would need is a single code change that affected all 50+ locations. So perhaps a global method makes more sense for whatever you’re doing.

[quote=263917:@Christian Schmitz]Did you try?
I think it works if you use BindType once before the loop.[/quote]

So with a quick test:
Module properties ps & db
Module Method:
ps = db.Prepare(“INSERT INTO myTable(c1, c2, c3) VALUES (?, ?, ?)”)
ps.BindType(0, REALSQLPreparedStatement.SQLITE_BOOLEAN)
ps.BindType(1, REALSQLPreparedStatement.SQLITE_DOUBLE)
ps.BindType(2, REALSQLPreparedStatement.SQLITE_TEXT)

Pushbutton:
db.SQLExecute(“BEGIN”)
ps.SQLExecute(True, 12.5, “Hello World”)
If db.Error then MsgBox db.ErrorMessage
db.SQLExecute(“END”)

Hit the pushbutton 20 times and no error and 20 records put into the table, so the bindtype doesn’t need to be done before each insert

Some DB’s will require you to prepare each time
So you may have to rebind each time

[quote=264013:@Norman Palardy]Some will require you to prepare each time
So you may have to rebind each time[/quote]
Norman, what do you mean by “Some” ? Some datatypes, Some Databases … ? thx

edited - some DB’s will require you to prepare each time
Its just how their prepared statements work
Some dont so you can gain a bit of speed by preparing once then reexecuting over & over with different bound values