Application Development Guide

Example Input-SQLDA Programs

Following is a sample program demonstrating the use of an input SQLDA structure. The client application invokes a stored procedure that creates a table named Presidents and loads the table with data.

This program creates a table called Presidents in the SAMPLE database. It then inserts the values Washington, Jefferson, and Lincoln into the table.

Without using stored procedures, the sample program would have been designed to transmit data across the network in four separate requests in order to process each SQL statement, as shown in Figure 11.

Figure 11. Input-SQLDA Sample Without a Stored Procedure

Instead, the sample program makes use of the stored procedures technique to transmit all of the data across the network in one request, allowing the server procedure to execute the SQL statements as a group. This technique is shown in Figure 12.

Figure 12. Input-SQLDA Sample With a Stored Procedure

A sample input-SQLDA client application and sample input-SQLDA stored procedure is shown on "How the Example Input-SQLDA Client Application Works".

How the Example Input-SQLDA Client Application Works

Initialize the Input SQLDA Structure. The following fields of the input SQLDA are initialized:
- The SQLN and SQLD elements are set to the total number of SQLVAR elements allocated and used.
- The SQLTYPE elements are set to indicate character data type.
- The first SQLDATA element is set to the name of the table. The second through fourth SQLDATA elements are set to the values Washington, Jefferson, and Lincoln.
- The SQLLEN elements are set to the length of each SQLDATA element (plus 1 byte for the C language null terminator).
- The SQLIND elements are set to NULL.
Invoke the Server Procedure. The application invokes the procedure inpsrv at the location of the database, sample using:
1. CALL statement with host variables
2. CALL statement with an SQLDA.

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:

C: check_error is redefined as CHECKERR and is located in the util.c file.
COBOL: CHECKERR is an external program named checkerr.cbl.
FORTRAN: CHECKERR is a subroutine located in the util.f file.
REXX: CHECKERR is a procedure located at bottom of the current program.

See Using GET ERROR MESSAGE in Example Programs for the source code for this error checking utility.

FORTRAN UNIX Example: INPCLI.SQF

      program inpcli
      implicit none
 
*     Copy Files for Constants and Structures
      include 'sql.f'
      include 'sqlenv.f'
      include 'sqlutil.f'
      include 'sqldact.f'
 
*     Declare an SQLCA
      EXEC SQL INCLUDE SQLCA
 
*     Declare host variables.
      EXEC SQL BEGIN DECLARE SECTION
        character*8    dbname
        character*8    userid
        character*18   passwd
        character*11   procname   /'inpsrv'/
        character*10   table_name /'PRESIDENTS'/
        character*20   data_item0 /'Washington'/
        character*20   data_item1 /'Jefferson'/
        character*20   data_item2 /'Lincoln'/
        integer*2      tableind  /0/
        integer*2      dataind0  /0/
        integer*2      dataind1  /0/
        integer*2      dataind2  /0/
      EXEC SQL END DECLARE SECTION
 
*     Declare Variables Used to Create an SQLDA
      integer*2  sqlvar1
      parameter ( sqlvar1 = sqlda_header_sz + 0*sqlvar_struct_sz )
      integer*2  sqlvar2
      parameter ( sqlvar2 = sqlda_header_sz + 1*sqlvar_struct_sz )
      integer*2  sqlvar3
      parameter ( sqlvar3 = sqlda_header_sz + 2*sqlvar_struct_sz )
      integer*2  sqlvar4
      parameter ( sqlvar4 = sqlda_header_sz + 3*sqlvar_struct_sz )
 
