/**********************************************************************
* 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