In this Tutorial I will show you how to use precompiled prepared SQL queries. Precompiled queries bring some convenience to the programmer. They are faster in some situation and you can use wildcards in the queries. But first things first.
You will need Teamonkey's SQLite wrapper. Thanks to Assari who recompiled the SQLite wrapper for BlitzMax 1.18+. And you need the SQLiter v1.02+ module from here. The SQLite wrapper is contained in the archive, so you only need this one download. Except you are using pre BlitzMax 1.18. In this case you need the original SQLite wrapper from Teamonkey's webseite.
Furthermore I recommend to read the first SQLiter tuorial so you know about the basics like openening a database, doing a simple SQL query, and so on...
SuperStrict
Import pyropixel.sqliter
'Create new SQLite Object
Global db:TSQLiter = New TSQLiter
'Open Database File
db.Open("test.db")
'Prepare a SQL query
Global prep_sql:Int = db.PrepareQuery("SELECT * FROM test_table")
'Do a prepared query and recieve its data
While db.StepQuery(prep_sql) = SQLITE_ROW
Print db.P(prep_sql, "name") +"-"+ db.P(prep_sql, "age")
Wend
'Close the prepared query
db.FinalizeQuery(prep_sql)
'Close database
db.Close()
End
Listing 2.1
Let start to examine the following line:
Global prep_sql:Int = db.PrepareQuery("SELECT * FROM test_table") To precompile a SQL query we use the TSQLiter method PrepareQuery(). This method need one parameter:
the SQL query you want to precompile, in this case "SELECT * FROM test_table".
Furthermore this method returns an integer number, the handle of the precompiled query. From now on we will use this handle to operate with the prepared query. In the listing we store the handle in the variable prep_sql.
While db.StepQuery(prep_sql) = SQLITE_ROW
Print db.P(prep_sql, "name") +"-"+ db.P(prep_sql, "age")
Wend
When using a normal SQL query we used the method NextRecord() to iterate each row of data of the query result. When using a prepared query we do the same with the method called StepQuery(). Since we can prepare several queries, StepQuery() needs to know, which of the prepared queries we want to use. That is why you have to give the handle of a prepared query to the method as a parameter. In the example this would be: db.StepQuery(prep_sql)
Each time you call StepQuery() it will check if there is a row of data to read from. StepQuery() returns SQLITE_ROW (a SQLite constant) if there is a further row. If there is nor more row left, StepQuery() will return something else so that the loop above will be quitted. You can look into the docs of SQLiter to see what else can be returned by StepQuery(). But for basic usage, this should be enough.
Now how do we actually read the data of a column? As we use the method F() to read a column's Field of normal queries, we now use the method P() for Prepared queries, in the same way. A small difference is that P() needs one more parameter than F(). We first need the handle of the prepared query again and the the column's name to read the field from. In the listing we read the column called 'name' of the current row with db.P(prep_sql, "name")
If you don't need the prepared query anymore in the code, we should close the prepared query properly with the following line of code:
db.FinalizeQuery(prep_sql)
Thats it. The basic usage of prepared queries. Now let me explain one advantage of the prepared query: speed. A normal query has to be parsed by SQLiter everytime you do the query. A prepared query is parsed only one time to machine code which can be used by SQLite. If you only use one query, I think there will be no speed advantage (I even believe the normal query should be faster). But take a look at the demo5.bmx that comes with the SQLiter download archive. There I query the database 100000 times with a normal query and another 100000 times with a prepared query. On my machine the normal query took about 18 seconds. The prepared one took about 7 seconds.
Now let's get to the second advantage: the wildcards. Therefore we need a new listing.
SuperStrict
Import pyropixel.sqliter
'Create new SQLite Object
Global db:TSQLiter = New TSQLiter
'Open Database File
db.Open("test.db")
'Prepare a SQL query with two wildcards
Global prep_sql:Int = db.PrepareQuery("SELECT * FROM test_table WHERE loan>? AND age>?")
'Bind values to the wildcards
db.Bind(prep_sql, 1, "122")
db.Bind(prep_sql, 2, "16")
'Do a prepared query and recieve its data
Print "Query 1 ---------------------------------------------------"
While db.StepQuery(prep_sql) = SQLITE_ROW
Print db.P(prep_sql, "name") +"-"+ db.P(prep_sql, "age") +"-"+ db.P(prep_sql, "loan")
Wend
Print ""
'Reset the prepared query
db.ResetQuery(prep_sql)
'Bind values to the wildcards
db.Bind(prep_sql, 1, "1000")
db.Bind(prep_sql, 2, "20")
'Do a prepared query and recieve its data
Print "Query 2 ---------------------------------------------------"
While db.StepQuery(prep_sql) = SQLITE_ROW
Print db.P(prep_sql, "name") +"-"+ db.P(prep_sql, "age") +"-"+ db.P(prep_sql, "loan")
Wend
'Close the prepared query
db.FinalizeQuery(prep_sql)
'Close database
db.Close()
End
Listing 2.2
Let's start again with preparation of the query with the following line of code:
Global prep_sql:Int = db.PrepareQuery("SELECT * FROM test_table WHERE loan>? AND age>?") Have a look at the SQL query string. You see the two '?'. Maybe you know these wildcards from Linux/Mac/DOS console. You can use wildcards in Windows' file search as well. In this case the wildcards are placeholders. Let's see how we can insert something at the wildcards position.
db.Bind(prep_sql, 1, "1000")
db.Bind(prep_sql, 2, "20")
The Bind() method 'binds' content to the wildcards position in a certain prepared query. This method needs three parameters: the handle of the prepared query, the number of the wildcard and the string you want to place at the wildcard's position. In the example the bindings result in the following query:
"SELECT * FROM test_table WHERE loan>1000 AND age>20".
It's possible change wildcard bindings now and do another query. To bind new values to the wildcards of the prepared query you first have to reset the prepared query with the following line:
db.ResetQuery(prep_sql)
Now you can do new bindings to the wildcards of the prepared query like we did above. Have in mind that you cannot do any bindings or queries after closing the prepared query with FinalizeQuery().
Finally we have reached the end of the second SQLiter tutorial tutorial. I hope I explained everything detailed enough so that you can now work with prepared queries in SQLite. Feel free to send any questions, feedbacks or corrections about the tutorials.
With regards,
René