3.2Class Handle

DBI connection handle returned by connect.

Class Handle

This is the main database interface connection abstraction, which allows to issue SQL statements and inspect the result of SQL queries.

Properties
affected	Indicates the amount of rows affected by the last query performed on this connection.
Methods
aquery	Execute a SQL query bound to return a recordset.
begin	Issues a "begin work", "start transaction" or other appropriate command.
commit	Issues a "commit work" command.
expand	Expands a sql query with provided parameters.
getLastID	Get the ID of the last record inserted.
lselect	Returns a "select" query configured to access a sub-recordset.
options	Sets the default options for SQL operations performed on this handle.
prepare	Prepares a repeated statement.
query	Execute a SQL query bound to return a recordset.
rollback	Issues a "rollback work" command.

Properties

affected

Indicates the amount of rows affected by the last query performed on this connection.

Will be 0 if none, -1 if unknown, or a positive value if the number of rows can be determined.

Methods

aquery

Execute a SQL query bound to return a recordset.

Handle.aquery( sql, params )

sql	The SQL query
params	Values to be passed to the query in an array.
Return	an instance of Recordset, or nil.
Raise	DBIError if the database engine reports an error.

On a succesful query, the property Handle.affected is assumes the count of affected rows, or -1 if the driver can't provide this information.

begin

Issues a "begin work", "start transaction" or other appropriate command.

Handle.begin()

Raise

DBIError in case of error in starting the transaction.

This method helps creating code portable across different database engines. It just issues the correct command for the database engine to start a transaction.

It is not mandatory to manage transactions through this method, and this method can be intermixed with direct calls to Handle.perform calling the database engine commands directly.

If the database engine doesn't support transaction, the command is ignored.

commit

Issues a "commit work" command.

Handle.commit()

Raise

DBIError in case of error in starting the transaction.

This method helps creating code portable across different database engines. It just issues the correct command for the database engine to commit the current transaction.

It is not mandatory to manage transactions through this method, and this method can be intermixed with direct calls to Handle.perform calling the database engine commands directly.

If the database engine doesn't support transaction, the command is ignored.

expand

Expands a sql query with provided parameters.

Handle.expand( sql, [...] )

sql	The SQL query
...	Parameters for the query
Return	A string containing a complete SQL statement.
Raise	DBIError in case the expansion fails.

Some underlying database engine may not be consistently working with the Falcon types used as parameters. As such, this utility can be used to create complete query strings that doesn't require driver-side parameter binding and expansion.

getLastID

Get the ID of the last record inserted.

Handle.getLastID( [name] )

name	A sequence name that is known by the engine.
Return	The value of the last single-field numeric key inserted in this transaction.

This is database dependent but so widely used, it is included in the DBI module. Some databases such as MySQL only support getting the last inserted ID globally in the database server while others like PostgreSQL allow you to get the last inserted ID of any table. Thus, it is suggested that you always supply the sequence id as which to query. DBI drivers such as MySQL are programmed to ignore the extra information and return simply the last ID inserted into the database.

lselect

Returns a "select" query configured to access a sub-recordset.

Handle.lselect( sql, [begin],[count] )

sql	The query (excluded the "select" command).
begin	The first row to be returned (0 based).
count	The number of rows to be returned.
Return	A fully configured sql command that can be fed into Handle.query

This method should create a "select" query adding the commands and/or the parameters needed by the engine to limit the resultset to a specified part part of the dataset.

The query parameter must be a complete query EXCEPT for the "select" command, which is added by the engine. It must NOT terminate with a ";", which, in case of need is added by the engine.

For example, to limit the following query to rows 5-14 (row 5 is the 6th row):

       SELECT field1, field2 FROM mytable WHERE key = ?;

write this Falcon code:

       // dbh is a DBI handle
       rset = dbh.query(
                   dbh.lselect( "field1, field2 FROM mytable WHERE key = ?", 5, 10 ),
                   "Key value" )

The count parameter can be 0 or nil to indicate "from begin to the end". It's not possible to return the n-last elements; to do that, reverse the query ordering logic.

Note: If the engine doesn't support limited recordsets, the limit parameters are ignored.

options

Sets the default options for SQL operations performed on this handle.

Handle.options( options )

options	The string containing the transaction options.
Raise	DBIError if the options are invalid.

This method sets the default options that are used to create new transactions or performing statements.

The options are set using a string where the settings are specified as = pairs.

Common options to all drivers include the followings:

- prefetch: number of records to be pre-fetched at each query. The value may be "all" to wholly fetch queries locally, "none" to prefetch none or an arbitrary number of rows to be read from the server. By default, it's "all".

• autocommit: Performs a transaction commit after each sql command. Can be "on" or "off"; it's "off" by default.
• cursor: Number of records returned by a query that should trigger the creation of a server side cursor. Can be "none" to prevent creation of server side cursor (the default) "all" to always create a cursor or an arbitrary number to create a server side cursor only if the query returns at least the indicated number of rows.
• strings: All the data in the resultset is returned as a string, without transformation into Falcon items. In case the data is queried to just perform a direct output without particular needs for string formatting, or if the needed data formatting is performed by the engine, this option may improve performance considerably. Also, some data types may be not easily represented by Falcon types, and having the native engine representation may be crucial. If the database engine doesn't offer this option natively, the driver may ignore it or emulate it.

Different database drivers may specify more transaction options; refer to their documentation for further parameters.

prepare

Prepares a repeated statement.

Handle.prepare( sql )

sql	The SQL query
Raise	DBIError if the database engine reports an error.

This method creates a "prepared statement" that can be iteratively called with different parameters to perform multiple time the same operations.

Typically, the SQL statement will be a non-query data statement meant

query

Execute a SQL query bound to return a recordset.

Handle.query( sql, [...] )

sql	The SQL query
...	Parameters for the query
Return	an instance of Recordset, or nil.
Raise	DBIError if the database engine reports an error.

On a succesful query, the property Handle.affected is assumes the count of affected rows, or -1 if the driver can't provide this information.

rollback

Issues a "rollback work" command.

Handle.rollback()

Raise

DBIError in case of error in starting the transaction.

This method helps creating code portable across different database engines. It just issues the correct command for the database engine to roll back the current transaction.

It is not mandatory to manage transactions through this method, and this method can be intermixed with direct calls to Handle.perform calling the database engine commands directly.

If the database engine doesn't support transaction, the command is ignored.