*     Declare an Input SQLDA Structure -- 4 Variables
      character    io_sqlda(sqlda_header_sz + 4*sqlvar_struct_sz)
 
      character*8  io_sqldaid     ! Header
      integer*4    io_sqldabc
      integer*2    io_sqln
      integer*2    io_sqld
 
      integer*2    io_sqltype1    ! First Variable
      integer*2    io_sqllen1
      integer*4    io_sqldata1
      integer*4    io_sqlind1
      integer*2    io_sqlnamel1
      character*30 io_sqlnamec1
 
      integer*2    io_sqltype2    ! Second Variable
      integer*2    io_sqllen2
      integer*4    io_sqldata2
      integer*4    io_sqlind2
      integer*2    io_sqlnamel2
      character*30 io_sqlnamec2
 
      integer*2    io_sqltype3    ! Third Variable
      integer*2    io_sqllen3
      integer*4    io_sqldata3
      integer*4    io_sqlind3
      integer*2    io_sqlnamel3
      character*30 io_sqlnamec3
 
      integer*2    io_sqltype4    ! Fourth Variable
      integer*2    io_sqllen4
      integer*4    io_sqldata4
      integer*4    io_sqlind4
      integer*2    io_sqlnamel4
      character*30 io_sqlnamec4
 
      equivalence( io_sqlda(sqlda_sqldaid_ofs), io_sqldaid )
      equivalence( io_sqlda(sqlda_sqldabc_ofs), io_sqldabc )
      equivalence( io_sqlda(sqlda_sqln_ofs), io_sqln )
      equivalence( io_sqlda(sqlda_sqld_ofs), io_sqld )
 
      equivalence( io_sqlda(sqlvar1+sqlvar_type_ofs), io_sqltype1 )
      equivalence( io_sqlda(sqlvar1+sqlvar_len_ofs), io_sqllen1 )
      equivalence( io_sqlda(sqlvar1+sqlvar_data_ofs), io_sqldata1 )
      equivalence( io_sqlda(sqlvar1+sqlvar_ind_ofs), io_sqlind1 )
      equivalence( io_sqlda(sqlvar1+sqlvar_name_length_ofs),
     +             io_sqlnamel1 )
      equivalence( io_sqlda(sqlvar1+sqlvar_name_data_ofs),
     +             io_sqlnamec1 )
 
      equivalence( io_sqlda(sqlvar2+sqlvar_type_ofs), io_sqltype2 )
      equivalence( io_sqlda(sqlvar2+sqlvar_len_ofs), io_sqllen2 )
      equivalence( io_sqlda(sqlvar2+sqlvar_data_ofs), io_sqldata2 )
      equivalence( io_sqlda(sqlvar2+sqlvar_ind_ofs), io_sqlind2 )
      equivalence( io_sqlda(sqlvar2+sqlvar_name_length_ofs),
     +             io_sqlnamel2 )
      equivalence( io_sqlda(sqlvar2+sqlvar_name_data_ofs),
     +             io_sqlnamec2 )
 
      equivalence( io_sqlda(sqlvar3+sqlvar_type_ofs), io_sqltype3 )
      equivalence( io_sqlda(sqlvar3+sqlvar_len_ofs), io_sqllen3 )
      equivalence( io_sqlda(sqlvar3+sqlvar_data_ofs), io_sqldata3 )
      equivalence( io_sqlda(sqlvar3+sqlvar_ind_ofs), io_sqlind3 )
      equivalence( io_sqlda(sqlvar3+sqlvar_name_length_ofs),
     +             io_sqlnamel3 )
      equivalence( io_sqlda(sqlvar3+sqlvar_name_data_ofs),
     +             io_sqlnamec3 )
 
      equivalence( io_sqlda(sqlvar4+sqlvar_type_ofs), io_sqltype4 )
      equivalence( io_sqlda(sqlvar4+sqlvar_len_ofs), io_sqllen4 )
      equivalence( io_sqlda(sqlvar4+sqlvar_data_ofs), io_sqldata4 )
      equivalence( io_sqlda(sqlvar4+sqlvar_ind_ofs), io_sqlind4 )
      equivalence( io_sqlda(sqlvar4+sqlvar_name_length_ofs),
     +             io_sqlnamel4 )
      equivalence( io_sqlda(sqlvar4+sqlvar_name_data_ofs),
     +             io_sqlnamec4 )
 
      character*80     errloc
      integer*4        rc
 
*     Program Logic
      print *, 'Enter in the database name :'
      read 100, dbname
100   format (a8)      
 
      print *, 'Enter your user id (default none):'
      read 100, userid
 
      if( userid(1:1) .eq. ' ' ) then
	EXEC SQL CONNECT TO :dbname 
      else
	print *, 'Enter your password :'
	read 100, passwd
 
        print *,'CONNECT to Remote Database.'
 
	EXEC SQL CONNECT TO :dbname USER :userid USING :passwd
      end if
 
*     Connect to Remote Database
      errloc = 'CONNECT'
      call checkerr (sqlca, errloc, *999)
 
*     Call the Remote Procedure via CALL with Host Variables
* (2a)
      print *,'Use CALL with Host Variable '
      print *,'to invoke the Server Procedure named inpsrv.'
      EXEC SQL CALL :procname (:table_name:tableind,
     +                         :data_item0:dataind0,
     +                         :data_item1:dataind1,
     +                         :data_item2:dataind2)
      errloc = 'CALL with HOST VARIABLES'
      call checkerr (sqlca, errloc, *999)
      print *,'Server Procedure Complete.'
 
