SqueakDBX - Retrieving and processing results


	links to this page:

Last updated at 8:23 pm UTC on 26 September 2009

Retrieving results

As we explain in SqueakDBX - Execute any SQL query the method execute of DBXConnection returns a DBXResultSet if it was a select statement and a DBXResult in case the query was an update, delete, insert, create, drop or any other DML query. Or, in case of a multistatement query, it will return a DBXMultiStatementResultSetIterator.

DBXResult: You can ask to it the number of affected rows. This value depends on the type of query:

DDL query: It will always be zero.
Insert query: It will always be one.
Delete and update: Returns the number of rows that have been changed by the current statement. However, their concrete number depends on the database implementation. Instead returning the number of rows which are matched by the WHERE clause, MySQL for example does only count the rows whose values have really been changed.

Examples:

    | conn result |

    result := conn execute: 'delete from materia where nombre = ''TADP'''.

    Transcript show: 'They were affected ' , result rowsAffected asString, ' rows';.

    result := conn execute: 'update materia set observaciones = ''something'' where codigo = 60'.

    Transcript show: 'They were affected ' , result rowsAffected asString, ' rows';.

DBXResultSet: this class has several useful methods:

obtain the number of selected columns (columnCount) and column descriptions (columnDescriptions) by different ways
rows (it return all the rows of a resultSet) y rowsDo (executes a block over all rows).
allRowsDO: it is different from rowsDo: because allRowsDo, first obtain (query) all the rows and then iterates them and evaluates the block. On the other hand, rowsDo: goes row by row executing the block.
nextRow: it returns the next row of the resultSet. If there is no more row, it returns nil.
some other useful methods you can see in the code.

Here are some examples:

    | conn result aRow columnDescription columnCount|

    result := conn execute: 'SELECT nombre FROM materia'.

    columnDescription := result columnDescriptionAt: 1.

    columnDescription := result columnDescriptionWithName: 'nombre'.

    Transcript show: columnDescription dbxType, columnDescription name, columnDescription size, columnDescription type.

    columnCount := result columnCount.

    aRow := result nextRow.

DBXMultiStatementResultSetIterator: This is the return object when the query is multistatement.

The main method here is "next" where you can get the next result. This result, can be a DBXResult or a DBXResultSet, depending of the type of query. You can call next till nil is returned.
There is also a allResultsDo: aBlock that evaluates the block for each result.

Examples:

insert := 'INSERT INTO materia(codigo, nombre, observaciones, id_alumno) VALUES (11, ''Something'', ''Nothing'', 2)'.
select := 'SELECT * FROM alumno'.
iterator := conn executeMultiStatement: (select, '; ', insert).

"the first result must be the one of the select"
sqlResult := iterator next.
self assert: sqlResult class = DBXResultSet.
self shouldnt: [ sqlResult nextRow ] raise: DBXError.
[ aRow := sqlResult nextRow ] doWhileTrue: [ aRow notNil ].
self assert: aRow isNil.

"the second result must be the one of the select"	
dmlResult := iterator next.
self assert: dmlResult class = DBXResult.
self assert: dmlResult rowsAffected = 1.

"There are no more results, so it must be nil"
self assert: iterator next isNil.

VERY IMPORTANT THINGS:

After doing a select query you should call nextRow till it returns you nil. If you don't do this, you will get a segmentation fault in your next query. You have to do this no matter which number of rows you have got. Suppose you are looking for some record and you find it in the second one. The number of rows is 10. In this case, you should call nextRow 10 times, no matter you found the record in the second one. This limitation is in the database client library. We assume that someone has to do this (retrieve all results before execute another query): openDBX, database client library, SqueakDBX or SqueakDBX user. So, we decided SqueakDBX should do this. The other approach was the SqueakDBX user should do it and if it doesn't do it, SqueakDBX throws an error. In conclusion, you DON'T need to call nextRow till nil its returned, the framework will do it for you in your next query.
Somes queries (for example calling stored procedures or functions) may return more than one resultSet. In this case you MUST use executeMultiStatement:

Processing results

As you read above, using nextRow or rowsDo you are able to obtain and do something with a row. You have retrieve the results, so now you are able to process it. In order to do this, you may use some of the following classes:

DBXRow: Represents a row of a resultset. It has these useful messages:

valueAt: anIndex and valueNamed: aString. They return the value at that position or name. This values are automatically converted from String (openDBX returns String always) to the specific Squeak type. See SqueakDBX - Mapings from String to specific squeak types in selects for more details.
values: Returns an Array with all the returned values.

DBXColumnDescription: Represents a description of a column. It has these useful messages:

Obtain name, type, size and dbxType.

Unit Tests

For more examples, information and usage, you can see the tests we have related with this. These tests are: DBXQueryTest, DBXDescriptionTest, DBXMultiStatementTest and DBXRowTest.