The methods provided in the DB2 access classes have the same external behaviors on the workstation as on OS/390 and OS/400. If you are porting a VisualAge DB2 application, note that not all VisualAge methods will work on OS/390 or on OS/400. In particular, static or embedded SQL is not yet available for Smalltalk applications on OS/400.
To interact with DB2, you can use nonvisual parts, provided you use DB2 Version 5.0 or later. Regardless of the DB2 release level, you can use also methods from the following classes to interact with DB2:
This section illustrates how to use the DB2 access classes. Sample source can be found in the class methods of AbtSampleStaticIbmDatabaseApp in the application with the same name. For the full sample database application, import AbtSampleStaticIbmDatabaseApp as part of the configuration map Server Samples in file abtmvssp.dat. See the VisualAge Smalltalk Server Guide for more information. These sample methods use the query specs found in SQL package AbtTest (class AbtSqlPackageSample).
As to OS/400, sample source can be found in the class methods of ToeDbAccess in the application TechOrderEntryBase. For the full sample database application and code samples, load the configuration map OS/400 Technical Order Entry Example. Packaging instructions for the sample applications can be found in the configuration map OS/400 Technical Order entry Instructions. See Sample DB2-CLI application for OS/400 for more information.
The following topics are included in this section:
Database access on workstations, OS/390, and OS/400 is made through a database connection. Workstation databases are accessed in client/server mode. There are several ways to obtain a connection.
You can use nonvisual parts to make a connections. Nonvisual parts use a connection spec to make a connection.
Or, using Smalltalk, you can write a simple method that asks the access set for a specific connection spec and then asks the connection spec for a connection. On workstations, the result is an actual connection to the database. Under CICS or IMS/ESA, however, the connection is described outside the transaction. As a result, the information provided by your application is used in the workstation's operating system but largely ignored in OS/390 when you are under CICS or IMS. Under OS/390, for an OS/390 Native application, you use the DB2 call attach facility (CAF) to make a connection to the database. See Obtaining an active OS/390 Native database connection for more information.
getConnection "Ask the access set for a connection spec and ask the connection spec for a connection" ^MyAccessSet abtDefaultConnection connection
For an example of obtaining a connection, see sample method getConnection in sample application AbtSampleStaticIbmDatabaseApp.
You can get a connection to a database from an OS/390 Native application in several different ways. The connection from an OS/390 Native application to a DB2 database is acquired using the call attach facility (CAF). There are several class methods available in the AbtIbmMvsDatabaseConnection class that allow you to connect to a DB2 database.
If you plan to test your OS/390 Native application on the workstation before porting it to OS/390, you should use the method connectToDatabaseNamed:userid:password:mvsSsid:mvsPlan:. On the workstation, this method will perform the correct connections for a workstation-based DB2 database. On OS/390, this method will perform the correct connections using the CAF.
The connectToSubsystem:withPlanNamed: method uses the other class methods provided in AbtIbmMvsDatabaseConnection. In other words, you could also use the CAF to get a connection to the DB2 database by issuing these two class methods, in order:
Like the CICS and IMS connections, an OS/390 Native connection must be disconnected. To disconnect an OS/390 Native database connection, use the class method db2CafDisconnect. Before you disconnect you can use the class method db2CafClose to commit and rollback changes.
See the VisualAge Smalltalk Server Guide for more information about the messages and error codes you might encounter.
If you are using CLI, you do not need to perform any additional steps to make the connection.
After your transactional native application completes its database access, disconnect from the database by sending the disconnect method to the active connection.
connection disconnect.
You can dynamically run any SQL statement, other than SELECT, by passing a string containing the statement to an active connection using method executeSQLStatement:.
dropTableQs "Drop (dynamically) the table created by #initializeTableQs so that you can go back and do it all over again!" | connection | connection := self getConnection. connection executeSQLStatement: 'DROP TABLE NUMBERS'. connection commitUnitOfWork. connection disconnect.
The method initializeTableQs follows:
initializeTableQs "Use the createTable querySpec stored in SQL package AbtSqlPackageSample to create a table in the database. Be sure that #bind has been run once against the database before invoking this method." | connection querySpec | connection := self getConnection. "Execute the SQL that creates the table." querySpec := AbtSqlPackageSample runtimeQuerySpecNamed: #createTable. connection executeQuerySpec: querySpec. connection disconnect.
For an example, see sample methods dropTableQs and initializeTableQs in application AbtSampleStaticIbmDatabaseApp.
Note: | If you plan to use either date or time in your DB2 tables, you must pass these values as strings in the ISO format. ISO is the only format supported by VisualAge Smalltalk Server. |
You can statically run non-SELECT SQL statements by first creating a query spec for the statement and then by running the query spec. The query spec contains all of the information needed by the precompiler and DB2 to run the statement. Every query spec will contain a string that holds the SQL statement. If you want your program to pass data to the SQL statement at run-time, you must tell the query spec the data type and length of each of those data values. The collection of data types and lengths is called a shape. Each non-SELECT query spec has an input shape, even if that shape is empty.
You specify the input shape by providing an input shape string either to the instance of the query spec or through the TrailBlazer browser.
An input shape string contains what is essentially a declaration of each value passed between your application and the database. Each declaration contains the name of the value and a description of the value. The syntax for the description is the same as the syntax used to describe columns in a CREATE TABLE statement.
fillTableQs "Use the addRowToTable querySpec stored in SQL package AbtSqlPackageSample to add rows in the table created by #initializeTableQs. Each row will contain an integer and the written form of the number." | connection querySpec names values | connection := self getConnection. querySpec := AbtSqlPackageSample runtimeQuerySpecNamed: #addRowToTable.
names := #('ONE ' 'TWO ' 'THREE ' 'FOUR ' 'FIVE ' 'SIX ' 'SEVEN ' 'EIGHT '). values := Dictionary new. "Add a row for each word in array names." names doWithIndex: [ :n :i | "Put the data for the row into dictionary values." values at: 'INT' put: i. values at: 'CHAR' put: n. "Add a row using the data in values." connection executeQuerySpec: querySpec withValues: values. ]. connection disconnect.
The sample method fillTableQs in application AbtSampleStaticIbmDatabaseApp shows how a query spec can be used to insert rows into a table. The query spec is named addRowToTable. The table contains two columns, a small integer and an eight-character string. The input shape looks like this:
INT smallint not null, CHAR char(8) not null
The insert statement (also held by the query spec) looks like this:
insert into numbers values ( :INT , :CHAR )
The names INT and CHAR have nothing to do with the actual column names in the table. Instead, they are used to tell DB2 that the first value passed in the INSERT statement is a small integer and the second value is an eight-character string.
When it comes time to insert the rows, the application asks the SQL package for the query spec (using runtimeQuerySpecNamed:forPackageSpecNamed:). For each row to be added, the active connection will be prompted to run the query spec (using executeQuerySpec:). Data is passed to DB2 using a dictionary. The dictionary is keyed by the names of the values (which are the same names used in the input shape and in the query spec's SQL statement). The values in the dictionary are the data values to be passed to the database.
Running SELECT statements is very much like running non-SELECT statements except that the database passes data back to the program. An input shape is used to describe any data being passed to the database (as part of the WHERE clause). Another shape, called the output shape, is used to describe the data passed back to the program.
readFromTableQs "Use the queryTable querySpec stored in SQL package AbtSqlPackageSample to read all of the rows in the table (created by #initializeTableQs and filled by #fillTableQs) for which the value in column INTKEY is greater than 0. This should result in 8 rows being fetched." | connection querySpec values rt | connection := self getConnection. querySpec := AbtSqlPackageSample runtimeQuerySpecNamed: #queryTable. "Ask the connection for a result table containing (logically) the rows for which 0 < intkey. The '0' value is passed using host variable INT." values := Dictionary new. values at: 'INT' put: 0. rt := connection resultTableFromQuerySpec: querySpec withValues: values.
"Read the rows, one at a time and write the numeric and character data to the Transcript. Note that the rows are fetched one at a time by the #next method." rt do: [ :row | (self outputGlobal) cr; show: 'Fetched row contains ', ((row at: 'INTKEY') printString), ' and ', ((row at: 'CHARVAL') printString). ]. connection disconnect.
To query a database, get the query spec from the SQL package. The sample method named readFromTableQs in application AbtSampleStaticIbmDatabaseApp uses the query spec named queryTable to query the rows whose INTVAL value is greater than 0. Because multiple rows can be returned, the application asks the active connection for a result table that logically contains the rows described by the query spec. Rows are returned, one at a time, by iterating over the result table. During this iteration, the iterator is an instance of AbtIbmRow. The fields in the row are named, typed, and ordered according to the output shape.
You can delete a row from a table in one of the following ways:
The following sample code deletes all of the result table's rows by sending that result table the deletePresentRow method in a loop.
deleteAllRowsFromTableQs "Use the queryTable querySpec stored in SQL package AbtSqlPackageSample to read all of the rows in the table and delete those rows (using current cursor position)." | connection querySpec values rt | connection := self getConnection. querySpec := AbtSqlPackageSample runtimeQuerySpecNamed: #queryTable. "Ask the connection for a result table containing (logically) the rows for which 0 < intkey. The '0' value is passed using host variable INT." values := Dictionary new. values at: 'INT' put: 0. rt := connection resultTableFromQuerySpec: querySpec withValues: values. "Read and delete the rows, one at a time." rt do: [ :row | rt deletePresentRow ]. connection disconnect.
See the sample method deleteAllRowsFromTableQs in the application AbtSampleStaticIbmDatabaseApp.
You can update a row in a table in one of the following ways:
The following sample method, updateTableQs in application AbtSampleStaticIbmDatabaseApp, is an example of updating a result table's current row.
updateTableQs "Use the queryTable querySpec stored in SQL package AbtSqlPackageSample to modify the rows in the table (created by #initializeTableQs and filled by #fillTableQs): - Only modify rows whose INTKEY value is greater than 4, - If the character value contains the letter 'F', change it to 'CHANGED ', - delete the row whose INTKEY value is 7." | connection querySpec values rt updates updateRow | connection := self getConnection. querySpec := AbtSqlPackageSample runtimeQuerySpecNamed: #queryTable. "Ask the connection for a result table containing (logically) the rows for which 4 < intkey." values := Dictionary new. values at: 'INT' put: 4. rt := connection resultTableFromQuerySpec: querySpec withValues: values.
"Read the rows, one at a time and perform any appropriate updates." rt do: [ :row | ((row at: 'CHARVAL') includes: $F) ifTrue: [ (self outputGlobal) cr; show: 'Updating row ',((row at: 'INTKEY') printString). updates := Dictionary new. updates at: 'CHARVAL' put: 'CHANGED'. updateRow := querySpec updateRowWithValues: updates. rt atCurrentRowPutRow: updateRow. ]. ((row at: 'INTKEY') = 7) ifTrue: [ (self outputGlobal) cr; show: 'Deleting row ',((row at: 'INTKEY') printString). rt deletePresentRow. ]. ]. connection disconnect.
To update a result table's current row, make sure that the data (the columns listed in the FOR UPDATE CLAUSE) with which you will update the table is described in the Output Shape of the query spec.
The actual method that updates the table takes as an argument an instance of AbtIbmRow, whose compound type corresponds to the Output Shape of the SELECT query spec. You can instantiate this IbmRow yourself, or you can ask the query spec to instantiate and initialize a row for you. The method updateRowWithValues: takes a dictionary containing names and values to be used in the update and answers an AbtIbmRow. This row can be passed to the result table using method atCurrentRowPutRow:.
The sample application AbtSampleStaticIbmDatabaseApp uses methods outputGlobal and outputGlobal: to manage the global (CICSTTY for CICS or Transcript anywhere) to which messages should be written. In this way, the sample methods can be run in CICS/ESA, IMS/ESA, or OS/390.
For example, to run the readFromTableQs method in CICS, use the following startup code:
AbtSampleStaticDatabaseApp outputGlobal: CICSTTY; readFromTableQs