I like SQLite. SQLite is small, fast and reliable. There is an SQLite FFI wrapper on Squeaksource which is very simple to use. Let's start with this database:

$ sqlite3 /tmp/test.db
SQLite version 3.7.6
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a (k integer primary key, tv text, iv integer);
sqlite> insert into a values (1, 'wah wah wah', 7);
sqlite> insert into a values (2, 'quick brown fox', 42);

Run the following snippet in a workspace:

| db |
Transcript clear.
db := SQLiteConnection fileNamed: '/tmp/test.db'.
db withResultForQuery: 'select * from a;' do: [ :result |
    result do: [ :row | Transcript show: row asString; cr ]].
db close.

The output is shown in the Transcript thus:

a Dictionary('iv'->'7' 'k'->'1' 'tv'->'wah wah wah' )
a Dictionary('iv'->'42' 'k'->'2' 'tv'->'quick browm fox' )

The underlying SQLite API used by withResultForQuery:do: is sqlite3_get_table, which is "a legacy interface preserved for backwards compatibility". Using this API, at the C level, NULL values are NULL pointers, and all other values are in their UTF-8 zero-terminated string representation. Not very interesting at the Smalltalk level, either, as seen from the Transcript.

SQLite also supports "prepared statements", aka "parameter binding". A prepared statement is compiled once (using sqlite3_prepare, into an SQLite VDBE virtual machine program), then executed multiple times (using sqlite3_step). Parameters are bound using sqlite3_bind_XXX calls, and values are retrieved using sqlite3_column_XXX calls. Parameter binding defends against SQL injection attacks.

I'm extending the SQLite FFI wrapper to support some of the parameter binding APIs. (Well, until enough to do my stuff.) Here's an example code snippet:

| db stmt |
Transcript clear.
db := SQLiteConnection fileNamed: '/tmp/test.db'.
db open.
[ stmt := db prepare: 'select * from a;'.
  stmt isNil ifFalse: [  
      | value |
      stmt execute: [ :row |
          value := row stringAt: 1.
          Transcript show: value, ' (', value class asString, ')'; cr.
          value := row integerAt: 2.
          Transcript show: value asString, ' (', value class asString, ')'; cr ].
      stmt finalize ]]
ensure: [ db close ]

The Transcript displays the following:

wah wah wah (ByteString)
7 (SmallInteger)
the quick brown fox (ByteString)
42 (SmallInteger)

Tags: SQLite