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******************************************************/