When getting started on the SQLite C API they recommend 2 basic ways:
sqlite3_get_table() both are convenience wrappers around the core API Calls. They give good examples for the
sqlite3_exec() call, and the
sqlite3_get_table() is deprecated in the documentation. Using
sqlite3_exec() employs a callback function which gives the advantage of speed and low memory usage - due to being able to process each row at a time. It however, does not allow for knowing when all the rows are returned, as well as, being synchronous.
Below is a simple example using the core API functions in SQLite where we synchronously read all rows from a database into memory row-by-row. It uses a table called
people with two text columns
Let’s start with the actual database schema:
We can create a database with:
Create a quick seed script to populate our table:
Then go ahead and seed the database:
A example written in C is below. It takes the following steps:
1. Open the database 2. Prepare the SQL Command creating a prepared statement 3. Bind the parameters in the prepared statement 4. Step through the result set 5. Close the prepared statement 6. Close the database
It might look like a lot, but logically this is what is required. Working in other databases and other languages you will see the same procedure. Some you’ll actually see an extra step to execute the statement. Here, we can step through the results without executing the prepared statement. We have to be careful about errors on every step to both finalize our prepared statement and close our database to not cause memory leaks.
Before we build the c source we need to talk about getting including SQLite into the system. I take the recommended approach of downloading the source and compiling it onto my system. This is the recommended approach on the website. Once all said and done we will have two files
Makefile is as follows:
Building and running the example:
That’s it! Nice and easy. To extend this would be easy. Make more complex queries are parameterize them as you like with ?1, ?2 or even use named parameters. See the SQLite Docs. When binding be sure to use the correct binding type: text, int, blob … Then be sure when extracting the columns choose the right type and index.