/**********************************************************************
* FILENAME :        CreateTable.c
*
* DESCRIPTION :
*       Example that creates one of 8 tables used in other
*       examples
*
* ODBC USAGE :
* 		Prompts for table no
* 		SQLExecDirect - to execute CREATE TABLE statement
*
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>

#include "util.c"

int main () {
    SQLHENV  henv  = SQL_NULL_HENV;   	// Environment
    SQLHDBC  hdbc  = SQL_NULL_HDBC;   	// Connection handle
    SQLHSTMT hstmt = SQL_NULL_HSTMT;  	// Statement handle
    SQLRETURN retcode;			// Return status



        char sqlStatement[9][256]= {
    	{"CREATE TABLE TestTBL1 (PersonID int NOT NULL IDENTITY(1,1),"
                                 "FirstName varchar(255) NOT NULL,"
                                 "LastName varchar(255),"
                                 "Address varchar(255), City varchar(255))"},
    	{"CREATE TABLE TestTBL1Copy (PersonID int NOT NULL IDENTITY(1,1),"
                                 "FirstName varchar(255) NOT NULL,"
                                 "LastName varchar(255),"
                                 "Address varchar(255), City varchar(255))"},
    	{"CREATE TABLE TestTBL2 (FirstName varchar(255) NOT NULL,"
                                 "LastName varchar(255), Address varchar(255),"
                                 "City varchar(255))"},
    	{"CREATE TABLE TestTBL3 (NAME char(30), AGE int, BIRTHDAY datetime,"
                                 "Memo1 text)"},
    	{"CREATE TABLE TestTBL4 (NAME char(30), AGE int, BIRTHDAY datetime,"
                                 "Memo1 text, Memo2 text)"},
    	{"CREATE TABLE TestTBL5 (P_Id int IDENTITY NOT NULL,"
                                 "LastName varchar(255) NOT NULL,"
                                 "FirstName varchar(255),"
                                 "F3_Id int, PRIMARY KEY (P_Id),"
                                 "CONSTRAINT fk_F3_Id FOREIGN KEY (F3_Id)"
                                 "REFERENCES TestTBL8(F3_Id))"},
    	{"CREATE TABLE TestTBL6 (F1_Id int NOT NULL, OrderNo int NOT NULL,"
                                 "P_Id int, PRIMARY KEY (F1_Id),"
                                 "CONSTRAINT fk_F1_Id FOREIGN KEY (P_Id)"
                                 "REFERENCES TestTBL5(P_Id))"},
    	{"CREATE TABLE TestTBL7 (F2_Id int NOT NULL,"
                                 "OrderNo int NOT NULL, P_Id int,"
                                 "PRIMARY KEY (F2_Id),"
                                 "CONSTRAINT fk_F2_Id FOREIGN KEY (P_Id)"
                                 "REFERENCES TestTBL5(P_Id))"},
    	{"CREATE TABLE TestTBL8 (F3_Id int IDENTITY NOT NULL,"
                                 "OrderNo int NOT NULL,  PRIMARY KEY (F3_Id))"}
	};


        int tableNo=-1;
        char confirm='Y';
        char reply=' ';

	// Allocate an environment handle
	retcode=SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
        CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
                    henv, SQL_HANDLE_ENV);

	// We want ODBC 3 support
	retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                            (void *) SQL_OV_ODBC3, 0);
        CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
                    hdbc, SQL_HANDLE_DBC);

	// Allocate a connection handle
	retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
	CHECK_ERROR(retcode, "SQLAllocHandle", hdbc, SQL_HANDLE_DBC);

	// Connect to the DSN
	retcode=SQLDriverConnect(hdbc, NULL, "DSN=DATASOURCE;", SQL_NTS, NULL,
                             0, NULL, SQL_DRIVER_COMPLETE);
        CHECK_ERROR(retcode, "SQLDriverConnect(DSN=DATASOURCE;)",
                    hdbc, SQL_HANDLE_DBC);

	// Allocate a statement handle
	retcode=SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
        CHECK_ERROR(retcode, "SQLAllocHandle(STMT)",
                    hstmt, SQL_HANDLE_STMT);

	while (tableNo!=0) {
	    reply=getInt ("Which Table\n0 (Quit)"
                       "\n1 (TestTBL1 ID)"
                       "\n2 (TestTBL1Copy ID)"
                       "\n3 (TestTBL2 NoID)"
                       "\n4 (TestTBL3 Memo1)"
                       "\n5 (TestTBL4 Memo1, Memo2)"
                       "\n6 (TestTBL5 PK FK in 8)"
                       "\n7 (TestTBL6 FK in 5)"
                       "\n8 (TestTBL7 FK in 5)"
                       "\n9 (TestTBL8 PK)"
                       "\n  ?", &tableNo, confirm, 0);


	    if (tableNo!=0 && (reply == 'Y' || reply =='y')) {
			printf ("Sending %s\n", &sqlStatement[tableNo-1][0]);
			retcode = SQLExecDirect(hstmt, &sqlStatement[tableNo-1][0],
                                    SQL_NTS);
			if (retcode == SQL_SUCCESS ||
                retcode == SQL_SUCCESS_WITH_INFO) {
				printf ("Table Created\n");
			} else {
				printf ("Table Create Failed: %i\n", (int) retcode);
				CHECK_ERROR(retcode, "SQLExecDirect()", hstmt,
                            SQL_HANDLE_STMT);
			}
		}
	}

exit:

    printf ("\nComplete.\n");

    // Free handles
    // Statement
    if (hstmt != SQL_NULL_HSTMT)
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);

    // Connection
    if (hdbc != SQL_NULL_HDBC) {
        SQLDisconnect(hdbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    }

    // Environment
    if (henv != SQL_NULL_HENV)
        SQLFreeHandle(SQL_HANDLE_ENV, henv);

    return 0;
}

See Also


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.