friedkiwi/ibm-information-center
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
master
ibm-information-center/dist/eclipse/plugins/i5OS.ic.cli_5.4.0.1/rzadpxminter.htm

445 lines
15 KiB
HTML
Raw Permalink Blame History

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html lang="en-us" xml:lang="en-us">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="security" content="public" />
<meta name="Robots" content="index,follow" />
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
<meta name="DC.Type" content="reference" />
<meta name="DC.Title" content="Example: Interactive SQL and the equivalent DB2 UDB CLI function calls" />
<meta name="DC.Relation" scheme="URI" content="rzadphdxmp.htm" />
<meta name="copyright" content="(C) Copyright IBM Corporation 1999, 2006" />
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1999, 2006" />
<meta name="DC.Format" content="XHTML" />
<meta name="DC.Identifier" content="rzadpxminter" />
<meta name="DC.Language" content="en-us" />
<!-- All rights reserved. Licensed Materials Property of IBM -->
<!-- US Government Users Restricted Rights -->
<!-- Use, duplication or disclosure restricted by -->
<!-- GSA ADP Schedule Contract with IBM Corp. -->
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
<link rel="stylesheet" type="text/css" href="./ic.css" />
<title>Example: Interactive SQL and the equivalent DB2 UDB CLI function calls</title>
</head>
<body id="rzadpxminter"><a name="rzadpxminter"><!-- --></a>
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
<h1 class="topictitle1">Example: Interactive SQL and the equivalent DB2<sup>®</sup> UDB CLI function calls</h1>
<div><div class="section"><p>This example shows the processing of interactive SQL statements,
and follows the flow described in <a href="rzadphdovv.htm#rzadphdovv">Write a DB2 UDB CLI application</a>. </p>
</div>
<div class="section"><div class="note"><span class="notetitle">Note:</span> By using the code examples, you agree to the terms
of the <a href="codedisclaimer.htm">Code license and disclaimer information</a>.</div>
</div>
<div class="section"><div class="p"> <pre>/*************************************************************************
** 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 &lt;stdlib.h&gt;
#include &lt;stdio.h&gt;
#include &lt;string.h&gt;
#include "sqlcli.h"
#define MAX_STMT_LEN 255
#define MAXCOLS 100
#define max(a,b) (a &gt; 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(&amp;henv, &amp;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 &gt; 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, &amp;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, &amp;rowcount);
rc = SQLNumResultCols (hstmt, &amp;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 &gt; 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, &amp; 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 &lt; nresultcols; i++)
{
SQLDescribeCol (hstmt, i+1, colname, sizeof (colname),
&amp;colnamelen, &amp;coltype[i], &amp;collen[i], &amp;scale, &amp;nullable);
/* get display length for column */
SQLColAttributes (hstmt, i+1, SQL_DESC_PRECISION, NULL, 0 ,
NULL, &amp;displaysize);
/* set column length to max of display length, and column name
length. Plus one byte for null terminator */
collen[i] = max(displaysize, collen[i]);
collen[i] = max(collen[i], strlen((char *) colname) ) + 1;
printf ("%-*.*s", collen[i], collen[i], colname);
/* allocate memory to bind column */
data[i] = (SQLCHAR *) malloc (collen[i]);
/* bind columns to program vars, converting all types to CHAR */
SQLBindCol (hstmt, i+1, SQL_C_CHAR, data[i], collen[i], &amp;outlen[i]);
}
printf("\n");
/* display result rows */
while ((rc = SQLFetch (hstmt)) != SQL_NO_DATA_FOUND)
{
errmsg[0] = '\0';
for (i = 0; i &lt; nresultcols; i++)
{
/* Build a truncation message for any columns truncated */
if (outlen[i] &gt;= collen[i])
{ sprintf ((char *) errmsg + strlen ((char *) errmsg),
"%d chars truncated, col %d\n",
outlen[i]-collen[i]+1, i+1);
}
if (outlen[i] == SQL_NULL_DATA)
printf ("%-*.*s", collen[i], collen[i], "NULL");
else
printf ("%-*.*s", collen[i], collen[i], data[i]);
} /* 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 &lt; nresultcols; i++)
{
free (data[i]);
}
}/* end display_results
/*******************************************************************
** SUPPORT FUNCTIONS
** - print_error - call SQLError(), display SQLSTATE and message
** - check_error - call print_error
** - check severity of Return Code
** - rollback &amp; 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, &amp;sqlcode, buffer,
SQL_MAX_MESSAGE_LENGTH + 1, &amp;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);
}
</pre>
</div>
</div>
</div>
<div>
<div class="familylinks">
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzadphdxmp.htm" title="This topic provides complete examples of DB2 UDB CLI applications.">Examples: DB2 UDB CLI applications</a></div>
</div>
</div>
</body>
</html>
Reference in New Issue View Git Blame Copy Permalink