示例：交互式 SQL 和等价的 DB2 UDB CLI 函数调用

mofdan

　　

示例：交互式 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
#define  MAXCOLS   100
#define  max(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,
SQLRETURN  frc);
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[MAX_STMT_LEN + 1]="";
SQLCHAR     sqltrans[sizeof("ROLLBACK")];
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[0] !='q')
{
while (sqlstmt[0] != '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[0] == '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[0] == '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[18],
uid[10],
pwd[10];
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] == '\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[32];
SQLSMALLINT     coltype[MAXCOLS];
SQLSMALLINT     colnamelen;
SQLSMALLINT     nullable;
SQLINTEGER      collen[MAXCOLS];
SQLSMALLINT     scale;
SQLINTEGER      outlen[MAXCOLS];
SQLCHAR *       data[MAXCOLS];
SQLCHAR         errmsg[256];
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] = '\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[SQL_MAX_MESSAGE_LENGTH + 1];
SQLCHAR     sqlstate[SQL_SQLSTATE_SIZE + 1];
SQLINTEGER  sqlcode;
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,
SQLRETURN  frc)
{
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);
}