Transaction processing consist of three or four steps depending upon whether a statement was prepared prior to execution.
There are two basic methods of specifying and executing SQL statements:
stmt := connection prepareSql: ‘SELECT * FROM STAFF’. - or - (sd := connection newSqlDescriptor) sql: ‘SELECT * FROM STAFF’; yourself. stmt := sd prepareOn: connection. - followed by - rslt := stmt execute. rslt close. ... stmt close.
Note: | Explicitly prepared statements must be explicitly closed. All other statements are implicitly closed when their results are closed. |
rslt := connection executeSql: ‘SELECT * FROM STAFF’. - or - (sd := connection newSqlDescriptor) sql: ‘SELECT * FROM STAFF’; yourself. rslt := sd executeOn: connection.
"Prepare and Execute" is used when:
"Execute Direct" combines the prepare step and the execute step into one. This method is useful when:
DB2 Universal Database version 5 or later has a global dynamic statement cache stored on the server. This cache is used to store the most popular access plans for prepared SQL statements. Before each statement is prepared, the server searches this cache to see if an access plan has already been created for this exact SQL statement (by this application or any other application or client). If so, the server does not need to generate a new access plan, but will use the one in the cache instead. There is now no need for the application to cache connections at the client. For information on caching connections at the client see "Caching Statement Handles on the Client" in the Migration section of DB2’s Call Level Interface User Guide and Reference.
In both of the Preparation and Execute examples shown above, two ways of carrying out the same operation were shown. The first method, before the OR, illustrates the simplest approach while the second method, after the OR, illustrates the same action using a SQL descriptor. SQL descriptions, like connection descriptors, are a session independent means of describing how an SQL statement is to be executed.
Parameter markers are represented by the '?' character and indicate the position in the SQL statement where the contents of application variables are to be substituted when the statement is executed. The parameter markers are referenced sequentially, from left to right, starting at 1.
Parameter markers are represented by the '?' character and indicate the position in the SQL statement where the contents of application variables are to be substituted when the statement is executed. The parameter markers are referenced sequentially, from left to right, starting at 1.
The following table lists the Smalltalk classes that are used to represent a distinct built-in SQL data type. The table also indicates the Smalltalk class used to represent values of this type.
The use of named parameters (i.e. host variables) is not supported by the DB2 Call Level Interface. However, the Advanced Database feature has implemented this capability in a separate add-on VisualAge application called UtyDB2CLIAdditionsApp.
Application developers are encouraged to avoid using named parameters (host variables) in performance critical areas of their code.
If parameter types are defined using an EsOrderedDictionary, then parameter values may be either a sequential (Array/OrderedCollection) or keyed collection (Dictionary).
(valueDict := EsOrderedDictionary new: 7) at: 'ID' put: 11; at: 'NAME' put: 'Jones'; at: 'DEPT' put: 20; at: 'JOB' put: 'Mgr'; at: 'YEARS' put: 7; at: 'SALARY' put: 76543.21; at: 'COMM' put: 12345.67; yourself. stmt := self prepareSql: ('INSERT INTO STAFF VALUES ', '( :ID, :NAME, :DEPT, :JOB, :YEARS, :SALARY, :COMM )') withParameterTypes: ((EsOrderedDictionary new: 7) at: 'ID' put: (UtyDB2SmallIntType new); at: 'NAME' put: (UtyDB2VarCharType precision: 9); at: 'DEPT' put: (UtyDB2SmallIntType new); at: 'JOB' put: (UtyDB2CharType precision: 5); at: 'YEARS' put: (UtyDB2SmallIntType new); at: 'SALARY' put: (UtyDB2DecimalType precision: 7 scale: 2); at: 'COMM' put: (UtyDB2DecimalType precision: 7 scale: 2); yourself). stmt executeWithParameterValues: valueDict. valueDict at: 'ID' put: 12. stmt executeWithParameterValues: (valueDict values). stmt close.
The next step after the statement has been executed depends on the type of SQL statement:
If the statement is a query statement, the object returned by method that is used to execute the SQL will be an instance of the UtyDB2ResultTable class, which can be used to enumerate each row in the result set. Typically the user will either retrieve only the first row, all rows, or one row at a time. Each of theses task can be accomplished by sending the result table the first, rows or next messages respectfully.
If there are no more rows that can be fetched into the image, then the next message will return nil.
Note: | If the application chooses to use the next message, it must either process the rows until nil is returned or it must close the result table object implicitly. |
stmt := connection prepareSql: 'SELECT * FROM STAFF'. "Example of how to use the rows method." result := stmt execute. rows := result rows. "Example of how to use the fetch method" result := stmt execute. rows := OrderedCollection new. [(row := Result fetch) notNil] whileTrue: [ rows add: row ]. "Example of how to use the collect: method" result := stmt execute. ids := result collect: [ :aRow | aRow at: 'ID' ].
If the statement is modifying data (UPDATE, DELETE, or INSERT), no action is required after statement execution. However, the method numberOfAffectedRows may be sent to the instance of UtyDB2Result object that was returned by the method used to execute the SQL.
result := connection execute: 'DELETE FROM STAFF WHERE ID > 10 AND ID < 20'. result numberOfAffectedRows
If the statement neither queries nor modifies the data, then there is no further action is required after statement execution.
A transaction is a recoverable unit of work, or a group of SQL statements that can be treated as one atomic operation. This means that all the operations within the group are guaranteed to be completed (committed) or undone (rolled back), as if they were a single operation. A transaction can also be referred to as a Unit of Work or a Logical Unit of Work. When the transaction spans multiple connections, it is referred to as a Distributed Unit of Work.
DB2 CLI supports two commit modes:
The default commit mode is auto-commit. An application can switch between manual-commit and auto-commit modes by calling the mit: methods with a value of true or false. Typically, a query-only application may wish to stay in auto-commit mode. Applications that need to perform updates to the database should turn off auto-commit as soon as the database connection has been established.
When multiple connections exist to the same or different databases, each connection has its own transaction.
If the application is in auto-commit mode, it never needs to call rollback or commit. A commit is issued implicitly at the end of each statement execution.
In manual-commit mode, rollback or commit must be called before calling the disconnect or close methods.
It is recommended that an application that performs updates should not wait until the disconnect or close is called before committing or rolling back the transaction. The other extreme is to operate in auto-commit mode, which is also not recommended as this adds extra processing.
Consider the following when deciding where in the application to end a transaction:
When a transaction ends: