Writing SQL statements using Smalltalk
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:
Using the Trailblazer browser to code query specs
The following steps describe how you can create a query spec using the TrailBlazer browser:
1. Make the development image the current image. Although nothing prevents you from creating query specs in a passive image, you cannot run the query specs from the passive image.
2. Verify that the Use Enhanced Browsers menu item has been selected from the Tools menu on the Transcript window.
3. Select Browse Applications from the Tools menu.
4. Select your application from the list of applications.
5. Use the navigation chooser for the second list box (now showing Classes) to change the list to Access Sets.
6. The middle list box now shows all access sets for the selected application.
7. Use the pop-up menu for the middle list box and select Create to add a new access set to the application.
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 MVS, 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.
8. Once the access set is created, it appears in the list of access sets for the application.
9. The right-most list of the TrailBlazer will now be a list of the package specs in the selected access set. When an access set is created, the new access set will be given a default package spec.
10. When the package spec in the right-most list is selected, the properties area will display the current precompile options for the package spec and the TrailBlazer browser will scroll to display a list of query specs for the package spec.
11. The pop-up menu for the query spec list allows you to create and delete query specs in the selected package spec.
12. Once you have created a query spec, you will need to specify the query spec's SQL statement and specify the data types and lengths of any data being passed between your application and DB2.
| |
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. |
13. Use the properties chooser to change the displayed property (Statement, Input Shape, Output Shape, for QuerySpecs).
14. 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:
•For query specs running dynamically on DB2 for workstation, field names should be in uppercase so that they match the uppercase column names passed between DB2 for workstation and the database access layer. The keys for the value dictionaries should also be in uppercase.
•For query specs running statically on workstations and MVS, field names and the keys for the value dictionaries should be in the same case as the names in the column passed between DB2 for workstation and the database access layer. Mixed case is supported. For code portability, however, uppercase should be used on both workstation and MVS.
Using embedded SQL to code SQL statements
Embedded SQL is supported in VA Smalltalk Server with precompilers that generate Smalltalk code to access DB2 for workstation and DB2 for MVS. 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 MVS 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 VA Smalltalk / VAST Platform 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.
Selecting and running Smalltalk code with embedded SQL
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.
Embedding SQL statements
Embedded SQL statements consist of the following three elements:
Initializer
{exec sql
String
Any valid SQL statement
Terminator
}
Shape statements
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
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 MVS. The following rules apply to host variables:
•Any valid Smalltalk variable name is allowed. However, do not start a name with "SQL" in any mixed case combination.
•The type and length of each host variable must be declared in a Shape statement in each method in which the variable is referenced in an embedded SQL statement.
•Generally, host variables are expected to reference Smalltalk objects in Smalltalk memory. Exceptions are the user-supplied sqlda, which is expected to reside in external OS memory, and an AbtIbmRow, which can reside either in Smalltalk memory or in external memory.
•On Windows, AIX, and MVS, host variable names are case sensitive. When a variable is used as a host variable, it is "cased" exactly as it is elsewhere in the Smalltalk code. In addition, its case must match exactly that used in the Shape statement.
Host variables as instances of AbtRecord
To make the declaration and use of host variables easier, Smalltalk allows host variables in SQL statements to be VA Smalltalk 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}
Determining the SQLTYPE and SQLLEN of host variables
The SQLTYPE and SQLLEN of host variables can be determined using the following table:
| | | |
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 |
SQLCA
An sqlca is used by DB2 for workstation and DB2 for MVS 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.
Restrictions when embedding SQL
When embedding SQL statements, the following restrictions apply:
•The following SQL statements are not supported:
•CONNECT
•WHENEVER
•BEGIN DECLARE SECTION
•END DECLARE SECTION
•Host variables in connect and execute immediate statements must be declared.
•Cursor names must be unique within an SQL package.
•The scope of a host variable's shape is the method in which it is used. If a host variable is used in SQL statements in multiple methods, the host variable's shape must be declared in each of those methods.
•The first reference to a cursor must be in a DECLARE CURSOR statement. The precompiler will attempt to precompile methods so that declarations are precompiled first.
•When you copy or move a method that contains embedded SQL, it is the expanded source of the SQL that gets copied or moved.
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:
15. Create a new SQL workspace by executing AbtSqlWorkspace new open.
16. Copy the code to the new workspace.
17. Execute the code. Note that you might need to declare some variables that were instance variables.
18. The same compiler error should appear.
19. From the Transcript, not the SQL workspace, execute EtWorkspace fromLastPrecompile.
20. A new workspace appears containing the generated source from the attempted execution from the SQL workspace.
21. In the new workspace, select all and execute.
The compiler error should display again, but this time the problem should be obvious because all of the Smalltalk source code is visible.