Squeak
  links to this page:    
View this PageEdit this PageUploads to this PageHistory of this PageTop of the SwikiRecent ChangesSearch the SwikiHelp Guide
SqueakDBX - Execute any SQL query
Last updated at 1:29 pm UTC on 2 July 2009

Sending queries

Sending queries to database is simple. The only thing you need is the execute message in DBXConnection. Here are some examples:

    | conn result result2 |

    
conn execute: 'insert into materia(codigo, nombre, observaciones) VALUES (59, ''TADP'', ''Tecnicas Av'') '.

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

    
result2 := conn execute: 'select nombre from materia'.

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

    
conn execute:    'CREATE TABLE curso(

        id_curso INTEGER,

        id_alumno INTEGER,

        codigo_materia INTEGER,

        Fecha_inicio DATE,

        CONSTRAINT pk_cursos_id_curso PRIMARY KEY (id_alumno, codigo_materia)

    )'
.

There are some important things:

DBXQuerySettings

This class represents the settings of a particular query. Just for now, this object, has two settings:

DBXQueryTimeout

This settings can have 2 values: nil or a positive number (seconds and microseconds). If you set a nil this means that when you send a query, openDBX will wait until a result arrives. As FFI blocks the VM when you call an external function (see SqueakDBX - Architecture and desing (External call implementation)), your VM will be blocked until that moment. Otherwise, it can contain any number of seconds and microseconds to wait for. If the server doesn't respond within the timeout, the query isn't canceled! Instead, the next call to this function will wait for the same result. Waiting the specified time may be implemented in the backends if it is possible, but there is no guarantee. If not, it will return not before a responds arrives.
Because of this, it is generally recommended to set a positive number of seconds and microseconds. If you do this, openDBX will wait the results for that time and if it is no completed, It will return the control anyway. This let us, if you have forks, process another query. SqueakDBX loops until openDBX can get the result. So, for the user of the driver, this is transparent. You only have to set the timeout value. This is a very interesting setting when building Web applications.
Unfortunately this timeout doesn't work in ALL backends but in mssql, pgsql (Unix only), sqlite and sqlite3 support timeouts.
You can see DBXQuerySettings defaultTimeout to get the default timeout.

Examples:

DBXQuerySettings with the default pageSize and a timeout of 3 seconds and zero microseconds.
aTimeSpec := DBXQueryTimeout seconds: 3 microseconds: 0.
aQuerySettings := DBXQuerySettings timeout: aTimeSpec.
conn execute: 'select * from alumno' querySettings: aQuerySettings.

DBXQuerySettings with the a pageSize of 100 and a nil as timeout
aQuerySettings := DBXQuerySettings pageSize: 100 timeout: nil.
conn execute: 'select * from alumno' querySettings: aQuerySettings.

This:
conn execute: 'select * from alumno' querySettings: DBXQuerySettings default.

is the same as:
conn execute: 'select * from alumno'

See DBXQueryTimeoutTest for tests about this.

Paged results

After executing a query, we will then want to retrieve the results. But it may be possible to retrieve all rows at once, one by one or more than one row at once. All positive values including zero are allowed as values for a page size. Why we said "it may be possible" in last paragraph? Because this is a special option that not all the engines support. You must enable this feature. If you don't enable this option, no matter the page size you tell and no matter that the backend supports this feature, the page size parameter might be used or not, depending on the backend. Most of the time it's silently ignored. See SqueakDBX - Special options . If paging (more than one row at once) is not supported by the backend, it will use "one by one" or "all at once" if this is the only option provided.

Examples:

Use default timeout and 100 as page size if possible.
        conn := self doConnect.
	result := conn enablePagedResults.
	conn open.
	result
		ifTrue: [ querySettings := DBXQuerySettings pageSize: 100.
				 resultQuery := aConnection execute: 'select * from alumno' querySettings: querySettings.
		] ifFalse: [
				 resultQuery := aConnection execute: 'select * from alumno'.
		]	
	


Use a timeout of 1 seconds and a page size of 1000.
        conn := self doConnect.
	result := conn enablePagedResults.
	conn open.
	result
		ifTrue: [ querySettings := DBXQuerySettings pageSize: 100 timeout: (DBXQueryTimeout seconds: 1 microseconds: 0).
				 resultQuery := aConnection execute: 'select * from alumno' querySettings: querySettings.
		] ifFalse: [
				 resultQuery := aConnection execute: 'select * from alumno'.
		]	


Now you see all these examples, you may noticed that execute: aQuery is the default case: the default timeout and the default pageSize (no paged results).

MultiStatement queries

To be able to execute multiStatements you must first enable this option (if the backend you want to use supports it) as explained here: SqueakDBX - Special options
Then, you could execute multistatements queries in this way:
connection executeMultiStatement: 'INSERT INTO materia(codigo, nombre, observaciones, id_alumno) VALUES (11, ''Something'', ''Nothing'', 2);	select := SELECT * FROM alumno'.

See SqueakDBX - Retrieving and processing results to know how to process the results.

Execute DDL script

Its very common you need to execute a complete DDL script: create, drop or alter tables. In these cases, you don't have any interesting results from each query. So, we have executeDDLScript: aDDLScript that do this work for you. Remember SqueakDBX doesn't do any translation so your statement delimiter must be understood by the backend. In order to know which delimiter we use, you can see the message queryDelimiter of the current platform backend, for example DBXPostgresPlatform.
This message doesn't use the multistatements option of openDBX, it is all done by SqueakDBX so you don't have to care about it.
Here is an example:

    
conn executeDDLScript: 'DROP TABLE curso;

        DROP TABLE materia;

        DROP TABLE alumno'
.

        

Scaping/SQL injection


Unit Tests

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