/**********************************************************************
* FILENAME : CreateTableWithID.c
*
* DESCRIPTION :
* Simple SQL SERVER example to create a basic table with an
* identity field
*
* ODBC USAGE :
* Prompts for table name
* SQLExecDirect - to execute CREATE TABLE statement
*
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include "util.c"
#define NAME_LEN 64
#define STMT_LEN 128
int main () {
SQLHENV henv = SQL_NULL_HENV; // Environment
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLRETURN retcode;
char sqlStmtCreate[] = "CREATE TABLE %s"
"(PersonID int NOT NULL IDENTITY(1,1),"
"FirstName varchar(255) NOT NULL,"
"LastName varchar(255),"
"Address varchar(255), City varchar(255))";
char table[NAME_LEN];
char sqlstr[STMT_LEN];
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);
// Ask user for table name (TestTBL1)
reply=getStr ("Table Name", table, NAME_LEN, confirm);
if (reply == 'Y' || reply =='y') {
// Substitute table name in statement
sprintf (sqlstr, sqlStmtCreate, table);
printf ("Sending : %s\n", sqlstr);
// Execute CREATE TABLE
retcode = SQLExecDirect(hstmt, sqlstr, SQL_NTS);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
printf ("Table Created\n");
} else {
printf ("Table Create Failed : \n");
CHECK_ERROR(retcode, "SQLExecDirect(STMT)",
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;
}
Further information
