Client application calling a Db2 ODBC stored procedure
The client application, APD29, calls the stored procedure, SPD29, and processes query result sets. A query cursor opens in a stored procedure and returns to the client for fetching.
The CREATE TABLE, data INSERT,
and CREATE PROCEDURE statements are provided to define the Db2 objects and procedures that
this example uses.
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******************************************************/