示例：交互式 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]