*     Initialize the Input SQLDA Structure
* (1)
      io_sqldaid   = 'IN_SQLDA'
      io_sqldabc   = sqlda_header_sz + 4*sqlvar_struct_sz
      io_sqln      = 4
      io_sqld      = 4
 
      io_sqltype1  = SQL_TYP_NCHAR
      io_sqllen1   = 10
      io_sqlind1   = 0
      rc = sqlgaddr(%ref(table_name), %ref(io_sqldata1))
      rc = sqlgaddr(%ref(tableind), %ref(io_sqlind1))
 
      io_sqltype2  = SQL_TYP_NCHAR
      io_sqllen2   = 20
      io_sqlind2   = 0
      rc = sqlgaddr(%ref(data_item0), %ref(io_sqldata2))
      rc = sqlgaddr(%ref(dataind0), %ref(io_sqlind2))
 
      io_sqltype3  = SQL_TYP_NCHAR
      io_sqllen3   = 20
      io_sqlind3   = 0
      rc = sqlgaddr(%ref(data_item1), %ref(io_sqldata3))
      rc = sqlgaddr(%ref(dataind1), %ref(io_sqlind3))
 
      io_sqltype4  = SQL_TYP_NCHAR
      io_sqllen4   = 20
      io_sqlind4   = 0
      rc = sqlgaddr(%ref(data_item2), %ref(io_sqldata4))
      rc = sqlgaddr(%ref(dataind2), %ref(io_sqlind4))
 
*     Call the Remote Procedure via CALL with SQLDA
* (2b)
      print *,'Use CALL with SQLDA to invoke the Server Procedure named
     cinpsrv.'
      EXEC SQL CALL :procname USING DESCRIPTOR :io_sqlda
      errloc = 'CALL with SQLDA'
      call checkerr (sqlca, errloc, *999)
      print *,'Server Procedure Complete.'
 
*     Disconnect from Remote Database.
      EXEC SQL CONNECT RESET
      errloc = 'CONNECT RESET'
      call checkerr (sqlca, errloc, *999)
999   stop
      end

How the Example Input-SQLDA Stored Procedure Works

Declare Server Procedure. The procedure accepts pointers to SQLDA and SQLCA structures.
Create Table. Using the data passed in the first SQLVAR of the SQLDA structure, a CREATE TABLE statement is constructed and executed to create a table named Presidents.
Prepare Insert Statement. An INSERT statement with a parameter marker ? is prepared.
Insert Data. The INSERT statement prepared previously is executed using the data passed in the second through fourth SQLVAR of the SQLDA structure. The parameter markers are replaced with the values Washington, Jefferson, and Lincoln. These values are inserted into the Presidents table.
Return to the Client Application. The server procedure copies the SQLCA to the SQLCA of the client application, issues a COMMIT statement if the transaction is successful, and returns the value SQLZ_DISCONNECT_PROC, indicating that no further calls to the server procedure will be made.

Note: Server procedures cannot be written in REXX on AIX systems.

REXX OS/2 Example: INPSRV.CMD

/* REXX INPut SeRVer */
 
/* this variable (SYSTEM) must be user defined */
SYSTEM = NT
if SYSTEM = NT then do
  if rxfuncquery('SQLDBS') <> 0 then
    rcy = rxfuncadd( 'SQLDBS',  'DB2AR', 'SQLDBS'  )
 
  if rxfuncquery('SQLEXEC') <> 0 then
    rcy = rxfuncadd( 'SQLEXEC', 'DB2AR', 'SQLEXEC' )
end
 
if SYSTEM = AIX then
  rcy = SysAddFuncPkg("/usr/lpp/db2_05_00/lib/db2rexx")
 
 
table_stmt = 'CREATE TABLE ' || sqlrida.1.sqldata ||  ' (name CHAR(20))' (2)
 
call SQLEXEC 'EXECUTE IMMEDIATE :table_stmt'
if sqlca.sqlcode = -601 then
  sqlca.sqlcode = 0
call CHECKERR 'EXECUTE IMMEDIATE CREATE TABLE'
 
insert_stmt = 'INSERT INTO ' || sqlrida.1.sqldata || ' VALUES (?)'
call SQLEXEC 'PREPARE s1 FROM :insert_stmt'  (3)
call CHECKERR 'PREPARE INSERT STATEMENT'
 
cntr = 1
do while ( cntr < SQLRIDA.sqld & sqlca.sqlcode = 0 )
   cntr        = cntr + 1
   insert_data = SQLRIDA.cntr.sqldata
   call SQLEXEC 'EXECUTE s1 USING :insert_data'  (4)
end
call CHECKERR 'EXECUTION OF INSERT STATEMENT'
 
call SQLEXEC 'COMMIT'
call CHECKERR 'COMMIT'
 
exit 0  (5)
 
 
CHECKERR:
  arg errloc
 
  if  ( SQLCA.SQLCODE = 0 ) then
    return 0
  else
    say '--- error report ---'
    say 'ERROR occured :' 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
      say 'WARNING - CONTINUING PROGRAM WITH ERRORS'
      return 0
    end
  end
return 0

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]