Use of prepared statements versus InsertRecord & Update Methods

Databases
#1

Hello everyone,

In using databases, what is the difference, and what do you suggest between using prepared statements and the db.InsertRecord / rs.Update methods?
The request concerns SQLite, PostgreSQL, MSSQLServer and ODBC databases.

The reason for my question is that I am preparing some generic classes for managing DBs and I am not really certain how to evaluate the different options.
I suppose that the general suggestion to use prepared statement has to do with querying the database (SQLSelect) and not so much with inserting / updating records?

#2

That’s more of a matter of preference. Prepared statements let you use any SQL statement and it also prevents SQL injection attacks. InsertRecord and the like are mainly utility methods when using RecordSets/DatabaseRecord classes.

So depending on how you like to manipulate your data will determine the easiest/best mechanism.

#3

Thank you Philip, what you are saying is that there is no special reason why one should use one or the other? Right?

#4

Essentially. I mean say you have your own custom classes representing some data. It might be more straight forward to use prepared statements and insert the data that way. If you were to use the DatabaseRecord class you would have to fill an instance of that and pass it to the database.

You could in theory use DatabaseRecord to store information that you don’t intend to use in another object. Then store it directly in the database, in that scenario it makes sense to stick with DatabaseRecord. Hope that helps.

#5 
I suppose that the general suggestion to use prepared statement has to do with querying the database (SQLSelect) and not so much with inserting / updating records?

Yes. Using prepared statements basically has 2 advantages.

  1. It properly escapes the values (so you don’t have to worry about doubling your quotes and the likes)
  2. The database query planner gets a chance to get to know your query before you execute it, and can evaluate the best query plan before you actually execute the query. Obviously this only helps if you prepare the query only once, so you might want to have a dictionary store all of your already prepared statements, and reuse those.

But both of these advantages are negligible when it comes to inserting or updating.

#6

[quote=43804:@Maximilian Tyrtania]Obviously this only helps if you prepare the query only once, so you might want to have a dictionary store all of your already prepared statements, and reuse those.
[/quote]

I put the execution of prepared statements in methods with parameters in a DB module. In the methods I put the prepared statements themselves into static variables so they are created once. That approach encapsulates everything nicely, and allow autocomplete to help with coding.

#7

I did some testing on this just before last years developers conference. In my testing, I found negligible difference in keeping PreparedStatements around vs creating them new each time. I know, it’s counterintuitive but I tested this with SQLite, MySQL, PostgreSQL and even MS SQL Server.

I know I asked the Xojo engineers at the time but I don’t remember their exact answer. It might be that the plugins or the databases themselves are caching the PreparedStatements so if it’s seen that statement before it simply looks it up rather than recompiling it.

#8

Thank you Phillip, Maximilian, Karen and Bob for your input.

I think the best approach for me is to use prepared statements for the Selects and the methods for insert and edit.

#9

That’s what we do in our projects. Though we tend to use ActiveRecord which abstracts most of that out (insert, update, and generalized selects go though prepared statements and the db methods). AR has some advantages and some disadvantages depending upon your requirements. More info on ActiveRecord at http://www.bkeeney.com/rbinto/activerecord/

#10

Yes Bob, I have downloaded ActiveRecord and also purchased ARGen quite some time ago. However, we did have our own framework, which we are updating / rewriting now, and it fits our needs better…

#11

Execution plans are usually cached by SQL servers regardless of whether they’re created with Prepared Statements or not. The benefits of this will vary depending on the resources available to the SQL server and the load placed on it. SQLite is an exception because it’s not designed as a server product.

That said, the use of Prepared Statements is more about escaping data and minimizing SQL injection attacks than improving performance.

#12