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

See Also


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