Database Interface

1. Database Interface Overview

The Database Interface allows you to connect to a local or remote database, execute SQL statements, and work with SQL query results. You can connect to the following databases, or any ODBC-compliant database in general:

Even though the ODBC interface works with any ODBC-compliant database, be aware that SQL syntax and capabilities vary across databases and you have to manage these differences yourself. The SQLite proprietary API has been wrapped with ODBC-like Smalltalk classes to make it easier to change between SQLite and ODBC back-ends.

2. Connecting to a Database

2.1. Connecting to an SQLite Database

To connect to an SQLite database you need only specify the path to the database file. If the file does not exist, it will be created.

connection := SqliteConnection new.
connection connect: Directory desktop path, 'test.sqlite'.

2.2. Connecting to a Database using ODBC

To connect to an ODBC-compliant database you need to specify a connection string containing several fields. At minimum, you must have either a Driver field or Data Source Name field. Any additional fields are specific to the database you are connecting to, but typically these are a host, database name, user name, and password.

Here is an example of how to connect to a MySQL database using a Driver field:

connection := SqlConnection new.
connection connect:
    'driver=libmyodbc3.dylib;',
    'host=localhost;',
    'database=test;',
    'user=root;',
    'password=;',
    'stmt=set names utf8;'.

Here is an example of how to connect to a PostgreSQL database using a similar Driver field:

connection := SqlConnection new.
connection connect:
    'driver=libpsqlodbc.dylib;',
    'host=localhost;',
    'database=template1;',
    'uid=postgres;',
    'pwd=;'.

Data Source Names are created and managed by the ODBC Administrator application that comes with Mac OS X. Your connection string can add to and overwrite any fields specified by the DSN. To connect via a DSN, you would write something like this:

connection := SqlConnection new.
connection connect: 
    'dsn=my data;',
    'uid=fred;',
    'pwd=secret'.

To list all available data sources, evaluate the following:

SqlEnvironment default dataSources.

3. Disconnecting from a Database

Once you are finished using a database, either SQLite or ODBC-compliant, you can disconnect:

connection disconnect.

4. Working with a Database

4.1. Executing SQL Statements

To execute SQL statements, first obtain a statement object from the database connection and then execute the SQL string directly. It is possible to reuse a statement object for other queries:

statement := connection newStatement.
statement execute: 'create table Person(firstName text, lastName text)'.
statement execute: 'drop table Person'.

To execute parameterized SQL statements, first prepare the statement and then execute it with the appropriate parameter values:

statement := connection newStatement.
statement prepare: 'insert into Person values(?, ?)'.
statement executeWithParameters: #('Harry' 'Potter').
statement executeWithParameters: #('Frodo' 'Baggins').

4.2. Working with Result Sets

Some SQL statements produce a result set. The result set is a collection of rows where each row is an array of column values. To get the entire result set at once use the #allRows or #allRowsWithColumnNames method.

statement := connection newStatement.
statement execute: 'select * from Person'.
statement allRowsWithColumnNames.

You can also iterate over the result set one row at a time by using the #fetchNextRow method. This method will return true after repositioning the result set on the next row. If there are no more rows left in the result set then #fetchNextRow will return false. Note that you must send the #fetchNextRow message once to get the first row of the result set.

Here is an example that prints a result set to the Transcript one row at a time:

statement := connection newStatement.
statement execute: 'select * from Person'.
[statement fetchNextRow] whileTrue: [
    1 to: statement numberOfColumns do: [:columnIndex |
        Transcript
            nextPutAll: (statement columnNamesAt: columnIndex);
            nextPut: $=;
            print: (statement columnValuesAt: columnIndex);
            nextPutAll: '; '].
    Transcript cr].

For each row, you can query the column names and values using the methods #currentRow, #columnValuesAt:, #columnNames, #columnNamesAt:, and #numberOfColumns.

5. Mapping Types between Smalltalk and Databases

The Database interface automatically maps result values to Smalltalk types, but it is up to you to supply the appropriate, database-specific, values and types for parameters. If you specify these incorrectly, the database may coerce the parameter values or may reject them altogether.

Five basic data types can be used as parameters and result values:

  • integer

    Smalltalk represents integers as instances of Integer subclasses, SmallInteger or LargeInteger, depending on the integer size. When passing integers to the database, be careful to match the sign, and size restrictions of the database-specific integer type.

  • float

    Smalltalk represents floats as instances of Float. Both single-precision and double-precision floats are represented as IEEE 64-bit double-precision values, which matches the limits of the ODBC and SQLite APIs.

    When reading single-precision floats from the database, be careful comparing them with existing Smalltalk float values. To test for equality, for example, you should reduce the double-precision float to single-precision, so values that differ only by the insignificant bits of precision will still be equal. To reduce the precision, use the #roundToSinglePrecision or #truncateToSinglePrecision methods.

  • text

    Smalltalk represents text as instances of String subclasses, String8, String16 or String24, depending on the characters used. When passing strings to the database, be careful to satisfy the character set and length restrictions of the database-specific text type.

  • blob

    Smalltalk represents blobs as instances of ByteArray. When passing byte arrays to the database, be careful to satisfy the length restrictions of the database-specific blob type.

  • null

    Smalltalk represents null as nil, the sole instance of UndefinedObject. A null value may be used in place of any other data type, where allowed by the database.

Each database has its own data types, most of which can be classified as one of the five basic data types listed above. Any other database-specific types, such as dates and points, are converted to and from textual format.

Last modified May 22, 2006.