示例：交互式 SQL 和等价的 DB2 UDB CLI 函数调用
示例：交互式 SQL 和等价的 DB2 UDB CLI 函数调用
此示例显示交互式 SQL 语句的执行，并遵循编写 DB2 UDB CLI 应用程序中描述的流程。
有关代码示例的信息，请参阅代码免责信息。
/*************************************************************************** file = typical.c**** Example of executing interactive SQL statements, displaying result sets** and simple transaction management.****Functions used:**** SQLAllocConnect SQLFreeConnect** SQLAllocEnv SQLFreeEnv** SQLAllocStmt SQLFreeStmt** SQLConnect SQLDisconnect**** SQLBindCol SQLFetch** SQLDescribeCol SQLNumResultCols** SQLError SQLRowCount** SQLExecDirect SQLTransact****************************************************************************/ #include <stdlib.h>#include <stdio.h>#include <string.h>#include "sqlcli.h" #define MAX_STMT_LEN 255#defineMAXCOLS 100 #definemax(a,b) (a > b ? a : b) int initialize(SQLHENV *henv, SQLHDBC *hdbc); int process_stmt(SQLHENV henv, SQLHDBC hdbc, SQLCHAR *sqlstr); int terminate(SQLHENV henv, SQLHDBC hdbc); int print_error(SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt); int check_error(SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt, SQLRETURNfrc); void display_results(SQLHSTMT hstmt, SQLSMALLINT nresultcols); /********************************************************************* main** - initialize** - start a transaction** - get statement** - another statement?** - COMMIT or ROLLBACK** - another transaction?** - terminate*******************************************************************/int main(){ SQLHENV henv; SQLHDBC hdbc; SQLCHAR sqlstmt=""; SQLCHAR sqltrans; SQLRETURN rc; rc = initialize(&henv, &hdbc); if (rc == SQL_ERROR) return(terminate(henv, hdbc)); printf("Enter an SQL statement to start a transaction(or 'q' to Quit):\n"); gets(sqlstmt); while (sqlstmt !='q') { while (sqlstmt != 'q') { rc = process_stmt(henv, hdbc, sqlstmt); if (rc == SQL_ERROR) return(SQL_ERROR); printf("Enter an SQL statement(or 'q' to Quit):\n"); gets(sqlstmt); } printf("Enter 'c' to COMMIT or 'r' to ROLLBACK the transaction\n"); fgets(sqltrans, sizeof("ROLLBACK"), stdin); if (sqltrans == 'c') { rc = SQLTransact (henv, hdbc, SQL_COMMIT); if (rc == SQL_SUCCESS) printf ("Transaction commit was successful\n"); else check_error (henv, hdbc, SQL_NULL_HSTMT, rc); } if (sqltrans == 'r') { rc = SQLTransact (henv, hdbc, SQL_ROLLBACK); if (rc == SQL_SUCCESS) printf ("Transaction roll back was successful\n"); else check_error (henv, hdbc, SQL_NULL_HSTMT, rc); } printf("Enter an SQL statement to start a transaction or 'q' to quit\n"); gets(sqlstmt); } terminate(henv, hdbc); return (SQL_SUCCESS);}/* end main */ /********************************************************************* process_stmt** - allocates a statement handle** - executes the statement** - determines the type of statement** - if there are no result columns, therefore non-select statement** - if rowcount > 0, assume statement was UPDATE, INSERT, DELETE** else** - assume a DDL, or Grant/Revoke statement** else** - must be a select statement.** - display results** - frees the statement handle*******************************************************************/ int process_stmt (SQLHENV henv, SQLHDBC hdbc, SQLCHAR *sqlstr){SQLHSTMT hstmt;SQLSMALLINT nresultcols;SQLINTEGER rowcount; SQLRETURN rc; SQLAllocStmt (hdbc, &hstmt); /* allocate a statement handle */ /* execute the SQL statement in "sqlstr" */ rc = SQLExecDirect (hstmt, sqlstr, SQL_NTS); if (rc != SQL_SUCCESS) if (rc == SQL_NO_DATA_FOUND) { printf("\nStatement executed without error, however,\n"); printf("no data was found or modified\n"); return (SQL_SUCCESS); } else check_error (henv, hdbc, hstmt, rc); SQLRowCount (hstmt, &rowcount); rc = SQLNumResultCols (hstmt, &nresultcols); if (rc != SQL_SUCCESS) check_error (henv, hdbc, hstmt, rc); /* determine statement type */ if (nresultcols == 0) /* statement is not a select statement */ { if (rowcount > 0 ) /* assume statement is UPDATE, INSERT, DELETE */ { printf ("Statement executed, %ld rows affected\n", rowcount); } else/* assume statement is GRANT, REVOKE or a DLL statement */ { printf ("Statement completed successful\n"); } } else /* display the result set */ { display_results(hstmt, nresultcols); } /* end determine statement type */ SQLFreeStmt (hstmt, SQL_DROP ); /* free statement handle */ return (0);}/* end process_stmt */ /********************************************************************* initialize**- allocate environment handle**- allocate connection handle**- prompt for server, user id, & password**- connect to server*******************************************************************/ int initialize(SQLHENV *henv, SQLHDBC *hdbc){SQLCHAR server, uid, pwd; SQLRETURN rc; rc = SQLAllocEnv (henv); /* allocate an environment handle */ if (rc != SQL_SUCCESS ) check_error (*henv, *hdbc, SQL_NULL_HSTMT, rc); rc = SQLAllocConnect (*henv, hdbc);/* allocate a connection handle */ if (rc != SQL_SUCCESS ) check_error (*henv, *hdbc, SQL_NULL_HSTMT, rc); printf("Enter Server Name:\n"); gets(server); printf("Enter User Name:\n"); gets(uid); printf("Enter Password Name:\n"); gets(pwd); if (uid == '\0') { rc = SQLConnect (*hdbc, server, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS); if (rc != SQL_SUCCESS ) check_error (*henv, *hdbc, SQL_NULL_HSTMT, rc); } else { rc = SQLConnect (*hdbc, server, SQL_NTS, uid, SQL_NTS, pwd, SQL_NTS); if (rc != SQL_SUCCESS ) check_error (*henv, *hdbc, SQL_NULL_HSTMT, rc); }}/* end initialize */ /********************************************************************* terminate**- disconnect**- free connection handle**- free environment handle*******************************************************************/int terminate(SQLHENV henv, SQLHDBC hdbc){ SQLRETURN rc; rc = SQLDisconnect (hdbc); /* disconnect from database*/ if (rc != SQL_SUCCESS ) print_error (henv, hdbc, SQL_NULL_HSTMT); rc = SQLFreeConnect (hdbc); /* free connection handle */ if (rc != SQL_SUCCESS ) print_error (henv, hdbc, SQL_NULL_HSTMT); rc = SQLFreeEnv (henv); /* free environment handle */ if (rc != SQL_SUCCESS ) print_error (henv, SQL_NULL_HDBC, SQL_NULL_HSTMT); }/* end terminate */ /********************************************************************* display_results - displays the selected character fields****- for each column** - get column name** - bind column**- display column headings**- fetch each row** - if value truncated, build error message** - if column null, set value to "NULL"** - display row** - print truncation message**- free local storage*********************************************************************/void display_results(SQLHSTMT hstmt, SQLSMALLINT nresultcols){SQLCHAR colname;SQLSMALLINT coltype;SQLSMALLINT colnamelen;SQLSMALLINT nullable;SQLINTEGER collen;SQLSMALLINT scale;SQLINTEGER outlen;SQLCHAR * data;SQLCHAR errmsg; SQLRETURN rc;SQLINTEGER i;SQLINTEGER displaysize; for (i = 0; i < nresultcols; i++) { SQLDescribeCol (hstmt, i+1, colname, sizeof (colname), &colnamelen, &coltype, &collen, &scale, &nullable); /* get display length for column */ SQLColAttributes (hstmt, i+1, SQL_DESC_PRECISION, NULL, 0 , NULL, &displaysize); /* set column length to max of display length, and column name length.Plus one byte for null terminator */ collen = max(displaysize, collen); collen = max(collen, strlen((char *) colname) ) + 1; printf ("%-*.*s", collen, collen, colname); /* allocate memory to bind column */ data = (SQLCHAR *) malloc (collen); /* bind columns to program vars, converting all types to CHAR */ SQLBindCol (hstmt, i+1, SQL_C_CHAR, data, collen, &outlen); } printf("\n"); /* display result rows */ while ((rc = SQLFetch (hstmt)) != SQL_NO_DATA_FOUND) { errmsg = '\0'; for (i = 0; i < nresultcols; i++) { /* Build a truncation message for any columns truncated */ if (outlen >= collen) { sprintf ((char *) errmsg + strlen ((char *) errmsg), "%d chars truncated, col %d\n", outlen-collen+1, i+1); } if (outlen == SQL_NULL_DATA) printf ("%-*.*s", collen, collen, "NULL"); else printf ("%-*.*s", collen, collen, data); } /* for all columns in this row*/ printf ("\n%s", errmsg);/* print any truncation messages */ } /* while rows to fetch */ /* free data buffers */ for (i = 0; i < nresultcols; i++) { free (data); } }/* end display_results /********************************************************************* SUPPORT FUNCTIONS**- print_error - call SQLError(), display SQLSTATE and message**- check_error - call print_error** - check severity of Return Code** - rollback & exit if error, continue if warning*******************************************************************/ /*******************************************************************/int print_error (SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt){SQLCHAR buffer;SQLCHAR sqlstate;SQLINTEGERsqlcode;SQLSMALLINT length; while ( SQLError(henv, hdbc, hstmt, sqlstate, &sqlcode, buffer, SQL_MAX_MESSAGE_LENGTH + 1, &length) == SQL_SUCCESS ) { printf("\n **** ERROR *****\n"); printf(" SQLSTATE: %s\n", sqlstate); printf("Native Error Code: %ld\n", sqlcode); printf("%s \n", buffer); }; return;} /*******************************************************************/int check_error (SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt, SQLRETURNfrc){ SQLRETURN rc; print_error(henv, hdbc, hstmt); switch (frc){ case SQL_SUCCESS : break; case SQL_ERROR : case SQL_INVALID_HANDLE: printf("\n ** FATAL ERROR, Attempting to rollback transaction **\n"); rc = SQLTransact(henv, hdbc, SQL_ROLLBACK); if (rc != SQL_SUCCESS) printf("Rollback Failed, Exiting application\n"); else printf("Rollback Successful, Exiting application\n"); terminate(henv, hdbc); exit(frc); break; case SQL_SUCCESS_WITH_INFO : printf("\n ** Warning Message, application continuing\n"); break; case SQL_NO_DATA_FOUND : printf("\n ** No Data Found ** \n"); break; default : printf("\n ** Invalid Return Code ** \n"); printf(" ** Attempting to rollback transaction **\n"); SQLTransact(henv, hdbc, SQL_ROLLBACK); terminate(henv, hdbc); exit(frc); break; } return(SQL_SUCCESS); }
页:
[1]