Garage

Executing queries

With Garage, the simplest way to execute a SQL query is by sending the message execute: to a connection object. The message execute: accepts any kind of statements, from SELECT and INSERT queries to CREATE TABLE or CREATE FOREIGN KEY statements.

connection execute: 'CREATE TABLE CATS (
	name varchar(50),
	owner varchar(50))'.

connection execute: 'SELECT * FROM CATS'.

Using the execute: message, the driver will immediately sent the query to the database and interpret the results. If there is a syntax error in the query, the operation will fail with an exception indicating where such error was produced.

1. Statement results

Executing a statements such as INSERT, UPDATE or CREATE TABLE does not return a set of rows as a result. Instead they notify the number of affected rows in the database. We can access the number of affected rows of a result by sending it the rowsAffected message. However, the value returned by rowsAffected will vary between different database servers. For example, generally database servers return the number of rows that result from the WHERE clause. However, MySQL only counts the rows whose values have effectively changed. To know if a driver returns the number of really changed rows as affected rows, they understand the affectsOnlyChangedRows message.

mysqlConnection affectsOnlyChangedRows. "=> true"
pgsqlConnection affectsOnlyChangedRows. "=> false"

In general, statements will return the following as affected rows:

Data Definition Language (DDL) statements: DDL statements such as CREATE TABLE affect no rows. rowsAffected returns zero.

result := connection execute: 'CREATE TABLE CATS (
	name varchar(50),
	owner varchar(50))'.

self assert: result affectedRows = 0.

INSERT statement: An INSERT statement insert one row and thus, rowsAffected returns one.

result := connection execute: 'INSERT INTO CATS(name, owner) VALUES (''lutz'', ''julien'')'.
self assert: result affectedRows = 1.

result := connection execute: 'INSERT INTO CATS(name, owner) VALUES (''inuk'', ''victor'')'.
self assert: result affectedRows = 1.

DELETE and UPDATE statement: DELETE and UPDATE statements affect the rows that correspond to the WHERE clause, or all rows in the table if no WHERE clause is written. rowsAffected returns the amount of rows selected by the WHERE clause.

result := connection execute: 'UPDATE FROM CATS SET owner=''guille'''.
self assert: result affectedRows = 2.

result := connection execute: 'DELETE FROM CATS WHERE name=''lutz'''.
self assert: result affectedRows = 1.

Sql queries with arguments: The #execute: message receives as argument a single string. It does not accept SQL with parameters (e.g., specified with the question mark '?'). The user of garage should then use prepared statements when supported by the database driver, or string concatenation to build his queries. Regarding the latter, the #format: message of String may come in handy. Be careful also that manually concatenating strings may become a target for SQL injection.

2. Iterating Rows

Differently from the statements we have already seen, SELECT statements return a set of rows as a result, namely a result set. A Garage result set behaves almost as an sequenceable collection of rows. It provides, for example, the usual Pharo's iteration methods such as do:, collect: and select:.

INFO: Notice that a normal SELECT statement executed through the execute: message brings all results at once. In a next article we will discuss the optimization of row retrieval.

result := connection execute: 'SELECT name AS catname, owner FROM CATS'.
result do: [ :row | ... ].
result select: [ :row | ... ].
result collect: [ :row | ... ].

Other helper methods allows us to access particular elements inside the result set. first returns the first row of the result set. at: returns the row at a particular index. In case there is no row in the demanded index, the message will fail with an exception. isEmpty tells if the result did not bring any rows from the database.

result first.
result at: 1.

A result set can also be iterated as a cursor asking it for a readStream. Stream access to a result set is useful when we do not want to iterate the whole collection at once but instead defer the iteration in time. Using stream access the method next returns the element that follows in the stream while next: returns a collection with the following N rows where N is the number used as argument. next: will return at least the required amount of rows, except in the case where there are no more rows. When there are no more rows in the stream, next returns nil and next: returns an empty array.

result := connection execute: 'SELECT name AS catname, owner FROM CATS'.
stream := result readStream.

(stream next: 10) do: [ :row | ... ].

[ next := stream next. next isNil ] whileFalse: [
   ...
]

A result set does not have a size method. This is made on purpose because we do not really know the size of the result in advance if the driver is accessing a result in a cursored way.

TODO: This is maybe implementable.

3. Accessing Row Values

The data in rows is organized in columns. We can access the data inside a row either by the name of the column or the index of the column using respectively the atName: and atIndex: messages. The column index and names are those specified in the query and not in the original table we are querying. For example, a query accessing only three columns from a table with four columns will only bring the three columns we asked for. The order in which those columns are retrieved is the order in which we asked for or the original order in the table if we didn't specify any. Also, if we put an alias into a column's name, we will only be able to access the column by the alias and not by it's original name.

result := connection execute: 'SELECT name AS catname, owner FROM CATS'.
catRow := result rows first.

self assert: (catRow atName: 'catname') equals: 'lutz'.
self assert: (catRow atName: 'owner') equals: 'julien'.

self assert: (catRow atIndex: 1) equals: 'lutz'.
self assert: (catRow atIndex: 2) equals: 'julien'.

The results will be automatically transformed to Pharo objects by the driver. That is, if a column is of type INTEGER, the driver will transform it to a corresponding Pharo Integer. The same happens with other database types such as dates or times. However, these conversions depend on the particular databases and its driver implementation.

result := connection execute: 'SELECT COUNT(*) as count FROM CATS'.
row := result rows first.

self assert: (catRow atName: 'count') equals: 1.

"TODO:" make a conversion table between types.