3.3 Database connectivity

GNU Smalltalk includes support for connecting to databases. Currently this support is limited to retrieving result sets from SQL selection queries and executing SQL data manipulation queries; in the future however a full object model will be available that hides the usage of SQL.

Classes that are independent of the database management system that is in use reside in package DBI, while the drivers proper reside in separate packages which have DBI as a prerequisite; currently, drivers are supplied for MySQL and PostgreSQL, in packages DBD-MySQL and DBD-PostgreSQL respectively.

Using the library is fairly simple. To execute a query you need to create a connection to the database, create a statement on the connection, and execute your query. For example, let’s say I want to connect to the test database on the localhost. My user name is doe and my password is mypass.

| connection statement result |

connection := DBI.Connection
    connect: 'dbi:MySQL:dbname=test;hostname=localhost'
    user: 'doe'
    password: 'mypass').

You can see that the DBMS-specific classes live in a sub-namespace of DBI, while DBMS-independent classes live in DBI.

Here is how I execute a query.

statement := connection execute: 'insert into aTable (aField) values (123)'.

The result that is returned is a ResultSet. For write queries the object returns the number of rows affected. For read queries (such as selection queries) the result set supports standard stream protocol (next, atEnd to read rows off the result stream) and can also supply collection of column information. These are instances of ColumnInfo) and describe the type, size, and other characteristics of the returned column.

A common usage of a ResultSet would be:

| resultSet values |
[resultSet atEnd] whileFalse: [values add: (resultSet next at: 'columnName') ].