Db2 ODBC ストアード・プロシージャーを呼び出すクライアント・アプリケーション

クライアント・アプリケーション、APD29 は、ストアード・プロシージャー、SPD29 を呼び出し、照会の結果セットを処理します。ストアード・プロシージャー内で照会カーソルがオープンし、 クライアントにフェッチ結果を返します。

Db2® オブジェクトを定義し、この例を使用するプロシージャーを定義するため に、CREATE TABLE、データ INSERT、および CREATE PROCEDURE ステートメントが準備されています。
STEP 1. Create table
 
     printf("¥nAPDDL SQLExecDirect  stmt=
    strcpy((char *)sqlstmt,
 "CREATE TABLE TABLE2A (INT4 INTEGER,SMINT SMALLINT,FLOAT8 FLOAT");
    strcat((char *)sqlstmt,
 ",DEC312 DECIMAL(31,2),CHR10 CHARACTER(10),VCHR20 VARCHAR(20)");
    strcat((char *)sqlstmt,
 ",LVCHR LONG VARCHAR,CHRSB CHAR(10),CHRBIT CHAR(10) FOR BIT DATA");
    strcat((char *)sqlstmt,
 ",DDATE DATE,TTIME TIME,TSTMP TIMESTAMP)");
    printf("¥nAPDDL sqlstmt=
    rc=SQLExecDirect(hstmt,sqlstmt,SQL_NTS);
    if( rc != SQL_SUCCESS ) goto dberror;
 
STEP 2. Insert 101 rows into table
 
    /* insert 100 rows into table2a */
    for (jx=1;jx<=100 ;jx++ ) {
      printf("¥nAPDIN SQLExecDirect  stmt=
      strcpy((char *)sqlstmt,"insert into table2a values(");
      sprintf((char *)sqlstmt+strlen((char *)sqlstmt),"
      strcat((char *)sqlstmt,
 ",4,8.2E+30,1515151515151.51,'CHAR','VCHAR','LVCCHAR','SBCS'");
      strcat((char *)sqlstmt,
 ",'MIXED','01/01/1991','3:33 PM','1999-09-09-09.09.09.090909')");
      printf("¥nAPDIN sqlstmt=
      rc=SQLExecDirect(hstmt,sqlstmt,SQL_NTS);
      if( rc != SQL_SUCCESS ) goto dberror;
    } /* endfor */
 
STEP 3. Define stored procedure with CREATE
PROCEDURE SQL statement
 
  CREATE PROCEDURE SPD29
  (INOUT INTEGER)
  PROGRAM TYPE MAIN
  EXTERNAL NAME SPD29
  COLLID DSNAOCLI
  LANGUAGE C
  RESULT SET 2
  MODIFIES SQL DATA
  PARAMETER STYLE GENERAL
  WLM ENVIRONMENT WLMENV1;   
 
STEP 4. Stored procedure
 
  /*START OF SPD29****************************************************/
   /* PRAGMA TO CALL PLI SUBRTN CSPSUB TO ISSUE CONSOLE MSGS        */
   #pragma options (rent)
   #pragma runopts(plist(os))
   /******************************************************************/
   /* Include the 'C' include files                                  */
   /******************************************************************/
   #include <stdio.h>
   #include <string.h>
   #include <stdlib.h>
   #include "sqlcli1.h"
   #include <sqlca.h>
   #include <decimal.h>
   #include <wcstr.h>
   /******************************************************************/
   /* Variables for COMPARE routines                                 */
   /******************************************************************/
#ifndef NULL
#define NULL   0
#endif
 
    SQLHENV henv = SQL_NULL_HENV;
    SQLHDBC hdbc = SQL_NULL_HDBC;
    SQLHSTMT hstmt = SQL_NULL_HSTMT;
    SQLHSTMT hstmt2 = SQL_NULL_HSTMT;
    SQLRETURN rc = SQL_SUCCESS;
    SQLINTEGER      id;
    SQLCHAR         name[51];
    SQLINTEGER      namelen, intlen, colcount;
    SQLSMALLINT     scale;
    struct sqlca    sqlca;
    SQLCHAR         server[18];
    SQLCHAR         uid[30];
    SQLCHAR         pwd[30];
    SQLCHAR         sqlstmt[500];
    SQLCHAR         sqlstmt2[500];
    SQLSMALLINT     pcpar=0;
    SQLSMALLINT     pccol=0;
    SQLCHAR         cursor[19];
    SQLSMALLINT     cursor_len;
 
    SQLINTEGER    SPCODE;
    struct {
      SQLSMALLINT LEN;
      SQLCHAR     DATA_200¨; }         STMTSQL;
 
    SQLSMALLINT             H1SMINT;
    SQLINTEGER              H1INT4;
    SQLDOUBLE               H1FLOAT8;
    SQLDOUBLE               H1DEC312;
    SQLCHAR                 H1CHR10[11];
    SQLCHAR                 H1VCHR20[21];
    SQLCHAR                 H1LVCHR[21];
    SQLCHAR                 H1CHRSB[11];
    SQLCHAR                 H1CHRBIT[11];
    SQLCHAR                 H1DDATE[11];
    SQLCHAR                 H1TTIME[9];
    SQLCHAR                 H1TSTMP[27];
   SQLSMALLINT                 I1SMINT;
   SQLSMALLINT                 I1INT4;
   SQLSMALLINT                 I1FLOAT8;
   SQLSMALLINT                 I1DEC312;
   SQLSMALLINT                 I1CHR10;
   SQLSMALLINT                 I1VCHR20;
   SQLSMALLINT                 I1LVCHR;
   SQLSMALLINT                 I1CHRSB;
   SQLSMALLINT                 I1CHRBIT;
   SQLSMALLINT                 I1DDATE;
   SQLSMALLINT                 I1TTIME;
   SQLSMALLINT                 I1TSTMP;
 
   SQLINTEGER                  LEN_H1SMINT;
   SQLINTEGER                  LEN_H1INT4;
   SQLINTEGER                  LEN_H1FLOAT8;
   SQLINTEGER                  LEN_H1DEC312;
   SQLINTEGER                  LEN_H1CHR10;
   SQLINTEGER                  LEN_H1VCHR20;
   SQLINTEGER                  LEN_H1LVCHR;
   SQLINTEGER                  LEN_H1CHRSB;
   SQLINTEGER                  LEN_H1CHRBIT;
   SQLINTEGER                  LEN_H1DDATE;
   SQLINTEGER                  LEN_H1TTIME;
   SQLINTEGER                  LEN_H1TSTMP;
 
   SQLSMALLINT                 H2SMINT;
   SQLINTEGER                  H2INT4;
   SQLDOUBLE                   H2FLOAT8;
   SQLCHAR                     H2CHR10[11];
   SQLCHAR                     H2VCHR20[21];
   SQLCHAR                     H2LVCHR[21];
   SQLCHAR                     H2CHRSB[11];
   SQLCHAR                     H2CHRBIT[11];
   SQLCHAR                     H2DDATE[11];
   SQLCHAR                     H2TTIME[9];
   SQLCHAR                     H2TSTMP[27];
 
   SQLSMALLINT                 I2SMINT;
   SQLSMALLINT                 I2INT4;
   SQLSMALLINT                 I2FLOAT8;
   SQLSMALLINT                 I2CHR10;
   SQLSMALLINT                 I2VCHR20;
   SQLSMALLINT                 I2LVCHR;
   SQLSMALLINT                 I2CHRSB;
   SQLSMALLINT                 I2CHRBIT;
   SQLSMALLINT                 I2DDATE;
   SQLSMALLINT                 I2TTIME;
   SQLSMALLINT                 I2TSTMP;
 
   SQLINTEGER                  LEN_H2SMINT;
   SQLINTEGER                  LEN_H2INT4;
   SQLINTEGER                  LEN_H2FLOAT8;
   SQLINTEGER                  LEN_H2CHR10;
   SQLINTEGER                  LEN_H2VCHR20;
   SQLINTEGER                  LEN_H2LVCHR;
   SQLINTEGER                  LEN_H2CHRSB;
   SQLINTEGER                  LEN_H2CHRBIT;
   SQLINTEGER                  LEN_H2DDATE;
   SQLINTEGER                  LEN_H2TTIME;
   SQLINTEGER                  LEN_H2TSTMP;
   SQLCHAR locsite[18] =  "stlec1";
   SQLCHAR remsite[18] =  "stlec1b";
 
   SQLCHAR    spname[8];
   SQLINTEGER    ix,jx,locix;
   SQLINTEGER     result;
   SQLCHAR    state_blank[6] ="     ";
 
SQLRETURN
check_error(SQLHENV henv,
            SQLHDBC hdbc,
            SQLHSTMT hstmt,
            SQLRETURN frc);
SQLRETURN
prt_sqlca();
   /******************************************************************/
   /* Main Program                                                   */
   /******************************************************************/
SQLINTEGER
main(SQLINTEGER argc, SQLCHAR *argv[] )
{
 printf("¥nSPD29 INITIALIZATION");
 scale = 0;
 rc=0;
 
 rc=0;
 SPCODE=0;
 
 /* argv0 = sp module name */
 if (argc != 2)
  {
   printf("SPD29 parm number error¥n   ");
   printf("SPD29 EXPECTED =
   printf("SPD29 received =
   goto dberror;
  }
  strcpy((char *)spname,(char *)argv[0]);
  result = strncmp((char *)spname,"SPD29",5);
  if (result != 0)
   {
    printf("SPD29 argv0 sp name  error¥n   ");
    printf("SPD29 compare result =
    printf("SPD29 expected =
    printf("SPD29 received spname=
    printf("SPD29 received argv0 =0]);
    goto dberror;
   }
  /* get input spcode value */
  SPCODE        = *(SQLINTEGER *)  argv[1];
 printf("¥nSPD29 SQLAllocEnv        number=     1¥n");
 henv=0;
 rc = SQLAllocEnv(&henv);
 if( rc != SQL_SUCCESS ) goto dberror;
 printf("¥nSPD29-henv=
 /*****************************************************************/
 printf("¥nSPD29 SQLAllocConnect                             ");
 hdbc=0;
 SQLAllocConnect(henv, &hdbc);
 if( rc != SQL_SUCCESS ) goto dberror;
 printf("¥nSPD29-hdbc=
 /*****************************************************************/
 /* Make sure no autocommits after cursors are allocated, commits */
 /* cause sp failure. AUTOCOMMIT=0 could also be specified in the */
 /* INI file.                                                     */
 /* Also, sp could be defined with COMMIT_ON_RETURN in the        */
 /* DB2 catalog table SYSIBM.SYSROUTINES,   but be wary that this */
 /* removes control from the client appl to control commit scope. */
 /*****************************************************************/
 printf("¥nSPD29 SQLSetConnectOption-no autocommits in stored procs");
 rc = SQLSetConnectOption(hdbc,SQL_AUTOCOMMIT,SQL_AUTOCOMMIT_OFF);
 if( rc != SQL_SUCCESS ) goto dberror;
 /*****************************************************************/
 printf("¥nSPD29 SQLConnect  NULL connect in stored proc      ");
 strcpy((char *)uid,"cliuser");
 strcpy((char *)pwd,"password");
 printf("¥nSPD29 server=
 printf("¥nSPD29 uid=
 printf("¥nSPD29 pwd=
 rc=SQLConnect(hdbc, NULL, 0, uid, SQL_NTS, pwd, SQL_NTS);
 if( rc != SQL_SUCCESS ) goto dberror;
 /*****************************************************************/
 /* Start SQL statements ******************************************/
 /*****************************************************************/
 switch(SPCODE)
 {
  /****************************************************************/
  /* CASE(SPCODE=0) do nothing and return                     *****/
  /****************************************************************/
   case 0:
     break;
   case 1:
  /****************************************************************/
  /* CASE(SPCODE=1)                                           *****/
  /*  -sqlprepare/sqlexecute insert int4=200                  *****/
  /*  -sqlexecdirect         insert int4=201                  *****/
  /* *validated in client appl that inserts occur             *****/
  /****************************************************************/
     SPCODE=0;
 
     printf("¥nSPD29 SQLAllocStmt                                ¥n");
     hstmt=0;
     rc=SQLAllocStmt(hdbc, &hstmt);
     if( rc != SQL_SUCCESS ) goto dberror;
     printf("¥nSPD29-hstmt=
 
     printf("¥nSPD29 SQLPrepare                                   ¥n");
     strcpy((char *)sqlstmt,
     "insert into TABLE2A(int4) values(?)");
     printf("¥nSPD29 sqlstmt=
     rc=SQLPrepare(hstmt,sqlstmt,SQL_NTS);
     if( rc != SQL_SUCCESS ) goto dberror;
 
     printf("¥nSPD29 SQLNumParams                                ¥n");
     rc=SQLNumParams(hstmt,&pcpar);
     if( rc != SQL_SUCCESS) goto dberror;
     if (pcpar!=1) {
        printf("¥nSPD29 incorrect pcpar=
        goto dberror;
       }
     printf("¥nSPD29 SQLBindParameter   int4                       ¥n");
     H1INT4=200;
     LEN_H1INT4=sizeof(H1INT4);
     rc=SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_LONG,
     SQL_INTEGER,0,0,&H1INT4,0,(SQLINTEGER *)&LEN_H1INT4);
     if( rc != SQL_SUCCESS) goto dberror;
 
     printf("¥nSPD29 SQLExecute                                  ¥n");
     rc=SQLExecute(hstmt);
     if( rc != SQL_SUCCESS) goto dberror;
 
     printf("¥nSPD29  SQLFreeStmt                                 ¥n");
     rc=SQLFreeStmt(hstmt, SQL_DROP);
     if( rc != SQL_SUCCESS ) goto dberror;
  /*****************************************************************/
    printf("¥nAPDIN SQLAllocStmt   stmt=
    hstmt=0;
    rc=SQLAllocStmt(hdbc, &hstmt);
    if( rc != SQL_SUCCESS ) goto dberror;
    printf("¥nAPDIN-hstmt=
 
    jx=201;
    printf("¥nAPDIN SQLExecDirect  stmt=
    strcpy((char *)sqlstmt,"insert into table2a values(");
    sprintf((char *)sqlstmt+strlen((char *)sqlstmt),"
    strcat((char *)sqlstmt,
    ",4,8.2E+30,1515151515151.51,'CHAR','VCHAR','LVCCHAR','SBCS'");
      strcat((char *)sqlstmt,
    ",'MIXED','01/01/1991','3:33 PM','1999-09-09-09.09.09.090909')");
    printf("¥nAPDIN sqlstmt=
    rc=SQLExecDirect(hstmt,sqlstmt,SQL_NTS);
    if( rc != SQL_SUCCESS ) goto dberror;
 
    break;
  /*****************************************************************/
   case 2:
  /****************************************************************/
  /* CASE(SPCODE=2)                                           *****/
  /*  -sqlprepare/sqlexecute select int4  from table2a        *****/
  /*  -sqlprepare/sqlexecute select chr10 from table2a        *****/
  /* *qrs cursors should be allocated and left open by CLI    *****/
  /****************************************************************/
     SPCODE=0;
 
  /* generate 1st query result set  */
    printf("¥nSPD29 SQLAllocStmt                                ¥n");
    hstmt=0;
    rc=SQLAllocStmt(hdbc, &hstmt);
    if( rc != SQL_SUCCESS ) goto dberror;
    printf("¥nSPD29-hstmt=
 
    printf("¥nSPD29 SQLPrepare                                   ¥n");
    strcpy((char *)sqlstmt,
     "SELECT INT4 FROM TABLE2A");
    printf("¥nSPD29 sqlstmt=
    rc=SQLPrepare(hstmt,sqlstmt,SQL_NTS);
    if( rc != SQL_SUCCESS ) goto dberror;
 
    printf("¥nSPD29 SQLExecute                                  ¥n");
    rc=SQLExecute(hstmt);
    if( rc != SQL_SUCCESS ) goto dberror;
  /* allocate 2nd stmt handle for 2nd queryresultset */
  /* generate 2nd  queryresultset  */
    printf("¥nSPD29 SQLAllocStmt                                ¥n");
    hstmt=0;
    rc=SQLAllocStmt(hdbc, &hstmt2);
    if( rc != SQL_SUCCESS ) goto dberror;
    printf("¥nSPD29-hstmt2=
 
    printf("¥nSPD29 SQLPrepare                                   ¥n");
    strcpy((char *)sqlstmt2,
     "SELECT CHR10 FROM TABLE2A");
    printf("¥nSPD29 sqlstmt2=
    rc=SQLPrepare(hstmt2,sqlstmt2,SQL_NTS);
    if( rc != SQL_SUCCESS ) goto dberror;
 
    printf("¥nSPD29 SQLExecute                                  ¥n");
    rc=SQLExecute(hstmt2);
    if( rc != SQL_SUCCESS ) goto dberror;
 
    /*leave queryresultset cursor open for fetch back at client appl */
 
    break;
  /****************************************************************/
   default:
    {
     printf("SPD29 INPUT SPCODE INVALID¥n");
     printf("SPD29...EXPECTED SPCODE=0-2¥n");
     printf("SPD29...RECEIVED SPCODE=
     goto dberror;
     break;
     }
 }
 /*****************************************************************/
 /* End SQL statements ********************************************/
 /*****************************************************************/
 /*Be sure NOT to put a SQLTransact with SQL_COMMIT in a DB2 or   */
 /*  z/OS stored procedure.  Commit is not allowed in a DB2 or    */
 /*  z/OS stored procedure.  Use SQLTransact with SQL_ROLLBACK to */
 /*  force a must rollback condition for this sp and calling      */
 /*  client application.                                          */
 /*****************************************************************/
 printf("¥nSPD29 SQLDisconnect      number=     4¥n");
 rc=SQLDisconnect(hdbc);
 if( rc != SQL_SUCCESS ) goto dberror;
 /*****************************************************************/
 printf("¥nSPD29 SQLFreeConnect     number=     5¥n");
 rc = SQLFreeConnect(hdbc);
 if( rc != SQL_SUCCESS ) goto dberror;
 /*****************************************************************/
 printf("¥nSPD29 SQLFreeEnv         number=     6¥n");
 rc = SQLFreeEnv(henv);
 if( rc != SQL_SUCCESS ) goto dberror;
 /*****************************************************************/
 goto pgmend;
 
 dberror:
 printf("¥nSPD29 entry dberror label");
 printf("¥nSPD29 rc=
 check_error(henv,hdbc,hstmt,rc);
 printf("¥nSPD29 SQLFreeEnv         number=     7¥n");
 rc = SQLFreeEnv(henv);
 printf("¥nSPD29 rc=
 rc=12;
 rc=12;
 SPCODE=12;
 goto pgmend;
 
 pgmend:
 
 printf("¥nSPD29  TERMINATION   ");
 if (rc!=0)
  {
  printf("¥nSPD29 WAS NOT SUCCESSFUL");
  printf("¥nSPD29 SPCODE  = 
  printf("¥nSPD29 rc          = 
  }
 else
 {
  printf("¥nSPD29 WAS SUCCESSFUL");
 }
 /* assign output spcode value */
 *(SQLINTEGER  *) argv[1] = SPCODE;
 exit;
}  /*END MAIN*/
/*******************************************************************
** check_error - call print_error(), checks severity of return code
*******************************************************************/
SQLRETURN
check_error(SQLHENV henv,
            SQLHDBC hdbc,
            SQLHSTMT hstmt,
            SQLRETURN frc )
{
    SQLCHAR         buffer[SQL_MAX_MESSAGE_LENGTH + 1];
    SQLCHAR         cli_sqlstate[SQL_SQLSTATE_SIZE + 1];
    SQLINTEGER     cli_sqlcode;
    SQLSMALLINT   length;
 
    printf("¥nSPD29 entry check_error rtn");
 
    switch (frc) {
    case SQL_SUCCESS:
        break;
    case SQL_INVALID_HANDLE:
        printf("¥nSPD29 check_error> SQL_INVALID HANDLE ");
    case SQL_ERROR:
        printf("¥nSPD29 check_error> SQL_ERROR ");
        break;
    case SQL_SUCCESS_WITH_INFO:
        printf("¥nSPD29 check_error>  SQL_SUCCESS_WITH_INFO");
        break;
    case SQL_NO_DATA_FOUND:
        printf("¥nSPD29 check_error> SQL_NO_DATA_FOUND ");
        break;
    default:
        printf("¥nSPD29 check_error> Invalid rc from api rc=
        break;
    } /*end switch*/
    printf("¥nSPD29 SQLError  ");
    while ((rc=SQLError(henv, hdbc, hstmt, cli_sqlstate, &cli_sqlcode,
      buffer,SQL_MAX_MESSAGE_LENGTH + 1, &length)) == SQL_SUCCESS) {
        printf("         SQLSTATE: 
        printf("Native Error Code: 
        printf("
    };
    if (rc!=SQL_NO_DATA_FOUND)
      printf("SQLError api call failed rc=
 
    printf("¥nSPD29 SQLGetSQLCA  ");
    rc = SQLGetSQLCA(henv, hdbc, hstmt, &sqlca);
    if( rc == SQL_SUCCESS )
      prt_sqlca();
    else
      printf("¥n  SPD29-check_error SQLGetSQLCA failed rc=
 
    return (frc);
}
  /*******************************************************************/
  /*                  P r i n t   S Q L C A                          */
  /*******************************************************************/
SQLRETURN
  prt_sqlca()
  {
    SQLINTEGER i;
    printf("¥nlSPD29 entry prts_sqlca rtn");
    printf("¥r¥r*** Printing the SQLCA:¥r");
    printf("¥nSQLCAID .... 
    printf("¥nSQLCABC .... 
    printf("¥nSQLCODE .... 
    printf("¥nSQLERRML ... 
    printf("¥nSQLERRMC ... 
    printf("¥nSQLERRP  ... 
    for (i = 0; i < 6; i++)
      printf("¥nSQLERRD
    for (i = 0; i < 10; i++)
      printf("¥nSQLWARNi]);
    printf("¥nSQLWARNA ... 10]);
    printf("¥nSQLSTATE ... 
 
    return(0);
  }                                               /* End of prtsqlca */
  /*******************************************************************/
  /*END OF SPD29 *****************************************************/
STEP 5. Client application
 /********************************************************************/
 /*START OF SPD29*****************************************************/
 /*  SCEANRIO PSEUDOCODE:                                            */
 /*  APD29(CLI CODE CLIENT APPL)                                     */
 /*       -CALL SPD29 (CLI CODE STORED PROCEDURE APPL)                */
 /*         -SPCODE=0                                                */
 /*             -PRINTF MSGS (CHECK SDSF FOR SPAS ADDR TO VERFIFY)   */
 /*         -SPCODE=1                                                */
 /*             -PRINTF MSGS (CHECK SDSF FOR SPAS ADDR TO VERFIFY)   */
 /*             -SQLPREPARE/EXECUTE INSERT INT4=200                  */
 /*             -SQLEXECDIRECT      INSERT INT4=201                  */
 /*         -SPCODE=2                                                */
 /*             -PRINTF MSGS (CHECK SDSF FOR SPAS ADDR TO VERFIFY)   */
 /*             -SQLPREPARE/EXECUTE SELECT INT4 FROM TABLE2A         */
 /*             -SQLPREPARE/EXECUTE SELECT CHR10 FROM TABLE2A        */
 /*              (CLI CURSORS OPENED 'WITH RETURN')...               */
 /*         -RETURN                                                  */
 /*       -FETCH QRS FROM SP CURSOR                                  */
 /*       -COMMIT                                                    */
 /*       -VERFIFY INSERTS BY SPD29                                  */
 /********************************************************************/
   /* Include the 'C' include files                                  */
   /******************************************************************/
   #include <stdio.h>
   #include <string.h>
   #include <stdlib.h>
   #include "sqlcli1.h"
   #include <sqlca.h>
   /******************************************************************/
   /* Variables for COMPARE routines                                 */
   /******************************************************************/
#ifndef NULL
#define NULL   0
#endif
 
    SQLHENV henv = SQL_NULL_HENV;
    SQLHDBC hdbc = SQL_NULL_HDBC;
    SQLHSTMT hstmt = SQL_NULL_HSTMT;
    SQLRETURN rc = SQL_SUCCESS;
    SQLINTEGER      id;
    SQLCHAR         name[51];
    SQLINTEGER      namelen, intlen, colcount;
    SQLSMALLINT     scale;
    struct sqlca    sqlca;
    SQLCHAR   server[18];
    SQLCHAR   uid[30];
    SQLCHAR   pwd[30];
    SQLCHAR   sqlstmt[250];
    SQLSMALLINT  pcpar=0;
    SQLSMALLINT  pccol=0;
 
   SQLINTEGER    SPCODE;
   struct {
     SQLSMALLINT LEN;
     SQLCHAR  DATA[200]; }         STMTSQL;
   SQLSMALLINT                 H1SMINT;
   SQLINTEGER                  H1INT4;
   SQLDOUBLE               H1FLOAT8;
   SQLDOUBLE               H1DEC312;
   SQLCHAR                  H1CHR10[11];
   SQLCHAR                  H1VCHR20[21];
   SQLCHAR                  H1LVCHR[21];
   SQLCHAR                  H1CHRSB[11];
   SQLCHAR                  H1CHRBIT[11];
   SQLCHAR                  H1DDATE[11];
   SQLCHAR                  H1TTIME[9];
   SQLCHAR                  H1TSTMP[27];
 
   SQLSMALLINT                 I1SMINT;
   SQLSMALLINT                 I1INT4;
   SQLSMALLINT                 I1FLOAT8;
   SQLSMALLINT                 I1DEC312;
   SQLSMALLINT                 I1CHR10;
   SQLSMALLINT                 I1VCHR20;
   SQLSMALLINT                 I1LVCHR;
   SQLSMALLINT                 I1CHRSB;
   SQLSMALLINT                 I1CHRBIT;
   SQLSMALLINT                 I1DDATE;
   SQLSMALLINT                 I1TTIME;
   SQLSMALLINT                 I1TSTMP;
 
   SQLINTEGER                  LNH1SMINT;
   SQLINTEGER                  LNH1INT4;
   SQLINTEGER                  LNH1FLOAT8;
   SQLINTEGER                  LNH1DEC312;
   SQLINTEGER                  LNH1CHR10;
   SQLINTEGER                  LNH1VCHR20;
   SQLINTEGER                  LNH1LVCHR;
   SQLINTEGER                  LNH1CHRSB;
   SQLINTEGER                  LNH1CHRBIT;
   SQLINTEGER                  LNH1DDATE;
   SQLINTEGER                  LNH1TTIME;
   SQLINTEGER                  LNH1TSTMP;
 
   SQLSMALLINT                 H2SMINT;
   SQLINTEGER                  H2INT4;
   SQLDOUBLE                H2FLOAT8;
   SQLCHAR                  H2CHR10[11];
   SQLCHAR                  H2VCHR20[21];
   SQLCHAR                  H2LVCHR[21];
   SQLCHAR                  H2CHRSB[11];
   SQLCHAR                  H2CHRBIT[11];
   SQLCHAR                  H2DDATE[11];
   SQLCHAR                  H2TTIME[9];
   SQLCHAR                  H2TSTMP[27];
 
   SQLSMALLINT                 I2SMINT;
   SQLSMALLINT                 I2INT4;
   SQLSMALLINT                 I2FLOAT8;
   SQLSMALLINT                 I2CHR10;
   SQLSMALLINT                 I2VCHR20;
   SQLSMALLINT                 I2LVCHR;
   SQLSMALLINT                 I2CHRSB;
   SQLSMALLINT                 I2CHRBIT;
   SQLSMALLINT                 I2DDATE;
   SQLSMALLINT                 I2TTIME;
   SQLSMALLINT                 I2TSTMP;
   SQLINTEGER                  LNH2SMINT;
   SQLINTEGER                  LNH2INT4;
   SQLINTEGER                  LNH2FLOAT8;
   SQLINTEGER                  LNH2CHR10;
   SQLINTEGER                  LNH2VCHR20;
   SQLINTEGER                  LNH2LVCHR;
   SQLINTEGER                  LNH2CHRSB;
   SQLINTEGER                  LNH2CHRBIT;
   SQLINTEGER                  LNH2DDATE;
   SQLINTEGER                  LNH2TTIME;
   SQLINTEGER                  LNH2TSTMP;
 
   SQLCHAR locsite[18] =  "stlec1";
   SQLCHAR remsite[18] =  "stlec1b";
 
   SQLINTEGER    ix,jx,locix;
   SQLINTEGER     result;
   SQLCHAR    state_blank[6] ="     ";
 
SQLRETURN
check_error(SQLHENV henv,
            SQLHDBC hdbc,
            SQLHSTMT hstmt,
            SQLRETURN frc);
SQLRETURN
prt_sqlca();
   /******************************************************************/
   /* Main Program                                                   */
   /******************************************************************/
SQLINTEGER
main()
{
    printf("¥nAPD29 INITIALIZATION");
    scale = 0;
    rc=0;
 
    printf("¥nAPD29 SQLAllocEnv   stmt=
    henv=0;
    rc = SQLAllocEnv(&henv);
    if( rc != SQL_SUCCESS ) goto dberror;
    printf("¥nAPD29-henv=
 
for (locix=1;locix<=2;locix++)
{
 /* Start SQL statements ******************************************/
 /*****************************************************************/
    printf("¥nAPD29 SQLAllocConnect                             ");
    hdbc=0;
    SQLAllocConnect(henv, &hdbc);
    if( rc != SQL_SUCCESS ) goto dberror;
    printf("¥nAPD29-hdbc=
 /*****************************************************************/
    printf("¥nAPD29 SQLConnect                                  ");
    if (locix == 1)
    {
     strcpy((char *)server,(char *)locsite);
    }
    else
     {
      strcpy((char *)server,(char *)remsite);
     }
    strcpy((char *)uid,"cliuser");
    strcpy((char *)pwd,"password");
    printf("¥nAPD29 server=
    printf("¥nAPD29 uid=
    printf("¥nAPD29 pwd=
    rc=SQLConnect(hdbc, server, SQL_NTS, uid, SQL_NTS, pwd, SQL_NTS);
    if( rc != SQL_SUCCESS ) goto dberror;
 /*****************************************************************/
 /* CASE(SPCODE=0)  QRS RETURNED=0  COL=0  ROW=0                  */
 /*****************************************************************/
 printf("¥nAPD29 SQLAllocStmt   stmt=
 hstmt=0;
 rc=SQLAllocStmt(hdbc, &hstmt);
 if( rc != SQL_SUCCESS ) goto dberror;
 printf("¥nAPD29-hstmt=
 
 SPCODE=0;
 printf("¥nAPD29 call sp SPCODE =
 printf("¥nAPD29 SQLPrepare  stmt=
 strcpy((char*)sqlstmt,"CALL SPD29(?)");
 printf("¥nAPD29 sqlstmt=
 rc=SQLPrepare(hstmt,sqlstmt,SQL_NTS);
 if( rc != SQL_SUCCESS ) goto dberror;
 
 printf("¥nAPD29 SQLBindParameter  stmt=
 rc = SQLBindParameter(hstmt,
                       1,
                       SQL_PARAM_INPUT_OUTPUT,
                       SQL_C_LONG,
                       SQL_INTEGER,
                       0,
                       0,
                       &SPCODE,
                       0,
                       NULL);
 if( rc != SQL_SUCCESS ) goto dberror;
 
 printf("¥nAPD29 SQLExecute  stmt=
 rc=SQLExecute(hstmt);
 if( rc != SQL_SUCCESS ) goto dberror;
 if( SPCODE != 0 )
 {
   printf("¥nAPD29 SPCODE not zero, spcode=
   goto dberror;
 }
 
 printf("¥nAPD29 SQLTransact stmt=
 rc=SQLTransact(henv, hdbc, SQL_COMMIT);
 if( rc != SQL_SUCCESS ) goto dberror;
 
 printf("¥nAPD29  SQLFreeStmt stmt=
 rc=SQLFreeStmt(hstmt, SQL_DROP);
 if( rc != SQL_SUCCESS ) goto dberror;
 /*****************************************************************/
 /* CASE(SPCODE=1)  QRS RETURNED=0  COL=0  ROW=0                  */
 /*****************************************************************/
 printf("¥nAPD29 SQLAllocStmt   stmt=
 hstmt=0;
 rc=SQLAllocStmt(hdbc, &hstmt);
 if( rc != SQL_SUCCESS ) goto dberror;
 printf("¥nAPD29-hstmt=
 SPCODE=1;
 printf("¥nAPD29 call sp SPCODE =
 printf("¥nAPD29 SQLPrepare  stmt=
 strcpy((char*)sqlstmt,"CALL SPD29(?)");
 printf("¥nAPD29 sqlstmt=
 rc=SQLPrepare(hstmt,sqlstmt,SQL_NTS);
 if( rc != SQL_SUCCESS ) goto dberror;
 
 printf("¥nAPD29 SQLBindParameter  stmt=
 rc = SQLBindParameter(hstmt,
                       1,
                       SQL_PARAM_INPUT_OUTPUT,
                       SQL_C_LONG,
                       SQL_INTEGER,
                       0,
                       0,
                       &SPCODE,
                       0,
                       NULL);
 if( rc != SQL_SUCCESS ) goto dberror;
 
 printf("¥nAPD29 SQLExecute  stmt=
 rc=SQLExecute(hstmt);
 if( rc != SQL_SUCCESS ) goto dberror;
 if( SPCODE != 0 )
 {
   printf("¥nAPD29 SPCODE not zero, spcode=
   goto dberror;
 }
 
 printf("¥nAPD29 SQLTransact stmt=
 rc=SQLTransact(henv, hdbc, SQL_COMMIT);
 if( rc != SQL_SUCCESS ) goto dberror;
 
 printf("¥nAPD29  SQLFreeStmt stmt=
 rc=SQLFreeStmt(hstmt, SQL_DROP);
 if( rc != SQL_SUCCESS ) goto dberror;
 /*****************************************************************/
 /* CASE(SPCODE=2)  QRS RETURNED=2  COL=1(int4/chr10)  ROW=100+   */
 /*****************************************************************/
 printf("¥nAPD29 SQLAllocStmt         number=   18¥n");
 hstmt=0;
 rc=SQLAllocStmt(hdbc, &hstmt);
 if( rc != SQL_SUCCESS ) goto dberror;
 printf("¥nAPD29-hstmt=
 
 SPCODE=2;
 printf("¥nAPD29 call sp SPCODE =
 printf("¥nAPD29 SQLPrepare           number=   19¥n");
 strcpy((char*)sqlstmt,"CALL SPD29(?)");
 printf("¥nAPD29 sqlstmt=
 rc=SQLPrepare(hstmt,sqlstmt,SQL_NTS);
 if( rc != SQL_SUCCESS ) goto dberror;
 printf("¥nAPD29 SQLBindParameter     number=   20¥n");
 rc = SQLBindParameter(hstmt,
                       1,
                       SQL_PARAM_INPUT_OUTPUT,
                       SQL_C_LONG,
                       SQL_INTEGER,
                       0,
                       0,
                       &SPCODE,
                       0,
                       NULL);
 if( rc != SQL_SUCCESS ) goto dberror;
 
 printf("¥nAPD29 SQLExecute           number=   21¥n");
 rc=SQLExecute(hstmt);
 if( rc != SQL_SUCCESS ) goto dberror;
 if( SPCODE != 0 )
 {
   printf("¥nAPD29 spcode incorrect");
   goto dberror;
 }
 
  printf("¥nAPD29 SQLNumResultCols     number=   22¥n");
  rc=SQLNumResultCols(hstmt,&pccol);
  if (pccol!=1)
  {
    printf("APD29 col count wrong=
    goto dberror;
  }
 
 printf("¥nAPD29 SQLBindCol           number=   23¥n");
 rc=SQLBindCol(hstmt,
               1,
               SQL_C_LONG,
               (SQLPOINTER) &H1INT4,
               (SQLINTEGER)sizeof(SQLINTEGER),
               (SQLINTEGER *) &LNH1INT4       );
 if( rc != SQL_SUCCESS ) goto dberror;
 
 jx=0;
 printf("¥nAPD29 SQLFetch             number=   24¥n");
 while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS)
 {
  jx++;
  printf("¥nAPD29 fetch loop jx =
  if ( (H1INT4<=0) ||  (H1INT4>=202)
       || (LNH1INT4!=4 && LNH1INT4!=-1)  )
   { /* data error */
     printf("¥nAPD29 H1INT4=
     printf("¥nAPD29 LNH1INT4=
     goto dberror;
   }
  printf("¥nAPD29 SQLFetch             number=   24¥n");
 } /* end while loop */
 
 if( rc != SQL_NO_DATA_FOUND )
 {
   printf("¥nAPD29 invalid end of data¥n");
   goto dberror;
 }
 printf("¥nAPD29 SQLMoreResults       number=   25¥n");
 rc=SQLMoreResults(hstmt);
 if(rc != SQL_SUCCESS) goto dberror;
 
 printf("¥nAPD29 SQLNumResultCols     number=   26¥n");
 rc=SQLNumResultCols(hstmt,&pccol);
 if (pccol!=1) {
   printf("APD29 col count wrong=
   goto dberror;
 }
 
 printf("¥nAPD29 SQLBindCol           number=   27¥n");
 rc=SQLBindCol(hstmt,
               1,
               SQL_C_CHAR,
               (SQLPOINTER) H1CHR10,
               (SQLINTEGER)sizeof(H1CHR10),
               (SQLINTEGER *) &LNH1CHR10       );
 if( rc != SQL_SUCCESS ) goto dberror;
 
 jx=0;
 while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS)
 {
  jx++;
  printf("¥nAPD29 fetch loop jx =
   result=strcmp((char *)H1CHR10,"CHAR      ");
   if ( (result!=0)
       || (LNH1INT4!=4 && LNH1INT4!=-1)  )
   {
     printf("¥nAPD29 H1CHR10=
     printf("¥nAPD29 result=
     printf("¥nAPD29 LNH1CHR10=
     printf("¥nAPD29 strlen(H1CHR10)=
     goto dberror;
   }
  printf("¥nAPD29 SQLFetch             number=   24¥n");
 } /* end while loop */
 
 if( rc != SQL_NO_DATA_FOUND )
   goto dberror;
 
 printf("¥nAPD29 SQLMoreResults       number=   29¥n");
 rc=SQLMoreResults(hstmt);
 if( rc != SQL_NO_DATA_FOUND) goto dberror;
 
 printf("¥nAPD29 SQLTransact          number=   30¥n");
 rc=SQLTransact(henv, hdbc, SQL_COMMIT);
 if( rc != SQL_SUCCESS ) goto dberror;
 
 printf("¥nAPD29  SQLFreeStmt         number=   31¥n");
 rc=SQLFreeStmt(hstmt, SQL_DROP);
 if( rc != SQL_SUCCESS ) goto dberror;
 /*****************************************************************/
    printf("¥nAPD29 SQLDisconnect  stmt=
    rc=SQLDisconnect(hdbc);
    if( rc != SQL_SUCCESS ) goto dberror;
 /*****************************************************************/
    printf("¥nSQLFreeConnect  stmt=
    rc=SQLFreeConnect(hdbc);
    if( rc != SQL_SUCCESS ) goto dberror;
  /*****************************************************************/
  /* End SQL statements ********************************************/
} /* end for each site perform these stmts */
 
for (locix=1;locix<=2;locix++)
{
 /*****************************************************************/
    printf("¥nAPD29 SQLAllocConnect                             ");
    hdbc=0;
    SQLAllocConnect(henv, &hdbc);
    if( rc != SQL_SUCCESS ) goto dberror;
    printf("¥nAPD29-hdbc=
 /*****************************************************************/
    printf("¥nAPD29 SQLConnect                                  ");
    if (locix == 1)
    {
     strcpy((char *)server,(char *)locsite);
    }
    else
     {
      strcpy((char *)server,(char *)remsite);
     }
 
    strcpy((char *)uid,"cliuser");
    strcpy((char *)pwd,"password");
    printf("¥nAPD29 server=
    printf("¥nAPD29 uid=
    printf("¥nAPD29 pwd=
    rc=SQLConnect(hdbc, server, SQL_NTS, uid, SQL_NTS, pwd, SQL_NTS);
    if( rc != SQL_SUCCESS ) goto dberror;
 /*****************************************************************/
 /* Start validate SQL statements *********************************/
 /*****************************************************************/
    printf("¥nAPD01 SQLAllocStmt                                 ¥n");
    hstmt=0;
    rc=SQLAllocStmt(hdbc, &hstmt);
    if( rc != SQL_SUCCESS ) goto dberror;
    printf("¥nAPD01-hstmt=
 
    printf("¥nAPD01 SQLExecDirect                                ¥n");
    strcpy((char *)sqlstmt,
    "SELECT INT4 FROM TABLE2A WHERE INT4=200");
    printf("¥nAPD01 sqlstmt=
    rc=SQLExecDirect(hstmt,sqlstmt,SQL_NTS);
    if( rc != SQL_SUCCESS ) goto dberror;
 
    printf("¥nAPD01 SQLBindCol                                   ¥n");
    rc=SQLBindCol(hstmt,
                  1,
                  SQL_C_LONG,
                  (SQLPOINTER) &H1INT4,;
                  (SQLINTEGER)sizeof(SQLINTEGER),
                  (SQLINTEGER *) &LNH1INT4       );
    if( rc != SQL_SUCCESS ) goto dberror;
 
    printf("¥nAPD01 SQLFetch                                     ¥n");
    rc=SQLFetch(hstmt);
    if( rc != SQL_SUCCESS ) goto dberror;
    if ((H1INT4!=200) || (LNH1INT4!=4))
     {
     printf("¥nAPD01 H1INT4=
     printf("¥nAPD01 LNH1INT4=
     goto dberror;
     }
    printf("¥nAPD01 SQLTransact                                  ¥n");
    rc=SQLTransact(henv, hdbc, SQL_COMMIT);
    if( rc != SQL_SUCCESS ) goto dberror;
 
    printf("¥nAPD01  SQLFreeStmt                                 ¥n");
    rc=SQLFreeStmt(hstmt, SQL_CLOSE);
    if( rc != SQL_SUCCESS ) goto dberror;
 
    printf("¥nAPD01 SQLExecDirect                                ¥n");
    strcpy((char *)sqlstmt,
    "SELECT INT4 FROM TABLE2A WHERE INT4=201");
    printf("¥nAPD01 sqlstmt=
    rc=SQLExecDirect(hstmt,sqlstmt,SQL_NTS);
    if( rc != SQL_SUCCESS ) goto dberror;
 
    printf("¥nAPD01 SQLFetch                                     ¥n");
    rc=SQLFetch(hstmt);
    if( rc != SQL_SUCCESS ) goto dberror;
    if ((H1INT4!=201) || (LNH1INT4!=4))
     {
     printf("¥nAPD01 H1INT4=
     printf("¥nAPD01 LNH1INT4=
     goto dberror;
     }
 
    printf("¥nAPD01 SQLTransact                                  ¥n");
    rc=SQLTransact(henv, hdbc, SQL_COMMIT);
    if( rc != SQL_SUCCESS ) goto dberror;
 
    printf("¥nAPD01  SQLFreeStmt                                 ¥n");
    rc=SQLFreeStmt(hstmt, SQL_DROP);
    if( rc != SQL_SUCCESS ) goto dberror;
 /*****************************************************************/
 /* End validate SQL statements ***********************************/
 /*****************************************************************/
    printf("¥nAPD29 SQLDisconnect  stmt=
    rc=SQLDisconnect(hdbc);
    if( rc != SQL_SUCCESS ) goto dberror;
 /*****************************************************************/
    printf("¥nSQLFreeConnect  stmt=
    rc=SQLFreeConnect(hdbc);
    if( rc != SQL_SUCCESS ) goto dberror;
 
} /* end for each site perform these stmts */
 /*****************************************************************/
    printf("¥nSQLFreeEnv  stmt=
    rc=SQLFreeEnv(henv);
    if( rc != SQL_SUCCESS ) goto dberror;
 /*****************************************************************/
 goto pgmend;
 
 dberror:
 printf("¥nAPD29 entry dberror label");
 printf("¥nAPD29 rc=
 check_error(henv,hdbc,hstmt,rc);
 printf("¥nAPDXX SQLFreeEnv         number=     6¥n");
 rc=SQLFreeEnv(henv);
 printf("¥nAPDXX FREEENV rc =
 rc=12;
 printf("¥nAPDXX DBERROR set rc =
 goto pgmend;
 pgmend:
     printf("¥nAPD29  TERMINATION   ");
     if (rc!=0)
      {
      printf("¥nAPD29 WAS NOT SUCCESSFUL");
      printf("¥nAPD29 SPCODE  = 
      printf("¥nAPD29 rc          = 
      }
     else
      printf("¥nAPD29 WAS SUCCESSFUL");
 
     return(rc);
 
}  /*END MAIN*/
/*******************************************************************
** check_error - call print_error(), checks severity of return code
*******************************************************************/
SQLRETURN
check_error(SQLHENV henv,
            SQLHDBC hdbc,
            SQLHSTMT hstmt,
            SQLRETURN frc )
{
 
   SQLCHAR         buffer_SQL_MAX_MESSAGE_LENGTH + 1¨;
    SQLCHAR         cli_sqlstate_SQL_SQLSTATE_SIZE + 1¨;
    SQLINTEGER     cli_sqlcode;
    SQLSMALLINT   length;
 
    printf("¥nAPD29 entry check_error rtn");
 
    switch (frc) {
    case SQL_SUCCESS:
        break;
    case SQL_INVALID_HANDLE:
        printf("¥nAPD29 check_error> SQL_INVALID HANDLE ");
    case SQL_ERROR:
        printf("¥nAPD29 check_error> SQL_ERROR ");
        break;
    case SQL_SUCCESS_WITH_INFO:
        printf("¥nAPD29 check_error>  SQL_SUCCESS_WITH_INFO");
        break;
    case SQL_NO_DATA_FOUND:
        printf("¥nAPD29 check_error> SQL_NO_DATA_FOUND ");
        break;
    default:
        printf("¥nAPD29 check_error> Invalid rc from api rc=
        break;
    } /*end switch*/
 
    printf("¥nAPD29 SQLError  ");
    while ((rc=SQLError(henv, hdbc, hstmt, cli_sqlstate, &cli_sqlcode,
      buffer,SQL_MAX_MESSAGE_LENGTH + 1, &length)) == SQL_SUCCESS) {
        printf("         SQLSTATE: 
        printf("Native Error Code: 
        printf("
    };
    if (rc!=SQL_NO_DATA_FOUND)
      printf("SQLError api call failed rc=
    printf("¥nAPD29 SQLGetSQLCA  ");
    rc = SQLGetSQLCA(henv, hdbc, hstmt, &sqlca);
    if( rc == SQL_SUCCESS )
      prt_sqlca();
    else
      printf("¥n  APD29-check_error SQLGetSQLCA failed rc=
 
    return (frc);
}
  /*******************************************************************/
  /*                  P r i n t   S Q L C A                          */
  /*******************************************************************/
SQLRETURN
  prt_sqlca()
  {
    SQLINTEGER i;
    printf("¥nlAPD29 entry prts_sqlca rtn");
    printf("¥r¥r*** Printing the SQLCA:¥r");
    printf("¥nSQLCAID .... 
    printf("¥nSQLCABC .... 
    printf("¥nSQLCODE .... 
    printf("¥nSQLERRML ... 
    printf("¥nSQLERRMC ... 
    printf("¥nSQLERRP  ... 
    for (i = 0; i < 6; i++)
      printf("¥nSQLERRD
    for (i = 0; i < 10; i++)
      printf("¥nSQLWARNi]);
    printf("¥nSQLWARNA ... 10]);
    printf("¥nSQLSTATE ... 
 
    return(0);
  }                                               /* End of prtsqlca */
  /*END OF APD29******************************************************/