/*******************************************************************************
**
** Source File Name = tbmod.c
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 2000
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
** PURPOSE :
** Shows how to modify tables.
**
** For more information about these samples see the README file.
**
** For more information on programming in CLI see the:
** - "Building CLI Applications" section of the Application Building Guide, and the
** - CLI Guide and Reference.
**
** For more information on the SQL language see the SQL Reference.
**
*******************************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlcli1.h>
#include "utilcli.h" /* Header file for CLI sample code */
#include <sqlca.h>
int TbBasicInsert( SQLHANDLE);
int TbInsertWithParam( SQLHANDLE);
int TbBasicUpdate( SQLHANDLE);
int TbUpdateWithParam( SQLHANDLE);
int TbPositionedUpdateUsingCursor( SQLHANDLE);
int TbBasicDelete( SQLHANDLE);
int TbDeleteWithParam( SQLHANDLE);
int TbPositionedDeleteUsingCursor( SQLHANDLE);
SQLHANDLE henv; /* environment handle */
SQLHANDLE hdbc; /* connection handles */
/*******************************************************************
** main
*******************************************************************/
int main( int argc, char * argv[] )
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
char dbAlias[SQL_MAX_DSN_LENGTH + 1] ;
char user[MAX_UID_LENGTH + 1] ;
char pswd[MAX_PWD_LENGTH + 1] ;
/* checks the command line arguments */
rc = CmdLineArgsCheck1( argc, argv, dbAlias, user, pswd );
if ( rc != 0 ) return( rc ) ;
printf("\n\nTABLES: HOW TO MODIFY TABLES.\n");
/* initialize the CLI application */
rc = CLIAppInit( dbAlias, user, pswd, &henv, &hdbc,
(SQLPOINTER)SQL_AUTOCOMMIT_ON);
if ( rc != 0 ) return( rc ) ;
/* insert data in a table */
rc = TbBasicInsert( hdbc);
rc = TbInsertWithParam( hdbc);
/* update data in a table */
rc = TbBasicUpdate( hdbc);
rc = TbUpdateWithParam( hdbc);
rc = TbPositionedUpdateUsingCursor( hdbc);
/* delete data from a table */
rc = TbBasicDelete( hdbc);
rc = TbDeleteWithParam( hdbc);
rc = TbPositionedDeleteUsingCursor( hdbc);
/* terminate the CLI application */
rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
rc = CLIAppTerm( &henv, &hdbc, dbAlias);
return( rc ) ;
} /* end main */
/******************************************************************************
** TbBasicInsert - no more comments
******************************************************************************/
int TbBasicInsert( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt = ( SQLCHAR * )
"INSERT INTO org (deptnumb, location) "
"VALUES ( 120, 'Toronto'), ( 130, 'Vancouver'), ( 140, 'Ottawa')";
/*
strcpy((char *stmt
*/
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLExecDirect\n-SQLEndTran\n");
printf("-SQLFreeHandle\n");
printf("TO PERFORM A BASIC INSERT:\n");
/* set AUTOCOMMIT OFF */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf("\n Transactions enabled.\n");
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* execute directly the statement */
printf("\n Execute directly the statement\n");
printf(" INSERT INTO org( deptnumb, location)\n");
printf(" VALUES ( 120, 'Toronto'),\n");
printf(" ( 130, 'Vancouver'),\n");
printf(" ( 140, 'Ottawa')\n");
sqlrc = SQLExecDirect( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
printf(" Rolling back the transaction...\n");
sqlrc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK );
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf( " Transaction rolled back.\n");
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** TbInsertWithParam - no more comments
******************************************************************************/
int TbInsertWithParam( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt = ( SQLCHAR * )
"INSERT INTO org( deptnumb, location) "
"VALUES ( ?, ?)" ;
SQLSMALLINT parameter1[] = {
120, 130, 140
} ;
char * parameter2[] = {
"Toronto", "Vancouver", "Ottawa"
} ;
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLPrepare\n-SQLBindParameter\n");
printf("-SQLExecute\n-SQLEndTran\n-SQLFreeHandle\n");
printf("TO SHOW HOW TO EXECUTE AN INSERT STATEMENT\n");
printf("WITH PARAMETERS:\n");
/* set AUTOCOMMIT OFF */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf("\n Transactions enabled.\n");
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* prepare the statement */
printf(" Prepare the statement\n");
printf(" INSERT INTO org( deptnumb, location)\n");
printf(" VALUES ( ?, ?)\n");
sqlrc = SQLPrepare( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* bind parameter1 and parameter2 to the statement */
printf(" Bind parameter1 and parameter2 to the statement.\n");
sqlrc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SHORT,
SQL_SMALLINT, 0, 0, ¶meter1, 0, NULL);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, 20, 0, (SQLCHAR*)parameter2, 20, NULL);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* execute the statement for a set of values */
printf(" Execute the prepared statement for\n");
printf(" parameter1 = { 120, 130, 140}\n");
printf(" parameter2 = { 'Toronto', 'Vancouver', 'Ottawa'}\n");
sqlrc = SQLExecute( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
printf(" Rolling back the transaction...\n");
sqlrc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK );
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf( " Transaction rolled back.\n");
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** TbBasicUpdate - no more comments
******************************************************************************/
int TbBasicUpdate( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt = ( SQLCHAR * )
"UPDATE org "
"SET location = 'Toronto' "
"WHERE deptnumb < 50";
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLExecDirect\n-SQLEndTran\n");
printf("-SQLFreeHandle\n");
printf("TO PERFORM A BASIC UPDATE:\n");
/* set AUTOCOMMIT OFF */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf("\n Transactions enabled.\n");
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* execute directly the statement */
printf(" Execute directly the statement\n");
printf(" UPDATE org\n");
printf(" SET location = 'Toronto'\n");
printf(" WHERE deptnumb < 50\n");
sqlrc = SQLExecDirect( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
printf(" Rolling back the transaction...\n");
sqlrc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK );
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf( " Transaction rolled back.\n");
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** TbUpdateWithParam - no more comments
******************************************************************************/
int TbUpdateWithParam( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt = ( SQLCHAR * )
"UPDATE org "
"SET location = ? "
"WHERE deptnumb < ?";
SQLCHAR parameter1[20] ;
SQLSMALLINT parameter2 ;
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLPrepare\n-SQLBindParameter\n");
printf("-SQLExecute\n-SQLEndTran\n-SQLFreeHandle\n");
printf("TO SHOW HOW TO EXECUTE AN UPDATE STATEMENT\n");
printf("WITH PARAMETERS:\n");
/* set AUTOCOMMIT OFF */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf("\n Transactions enabled.\n");
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* prepare the statement */
printf(" Prepare the statement\n");
printf(" UPDATE org\n");
printf(" SET location = ? \n");
printf(" WHERE deptnumb < ?\n");
sqlrc = SQLPrepare( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* bind parameter1 and parameter2 to the statement */
printf(" Bind parameter1 and parameter2 to the statement.\n");
sqlrc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, 20, 0, parameter1, 20, NULL);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_SHORT,
SQL_SMALLINT, 0, 0, ¶meter2, 0, NULL);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* execute the statement for a set of values */
strcpy( (char*)parameter1, "Toronto");
parameter2 = 50;
printf(" Execute the prepared statement for\n");
printf(" parameter1 = %s\n", parameter1);
printf(" parameter2 = %d\n", parameter2);
sqlrc = SQLExecute( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
printf(" Rolling back the transaction...\n");
sqlrc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK );
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf( " Transaction rolled back.\n");
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** TbPositionedUpdateUsingCursor - no more comments
******************************************************************************/
int TbPositionedUpdateUsingCursor( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmtSelect ;
SQLHANDLE hstmtPositionedUpdate ;
SQLCHAR * stmtSelect = ( SQLCHAR * )
"SELECT * FROM org WHERE deptnumb < 50 FOR UPDATE";
SQLCHAR stmtPositionedUpdate[200];
SQLCHAR cursorName[20] ;
SQLSMALLINT cursorLen ;
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLExecDirect\n-SQLSetCursorName\n-SQLGetCursorName\n");
printf("-SQLFetch\n-SQLEndTran\n-SQLFreeHandle\n");
printf("TO SHOW HOW TO EXECUTE A POSITIONED UPDATE STATEMENT\n");
printf("USING THE CURSOR:\n");
/* set AUTOCOMMIT OFF */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf("\n Transactions enabled.\n");
/* allocate statement handles */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmtSelect ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmtPositionedUpdate ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* execute directly the select statement */
printf(" Execute directly\n");
printf(" %s\n", stmtSelect);
sqlrc = SQLExecDirect( hstmtSelect, stmtSelect, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmtSelect, sqlrc);
/* set the name of the cursor */
rc = SQLSetCursorName(hstmtSelect, (SQLCHAR *)"CURSNAME", SQL_NTS);
STMT_HANDLE_CHECK( hstmtSelect, sqlrc);
/* get the cursor name of the SELECT statement */
sqlrc = SQLGetCursorName( hstmtSelect,
cursorName, 20, &cursorLen ) ;
STMT_HANDLE_CHECK( hstmtSelect, sqlrc);
/* fetch each row, and update */
printf(" Fetch each row and update.\n");
sqlrc = SQLFetch( hstmtSelect );
STMT_HANDLE_CHECK( hstmtSelect, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
while (sqlrc != SQL_NO_DATA_FOUND)
{ sprintf((char *)stmtPositionedUpdate,
"UPDATE org SET location = 'Toronto' WHERE CURRENT of %s",
cursorName);
sqlrc = SQLExecDirect(hstmtPositionedUpdate,
stmtPositionedUpdate,
SQL_NTS);
STMT_HANDLE_CHECK( hstmtPositionedUpdate, sqlrc);
printf(" Row fetched and updated.\n");
sqlrc = SQLFetch( hstmtSelect );
STMT_HANDLE_CHECK( hstmtSelect, sqlrc);
}
printf(" Rolling back the transaction...\n");
sqlrc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK );
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf( " Transaction rolled back.\n");
/* free the statement handles */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmtSelect ) ;
STMT_HANDLE_CHECK( hstmtSelect, sqlrc);
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmtPositionedUpdate ) ;
STMT_HANDLE_CHECK( hstmtPositionedUpdate, sqlrc);
return(rc);
}
/******************************************************************************
** TbBasicDelete - no more comments
******************************************************************************/
int TbBasicDelete( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt = ( SQLCHAR * )
"DELETE FROM org WHERE deptnumb < 50";
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLExecDirect\n-SQLEndTran\n");
printf("-SQLFreeHandle\n");
printf("TO PERFORM A BASIC DELETE:\n");
/* set AUTOCOMMIT OFF */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf("\n Transactions enabled.\n");
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* execute directly the statement */
printf(" Execute directly the statement\n");
printf(" %s\n", stmt);
sqlrc = SQLExecDirect( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
printf(" Rolling back the transaction...\n");
sqlrc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK );
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf( " Transaction rolled back.\n");
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** TbDeleteWithParam - no more comments
******************************************************************************/
int TbDeleteWithParam( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt = ( SQLCHAR * )
"DELETE FROM org "
"WHERE deptnumb < ?";
SQLSMALLINT parameter1 ;
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLPrepare\n-SQLBindParameter\n");
printf("-SQLExecute\n-SQLEndTran\n-SQLFreeHandle\n");
printf("TO SHOW HOW TO EXECUTE A DELETE STATEMENT\n");
printf("WITH PARAMETERS:\n");
/* set AUTOCOMMIT OFF */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf("\n Transactions enabled.\n");
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* prepare the statement */
printf(" Prepare the statement\n");
printf(" %s\n", stmt);
sqlrc = SQLPrepare( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* bind parameter1 to the statement */
printf(" Bind parameter1 to the statement.\n");
sqlrc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SHORT,
SQL_SMALLINT, 0, 0, ¶meter1, 0, NULL);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* execute the statement for parameter1 = 50 */
parameter1 = 50;
printf(" Execute the prepared statement for\n");
printf(" parameter1 = %d\n", parameter1);
sqlrc = SQLExecute( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
printf(" Rolling back the transaction...\n");
sqlrc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK );
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf( " Transaction rolled back.\n");
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** TbPositionedDeleteUsingCursor - no more comments
******************************************************************************/
int TbPositionedDeleteUsingCursor( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmtSelect ;
SQLHANDLE hstmtPositionedDelete ;
SQLCHAR * stmtSelect = ( SQLCHAR * )
"SELECT * FROM org WHERE deptnumb < 50 FOR UPDATE";
SQLCHAR stmtPositionedDelete[200];
SQLCHAR cursorName[20] ;
SQLSMALLINT cursorLen ;
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLExecDirect\n-SQLGetCursorName\n");
printf("-SQLFetch\n-SQLEndTran\n-SQLFreeHandle\n");
printf("TO SHOW HOW TO EXECUTE A POSITIONED DELETE STATEMENT\n");
printf("USING THE CURSOR:\n");
/* set AUTOCOMMIT OFF */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf("\n Transactions enabled.\n");
/* allocate statement handles */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmtSelect ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmtPositionedDelete ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* execute directly the select statement */
printf(" Execute directly\n");
printf(" %s\n", stmtSelect);
sqlrc = SQLExecDirect( hstmtSelect, stmtSelect, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmtSelect, sqlrc);
/* get the cursor name of the SELECT statement */
sqlrc = SQLGetCursorName( hstmtSelect,
cursorName, 20, &cursorLen ) ;
STMT_HANDLE_CHECK( hstmtSelect, sqlrc);
/* fetch each row, and delete */
printf(" Fetch each row and update.\n");
sqlrc = SQLFetch( hstmtSelect );
STMT_HANDLE_CHECK( hstmtSelect, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
while (sqlrc != SQL_NO_DATA_FOUND)
{ sprintf((char *)stmtPositionedDelete,
"DELETE FROM org WHERE CURRENT of %s",
cursorName);
sqlrc = SQLExecDirect(hstmtPositionedDelete,
stmtPositionedDelete,
SQL_NTS);
STMT_HANDLE_CHECK( hstmtPositionedDelete, sqlrc);
printf(" Row fetched and deleted.\n");
sqlrc = SQLFetch( hstmtSelect );
STMT_HANDLE_CHECK( hstmtSelect, sqlrc);
}
printf(" Rolling back the transaction...\n");
sqlrc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK );
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf( " Transaction rolled back.\n");
/* free the statement handles */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmtSelect ) ;
STMT_HANDLE_CHECK( hstmtSelect, sqlrc);
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmtPositionedDelete ) ;
STMT_HANDLE_CHECK( hstmtPositionedDelete, sqlrc);
return(rc);
}