You can use the TrailBlazer browser to code SQL statements in a Smalltalk application in one of two ways: using query specs or embedded SQL. You can also use both of these ways in the same application. See the following for more information:
The following steps describe how you can create a query spec using the TrailBlazer browser:
You are prompted for a class name. Access sets are implemented as classes. The name you enter will be the name of the new class, which will hold the access set's query specs, package specs, and connection specs. If you create access sets through Trailblazer, the access sets will be implemented as private classes.
For OS/390, you will also be prompted for a DBRM name (1 to 8 characters long). This will determine the default filename to use when the default package spec is precompiled. The DBRM name you specify when creating an SQL package must be the same as the member name that will contain the DBRM in the DBRM library.
Property | Description |
---|---|
Statement | The SQL statement to be executed. For SELECT statements, enter only the SELECT statement (the other SQL statements needed to manage the cursor will be generated from the SELECT statement). |
Input shape | Text that describes the data being passed from your application to
DB2. This includes values to be used in a WHERE CLAUSE or as part of an
INSERT. The shape is defined by listing each host variable providing
input values followed by attributes identical to those used in CREATE
TABLE. For example, a host variable INTVAR and a CHAR(8) variable named
CHARVAL could be described like this:
INTVAR SMALLINT NOT NULL , CHARVAL CHAR(8) NOT NULL |
Output shape | Text that describes the data being passed from DB2 back to your application. This includes values being returned by a SELECT statement. If you will update the table using this query spec, the Output columns must include the columns to be updated. |
Use the properties chooser to change the displayed property (Statement, Input Shape, Output Shape, for QuerySpecs).
When defining host variables in Input Shape, make sure they follow the same order as the SQL statement.
The following case sensitivity recommendations apply to query specs:
Embedded SQL is supported in VisualAge Smalltalk Server with precompilers that generate Smalltalk code to access DB2 for workstation and DB2 for OS/390. SQL statements can be embedded in Smalltalk methods or in Smalltalk "doit" code that is selected to be run, displayed, or inspected.
As with other languages, additional steps must be taken in the development of Smalltalk code with embedded SQL statements. While every effort is made to minimize these additional steps, an understanding of the development process for coding Smalltalk programs with embedded SQL is necessary. Note that applications with embedded SQL statements are still required to obtain an active database connection.
The static SQL support for both DB2 for OS/390 and DB2 for workstation requires that SQL be collected into packages or plans for authorization control and for the database manager to determine the most effective algorithms with which to process that SQL. This requirement is satisfied in IBM Smalltalk by the use of a package spec, which represents a set of SQL statements to be bound in a DB2 plan. Package specs are stored by subclasses of AbtDatabaseAccessSet.
Each package spec can determine the SQL statements it contains. A package spec is associated with a number of methods (each with embedded SQL) and contains a number of query specs (each of which can generate SQL). The methods can be instance or class methods of any class defined or extended by the application that defines the package spec's access set. The package spec is also responsible for maintaining a set of precompiler options that affect the precompilation and binding of SQL contained in the package spec.
When a method containing embedded SQL is first created, an association is made between that method and a package spec. If there is only one package spec in the method's application, the association is automatic. If there are no package specs in the application, an error message is displayed and the method save fails. If there is more than one package spec, you are prompted to select the package spec to be associated with the method.
When precompiling embedded SQL in a package spec for the first time, several prompts will be displayed. If you have not yet chosen a precompiler for the application, the choice will be forced. This allows for precompiling for any one of several versions of DB2 on any of several platforms. For precompilation on DB2 for workstation, the connection spec known by the package spec is used to request a connection to the target DB2 database.
Only the Trailblazer browser recognizes and displays the source for embedded SQL statements. Other browsers display the generated source, but not the original SQL source code. To view the generated source for a method from Trailblazer, use the Expanded Source property.
You can save methods that use embedded SQL the same way you save other methods, by selecting Save from the pop-up menu.
An SQL workspace is available from which Smalltalk code with embedded SQL can be run, displayed, or inspected. To open an SQL workspace using the Trailblazer browser, select Open New for SQL from the Workspace menu. You can also open an existing SQL workspace by selecting Open for SQL from the Workspace menu.
Alternatively, you can run the one of the following statements from a Transcript or workspace to open a new SQL workspace:
AbtSqlWorkspace new open (AbtSqlWorkspace forFileNamed: fileName) open
You are prompted to select a DB2 for workstation database in which to evaluate the selected code. This selection remains in effect for the workspace until you explicitly change the target database. To change the target database, use the Database menu and choose the Set Database menu item. You are given a list of database names from which to choose. If there is only one database, that database automatically becomes the target database and the Set Database menu item cannot be selected.
Embedded SQL statements consist of the following three elements:
Because Smalltalk has no data typing, host variables must be declared using embedded shape statements. For example:
{Shape hostVar1 smallint not null, hostVar2 varchar(20) }
Host variables are Smalltalk variables (class, instance, class instance, global or temporary) that are referenced within SQL statements. They allow an application to pass input data to and to receive output data from DB2 for workstation and DB2 for OS/390. The following rules apply to host variables:
To make the declaration and use of host variables easier, Smalltalk allows host variables in SQL statements to be VisualAge rows. The following example illustrates a shape that expects to receive three possibly NULL columns from a select statement:
{shape col1 smallint , col2 char(8) , col3 date , ind1 smallint , ind2 smallint , ind3 smallint } {exec sql select * into :col1 indicator :ind1 , :col2 indicator :ind2 , :col3 indicator :ind3 from mytable }
These statements can be coded to make it easier for the method to pass the results of the query to other methods. After the select statement, the values for the three columns can be accessed with the at: method.
{shape aRow abtIbmRow with fields ( col1 smallint , col2 char(8) , col3 date ) } {exec sql select * into :aRow from mytable } Transcript cr; show: (aRow at: 'col1').
When the code for the select statement (in this example) is executed, it assumes that variable aRow contains an instance of a subclass of AbtIbmRow. The data and null indicators can be in either Smalltalk memory or in external memory.
You can also refer to specific fields in the row in an SQL statement. For example, the select statement above could have been coded as follows:
{exec sql select * into :aRow from mytable where intkey = :aRow.col1}
The SQLTYPE and SQLLEN of host variables can be determined
using the following table:
SQL data type | SQLTYPE | SQLLEN | Value class |
---|---|---|---|
DATE | 384/385 | 10 | Date |
TIME | 388/389 | 8 | Time |
TIMESTAMP | 392/393 | 26 | AbtTimestamp |
VARCHAR(n) | 448/449 | n + 2 | String |
CHAR(n) | 452/453 | n | String |
LONG VARCHAR(n) | 456/457 | n + 2 | String |
VARGRAPHIC(n) | 464/465 | n + 2 | String |
GRAPHIC(n) | 468/469 | n | String |
LONG VARGRAPHIC(n) | 472/473 | n + 2 | String |
FLOAT(n) | 480/481 | 8 | Float |
DECIMAL(p,s) | 484/485 | p in byte 1, s in byte 2 | ScaledDecimal |
INTEGER | 496/497 | 4 | Integer |
SMALLINT | 500/501 | 2 | Integer |
An sqlca is used by DB2 for workstation and DB2 for OS/390 to communicate return codes and additional error information to a program. Although an sqlca is automatically instantiated by run-time services, each method with embedded SQL must have a variable named "sqlca" in its compile scope. The variable can be a temporary, class instance, or instance variable. The code generated from each embedded SQL statement will set the sqlca.
An sqlca is treated by the Smalltalk DB2 interface as an instance of OSPtr whose data is in external OS memory. Runtime services will automatically free the storage used by an sqlca.
When embedding SQL statements, the following restrictions apply:
If code generated from an embedded SQL statement will not compile, a message will be produced and inserted just before the embedded SQL statement from which the generated source came. If the message is not understood, the following steps can help you diagnose the cause of the error:
The compiler error should display again, but this time the problem should be obvious because all of the Smalltalk source code is visible.