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.rzalf_5.4.0.1/rzalfdatabaseexamp.htm

416 lines
16 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: Call DB2 UDB for iSeries CLI functions in an i5/OS PASE program" />
<meta name="abstract" content="The example in this topic shows an i5/OS PASE program that accesses DB2 UDB for iSeries using the DB2 UDB for iSeries SQL call level interfaces." />
<meta name="description" content="The example in this topic shows an i5/OS PASE program that accesses DB2 UDB for iSeries using the DB2 UDB for iSeries SQL call level interfaces." />
<meta name="DC.Relation" scheme="URI" content="rzalfdatabase.htm" />
<meta name="copyright" content="(C) Copyright IBM Corporation 2000, 2006" />
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 2000, 2006" />
<meta name="DC.Format" content="XHTML" />
<meta name="DC.Identifier" content="rzalfdatabaseexamp" />
<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: Call DB2 UDB for iSeries CLI functions in an i5/OS PASE program</title>
</head>
<body id="rzalfdatabaseexamp"><a name="rzalfdatabaseexamp"><!-- --></a>
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
<h1 class="topictitle1">Example: Call DB2 UDB for iSeries CLI functions in an <span class="keyword">i5/OS</span> PASE program</h1>
<div><p>The example in this topic shows an <span class="keyword">i5/OS™</span> PASE
program that accesses DB2<sup>®</sup> UDB for iSeries™ using the DB2 UDB for iSeries SQL call level interfaces.</p>
<div class="example"><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>
<pre>/* i5/OS PASE DB2 UDB for iSeries example program
*
* To show an example of an i5/OS PASE program that accesses
* i5/OS DB2 UDB via SQL CLI
*
* Program accesses iSeries Access data base, QIWS/QCUSTCDT, that
* should exist on all systems
*
* Change system name, userid, and password in fun_Connect()
* procedure to valid parms
*
* Compilation invocation:
*
* xlc -I./include -bI:./include/libdb400.exp -o paseclidb4 paseclidb4.c
*
* FTP in binary, run from QP2TERM() terminal shell
*
* Output should show all rows with a STATE column match of MN */
/* Change Activity: */
/* End Change Activity */
#define SQL_MAX_UID_LENGTH 10
#define SQL_MAX_PWD_LENGTH 10
#define SQL_MAX_STM_LENGTH 255
#include &lt;stdio.h&gt;
#include &lt;stdlib.h&gt;
#include &lt;string.h&gt;
#include "sqlcli.h"
SQLRETURN fun_Connect( void );
SQLRETURN fun_DisConnect( void );
SQLRETURN fun_ReleaseEnvHandle( void );
SQLRETURN fun_ReleaseDbcHandle( void );
SQLRETURN fun_ReleaseStmHandle( void );
SQLRETURN fun_Process( void );
SQLRETURN fun_Process2( void );
void fun_PrintError( SQLHSTMT );
SQLRETURN nml_ReturnCode;
SQLHENV nml_HandleToEnvironment;
SQLHDBC nml_HandleToDatabaseConnection;
SQLHSTMT nml_HandleToSqlStatement;
SQLINTEGER Nmi_vParam;
SQLINTEGER Nmi_RecordNumberToFetch = 0;
SQLCHAR chs_SqlStatement01[ SQL_MAX_STM_LENGTH + 1 ];
SQLINTEGER nmi_PcbValue;
SQLINTEGER nmi_vParam;
char *pStateName = "MN";
void main( ) {
static
char*pszId = "main()";
SQLRETURN nml_ConnectionStatus;
SQLRETURN nml_ProcessStatus;
nml_ConnectionStatus = fun_Connect();
if ( nml_ConnectionStatus == SQL_SUCCESS ) {
printf( "%s: fun_Connect() succeeded\n", pszId );
} else {
printf( "%s: fun_Connect() failed\n", pszId );
exit( -1 );
} /* endif */
printf( "%s: Perform query\n", pszId );
nml_ProcessStatus = fun_Process();
printf( "%s: Query complete\n", pszId );
nml_ConnectionStatus = fun_DisConnect();
if ( nml_ConnectionStatus == SQL_SUCCESS ) {
printf( "%s: fun_DisConnect() succeeded\n", pszId );
} else {
printf( "%s: fun_DisConnect() failed\n", pszId );
exit( -1 );
} /* endif */
printf( "%s: normal exit\n", pszId );
} /* end main */
SQLRETURN fun_Connect()
{
static char *pszId = "fun_Connect()";
SQLCHAR chs_As400System[ SQL_MAX_DSN_LENGTH ];
SQLCHAR chs_UserName[ SQL_MAX_UID_LENGTH ];
SQLCHAR chs_UserPassword[ SQL_MAX_PWD_LENGTH ];
nml_ReturnCode = SQLAllocEnv( &amp;nml_HandleToEnvironment );
if ( nml_ReturnCode != SQL_SUCCESS ) {
printf( "%s: SQLAllocEnv() succeeded\n", pszId );
fun_PrintError( SQL_NULL_HSTMT );
printf( "%s: Terminating\n", pszId );
return SQL_ERROR;
} else {
printf( "%s: SQLAllocEnv() succeeded\n", pszId );
} /* endif */
strcpy( chs_As400System, "AS4PASE" );
strcpy( chs_UserName, "QUSER" );
strcpy( chs_UserPassword, "QUSER" );
printf( "%s: Connecting to %s userid %s\n", pszId, chs_As400System, chs_UserName );
nml_ReturnCode = SQLAllocConnect( nml_HandleToEnvironment,
&amp;nml_HandleToDatabaseConnection );
if ( nml_ReturnCode != SQL_SUCCESS ) {
printf( "%s: SQLAllocConnect\n", pszId );
fun_PrintError( SQL_NULL_HSTMT );
nml_ReturnCode = fun_ReleaseEnvHandle();
printf( "%s: Terminating\n", pszId );
return SQL_ERROR;
} else {
printf( "%s: SQLAllocConnect() succeeded\n", pszId );
} /* endif */
nml_ReturnCode = SQLConnect( nml_HandleToDatabaseConnection,
chs_As400System,
SQL_NTS,
chs_UserName,
SQL_NTS,
chs_UserPassword,
SQL_NTS );
if ( nml_ReturnCode != SQL_SUCCESS ) {
printf( "%s: SQLConnect(%s) failed\n", pszId, chs_As400System );
fun_PrintError( SQL_NULL_HSTMT );
nml_ReturnCode = fun_ReleaseDbcHandle();
nml_ReturnCode = fun_ReleaseEnvHandle();
printf( "%s: Terminating\n", pszId );
return SQL_ERROR;
} else {
printf( "%s: SQLConnect(%s) succeeded\n", pszId, chs_As400System );
return SQL_SUCCESS;
} /* endif */
} /* end fun_Connect */
SQLRETURN fun_Process()
{
static
char*pszId = "fun_Process()";
charcLastName[ 80 ];
nml_ReturnCode = SQLAllocStmt( nml_HandleToDatabaseConnection,
&amp;nml_HandleToSqlStatement );
if ( nml_ReturnCode != SQL_SUCCESS ) {
printf( "%s: SQLAllocStmt() failed\n", pszId );
fun_PrintError( SQL_NULL_HSTMT );
printf( "%s: Terminating\n", pszId );
return SQL_ERROR;
} else {
printf( "%s: SQLAllocStmt() succeeded\n", pszId );
} /* endif */
strcpy( chs_SqlStatement01, "select LSTNAM, STATE " );
strcat( chs_SqlStatement01, "from QIWS.QCUSTCDT " );
strcat( chs_SqlStatement01, "where " );
strcat( chs_SqlStatement01, "STATE = ? " );
nml_ReturnCode = SQLPrepare( nml_HandleToSqlStatement,
chs_SqlStatement01,
SQL_NTS );
if ( nml_ReturnCode != SQL_SUCCESS ) {
printf( "%s: SQLPrepare() failed\n", pszId );
fun_PrintError( nml_HandleToSqlStatement );
nml_ReturnCode = fun_ReleaseStmHandle();
printf( "%s: Terminating\n", pszId );
return SQL_ERROR;
} else {
printf( "%s: SQLPrepare() succeeded\n", pszId );
} /* endif */
Nmi_vParam = SQL_TRUE;
nml_ReturnCode = SQLSetStmtOption( nml_HandleToSqlStatement,
SQL_ATTR_CURSOR_SCROLLABLE,
( SQLINTEGER * ) &amp;Nmi_vParam );
if ( nml_ReturnCode != SQL_SUCCESS ) {
printf( "%s: SQLSetStmtOption() failed\n", pszId );
fun_PrintError( nml_HandleToSqlStatement );
nml_ReturnCode = fun_ReleaseStmHandle();
printf( "%s: Terminating\n", pszId );
return SQL_ERROR;
} else {
printf( "%s: SQLSetStmtOption() succeeded\n", pszId );
} /* endif */
Nmi_vParam = SQL_TRUE;
nml_ReturnCode = SQLSetStmtOption( nml_HandleToSqlStatement,
SQL_ATTR_FOR_FETCH_ONLY,
( SQLINTEGER * ) &amp;Nmi_vParam );
if ( nml_ReturnCode != SQL_SUCCESS ) {
printf( "%s: SQLSetStmtOption() failed\n", pszId );
fun_PrintError( nml_HandleToSqlStatement );
nml_ReturnCode = fun_ReleaseStmHandle();
printf( "%s: Terminating\n", pszId );
return SQL_ERROR;
} else {
printf( "%s: SQLSetStmtOption() succeeded\n", pszId );
} /* endif */
nmi_PcbValue = 0;
nml_ReturnCode = SQLBindParam( nml_HandleToSqlStatement,
1,
SQL_CHAR,
SQL_CHAR,
2,
0,
( SQLPOINTER ) pStateName,
( SQLINTEGER *) &amp;nmi_PcbValue );
if ( nml_ReturnCode != SQL_SUCCESS ) {
printf( "%s: SQLBindParam() failed\n", pszId );
fun_PrintError( nml_HandleToSqlStatement );
nml_ReturnCode = fun_ReleaseStmHandle();
printf( "%s: Terminating\n", pszId );
return SQL_ERROR;
} else {
printf( "%s: SQLBindParam() succeeded\n", pszId );
} /* endif */
nml_ReturnCode = SQLExecute( nml_HandleToSqlStatement );
if ( nml_ReturnCode != SQL_SUCCESS ) {
printf( "%s: SQLExecute() failed\n", pszId );
fun_PrintError( nml_HandleToSqlStatement );
nml_ReturnCode = fun_ReleaseStmHandle();
printf( "%s: Terminating\n", pszId );
return SQL_ERROR;
} else {
printf( "%s: SQLExecute() succeeded\n", pszId );
} /* endif */
nml_ReturnCode = SQLBindCol( nml_HandleToSqlStatement,
1,
SQL_CHAR,
( SQLPOINTER ) &amp;cLastName,
( SQLINTEGER ) ( 8 ),
( SQLINTEGER * ) &amp;nmi_PcbValue );
if ( nml_ReturnCode != SQL_SUCCESS ) {
printf( "%s: SQLBindCol() failed\n", pszId );
fun_PrintError( nml_HandleToSqlStatement );
nml_ReturnCode = fun_ReleaseStmHandle();
printf( "%s: Terminating\n", pszId );
return SQL_ERROR;
} else {
printf( "%s: SQLBindCol() succeeded\n", pszId );
} /* endif */
do {
memset( cLastName, '\0', sizeof( cLastName ) );
nml_ReturnCode = SQLFetchScroll( nml_HandleToSqlStatement,
SQL_FETCH_NEXT,
Nmi_RecordNumberToFetch );
if ( nml_ReturnCode == SQL_SUCCESS ) {
printf( "%s: SQLFetchScroll() succeeded, LastName(%s)\n", pszId, cLastName);
} else {
}/*endif */
} while ( nml_ReturnCode == SQL_SUCCESS );
if ( nml_ReturnCode != SQL_NO_DATA_FOUND ) {
printf( "%s: SQLFetchScroll() failed\n", pszId );
fun_PrintError( nml_HandleToSqlStatement );
nml_ReturnCode = fun_ReleaseStmHandle();
printf( "%s: Terminating\n", pszId );
return SQL_ERROR;
} else {
printf( "%s: SQLFetchScroll() completed all rows\n", pszId );
} /* endif */
nml_ReturnCode = SQLCloseCursor( nml_HandleToSqlStatement );
if ( nml_ReturnCode != SQL_SUCCESS ) {
printf( "%s: SQLCloseCursor() failed\n", pszId );
fun_PrintError( nml_HandleToSqlStatement );
nml_ReturnCode = fun_ReleaseStmHandle();
printf( "%s: Terminating\n", pszId );
return SQL_ERROR;
} else {
printf( "%s: SQLCloseCursor() succeeded\n", pszId );
} /* endif */
return SQL_SUCCESS;
} /* end fun_Process */
SQLRETURN fun_DisConnect()
{
static
char*pszId = "fun_DisConnect()";
nml_ReturnCode = SQLDisconnect( nml_HandleToDatabaseConnection );
if ( nml_ReturnCode != SQL_SUCCESS ) {
printf( "%s: SQLDisconnect() failed\n", pszId );
fun_PrintError( SQL_NULL_HSTMT );
printf( "%s: Terminating\n", pszId );
return 1;
} else {
printf( "%s: SQLDisconnect() succeeded\n", pszId );
} /* endif */
nml_ReturnCode = fun_ReleaseDbcHandle();
nml_ReturnCode = fun_ReleaseEnvHandle();
return nml_ReturnCode;
} /* end fun_DisConnect */
SQLRETURN fun_ReleaseEnvHandle()
{
static
char*pszId = "fun_ReleaseEnvHandle()";
nml_ReturnCode = SQLFreeEnv( nml_HandleToEnvironment );
if ( nml_ReturnCode != SQL_SUCCESS ) {
printf( "%s: SQLFreeEnv() failed\n", pszId );
fun_PrintError( SQL_NULL_HSTMT );
return SQL_ERROR;
} else {
printf( "%s: SQLFreeEnv() succeeded\n", pszId );
return SQL_SUCCESS;
} /* endif */
} /* end fun_ReleaseEnvHandle */
SQLRETURN fun_ReleaseDbcHandle()
{
static
char*pszId = "fun_ReleaseDbcHandle()";
nml_ReturnCode = SQLFreeConnect( nml_HandleToDatabaseConnection );
if ( nml_ReturnCode != SQL_SUCCESS ) {
printf( "%s: SQLFreeConnect() failed\n", pszId );
fun_PrintError( SQL_NULL_HSTMT );
return SQL_ERROR;
} else {
printf( "%s: SQLFreeConnect() succeeded\n", pszId );
return SQL_SUCCESS;
} /* endif */
} /* end fun_ReleaseDbcHandle */
SQLRETURN fun_ReleaseStmHandle()
{
static
char*pszId = "fun_ReleaseStmHandle()";
nml_ReturnCode = SQLFreeStmt( nml_HandleToSqlStatement, SQL_CLOSE );
if ( nml_ReturnCode != SQL_SUCCESS ) {
printf( "%s: SQLFreeStmt() failed\n", pszId );
fun_PrintError( nml_HandleToSqlStatement );
return SQL_ERROR;
} else {
printf( "%s: SQLFreeStmt() succeeded\n", pszId );
return SQL_SUCCESS;
} /* endif */
} /* end fun_ReleaseStmHandle */
void fun_PrintError( SQLHSTMT nml_HandleToSqlStatement )
{
static
char*pszId = "fun_PrintError()";
SQLCHAR chs_SqlState[ SQL_SQLSTATE_SIZE ];
SQLINTEGER nmi_NativeErrorCode;
SQLCHAR chs_ErrorMessageText[ SQL_MAX_MESSAGE_LENGTH + 1 ];
SQLSMALLINT nmi_NumberOfBytes;
nml_ReturnCode = SQLError( nml_HandleToEnvironment,
nml_HandleToDatabaseConnection,
nml_HandleToSqlStatement,
chs_SqlState,
&amp;nmi_NativeErrorCode,
chs_ErrorMessageText,
sizeof( chs_ErrorMessageText ),
&amp;nmi_NumberOfBytes );
if ( nml_ReturnCode != SQL_SUCCESS ) {
printf( "%s: SQLError() failed\n", pszId );
return;
} /* endif */
printf( "%s: SqlState - %s\n", pszId, chs_SqlState );
printf( "%s: SqlCode - %d\n", pszId, nmi_NativeErrorCode );
printf( "%s: Error Message:\n", pszId );
printf( "%s: %s\n", pszId, chs_ErrorMessageText );
} /* end fun_PrintError */</pre>
</div>
</div>
<div>
<div class="familylinks">
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzalfdatabase.htm" title="i5/OS PASE supports the DB2 UDB for iSeries Call Level Interface (CLI). DB2 CLI on AIX and i5/OS are not proper subsets of each other, so there are minor differences in a few interfaces, and some APIs in one implementation might not exist in another.">Database</a></div>
</div>
</div>
</body>
</html>
Reference in New Issue View Git Blame Copy Permalink