/*******************************************************************************
**
** Source File Name = dbmuse.c
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 2000
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
** PURPOSE :
** Shows how to use multiple databases.
**
** For more information about these samples see the README file.
**
** For more information on programming in CLI see the:
** - "Building CLI Applications" section of the Application Building Guide, and the
** - CLI Guide and Reference.
**
** For more information on the SQL language see the SQL Reference.
**
*******************************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlcli1.h>
#include "utilcli.h"
#include "utilapi.h"
int TwoConnType1Transact( SQLHANDLE , char *, char *, char *, char * ) ;
#define MAX_CONNECTIONS 2
/*******************************************************************
** main
*******************************************************************/
int main( int argc, char * argv[] )
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE henv; /* environment handle */
char dbAlias[SQL_MAX_DSN_LENGTH + 1] ;
char user[MAX_UID_LENGTH + 1] ;
char pswd[MAX_PWD_LENGTH + 1] ;
char remoteNodeName[ 10 ] ;
char db2Name[SQL_MAX_DSN_LENGTH + 1] = "SAMPLE2" ;
char db2Alias[SQL_MAX_DSN_LENGTH + 1] = "SAMPLE2" ;
/* checks the command line arguments */
rc = CmdLineArgsCheck2( argc, argv, dbAlias, user, pswd, remoteNodeName );
if ( rc != 0 ) return( rc ) ;
printf("\n\nDATABASES: HOW TO USE MULTIPLE DATABASES.\n");
/* allocate an environment handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
if ( sqlrc != SQL_SUCCESS )
{ printf( "\n--ERROR while allocating the environment handle.\n" ) ;
printf( " sqlrc = %d\n", sqlrc);
printf( " line = %d\n", __LINE__);
printf( " file = %s\n", __FILE__);
return( 1 ) ;
}
/* create and catalog a second database */
/* using administrative APIs*/
rc = DbCreate( remoteNodeName, user, pswd, db2Name, db2Alias ) ;
if( rc == 0)
{
/* perform transactions on two connections TYPE 1 */
rc = TwoConnType1Transact( henv, dbAlias, db2Alias, user, pswd ) ;
if ( rc != 0 )
{ DbDrop( remoteNodeName, user, pswd, db2Alias );
return( rc ) ;
}
/* drop and uncatalog the second database */
/* using administrative APIs */
rc = DbDrop( remoteNodeName, user, pswd, db2Alias ) ;
}
/* free the environment handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_ENV, henv ) ;
ENV_HANDLE_CHECK( henv, sqlrc ) ;
return( 0 ) ;
} /* end main */
/******************************************************************************
** TwoConnType1Transact - perform transactions on two connections TYPE 1
******************************************************************************/
int TwoConnType1Transact( SQLHANDLE henv,
char db1Alias[],
char db2Alias[],
char user[],
char pswd[] )
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE a_hdbc[MAX_CONNECTIONS] ; /* array of connection handles */
SQLHANDLE hstmt1, hstmt2; /* statement handles */
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLAllocHandle\n-SQLConnect\n");
printf("-SQLExecDirect\n-SQLEndTran\n");
printf("-SQLDisconnect\n-SQLFreeHandle\n");
printf("TO PERFORM TRANSACTIONS ON TWO CONNECTIONS\n");
printf("USING THE CONNECT TYPE 1:\n");
/* allocate the database connection handles */
sqlrc = SQLAllocHandle( SQL_HANDLE_DBC, henv, &a_hdbc[0] ) ;
MC_HANDLE_CHECK( SQL_HANDLE_DBC, a_hdbc[0], sqlrc,
&henv, a_hdbc, MAX_CONNECTIONS ) ;
sqlrc = SQLAllocHandle( SQL_HANDLE_DBC, henv, &a_hdbc[1] ) ;
MC_HANDLE_CHECK( SQL_HANDLE_DBC, a_hdbc[1], sqlrc,
&henv, a_hdbc, MAX_CONNECTIONS ) ;
/* connect to both databases */
printf( "\n Connecting to the database %s ...\n", db1Alias ) ;
sqlrc = SQLConnect( a_hdbc[0],
(SQLCHAR *)db1Alias, SQL_NTS,
(SQLCHAR *)user, SQL_NTS,
(SQLCHAR *)pswd, SQL_NTS
) ;
MC_HANDLE_CHECK( SQL_HANDLE_DBC, a_hdbc[0], sqlrc,
&henv, a_hdbc, MAX_CONNECTIONS ) ;
printf( " Connected to the database %s.\n", db1Alias ) ;
printf( "\n Connecting to the database %s ...\n", db2Alias ) ;
sqlrc = SQLConnect( a_hdbc[1],
(SQLCHAR *)db2Alias, SQL_NTS,
(SQLCHAR *)user, SQL_NTS,
(SQLCHAR *)pswd, SQL_NTS
) ;
MC_HANDLE_CHECK( SQL_HANDLE_DBC, a_hdbc[1], sqlrc,
&henv, a_hdbc, MAX_CONNECTIONS ) ;
printf( " Connected to the database %s.\n", db2Alias ) ;
/********* Start using the connections ************************/
/* set AUTOCOMMIT OFF for the both connections */
sqlrc = SQLSetConnectAttr( a_hdbc[0],
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_NTS) ;
MC_HANDLE_CHECK( SQL_HANDLE_DBC, a_hdbc[0], sqlrc,
&henv, a_hdbc, MAX_CONNECTIONS ) ;
printf("\n Transactions enabled for the connection 1.\n");
sqlrc = SQLSetConnectAttr( a_hdbc[1],
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_NTS) ;
MC_HANDLE_CHECK( SQL_HANDLE_DBC, a_hdbc[1], sqlrc,
&henv, a_hdbc, MAX_CONNECTIONS ) ;
printf(" Transactions enabled for the connection 2.\n");
/* allocate statement handles */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, a_hdbc[0], &hstmt1 ) ;
MC_HANDLE_CHECK( SQL_HANDLE_DBC, a_hdbc[0], sqlrc,
&henv, a_hdbc, MAX_CONNECTIONS ) ;
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, a_hdbc[1], &hstmt2 ) ;
MC_HANDLE_CHECK( SQL_HANDLE_DBC, a_hdbc[1], sqlrc,
&henv, a_hdbc, MAX_CONNECTIONS ) ;
printf("\n Perform statements on both connections.\n");
/* execute statement 1 on the connection 1 */
printf(" executing statement 1 on the connection 1...\n");
sqlrc = SQLExecDirect( hstmt1,
(SQLCHAR *)"CREATE TABLE table1( col1 INTEGER)",
SQL_NTS ) ;
MC_HANDLE_CHECK( SQL_HANDLE_STMT, hstmt1, sqlrc,
&henv, a_hdbc, MAX_CONNECTIONS ) ;
/* execute statement 2 on the connection 1 */
printf(" executing statement 2 on the connection 1...\n");
sqlrc = SQLExecDirect( hstmt1,
(SQLCHAR *)"CREATE TABLE table2( col1 INTEGER)",
SQL_NTS ) ;
MC_HANDLE_CHECK( SQL_HANDLE_STMT, hstmt1, sqlrc,
&henv, a_hdbc, MAX_CONNECTIONS ) ;
/* end the transaction */
printf(" End the transaction:\n");
printf(" rolling back the transaction on the connection 1...\n");
sqlrc = SQLEndTran( SQL_HANDLE_DBC, a_hdbc[0], SQL_ROLLBACK );
MC_HANDLE_CHECK( SQL_HANDLE_DBC, a_hdbc[0], sqlrc,
&henv, a_hdbc, MAX_CONNECTIONS ) ;
printf( " transaction rolled back.\n");
/* execute statement 3 on the connection 2 */
printf(" executing statement 3 on the connection 2...\n");
sqlrc = SQLExecDirect( hstmt2,
(SQLCHAR *)"CREATE TABLE table3( col1 INTEGER)",
SQL_NTS ) ;
MC_HANDLE_CHECK( SQL_HANDLE_STMT, hstmt2, sqlrc,
&henv, a_hdbc, MAX_CONNECTIONS ) ;
/* execute statement 4 on the connection 2 */
printf(" executing statement 4 on the connection 2...\n");
sqlrc = SQLExecDirect( hstmt2,
(SQLCHAR *)"CREATE TABLE table4( col1 INTEGER)",
SQL_NTS ) ;
MC_HANDLE_CHECK( SQL_HANDLE_STMT, hstmt2, sqlrc,
&henv, a_hdbc, MAX_CONNECTIONS ) ;
/* end the transaction */
printf(" End the transaction:\n");
printf(" committing the transaction on the connection 2...\n");
sqlrc = SQLEndTran( SQL_HANDLE_DBC, a_hdbc[1], SQL_COMMIT );
MC_HANDLE_CHECK( SQL_HANDLE_DBC, a_hdbc[1], sqlrc,
&henv, a_hdbc, MAX_CONNECTIONS ) ;
printf( " transaction committed.\n");
/* free the statement handles */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt1 ) ;
MC_HANDLE_CHECK( SQL_HANDLE_STMT, hstmt1, sqlrc,
&henv, a_hdbc, MAX_CONNECTIONS ) ;
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt2 ) ;
MC_HANDLE_CHECK( SQL_HANDLE_STMT, hstmt2, sqlrc,
&henv, a_hdbc, MAX_CONNECTIONS ) ;
/********* Stop using the connections *************************/
/* disconnect from both databases */
printf( "\n Disconnecting from the database %s ...\n", db1Alias ) ;
sqlrc = SQLDisconnect( a_hdbc[0] ) ;
MC_HANDLE_CHECK( SQL_HANDLE_DBC, a_hdbc[0], sqlrc,
&henv, a_hdbc, MAX_CONNECTIONS ) ;
printf( " Disconnected from the database %s.\n", db1Alias ) ;
printf( "\n Disconnecting from the database %s ...\n", db2Alias ) ;
sqlrc = SQLDisconnect( a_hdbc[1] ) ;
MC_HANDLE_CHECK( SQL_HANDLE_DBC, a_hdbc[1], sqlrc,
&henv, a_hdbc, MAX_CONNECTIONS ) ;
printf( " Disconnected from the database %s.\n", db2Alias ) ;
/* free the connection handles */
sqlrc = SQLFreeHandle( SQL_HANDLE_DBC, a_hdbc[0] ) ;
MC_HANDLE_CHECK( SQL_HANDLE_DBC, a_hdbc[0], sqlrc,
&henv, a_hdbc, MAX_CONNECTIONS ) ;
sqlrc = SQLFreeHandle( SQL_HANDLE_DBC, a_hdbc[1] ) ;
MC_HANDLE_CHECK( SQL_HANDLE_DBC, a_hdbc[1], sqlrc,
&henv, a_hdbc, MAX_CONNECTIONS ) ;
return( 0 ) ;
}