The following example executes a compound statement consisting of 4 sub-statements to insert rows into a new AWARDS table.
/* From CLI sample compnd.c */
/* ... */
SQLCHAR * stmt[] = {
"INSERT INTO awards (id, award) "
"SELECT id, 'Sales Merit' from staff "
"WHERE job = 'Sales' AND (comm/100 > years)",
"INSERT INTO awards (id, award) "
"SELECT id, 'Clerk Merit' from staff "
"WHERE job = 'Clerk' AND (comm/50 > years)",
"INSERT INTO awards (id, award) "
"SELECT id, 'Best ' concat job FROM STAFF "
"WHERE comm = (SELECT max(comm) FROM staff WHERE job = 'Clerk')",
"INSERT INTO awards (id, award) "
"SELECT id, 'Best ' concat job FROM STAFF "
"WHERE comm = (SELECT max(comm) FROM STAFF WHERE job = 'Sales')",
} ;
SQLINTEGER i ;
/* ... */
/* Prepare 4 substatements */
for ( i = 1; i < 4; i++ ) {
rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &cmhstmt[i] ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
rc = SQLPrepare( cmhstmt[i], stmt[i], SQL_NTS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, cmhstmt[i], rc ) ;
}
rc = SQLExecDirect( hstmt,
( SQLCHAR * ) "BEGIN COMPOUND NOT ATOMIC STATIC",
SQL_NTS
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Execute 4 substatements */
for ( i = 1; i < 4; i++ ) {
rc = SQLExecute( cmhstmt[i] ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, cmhstmt[i], rc ) ;
}
/* Execute the COMPOUND statement (of 4 sub-statements) */
printf( "Executing the COMPOUND statement (of 4 sub-statements)\n" ) ;
rc = SQLExecDirect( hstmt,
( SQLCHAR * ) "END COMPOUND COMMIT",
SQL_NTS
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
for ( i = 1; i < 4; i++ ) {
rc = SQLFreeHandle( SQL_HANDLE_STMT, cmhstmt[i] ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, cmhstmt[i], rc ) ;
}