/*******************************************************************************
**
** Source File Name = create.c 1.3
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 1999
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
** PURPOSE :
** Creates all Tables, User Defined Types & Functions for the Order scenario.
**
** 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 "samputil.h" /* Header file for CLI sample code */
/* For the Macintosh environment when generating 68K applications */
#ifdef DB268K
/* Need to include ASLM for 68K applications */
#include <LibraryManager.h>
#endif
/*
Global Variables for user id and password.
To keep samples simple, not a recommended practice.
*/
extern SQLCHAR server[SQL_MAX_DSN_LENGTH + 1] ;
extern SQLCHAR uid[MAX_UID_LENGTH + 1] ;
extern SQLCHAR pwd[MAX_PWD_LENGTH + 1] ;
/* main */
int main( int argc, char * argv[] ) {
SQLHANDLE henv, hdbc, hstmt ;
SQLRETURN rc ;
SQLINTEGER i ;
/*--> SQLL1X24.SCRIPT */
/* Initialize SQL statement strings */
SQLCHAR * stmt[] = {
( SQLCHAR * ) "CREATE DISTINCT TYPE CNUM AS INTEGER WITH COMPARISONS",
( SQLCHAR * ) "CREATE DISTINCT TYPE PUNIT AS CHAR(2) WITH COMPARISONS",
( SQLCHAR * ) "CREATE DISTINCT TYPE UPRICE AS DECIMAL(10, 2) "
"WITH COMPARISONS",
( SQLCHAR * ) "CREATE DISTINCT TYPE PRICE AS DECIMAL(10, 2) "
"WITH COMPARISONS",
( SQLCHAR * ) "CREATE FUNCTION PRICE( CHAR(12), PUNIT, char(16) ) "
"returns char(12) "
"NOT FENCED EXTERNAL NAME 'order!price' "
"NOT VARIANT NO SQL LANGUAGE C PARAMETER STYLE DB2SQL "
"NO EXTERNAL ACTION",
( SQLCHAR * ) "CREATE DISTINCT TYPE PNUM AS INTEGER WITH COMPARISONS",
( SQLCHAR * ) "CREATE FUNCTION \"+\"(PNUM, INTEGER) RETURNS PNUM "
"source sysibm.\"+\"(integer, integer)",
( SQLCHAR * ) "CREATE FUNCTION MAX(PNUM) RETURNS PNUM "
"source max(integer)",
( SQLCHAR * ) "CREATE DISTINCT TYPE ONUM AS INTEGER WITH COMPARISONS",
( SQLCHAR * ) "CREATE TABLE CUSTOMER ( "
"Cust_Num CNUM NOT NULL, "
"First_Name CHAR(30) NOT NULL, "
"Last_Name CHAR(30) NOT NULL, "
"Street CHAR(128) WITH DEFAULT, "
"City CHAR(30) WITH DEFAULT, "
"Prov_State CHAR(30) WITH DEFAULT, "
"PZ_Code CHAR(9) WITH DEFAULT, "
"Country CHAR(30) WITH DEFAULT, "
"Phone_Num CHAR(20) WITH DEFAULT, "
"PRIMARY KEY (Cust_Num) )",
( SQLCHAR * ) "CREATE TABLE PRODUCT ( "
"Prod_Num PNUM NOT NULL, "
"Description VARCHAR(256) NOT NULL, "
"Price DECIMAL(10,2) WITH DEFAULT , "
"Units PUNIT NOT NULL, "
"Combo CHAR(1) WITH DEFAULT, "
"PRIMARY KEY (Prod_Num), "
"CHECK (Units in (PUNIT('m'), PUNIT('l'), PUNIT('g'), PUNIT('kg'), "
"PUNIT(' '))) )",
( SQLCHAR * ) "CREATE TABLE PROD_PARTS ( "
"Prod_Num PNUM NOT NULL, "
"Part_Num PNUM NOT NULL, "
"Quantity DECIMAL(14,7), "
"PRIMARY KEY (Prod_Num, Part_Num), "
"FOREIGN KEY (Prod_Num) REFERENCES Product, "
"FOREIGN KEY (Part_Num) REFERENCES Product, "
"CHECK (Prod_Num <> Part_Num) )",
( SQLCHAR * ) "CREATE TABLE ORD_CUST ( "
"Ord_Num ONUM NOT NULL, "
"Cust_Num CNUM NOT NULL, "
"Ord_Date DATE NOT NULL, "
"PRIMARY KEY (Ord_Num), "
"FOREIGN KEY (Cust_Num) REFERENCES Customer )",
( SQLCHAR * ) "CREATE TABLE ORD_LINE ( "
"Ord_Num ONUM NOT NULL, "
"Prod_Num PNUM NOT NULL, "
"Quantity DECIMAL(14,7), "
"PRIMARY KEY (Ord_Num, Prod_Num), "
"FOREIGN KEY (Prod_Num) REFERENCES Product, "
"FOREIGN KEY (Ord_Num) REFERENCES Ord_Cust )",
( SQLCHAR * ) 0,
} ;
/*<-- */
/* For the Macintosh environment when generating 68K applications */
#ifdef DB268K
/*
Before making any API calls for 68K environment,
need to initialize the Library Manager
*/
InitLibraryManager(0,kCurrentZone,kNormalMemory);
atexit(CleanupLibraryManager);
#endif
/* macro to initalize server, uid and pwd */
INIT_UID_PWD ;
/* allocate an environment handle */
rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
/* allocate a connect handle, and connect */
rc = DBconnect( henv, &hdbc ) ;
if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
/*--> */
/* Execute Direct statements */
i = 0 ;
while ( stmt[i] != ( SQLCHAR * ) 0 ) {
printf( ">Executing Statement %ld\n", ( i + 1 ) ) ;
rc = SQLExecDirect( hstmt, stmt[i], SQL_NTS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
i++ ;
}
/*<-- */
rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
printf( ">Disconnecting .....\n" ) ;
rc = SQLDisconnect( hdbc ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
rc = SQLFreeHandle( SQL_HANDLE_DBC, hdbc ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
rc = SQLFreeHandle( SQL_HANDLE_ENV, henv ) ;
if ( rc != SQL_SUCCESS )
return( terminate( henv, rc ) ) ;
return( SQL_SUCCESS ) ;
} /* end main */