/*******************************************************************************
**
** Source File Name = dtudt.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 create/use/drop user define distinct types.
**
** 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" /* Header file for CLI sample code */
int UDTCreate( SQLHANDLE) ;
int UDTUse( SQLHANDLE) ;
int UDTDrop( SQLHANDLE) ;
/*******************************************************************
** main
*******************************************************************/
int main( int argc, char * argv[] )
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE henv; /* environment handle */
SQLHANDLE hdbc; /* connection handles */
char dbAlias[SQL_MAX_DSN_LENGTH + 1] ;
char user[MAX_UID_LENGTH + 1] ;
char pswd[MAX_PWD_LENGTH + 1] ;
/* checks the command line arguments */
rc = CmdLineArgsCheck1( argc, argv, dbAlias, user, pswd );
if ( rc != 0 ) return( rc ) ;
printf("\n\nDATA TYPES: HOW TO CREATE/USE/DROP UDTs.\n");
/* initialize the CLI application */
rc = CLIAppInit( dbAlias, user, pswd, &henv, &hdbc,
(SQLPOINTER)SQL_AUTOCOMMIT_ON);
if ( rc != 0 ) return( rc ) ;
rc = UDTCreate( hdbc) ;
rc = UDTUse( hdbc) ;
rc = UDTDrop( hdbc) ;
/* terminate the CLI application */
rc = CLIAppTerm( &henv, &hdbc, dbAlias);
return( rc ) ;
} /* end main */
/******************************************************************************
** UDTCreate
******************************************************************************/
int UDTCreate( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt1 = ( SQLCHAR * )
"CREATE DISTINCT TYPE UDT1 AS INTEGER WITH COMPARISONS";
SQLCHAR * stmt2 = ( SQLCHAR * )
"CREATE DISTINCT TYPE UDT2 AS CHAR(2) WITH COMPARISONS";
SQLCHAR * stmt3 = ( SQLCHAR * )
"CREATE DISTINCT TYPE UDT3 AS DECIMAL(7,2) WITH COMPARISONS";
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLExecDirect\n-SQLFreeHandle\n");
printf("TO CREATE USER DISTINCT TYPES:\n");
/* set AUTOCOMMIT on */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* create the UDT1 */
printf("\n Execute directly the statement\n");
printf(" %s\n", stmt1);
sqlrc = SQLExecDirect( hstmt, stmt1, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* create the UDT2 */
printf(" Execute directly the statement\n");
printf(" %s\n", stmt2);
sqlrc = SQLExecDirect( hstmt, stmt2, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* create the UDT3 */
printf(" Execute directly the statement\n");
printf(" %s\n", stmt3);
sqlrc = SQLExecDirect( hstmt, stmt3, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** UDTUse
******************************************************************************/
int UDTUse( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt1 = ( SQLCHAR *) "CREATE TABLE DTUDT ( "
"Col1 UDT1, "
"Col2 UDT2, "
"Col3 UDT3 )" ;
SQLCHAR * stmt2 = ( SQLCHAR *)
"INSERT INTO DTUDT VALUES "
"( CAST(77 AS UDT1), CAST('ab' AS UDT2), CAST(111.77 AS UDT3) )" ;
SQLCHAR * stmt3 = ( SQLCHAR *) "DROP TABLE DTUDT " ;
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLExecDirect\n-SQLFreeHandle\n");
printf("TO USE USER DISTINCT TYPES:\n");
/* set AUTOCOMMIT on */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* create the test table */
printf("\n Execute directly the statement\n");
printf(" CREATE TABLE DTUDT ( \n");
printf(" Col1 UDT1, \n");
printf(" Col2 UDT2, \n");
printf(" Col3 UDT3 ) \n");
sqlrc = SQLExecDirect( hstmt, stmt1, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* insert values in the test table */
printf(" Execute directly the statement\n");
printf(" INSERT INTO DTUDT VALUES\n");
printf(" ( CAST(77 AS UDT1),\n");
printf(" CAST('ab' AS UDT2) ,\n");
printf(" CAST(111.77 AS UDT3))\n");
sqlrc = SQLExecDirect( hstmt, stmt2, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* drop the test table */
printf(" Execute directly the statement\n");
printf(" %s\n", stmt3);
sqlrc = SQLExecDirect( hstmt, stmt3, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** UDTDrop
******************************************************************************/
int UDTDrop( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt1 = ( SQLCHAR *) "DROP DISTINCT TYPE UDT1" ;
SQLCHAR * stmt2 = ( SQLCHAR *) "DROP DISTINCT TYPE UDT2" ;
SQLCHAR * stmt3 = ( SQLCHAR *) "DROP DISTINCT TYPE UDT3" ;
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLExecDirect\n-SQLFreeHandle\n");
printf("TO DROP USER DISTINCT TYPES:\n");
/* set AUTOCOMMIT on */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* drop UDT1 */
printf("\n Execute directly the statement\n");
printf(" %s\n", stmt1);
sqlrc = SQLExecDirect( hstmt, stmt1, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* drop UDT2 */
printf(" Execute directly the statement\n");
printf(" %s\n", stmt2);
sqlrc = SQLExecDirect( hstmt, stmt2, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* drop UDT3 */
printf(" Execute directly the statement\n");
printf(" %s\n", stmt3);
sqlrc = SQLExecDirect( hstmt, stmt3, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}