ODBC 両方向スクロール・カーソルの例
この ODBC プログラムは、 両方向スクロール・カーソルを使用して結果セット内を前後に移動する方法の例を示しています。
/******************************************************************/
/* Include the 'C' include files */
/******************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include "sqlcli1.h"
/******************************************************************/
/* Variables */
/******************************************************************/
#ifndef NULL
#define NULL 0
#endif
SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc = SQL_NULL_HDBC;
SQLHDBC hstmt= SQL_NULL_HSTMT;
SQLRETURN rc = SQL_SUCCESS;
SQLINTEGER i,j,id;
SQLCHAR name[51];
SQLINTEGER namelen, intlen, colcount;
struct sqlca sqlca;
SQLCHAR server[18];
SQLCHAR uid[30];
SQLCHAR pwd[30];
SQLCHAR sqlstmt[500];
SQLINTEGER H1INT4;
SQLCHAR H1CHR10[11];
SQLINTEGER LNH1INT4;
SQLINTEGER LNH1CHR10;
SQLINTEGER output_nts,autocommit,cursor_hold;
// scrollable cursors
#define ROWSET_SIZE 10
SQLUINTEGER numrowsfetched;
SQLUSMALLINT rowStatus[ROWSET_SIZE];
static char ROWSTATVALUE[][26] = { "SQL_ROW_SUCCESS", \
"SQL_ROW_SUCCESS_WITH_INFO", \
"SQL_ROW_ERROR", \
"SQL_ROW_NOROW" };
// column-wise binding
SQLINTEGER SH1INT4[ROWSET_SIZE];
SQLCHAR SH1CHR10[ROWSET_SIZE][11];
SQLINTEGER SLNH1CHR10[ROWSET_SIZE];
SQLRETURN check_error(SQLSMALLINT,SQLHANDLE,SQLRETURN,int,char *);
SQLRETURN print_error(SQLSMALLINT,SQLHANDLE,SQLRETURN,int,char *);
SQLRETURN prt_sqlca(void);
#define CHECK_HANDLE( htype, hndl, rc ) if ( rc != SQL_SUCCESS ) ¥
{check_error(htype,hndl,rc,__LINE__,__FILE__);goto dberror;}
/******************************************************************/
/* Main Program */
/******************************************************************/
int main()
{
printf("APDLX INITIALIZATION¥n");
//*********************************************************************
printf("APDLX SQLAllocHandle-Environment¥n");
henv=0;
rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
CHECK_HANDLE( SQL_HANDLE_ENV, henv, rc );
printf("APDLX-henv=%i¥n",henv);
//*********************************************************************
printf("APDLX SQLAllocHandle-Connection¥n");
hdbc=0;
rc=SQLAllocHandle( SQL_HANDLE_DBC, henv, &hdbc);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc );
printf("APDLX-hdbc=%i¥n",hdbc);
//*********************************************************************
printf("APDLX SQLConnect¥n");
strcpy((char *)uid,"sysadm");
strcpy((char *)pwd,"sysadm");
strcpy((char *)server,"stlec1"); //uwo setting
rc=SQLConnect(hdbc, server, SQL_NTS, uid, SQL_NTS, pwd, SQL_NTS);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc );
printf("APDLX successfully issued a SQLconnect¥n");
//*********************************************************************
printf("APDLX SQLAllocHandle-Statement¥n");
hstmt=0;
rc=SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("APDLX hstmt=%i¥n",hstmt);
printf("APDLX successfully issued a SQLAllocStmt¥n");
/* Set the number of rows in the rowset */
printf("APDLX SQLSetStmtAttr¥n");
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_ARRAY_SIZE,
(SQLPOINTER) ROWSET_SIZE,
0);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* Set the cursor type */
printf("APDLX SQLSetStmtAttr¥n");
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_CURSOR_TYPE,
(SQLPOINTER) SQL_CURSOR_STATIC,
0);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* Set the pointer to the variable numrowsfetched: */
printf("APDLX SQLSetStmtAttr¥n");
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROWS_FETCHED_PTR,
&numrowsfetched,
0);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* Set pointer to the row status array */
printf("APDLX SQLSetStmtAttr¥n");
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_STATUS_PTR,
(SQLPOINTER) rowStatus,
0);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("APDLX SQLExecDirect¥n");
strcpy((char *)sqlstmt,"SELECT INT4,CHR10 FROM TABLE2A");
printf("APDLX sqlstmt=%s¥n",sqlstmt);
rc=SQLExecDirect(hstmt,sqlstmt,SQL_NTS);
printf("APDLX rc=%i¥n",rc);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("APDLX SQLColAttributes¥n");
colcount=-1;
rc=SQLColAttributes(hstmt,
0,
SQL_COLUMN_COUNT,
NULL,
0,
NULL,
&colcount);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
if(colcount!=2)
{
printf("¥nAPDLX colcount=%i¥n",colcount);
goto dberror;
}
printf("APDLX SQLBindCol¥n");
H1INT4=-1;
LNH1INT4=-1;
rc=SQLBindCol(hstmt,
1,
SQL_C_LONG,
(SQLPOINTER) SH1INT4,
(SQLINTEGER)sizeof(H1INT4),
(SQLINTEGER *) &LNH1INT4);
if( rc != SQL_SUCCESS ) goto dberror;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("APDLX SQLBindCol¥n");
strcpy(H1CHR10,"garbage");
LNH1CHR10=-1;
rc=SQLBindCol(hstmt,
2,
SQL_C_DEFAULT,
(SQLPOINTER) SH1CHR10,
11,
SLNH1CHR10 );
if( rc != SQL_SUCCESS ) goto dberror;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("¥nUse Column-Wise Binding to demonstrate SQLFetchScroll():¥n");
printf("¥nINT4 CHAR10 ¥n");
printf("-------- -------------- ¥n");
printf("APDLX SQLFetchScroll FIRST ¥n");
rc = SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 0);
/* Indicate how many rows were in the result set. */
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("(%i rows in rowset). ***¥n", numrowsfetched);
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %14s¥n", SH1INT4[i], SH1CHR10[i]);
}
/* Output the Row Status Array if the complete rowset was not returned. */
if (numrowsfetched != ROWSET_SIZE) {
printf(" Previous rowset was not full, here is the Row Status Array:¥n");
for (i = 0; i < ROWSET_SIZE; i++)
printf(" Row Status Array[%i] = %s¥n", i, ROWSTATVALUE[rowStatus[i]]);
}
printf("APDLX SQLFetchScroll NEXT ¥n");
rc = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);
/* Indicate how many rows were in the result set. */
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("(%i rows in rowset). ***¥n", numrowsfetched);
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %14s¥n", SH1INT4[i], SH1CHR10[i]);
}
/* Output the Row Status Array if the complete rowset was not returned. */
if (numrowsfetched != ROWSET_SIZE) {
printf(" Previous rowset was not full, here is the Row Status Array:¥n");
for (i = 0; i < ROWSET_SIZE; i++)
printf(" Row Status Array[%i] = %s¥n", i, ROWSTATVALUE[rowStatus[i]]);
}
printf("APDLX SQLFetchScroll SQL_FETCH_ABSOLUTE 3 ¥n");
rc = SQLFetchScroll(hstmt, SQL_FETCH_ABSOLUTE, 3);
/* Indicate how many rows were in the result set. */
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("(%i rows in rowset). ***¥n", numrowsfetched);
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %14s¥n", SH1INT4[i], SH1CHR10[i]);
}
/* Output the Row Status Array if the complete rowset was not returned. */
if (numrowsfetched != ROWSET_SIZE) {
printf(" Previous rowset was not full, here is the Row Status Array:¥n");
for (i = 0; i < ROWSET_SIZE; i++)
printf(" Row Status Array[%i] = %s¥n", i, ROWSTATVALUE[rowStatus[i]]);
}
printf("APDLX SQLFetchScroll SQL_FETCH_RELATIVE -1 ¥n");
rc = SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1);
/* Indicate how many rows were in the result set. */
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("(%i rows in rowset). ***¥n", numrowsfetched);
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %14s¥n", SH1INT4[i], SH1CHR10[i]);
}
/* Output the Row Status Array if the complete rowset was not returned. */
if (numrowsfetched != ROWSET_SIZE) {
printf(" Previous rowset was not full, here is the R;ow Status Array:¥n");
for (i = 0; i < ROWSET_SIZE; i++)
printf(" Row Status Array[%i] = %s¥n", i, ROWSTATVALUE[rowStatus[i]]);
}
printf("APDLX SQLFetchScroll SQL_FETCH_FIRST again ¥n");
rc = SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 0);
printf("rc=%d¥n", rc);
/* Indicate how many rows were in the result set. */
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("(%i rows in rowset). ***¥n", numrowsfetched);
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %14s¥n", SH1INT4[i], SH1CHR10[i]);
}
/* Output the Row Status Array if the complete rowset was not returned. */
if (numrowsfetched != ROWSET_SIZE) {
printf(" Previous rowset was not full, here is the Row Status Array:¥n");
for (i = 0; i < ROWSET_SIZE; i++)
printf(" Row Status Array[%i] = %s¥n", i, ROWSTATVALUE[rowStatus[i]]);
}
printf("APDLX SQLFetchScroll SQL_FETCH_NEXT to EOF ¥n");
for (j = 0; j < 2; j++) {
printf("APDLX SQLFetchScroll NEXT ¥n");
rc = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);
printf("rc=%d¥n", rc);
/* Indicate how many rows were in the result set. */
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("(%i rows in rowset). ***¥n", numrowsfetched);
for (i = 0; i < numrowsfetched; i++)
printf("%8ld %14s¥n", SH1INT4[i], SH1CHR10[i]);
/* Output the Row Status Array if the complete rowset was not returned. */
if (numrowsfetched != ROWSET_SIZE) {
printf(" Previous rowset was not full, here is the Row Status Array:¥n");
for (i = 0; i < ROWSET_SIZE; i++)
printf(" Row Status Array[%i] = %s¥n", i, ROWSTATVALUE[rowStatus[i]]);
}
} // end for
printf("APDLX SQLFreeHandle-Statement¥n");
rc=SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
hstmt=0;
printf("APDLX successfully issued a SQLFreeStmt¥n");
printf("APDLX SQLEndTran-Commit¥n");
rc=SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("APDLX successfully issued a SQLTransact¥n");
/******** SQLDisconnect ******************************************/
printf("APDLX SQLDisconnect¥n");
rc=SQLDisconnect(hdbc);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc );
printf("APDLX successfully issued a SQLDisconnect¥n");
/******** SQLFreeConnect *****************************************/
printf("APDLX SQLFreeHandle-Connection¥n");
rc=SQLFreeHandle(SQL_HANDLE_DBC,hdbc);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc );
hdbc=0;;
printf("APDLX successfully issued a SQLFreeConnect¥n");
/******** SQLFreeEnv *********************************************/
printf("APDLX SQLFreeHandle-Environment¥n");
rc=SQLFreeHandle(SQL_HANDLE_ENV,henv);
CHECK_HANDLE( SQL_HANDLE_ENV, henv, rc );
henv=0;
printf("APDLX successfully issued a SQLFreeEnv¥n");
pgmend:
printf("APDLX pgmend: Ending sample¥n");
if (rc==0)
printf("APDLX Execution was SUCCESSFUL¥n");
else
{
printf("APDLX***************************¥n");
printf("APDLX Execution FAILED¥n");
printf("APDLX rc = %i¥n", rc );
printf("APDLX ***************************¥n");
}
return(rc);
dberror:
printf("APDLX dberror: entry dberror rtn¥n");
printf("APDLX dberror: rc=%d¥n",rc);
printf("APDLX dberror: environment cleanup attempt¥n");
printf("APDLX dberror: cleanup SQLFreeEnv¥n");
rc=SQLFreeEnv(henv);
printf("APDLX dberror: cleanup SQLFreeEnv rc =%d¥n",rc);
rc=12;
printf("APDLX dberror: setting error rc=%d¥n",rc);
goto pgmend;
} /*END MAIN*/
/******************************************************************/
/* check_error */
/******************************************************************/
/* RETCODE values from sqlcli.h ***************************/
/*#define SQL_SUCCESS 0 ***************************/
/*#define SQL_SUCCESS_WITH_INFO 1 ***************************/
/*#define SQL_NO_DATA_FOUND 100 ***************************/
/*#define SQL_NEED_DATA 99 ***************************/
/*#define SQL_NO_DATA SQL_NO_DATA_FOUND **************/
/*#define SQL_STILL_EXECUTING 2 not currently returned*/
/*#define SQL_ERROR -1 ***************************/
/*#define SQL_INVALID_HANDLE -2 ***************************/
/******************************************************************/
SQLRETURN check_error( SQLSMALLINT htype, /* A handle type */
SQLHANDLE hndl, /* A handle */
SQLRETURN frc, /* Return code */
int line, /* Line error issued */
char * file /* file error issued */
) {
SQLCHAR cli_sqlstate[SQL_SQLSTATE_SIZE + 1];
SQLINTEGER cli_sqlcode;
SQLSMALLINT length;
printf("APDLX entry check_error rtn¥n");
switch (frc) {
case SQL_SUCCESS:
break;
case SQL_INVALID_HANDLE:
printf("APDLX check_error> SQL_INVALID HANDLE ¥n");
break;
case SQL_ERROR:
printf("APDLX check_error> SQL_ERROR¥n");
break;
case SQL_SUCCESS_WITH_INFO:
printf("APDLX check_error> SQL_SUCCESS_WITH_INFO¥n");
break;
case SQL_NO_DATA_FOUND:
printf("APDLX check_error> SQL_NO_DATA_FOUND¥n");
break;
default:
printf("APDLX check_error> Received rc from api rc=%i¥n",frc);
break;
} /*end switch*/
print_error(htype,hndl,frc,line,file);
printf("APDLX SQLGetSQLCA¥n");
rc = SQLGetSQLCA(henv, hdbc, hstmt, &sqlca);
if( rc == SQL_SUCCESS )
prt_sqlca();
else
printf("APDLX check_error SQLGetSQLCA failed rc=%i¥n",rc);
printf("APDLX exit check_error rtn¥n");
return (frc);
} /* end check_error */
/******************************************************************/
/* print_error */
/* calls SQLGetDiagRec()displays SQLSTATE and message */
/******************************************************************/
SQLRETURN print_error( SQLSMALLINT htype, /* A handle type */
SQLHANDLE hndl, /* A handle */
SQLRETURN frc, /* Return code */
int line, /* error from line */
char * file /* error from file */
) {
SQLCHAR buffer[SQL_MAX_MESSAGE_LENGTH + 1] ;
SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1] ;
SQLINTEGER sqlcode ;
SQLSMALLINT length, i ;
SQLRETURN prc;
printf("APDLX entry print_error rtn¥n");
printf("APDLX rc=%d reported from file:%s,line:%d ---¥n",
frc,
file,
line
) ;
i = 1 ;
while ( SQLGetDiagRec( htype,
hndl,
i,
sqlstate,
&sqlcode,
buffer,
SQL_MAX_MESSAGE_LENGTH + 1,
&length
) == SQL_SUCCESS ) {
printf( "APDLX SQLSTATE: %s¥n", sqlstate ) ;
printf( "APDLX Native Error Code: %ld¥n", sqlcode ) ;
printf( "APDLX buffer: %s ¥n", buffer ) ;
i++ ;
}
printf( ">--------------------------------------------------¥n" ) ;
printf("APDLX exit print_error rtn¥n");
return( SQL_ERROR ) ;
} /* end print_error */
/******************************************************************/
/* prt_sqlca */
/******************************************************************/
SQLRETURN
prt_sqlca()
{
int i;
printf("APDLX entry prt_sqlca rtn¥n");
printf("¥r¥rAPDLX*** Printing the SQLCA:¥r");
printf("¥nAPDLX SQLCAID .... %s",sqlca.sqlcaid);
printf("¥nAPDLX SQLCABC .... %d",sqlca.sqlcabc);
printf("¥nAPDLX SQLCODE .... %d",sqlca.sqlcode);
printf("¥nAPDLX SQLERRML ... %d",sqlca.sqlerrml);
printf("¥nAPDLX SQLERRMC ... %s",sqlca.sqlerrmc);
printf("¥nAPDLX SQLERRP ... %s",sqlca.sqlerrp);
for (i = 0; i < 6; i++)
printf("¥nAPDLX SQLERRD%d ... %d",i+1,sqlca.sqlerrd??(i??));
for (i = 0; i < 10; i++)
printf("¥nAPDLX SQLWARN%d ... %c",i,sqlca.sqlwarn[i]);
printf("¥nAPDLX SQLWARNA ... %c",sqlca.sqlwarn[10]);
printf("¥nAPDLX SQLSTATE ... %s",sqlca.sqlstate);
printf("¥nAPDLX exit prt_sqlca rtn¥n");
return(0);
} /* End of prt_sqlca */