With static SQL, host variables used in embedded SQL statements are known at application compile time. With dynamic SQL, the embedded SQL statements and consequently the host variables are not known until application run time. Thus, for dynamic SQL applications, you need to deal with the list of host variables that are used in your application. You can use the DESCRIBE statement to obtain host variable information for any SELECT statement that has been prepared (using PREPARE), and store that information into the SQL descriptor area (SQLDA).
| Note: | Java applications do not use the SQLDA structure, and therefore do not use the PREPARE or DESCRIBE statements. In JDBC applications you can use a PreparedStatement object and the executeQuery() method to generate a ResultSet object, which is the equivalent of a host language cursor. In SQLJ applications you can also declare an SQLJ iterator object with a CursorByPos or CursorByName cursor to return data from FETCH statements. |
When the DESCRIBE statement gets executed in your application, the database manager defines your host variables in an SQLDA. Once the host variables are defined in the SQLDA, you can use the FETCH statement to assign values to the host variables, using a cursor.
For complete information on the PREPARE, DESCRIBE, and FETCH statements, and a description of the SQLDA, refer to the SQL Reference.
For an example of a simple dynamic SQL program that uses the PREPARE, DESCRIBE, and FETCH statements without using an SQLDA, see Example: Dynamic SQL Program. For an example of a dynamic SQL program that uses the PREPARE, DESCRIBE, and FETCH statements and an SQLDA to process interactive SQL statements, see Example: ADHOC Program.
Processing a cursor dynamically is nearly identical to processing it using static SQL. When a cursor is declared, it is associated with a query.
In the static SQL case, the query is a SELECT statement in text form, as shown in Declare Cursor Statement.
In the dynamic SQL case, the query is associated with a statement name assigned in a PREPARE statement. Any referenced host variables are represented by parameter markers. Table 7 shows a DECLARE statement associated with a dynamic SELECT statement.
Table 7. Declare Statement Associated with a Dynamic SELECT
| Language | Example Source Code |
|---|---|
| C/C++ |
strcpy( prep_string, "SELECT tabname FROM syscat.tables"
"WHERE tabschema = ?" );
EXEC SQL PREPARE s1 FROM :prep_string;
EXEC SQL DECLARE c1 CURSOR FOR s1;
EXEC SQL OPEN c1 USING :host_var;
|
| Java (JDBC) |
PreparedStatement prep_string = ("SELECT tabname FROM syscat.tables
WHERE tabschema = ?" );
prep_string.setCursor("c1");
prep_string.setString(1, host_var);
ResultSet rs = prep_string.executeQuery();
|
| COBOL |
MOVE "SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = ?"
TO PREP-STRING.
EXEC SQL PREPARE S1 FROM :PREP-STRING END-EXEC.
EXEC SQL DECLARE C1 CURSOR FOR S1 END-EXEC.
EXEC SQL OPEN C1 USING :host-var END-EXEC.
|
| FORTRAN |
prep_string = 'SELECT tabname FROM syscat.tables WHERE tabschema = ?' EXEC SQL PREPARE s1 FROM :prep_string EXEC SQL DECLARE c1 CURSOR FOR s1 EXEC SQL OPEN c1 USING :host_var |
The main difference between a static and a dynamic cursor is that a static cursor is prepared at precompile time, and a dynamic cursor is prepared at run time. Additionally, host variables referenced in the query are represented by parameter markers, which are replaced by run-time host variables when the cursor is opened.
For more information about how to use cursors, see the following sections:
This sample program shows the processing of a cursor based upon a dynamic SQL statement. It lists all the tables in SYSCAT.TABLES except for the tables with the value STAFF in the name column. The sample is available in the following programming languages:
The CHECKERR macro/function is an error checking utility which is external to the program. The location of this error checking utility depends upon the programming language used:
See Using GET ERROR MESSAGE in Example Programs for the source code for this error checking utility.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "utilemb.h"
EXEC SQL INCLUDE SQLCA;
int main(int argc, char *argv[]) {
EXEC SQL BEGIN DECLARE SECTION;
char table_name[19];
char st[80]; (1)
char parm_var[19];
char userid[9];
char passwd[19];
EXEC SQL END DECLARE SECTION;
printf( "Sample C program: DYNAMIC\n" );
if (argc == 1) {
EXEC SQL CONNECT TO sample;
EMB_SQL_CHECK("CONNECT TO SAMPLE");
}
else if (argc == 3) {
strcpy (userid, argv[1]);
strcpy (passwd, argv[2]);
EXEC SQL CONNECT TO sample USER :userid USING :passwd;
EMB_SQL_CHECK("CONNECT TO SAMPLE");
}
else {
printf ("\nUSAGE: dynamic [userid passwd]\n\n");
return 1;
} /* endif */
strcpy( st, "SELECT tabname FROM syscat.tables" );
strcat( st, " WHERE tabname <> ?" );
EXEC SQL PREPARE s1 FROM :st; (2)
EMB_SQL_CHECK("PREPARE");
EXEC SQL DECLARE c1 CURSOR FOR s1; (3)
strcpy( parm_var, "STAFF" );
EXEC SQL OPEN c1 USING :parm_var; (4)
EMB_SQL_CHECK("OPEN");
do {
EXEC SQL FETCH c1 INTO :table_name; (5)
if (SQLCODE != 0) break;
printf( "Table = %s\n", table_name );
} while ( 1 );
EXEC SQL CLOSE c1; (6)
EMB_SQL_CHECK("CLOSE");
EXEC SQL COMMIT;
EMB_SQL_CHECK("COMMIT");
EXEC SQL CONNECT RESET;
EMB_SQL_CHECK("CONNECT RESET");
return 0;
}
/* end of program : DYNAMIC.SQC */
import java.sql.*;
class Dynamic
{ static
{ try
{ Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();
}
catch (Exception e)
{ System.out.println ("\n Error loading DB2 Driver...\n");
System.out.println (e);
System.exit(1);
}
}
public static void main(String argv[])
{ try
{ System.out.println (" Java Dynamic Sample");
// Connect to Sample database
Connection con = null;
// URL is jdbc:db2:dbname
String url = "jdbc:db2:sample";
if (argv.length == 0)
{ // connect with default id/password
con = DriverManager.getConnection(url);
}
else if (argv.length == 2)
{ String userid = argv[0];
String passwd = argv[1];
// connect with user-provided username and password
con = DriverManager.getConnection(url, userid, passwd);
}
else
{ throw new Exception("\nUsage: java Dynamic [username password]\n");
}
// Enable transactions
con.setAutoCommit(false);
// Perform dynamic SQL SELECT using JDBC
try
{ PreparedStatement pstmt1 = con.prepareStatement(
"SELECT tabname FROM syscat.tables " +
"WHERE tabname <> ? " +
"ORDER BY 1"); (2)
// set cursor name for the positioned update statement
pstmt1.setCursorName("c1"); (3)
pstmt1.setString(1, "STAFF");
ResultSet rs = pstmt1.executeQuery(); (4)
System.out.print("\n");
while( rs.next() ) (5)
{ String tableName = rs.getString("tabname");
System.out.println("Table = " + tableName);
};
rs.close();
pstmt1.close(); (7)
}
catch( Exception e )
{ throw e;
}
finally
{ // Rollback the transaction
System.out.println("\nRollback the transaction...");
con.rollback();
System.out.println("Rollback done.");
}
}
catch( Exception e )
{ System.out.println(e);
}
}
}
Identification Division.
Program-ID. "dynamic".
Data Division.
Working-Storage Section.
copy "sqlenv.cbl".
copy "sql.cbl".
copy "sqlca.cbl".
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 table-name pic x(20).
01 st pic x(80). (1)
01 parm-var pic x(18).
01 userid pic x(8).
01 passwd.
49 passwd-length pic s9(4) comp-5 value 0.
49 passwd-name pic x(18).
EXEC SQL END DECLARE SECTION END-EXEC.
77 errloc pic x(80).
Procedure Division.
Main Section.
display "Sample COBOL program: DYNAMIC".
display "Enter your user id (default none): "
with no advancing.
accept userid.
if userid = spaces
EXEC SQL CONNECT TO sample END-EXEC
else
display "Enter your password : " with no advancing
accept passwd-name.
* Passwords in a CONNECT statement must be entered in a VARCHAR format
* with the length of the input string.
inspect passwd-name tallying passwd-length for characters
before initial " ".
EXEC SQL CONNECT TO sample USER :userid USING :passwd
END-EXEC.
move "CONNECT TO" to errloc.
call "checkerr" using SQLCA errloc.
move "SELECT TABNAME FROM SYSCAT.TABLES
- " ORDER BY 1
- " WHERE TABNAME <> ?" to st.
EXEC SQL PREPARE s1 FROM :st END-EXEC. (2)
move "PREPARE" to errloc.
call "checkerr" using SQLCA errloc.
EXEC SQL DECLARE c1 CURSOR FOR s1 END-EXEC. (3)
move "STAFF" to parm-var.
EXEC SQL OPEN c1 USING :parm-var END-EXEC. (4)
move "OPEN" to errloc.
call "checkerr" using SQLCA errloc.
perform Fetch-Loop thru End-Fetch-Loop
until SQLCODE not equal 0.
EXEC SQL CLOSE c1 END-EXEC. (6)
move "CLOSE" to errloc.
call "checkerr" using SQLCA errloc.
EXEC SQL COMMIT END-EXEC.
move "COMMIT" to errloc.
call "checkerr" using SQLCA errloc.
EXEC SQL CONNECT RESET END-EXEC.
move "CONNECT RESET" to errloc.
call "checkerr" using SQLCA errloc.
End-Main.
go to End-Prog.
Fetch-Loop Section.
EXEC SQL FETCH c1 INTO :table-name END-EXEC. (5)
if SQLCODE not equal 0
go to End-Fetch-Loop.
display "TABLE = ", table-name.
End-Fetch-Loop. exit.
End-Prog.
stop run.
/* REXX DYNAMIC.CMD */
parse version rexxType .
parse source platform .
if platform == 'AIX/6000' & rexxType == 'REXXSAA' then
do
rcy = SysAddFuncPkg("db2rexx")
end
else
do
if RxFuncQuery('SQLDBS') <> 0 then
rcy = RxFuncAdd( 'SQLDBS', 'db2ar', 'SQLDBS' )
if RxFuncQuery('SQLEXEC') <> 0 then
rcy = RxFuncAdd( 'SQLEXEC', 'db2ar', 'SQLEXEC' )
end
/* pull in command line arguments */
parse arg userid passwd .
/* check to see if the proper number of arguments have been passed in */
PARSE ARG dbname userid password .
if ((dbname = "" ) | ,
(userid <> "" & password = "") ,
) then do
SAY "USAGE: dynamic.cmd <dbname> [<userid> <password>]"
exit -1
end
/* connect to database */
SAY
SAY 'Connect to' dbname
IF password= "" THEN
CALL SQLEXEC 'CONNECT TO' dbname
ELSE
CALL SQLEXEC 'CONNECT TO' dbname 'USER' userid 'USING' password
CALL CHECKERR 'Connect to '
SAY "Connected"
say 'Sample REXX program: DYNAMIC'
st = "SELECT tabname FROM syscat.tables WHERE tabname <> ? ORDER BY 1"
call SQLEXEC 'PREPARE s1 FROM :st' (2)
call CHECKERR 'PREPARE'
call SQLEXEC 'DECLARE c1 CURSOR FOR s1' (3)
call CHECKERR 'DECLARE'
parm_var = "STAFF"
call SQLEXEC 'OPEN c1 USING :parm_var' (4)
do while ( SQLCA.SQLCODE = 0 )
call SQLEXEC 'FETCH c1 INTO :table_name' (5)
if (SQLCA.SQLCODE = 0) then
say 'Table = ' table_name
end
call SQLEXEC 'CLOSE c1' (6)
call CHECKERR 'CLOSE'
call SQLEXEC 'CONNECT RESET'
call CHECKERR 'CONNECT RESET'
CHECKERR:
arg errloc
if ( SQLCA.SQLCODE = 0 ) then
return 0
else do
say '--- error report ---'
say 'ERROR occurred :' errloc
say 'SQLCODE :' SQLCA.SQLCODE
/******************************\
* GET ERROR MESSAGE API called *
\******************************/
call SQLDBS 'GET MESSAGE INTO :errmsg LINEWIDTH 80'
say errmsg
say '--- end error report ---'
if (SQLCA.SQLCODE < 0 ) then
exit
else do
say 'WARNING - CONTINUING PROGRAM WITH ERRORS'
return 0
end
end
return 0
An SQLDA contains a variable number of occurrences of SQLVAR entries, each of which contains a set of fields that describe one column in a row of data as shown in Figure 2. There are two types of SQLVAR entries: base SQLVARs, and secondary SQLVARs. For information about the two types, refer to the SQL Reference.
Figure 2. The SQL Descriptor Area (SQLDA)
|
Since the number of SQLVAR entries required depends on the number of columns in the result table, an application must be able to allocate an appropriate number of SQLVAR elements when needed. Two methods are available as discussed below. For information about the fields of the SQLDA that are mentioned, refer to the SQL Reference.
For the above methods, the question arises as to how many initial SQLVAR entries you should allocate. Each SQLVAR element uses up 44 bytes of storage (not counting storage allocated for the SQLDATA and SQLIND fields). If memory is plentiful, the first method of providing an SQLDA of maximum size is easier to implement.
The second method of allocating a smaller SQLDA is only applicable to programming languages such as C and C++ that support the dynamic allocation of memory. For languages such as COBOL and FORTRAN that do not support the dynamic allocation of memory, you have to use the first method.
Suppose an application declares an SQLDA structure named minsqlda that contains no SQLVAR entries. The SQLN field of the SQLDA describes the number of SQLVAR entries that are allocated. In this case, SQLN must be set to 0. Next, to prepare a statement from the character string dstring and to enter its description into minsqlda, issue the following SQL statement (assuming C syntax, and assuming that minsqlda is declared as a pointer to an SQLDA structure):
EXEC SQL
PREPARE STMT INTO :*minsqlda FROM :dstring;
Suppose that the statement contained in dstring was a SELECT statement that returns 20 columns in each row. After the PREPARE statement (or a DESCRIBE statement), the SQLD field of the SQLDA contains the number of columns of the result table for the prepared SELECT statement.
The SQLVARs in the SQLDA are set in the following cases:
The first SQLD SQLVAR entries are set and SQLDOUBLED is set to blank.
2* SQLD SQLVAR entries are set and SQLDOUBLED is set to 2.
The first SQLD SQLVAR entries are set and SQLDOUBLED is set to blank. If the SQLWARN bind option is YES, a warning SQLCODE +237 (SQLSTATE 01594) is issued.
The SQLVARs in the SQLDA are NOT set (requiring allocation of additional space and another DESCRIBE) in the following cases:
No SQLVAR entries are set and SQLDOUBLED is set to blank. If the SQLWARN bind option is YES, a warning SQLCODE +236 (SQLSTATE 01005) is issued.
Allocate SQLD SQLVARs for a successful DESCRIBE.
No SQLVAR entries are set and SQLDOUBLED is set to blank. If the SQLWARN bind option is YES, a warning SQLCODE +239 (SQLSTATE 01005) is issued.
Allocate 2*SQLD SQLVARs for a successful DESCRIBE including the names of the distinct types.
No SQLVAR entries are set and SQLDOUBLED is set to blank. A warning SQLCODE +238 (SQLSTATE 01005) is issued (regardless of the setting of the SQLWARN bind option).
Allocate 2*SQLD SQLVARs for a successful DESCRIBE.
The SQLWARN option of the BIND command is used to control whether the DESCRIBE (or PREPARE...INTO) will return the following warnings:
It is recommended that your application code always consider that these SQLCODEs could be returned. The warning SQLCODE +238 (SQLSTATE 01005) is always returned when there are LOB columns in the select list and there are insufficient SQLVARs in the SQLDA. This is the only way the application can know that the number of SQLVARs must be doubled because of a LOB column in the result set.
After the number of columns in the result table is determined, storage can be allocated for a second, full-size SQLDA. For example, if the result table contains 20 columns (none of which are LOB columns), a second SQLDA structure, fulsqlda, must be allocated with at least 20 SQLVAR elements (or 40 elements if the result table contains any LOBs or distinct types). For the rest of this example, assume that no LOBs or distinct types are in the result table.
The storage requirements for SQLDA structures consist of the following:
The number of SQLVAR entries needed for fulsqlda was specified in the SQLD field of minsqlda. This value was 20. Therefore, the storage allocation required for fulsqlda used in this example is:
16 + (20 * sizeof(struct sqlvar))
| Note: | On 64-bit platforms, sizeof(struct sqlvar) and sizeof(struct sqlvar2) returns 56. On 32-bit platforms, sizeof(struct sqlvar) and sizeof(struct sqlvar2) returns 44. |
This value represents the size of the header plus 20 times the size of each SQLVAR entry, giving a total of 896 bytes.
You can use the SQLDASIZE macro to avoid doing your own calculations and to avoid any version-specific dependencies.
Having allocated sufficient space for fulsqlda, an application must take the following steps:
Using the DESCRIBE statement is preferred because the costs of preparing the statement a second time are avoided. The DESCRIBE statement simply reuses information previously obtained during the prepare operation to fill in the new SQLDA structure. The following statement can be issued:
EXEC SQL DESCRIBE STMT INTO :fulsqlda
After this statement is executed, each SQLVAR element contains a description of one column of the result table.
Before fetching any rows of the result table using an SQLDA structure, an application must do the following:
Note that for Large Object (LOB) values, when the SELECT is described, the data type given in the SQLVAR is SQL_TYP_xLOB. This data type corresponds to a plain LOB host variable, that is, the whole LOB will be stored in memory at one time. This will work for small LOBs (up to a few MB), but you cannot use this data type for large LOBs (say 1 GB). It will be necessary for your application to change its column definition in the SQLVAR to be either SQL_TYP_xLOB_LOCATOR or SQL_TYPE_xLOB_FILE. (Note that changing the SQLTYPE field of the SQLVAR also necessitates changing the SQLLEN field.) After changing the column definition in the SQLVAR, your application can then allocate the correct amount of storage for the new type. For more information on LOBs, see Using the Object-Relational Capabilities.
These steps are accomplished by analyzing the description of each column and replacing the content of each SQLDATA field with the address of a storage area large enough to hold any values from that column. The length attribute is determined from the SQLLEN field of each SQLVAR entry for data items that are not of a LOB type. For items with a type of BLOB, CLOB, or DBCLOB, the length attribute is determined from the SQLLONGLEN field of the secondary SQLVAR entry.
In addition, if the specified column allows nulls, then the application must replace the content of the SQLIND field with the address of an indicator variable for the column.
After the SQLDA structure is properly allocated, the cursor associated with the SELECT statement can be opened and rows can be fetched by specifying the USING DESCRIPTOR clause of the FETCH statement.
When finished, the cursor should be closed and any dynamically allocated memory should be released.
To create an SQLDA structure with C, either embed the INCLUDE SQLDA statement in the host language or include the SQLDA include file to get the structure definition. Then, because the size of an SQLDA is not fixed, the application must declare a pointer to an SQLDA structure and allocate storage for it. The actual size of the SQLDA structure depends on the number of distinct data items being passed using the SQLDA. (For an example of how to code an application to process the SQLDA, see Example: ADHOC Program.)
In the C/C++ programming language, a macro is provided to facilitate SQLDA allocation. With the exception of the HP-UX platform, this macro has the following format:
#define SQLDASIZE(n) (offsetof(struct sqlda, sqlvar) + (n) × sizeof(struct sqlvar))
On the HP-UX platform, the macro has the following format:
#define SQLDASIZE(n) (sizeof(struct sqlda) + (n-1) × sizeof(struct sqlvar))
The effect of this macro is to calculate the required storage for an SQLDA with n SQLVAR elements.
To create an SQLDA structure with COBOL, you can either embed an INCLUDE SQLDA statement or use the COPY statement. Use the COPY statement when you want to control the maximum number of SQLVARs and hence the amount of storage that the SQLDA uses. For example, to change the default number of SQLVARs from 1489 to 1, use the following COPY statement:
COPY "sqlda.cbl"
replacing --1489--
by --1--.
The FORTRAN language does not directly support self-defining data structures or dynamic allocation. No SQLDA include file is provided for FORTRAN, because it is not possible to support the SQLDA as a data structure in FORTRAN. The precompiler will ignore the INCLUDE SQLDA statement in a FORTRAN program.
However, you can create something similar to a static SQLDA structure in a FORTRAN program, and use this structure wherever an SQLDA can be used. The file sqldact.f contains constants that help in declaring an SQLDA structure in FORTRAN.
Execute calls to SQLGADDR to assign pointer values to the SQLDA elements that require them.
The following table shows the declaration and use of an SQLDA structure
with one SQLVAR element.
| Language | Example Source Code |
|---|---|
| C/C++ |
#include <sqlda.h> struct sqlda *outda = (struct sqlda *)malloc(SQLDASIZE(1)); /* DECLARE LOCAL VARIABLES FOR HOLDING ACTUAL DATA */ double sal; short salind; /* INITIALIZE ONE ELEMENT OF SQLDA */ memcpy( outda->sqldaid,"SQLDA ",sizeof(outda->sqldaid)); outda->sqln = outda->sqld = 1; outda->sqlvar[0].sqltype = SQL_TYP_NFLOAT; outda->sqlvar[0].sqllen = sizeof( double );. outda->sqlvar[0].sqldata = (unsigned char *)&sal; outda->sqlvar[0].sqlind = (short *)&salind; |
| COBOL |
WORKING-STORAGE SECTION.
77 SALARY PIC S99999V99 COMP-3.
77 SAL-IND PIC S9(4) COMP-5.
EXEC SQL INCLUDE SQLDA END-EXEC
* Or code a useful way to save unused SQLVAR entries.
* COPY "sqlda.cbl" REPLACING --1489-- BY --1--.
01 decimal-sqllen pic s9(4) comp-5.
01 decimal-parts redefines decimal-sqllen.
05 precision pic x.
05 scale pic x.
* Initialize one element of output SQLDA
MOVE 1 TO SQLN
MOVE 1 TO SQLD
MOVE SQL-TYP-NDECIMAL TO SQLTYPE(1)
* Length = 7 digits precision and 2 digits scale
MOVE x"07" TO PRECISION.
MOVE x"02" TO SCALE.
MOVE DECIMAL-SQLLEN TO O-SQLLEN(1).
SET SQLDATA(1) TO ADDRESS OF SALARY
SET SQLIND(1) TO ADDRESS OF SAL-IND
|
| FORTRAN |
include 'sqldact.f'
integer*2 sqlvar1
parameter ( sqlvar1 = sqlda_header_sz + 0*sqlvar_struct_sz )
C Declare an Output SQLDA -- 1 Variable
character out_sqlda(sqlda_header_sz + 1*sqlvar_struct_sz)
character*8 out_sqldaid ! Header
integer*4 out_sqldabc
integer*2 out_sqln
integer*2 out_sqld
integer*2 out_sqltype1 ! First Variable
integer*2 out_sqllen1
integer*4 out_sqldata1
integer*4 out_sqlind1
integer*2 out_sqlnamel1
character*30 out_sqlnamec1
equivalence( out_sqlda(sqlda_sqldaid_ofs), out_sqldaid )
equivalence( out_sqlda(sqlda_sqldabc_ofs), out_sqldabc )
equivalence( out_sqlda(sqlda_sqln_ofs), out_sqln )
equivalence( out_sqlda(sqlda_sqld_ofs), out_sqld )
equivalence( out_sqlda(sqlvar1+sqlvar_type_ofs), out_sqltype1 )
equivalence( out_sqlda(sqlvar1+sqlvar_len_ofs), out_sqllen1 )
equivalence( out_sqlda(sqlvar1+sqlvar_data_ofs), out_sqldata1 )
equivalence( out_sqlda(sqlvar1+sqlvar_ind_ofs), out_sqlind1 )
equivalence( out_sqlda(sqlvar1+sqlvar_name_length_ofs),
+ out_sqlnamel1 )
equivalence( out_sqlda(sqlvar1+sqlvar_name_data_ofs),
+ out_sqlnamec1 )
C Declare Local Variables for Holding Returned Data.
real*8 salary
integer*2 sal_ind
C Initialize the Output SQLDA (Header)
out_sqldaid = 'OUT_SQLDA'
out_sqldabc = sqlda_header_sz + 1*sqlvar_struct_sz
out_sqln = 1
out_sqld = 1
C Initialize VAR1
out_sqltype1 = SQL_TYP_NFLOAT
out_sqllen1 = 8
rc = sqlgaddr( %ref(salary), %ref(out_sqldata1) )
rc = sqlgaddr( %ref(sal_ind), %ref(out_sqlind1) )
|
In languages not supporting dynamic memory allocation, an SQLDA with the desired number of SQLVAR elements must be explicitly declared in the host language. Be sure to declare enough SQLVAR elements as determined by the needs of the application.
Greater flexibility is available when passing data using an SQLDA than is
available using lists of host variables. For example, an SQLDA can be
used to transfer data that has no native host language equivalent, such as
DECIMAL data in the C language. The sample program called ADHOC is an
example using this technique. (See Example: ADHOC Program.) See Table 8 for a convenient cross-reference listing
showing how the numeric values and symbolic names are related.
Table 8. DB2 V2 SQLDA SQL Types
| Numeric Values and Corresponding Symbolic Names | ||
| SQL Column Type | SQLTYPE numeric value | SQLTYPE symbolic name1 |
|---|---|---|
| DATE | 384/385 | SQL_TYP_DATE / SQL_TYP_NDATE |
| TIME | 388/389 | SQL_TYP_TIME / SQL_TYP_NTIME |
| TIMESTAMP | 392/393 | SQL_TYP_STAMP / SQL_TYP_NSTAMP |
| n/a2 | 400/401 | SQL_TYP_CGSTR / SQL_TYP_NCGSTR |
| BLOB | 404/405 | SQL_TYP_BLOB / SQL_TYP_NBLOB |
| CLOB | 408/409 | SQL_TYP_CLOB / SQL_TYP_NCLOB |
| DBCLOB | 412/413 | SQL_TYP_DBCLOB / SQL_TYP_NDBCLOB |
| VARCHAR | 448/449 | SQL_TYP_VARCHAR / SQL_TYP_NVARCHAR |
| CHAR | 452/453 | SQL_TYP_CHAR / SQL_TYP_NCHAR |
| LONG VARCHAR | 456/457 | SQL_TYP_LONG / SQL_TYP_NLONG |
| n/a3 | 460/461 | SQL_TYP_CSTR / SQL_TYP_NCSTR |
| VARGRAPHIC | 464/465 | SQL_TYP_VARGRAPH / SQL_TYP_NVARGRAPH |
| GRAPHIC | 468/469 | SQL_TYP_GRAPHIC / SQL_TYP_NGRAPHIC |
| LONG VARGRAPHIC | 472/473 | SQL_TYP_LONGRAPH / SQL_TYP_NLONGRAPH |
| FLOAT | 480/481 | SQL_TYP_FLOAT / SQL_TYP_NFLOAT |
| REAL4 | 480/481 | SQL_TYP_FLOAT / SQL_TYP_NFLOAT |
| DECIMAL5 | 484/485 | SQL_TYP_DECIMAL / SQL_TYP_DECIMAL |
| INTEGER | 496/497 | SQL_TYP_INTEGER / SQL_TYP_NINTEGER |
| SMALLINT | 500/501 | SQL_TYP_SMALL / SQL_TYP_NSMALL |
| n/a | 804/805 | SQL_TYP_BLOB_FILE / SQL_TYPE_NBLOB_FILE |
| n/a | 808/809 | SQL_TYP_CLOB_FILE / SQL_TYPE_NCLOB_FILE |
| n/a | 812/813 | SQL_TYP_DBCLOB_FILE / SQL_TYPE_NDBCLOB_FILE |
| n/a | 960/961 | SQL_TYP_BLOB_LOCATOR / SQL_TYP_NBLOB_LOCATOR |
| n/a | 964/965 | SQL_TYP_CLOB_LOCATOR / SQL_TYP_NCLOB_LOCATOR |
| n/a | 968/969 | SQL_TYP_DBCLOB_LOCATOR / SQL_TYP_NDBCLOB_LOCATOR |
An application using dynamic SQL can be written to process arbitrary SQL statements. For example, if an application accepts SQL statements from a user, the application must be able to execute the statements without any prior knowledge of the statements.
By using the PREPARE and DESCRIBE statements with an SQLDA structure, an application can determine the type of SQL statement being executed, and act accordingly.
For an example of a program that processes interactive SQL statements, see Example: ADHOC Program.
When an SQL statement is prepared, information concerning the type of statement can be determined by examining the SQLDA structure. This information is placed in the SQLDA structure either at statement preparation time with the INTO clause, or by issuing a DESCRIBE statement against a previously prepared statement.
In either case, the database manager places a value in the SQLD field of the SQLDA structure, indicating the number of columns in the result table generated by the SQL statement. If the SQLD field contains a zero (0), the statement is not a SELECT statement. Since the statement is already prepared, it can immediately be executed using the EXECUTE statement.
If the statement contains parameter markers, the USING clause must be specified as described in the SQL Reference. The USING clause can specify either a list of host variables or an SQLDA structure.
If the SQLD field is greater than zero, the statement is a SELECT statement and must be processed as described in the following sections.
A varying-list SELECT statement is one in which the number and types of columns that are to be returned are not known at precompilation time. In this case, the application does not know in advance the exact host variables that need to be declared to hold a row of the result table.
To process a varying-list SELECT statement, an application can do the following:
These steps are described in detail in the following sections:
If your application allows users to save arbitrary SQL statements, you can save them in a table with a column having a data type of VARCHAR, LONG VARCHAR, CLOB, VARGRAPHIC, LONG VARGRAPHIC or DBCLOB. Note that the VARGRAPHIC, LONG VARGRAPHIC, and DBCLOB data types are only available in Double Byte Character Support (DBCS) and Extended UNIX Code (EUC) environments.
You must save the source SQL statements, not the prepared versions. This means that you must retrieve and then prepare each statement before executing the version stored in the table. In essence, your application prepares an SQL statement from a character string and executes this statement dynamically.
This sample program shows how the SQLDA is used to process interactive SQL statements.
| Note: | The example adhoc.sqc exists for C only. |
The EMB_SQL_CHECK macro/function is an error checking utility which is external to this program. For C programs that call DB2 APIs, the sqlInfoPrint function in utilapi.c is redefined as API_SQL_CHECK in utilapi.h. For C embedded SQL programs, the sqlInfoPrint function in utilemb.sqc is redefined as EMB_SQL_CHECK in utilemb.h. See Using GET ERROR MESSAGE in Example Programs for the source code for this error checking utility.
Note that this example uses a number of additional procedures that are provided as utilities in the file utilemb.sqc. These include:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlcodes.h>
#include <sqlda.h> (1)
#include "utilemb.h"
#ifdef DB268K
/* Need to include ASLM for 68K applications */
#include <LibraryManager.h>
#endif
EXEC SQL INCLUDE SQLCA ; (2)
#define SQLSTATE sqlca.sqlstate
int process_statement( char * ) ;
int main( int argc, char *argv[] ) {
int rc ;
char sqlInput[256] ;
char st[1024] ;
EXEC SQL BEGIN DECLARE SECTION ; (3)
char userid[9] ;
char passwd[19] ;
EXEC SQL END DECLARE SECTION ;
#ifdef DB268K
/*
Before making any API calls for 68K environment,
need to initial the Library Manager
*/
InitLibraryManager(0,kCurrentZone,kNormalMemory) ;
atexit(CleanupLibraryManager) ;
#endif
printf( "Sample C program : ADHOC interactive SQL\n" ) ;
/* Initialize the connection to a database. */
if ( argc == 1 ) {
EXEC SQL CONNECT TO sample ;
EMB_SQL_CHECK( "CONNECT TO SAMPLE" ) ;
}
else if ( argc == 3 ) {
strcpy( userid, argv[1] ) ;
strcpy( passwd, argv[2] ) ;
EXEC SQL CONNECT TO sample USER :userid USING :passwd ; (4)
EMB_SQL_CHECK( "CONNECT TO SAMPLE" ) ; (5)
}
else {
printf( "\nUSAGE: adhoc [userid passwd]\n\n" ) ;
return( 1 ) ;
} /* endif */
printf( "Connected to database SAMPLE\n" ) ;
/* Enter the continuous command line loop. */
*sqlInput = '\0' ;
while ( ( *sqlInput != 'q' ) && ( *sqlInput != 'Q' ) ) { (6)
printf( "Enter an SQL statement or 'quit' to Quit :\n" ) ;
gets( sqlInput ) ;
if ( ( *sqlInput == 'q' ) || ( *sqlInput == 'Q' ) ) break ;
if ( *sqlInput == '\0' ) { /* Don't process the statement */
printf( "No characters entered.\n" ) ;
continue ;
}
strcpy( st, sqlInput ) ;
while ( sqlInput[strlen( sqlInput ) - 1] == '\\' ) {
st[strlen( st ) - 1] = '\0' ;
gets( sqlInput ) ;
strcat( st, sqlInput ) ;
}
/* Process the statement. */
rc = process_statement( st ) ;
}
printf( "Enter 'c' to COMMIT or Any Other key to ROLLBACK the transaction :\n" ) ;
gets( sqlInput ) ;
if ( ( *sqlInput == 'c' ) || ( *sqlInput == 'C' ) ) {
printf( "COMMITING the transactions.\n" ) ;
EXEC SQL COMMIT ; (7)
EMB_SQL_CHECK( "COMMIT" ) ;
}
else { /* assume that the transaction is to be rolled back */
printf( "ROLLING BACK the transactions.\n" ) ;
EXEC SQL ROLLBACK ; (8)
EMB_SQL_CHECK( "ROLLBACK" ) ;
}
EXEC SQL CONNECT RESET ; (9)
EMB_SQL_CHECK( "CONNECT RESET" ) ;
return( 0 ) ;
}
/******************************************************************************
* FUNCTION : process_statement
* This function processes the inputted statement and then prepares the
* procedural SQL implementation to take place.
******************************************************************************/
int process_statement ( char * sqlInput ) {
int counter = 0 ;
struct sqlda * sqldaPointer ;
short sqlda_d ;
EXEC SQL BEGIN DECLARE SECTION ; (3)
char st[1024] ;
EXEC SQL END DECLARE SECTION ;
strcpy( st, sqlInput ) ; (10)
/* allocate an initial SQLDA temp pointer to obtain information
about the inputted "st" */
init_da( &sqldaPointer, 1 ) ; (11)
EXEC SQL PREPARE statement1 from :st ;
/* EMB_SQL_CHECK( "PREPARE" ) ; */
EXEC SQL DESCRIBE statement1 INTO :*sqldaPointer ;
/* Expecting a return code of 0 or SQL_RC_W236,
SQL_RC_W237, SQL_RC_W238, SQL_RC_W239 for cases
where this statement is a SELECT statment. */
if ( SQLCODE != 0 &&
SQLCODE != SQL_RC_W236 &&
SQLCODE != SQL_RC_W237 &&
SQLCODE != SQL_RC_W238 &&
SQLCODE != SQL_RC_W239
) {
/* An unexpected warning/error has occurred. Check the SQLCA. */
EMB_SQL_CHECK( "DESCRIBE" ) ;
} /* end if */
sqlda_d = sqldaPointer->sqld ;
free( sqldaPointer ) ;
if ( sqlda_d > 0 ) { (12)
/* this is a SELECT statement, a number of columns
are present in the SQLDA */
if ( SQLCODE == SQL_RC_W236 || SQLCODE == 0)
/* this out only needs a SINGLE SQLDA */
init_da( &sqldaPointer, sqlda_d ) ;
if ( SQLCODE == SQL_RC_W237 ||
SQLCODE == SQL_RC_W238 ||
SQLCODE == SQL_RC_W239 )
/* this output contains columns that need a DOUBLED SQLDA */
init_da( &sqldaPointer, sqlda_d * 2 ) ;
/* need to reassign the SQLDA with the correct number
of columns to the SQL statement */
EXEC SQL DESCRIBE statement1 INTO :*sqldaPointer ;
EMB_SQL_CHECK( "DESCRIBE" ) ;
/* allocating the proper amount of memory
space needed for the variables */
alloc_host_vars( sqldaPointer ) ; (14)
/* Don't need to check the SQLCODE for declaration of cursors */
EXEC SQL DECLARE pcurs CURSOR FOR statement1 ; (15)
EXEC SQL OPEN pcurs ; (15)
EMB_SQL_CHECK( "OPEN" ) ;
EXEC SQL FETCH pcurs USING DESCRIPTOR :*sqldaPointer; (16)
EMB_SQL_CHECK( "FETCH" ) ;
/* if the FETCH is successful, obtain data from SQLDA */
/* display the column titles */
display_col_titles( sqldaPointer ) ; (17)
/* display the rows that are fetched */
while ( SQLCODE == 0 ) {
counter++ ;
display_da( sqldaPointer ) ; (18)
EXEC SQL FETCH pcurs USING DESCRIPTOR :*sqldaPointer ;
} /* endwhile */
EXEC SQL CLOSE pcurs ; (19)
EMB_SQL_CHECK( "CLOSE CURSOR" ) ;
printf( "\n %d record(s) selected\n\n", counter ) ;
/* Free the memory allocated to this SQLDA. */
free_da( sqldaPointer ) ;
} else { /* this is not a SELECT statement, execute SQL statement */ (13)
EXEC SQL EXECUTE statement1 ;
EMB_SQL_CHECK( "Executing the SQL statement" ) ;
} /* end if */
return( 0 ) ;
} /* end of program : ADHOC.SQC */