/**********************************************************************
* FILENAME : InsertRecs.c
*
* DESCRIPTION :
* Example used to pre-load a table with a number of records.
* Does not use SQLBulkOperations as the name may suggest.
*
* ODBC USAGE :
* Assumes table already exists (either TestTBL1 ot TestTBL2) with
* fixed layout. Prompts for number of records to be inserted and
* tablename. Uses :
* SQLBindParameter - to bind 4 parameters used in INSERT
* SQLPrepare - to prepare the INSERT
* Loops for number of records to be inserted
* Prompts for column values
* SQLExecute - to execute the INSERT
*
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include "util.c"
#define PERSONID_LEN 2
#define LASTNAME_LEN 255
#define FIRSTNAME_LEN 255
#define ADDRESS_LEN 255
#define CITY_LEN 255
#define TRUE 1
#define FALSE 0
SQLLEN cPersonId;
SQLCHAR strFirstName[FIRSTNAME_LEN];
SQLCHAR strLastName[LASTNAME_LEN];
SQLCHAR strAddress[ADDRESS_LEN];
SQLCHAR strCity[CITY_LEN];
SQLLEN lenFirstName=0, lenLastName=0, lenAddress=0, lenCity=0;
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 commitMode='A'; // A - Auto, M -Manual
char sqlTable[2][16]= {
{"TestTBL1"},
{"TestTBL2"}
};
char sqlStmtInsert[] = "INSERT INTO %s (FirstName, LastName, Address, City)"
"VALUES (?, ?, ?, ?)";
char sqlstr[256];
int i, tableNo, numRecs=1;
// Get table into which records are to written
getInt ("Which Table\n0 (Quit)"
"\n1 (TestTBL1 ID)"
"\n2 (TestTBL2 NoID)"
"\n ?", &tableNo, 'N', 0);
if (tableNo != 1 && tableNo != 2)
goto exit;
// Generate statement
sprintf (sqlstr, sqlStmtInsert, sqlTable[tableNo-1]);
printf ("SQL is : %s\n",sqlstr);
// Get Number of records to insert
getInt ("Number of Recs", &numRecs, 'N', 0);
if (numRecs==0) goto exit;
// Allocate environment
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
// Set ODBC Verion
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER*)SQL_OV_ODBC3, 0);
CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
henv, SQL_HANDLE_ENV);
// Allocate Connection
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
henv, SQL_HANDLE_DBC);
// Set Login Timeout
retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)",
hdbc, SQL_HANDLE_DBC);
// Set Auto Commit
retcode = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)TRUE, 0);
CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT)",
hdbc, SQL_HANDLE_DBC);
// Connect to DSN
retcode = SQLConnect(hdbc, (SQLCHAR*) "DATASOURCE", SQL_NTS,
(SQLCHAR*) NULL, 0, NULL, 0);
CHECK_ERROR(retcode, "SQLConnect(DSN:DATASOURCE;)",
hdbc, SQL_HANDLE_DBC);
// Allocate Statement Handle
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
// Bind Parameters to all fields
retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR, FIRSTNAME_LEN, 0,
strFirstName, FIRSTNAME_LEN, &lenFirstName);
retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR, LASTNAME_LEN, 0,
strLastName, LASTNAME_LEN, &lenLastName);
retcode = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR, ADDRESS_LEN, 0,
strAddress, ADDRESS_LEN, &lenAddress);
retcode = SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR, CITY_LEN, 0,
strCity, CITY_LEN, &lenCity);
// Prepare Statement
retcode = SQLPrepare(hstmt, (SQLCHAR*) sqlstr, SQL_NTS);
// Write numRecs to table
for (i=0;i<numRecs;i++) {
// Setup data and lengths
memset (strFirstName, ' ', FIRSTNAME_LEN);
memset (strLastName, ' ', LASTNAME_LEN);
memset (strAddress, ' ', ADDRESS_LEN);
memset (strCity, ' ', CITY_LEN);
getStr ("First Name", strFirstName, FIRSTNAME_LEN, 'N');
getStr ("Last Name", strLastName, LASTNAME_LEN, 'N');
getStr ("Address ", strAddress, ADDRESS_LEN, 'N');
getStr ("City ", strCity, CITY_LEN, 'N');
lenFirstName=strlen(strFirstName);
lenLastName=strlen(strLastName);
lenAddress=strlen(strAddress);
lenCity=strlen(strCity);
retcode = SQLExecute(hstmt);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
printf ("Status : ok\n");
} else {
printf ("Status : Error %i\n", retcode);
}
}
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
