/**********************************************************************
* FILENAME : DescribeParamAndCol.c
*
* DESCRIPTION :
*
* Example prompts user for SQL Statement and uses SQLDescribeParam
* and SQLDescribeCol to identify parameters and columns. Prompts
* user to input values for parameters, executes statement and outputs
* results sets.
*
* Deals with multiple 'text' fields, either as Parameters or Columns.
* i.e. longvarchars. Memory is allocated and bound to all columns and
* parameters. Data is requested from user for parameters. 'text' data
* parameter lengths are set using SQL_LEN_DATA_AT_EXEC, indicating data
* for them is acquired at exec time, in chunks.
*
* NOTE: See comments on cursor being set to DYNAMIC which is done so
* that 'text' fields can be re-retrieved in full by SQLGetData after the
* SQLFetch has already returned them initially. The default FORWARD ONLY
* cursor would not allow this.
*
* ODBC USAGE :
*
* Prompts user for statement
* (e.g. INSERT INTO TestTBL4 (name, Memo1, Memo2) VALUES (?, ?, ?) OR
* SELECT name, Memo1, Memo2 from TestTBL4, etc)
* SQLSetStmtAttr() to set cursor type to SQL_CURSOR_DYNAMIC
* SQLPrepare() to prepare statement
* For Parameters:-
* SQLNumParams() to get parameter count
* SQLDescribeParam() to get DB information on each parameter
* Allocates storage and uses SQLBindParameter based on parameter
* data type. Sets 'text' fields to data-at-exec and asks user for
* other parameter values
*
* For Column/row data:-
* SQLNumColumns() to get column count
* SQLDescribeColumn() for get DB information on each column requested
* Allocates storage and uses SQLBindColumn based on parameter data
* type. Sets buffer length to chunk size for 'text' fields, column
* data size otherwise.
*
* Execute statement:
* SQLExecute() for prerared statement
* For non-SELECTS (i.e. columns is zero) :-
* while SQL_NEED_DATA returned
* SQLParamData() and find column needing more data
* Prompts for more data with GetParamValue()
* SQLPutData() to write next/last chunk
* For SELECTS (i.e. columns is non-zero:-
* While SQLFetch returns ok and not NO_MORE_DATA
* Display Non - LONGVARCHAR column data
* SQLGetData() to get LONVARCHAR column data
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include "util.c"
#define MAXFIELDS 20
#define MAXQUERY 100
#define TEXTSIZE 100 // How big the text fields are
#define MAXBATCHSIZE 20+2 // Maximum read/write in one go
#define TRUE 1
// (Some) SQL data type codes (JUST FOR REF)
//#define SQL_UNKNOWN_TYPE 0
//#define SQL_CHAR 1
//#define SQL_NUMERIC 2
//#define SQL_DECIMAL 3
//#define SQL_INTEGER 4
//#define SQL_SMALLINT 5
//#define SQL_FLOAT 6
//#define SQL_REAL 7
//#define SQL_DOUBLE 8
//#if (ODBCVER >= 0x0300)
//#define SQL_DATETIME 9
//#endif
//#define SQL_VARCHAR 12
//* One-parameter shortcuts for date/time data types */
//#if (ODBCVER >= 0x0300)
//#define SQL_TYPE_DATE 91
//#define SQL_TYPE_TIME 92
//#define SQL_TYPE_TIMESTAMP 93
//#endif
SQLCHAR Statement[MAXQUERY];
SQLSMALLINT NumParams, NumColumns;
SQLSMALLINT i, DataType, DecimalDigits, Nullable, paramNo;
SQLULEN bytesRemaining;
//
// Prompts user for input
//
void gStr (char * str, char * prompt, int max) {
// Ask user for something to prepare
printf("%s : ", prompt);
// Get the name, with size limit.
fgets (str, max, stdin);
// remove \n
str[strlen(str)-1] = '\0';
return;
}
//
// Uses gStr() to prompt user for parameter value
//
void GetParamValue(SQLPOINTER BufferPtr, SQLINTEGER BufferLen,
SQLLEN *LenOrInd, SQLSMALLINT ParamNum, SQLSMALLINT DataType) {
char ParamPrompt[]="Parameter %i";
char Prompt[sizeof(ParamPrompt)+10];
SQLINTEGER tmp;
sprintf (Prompt, ParamPrompt, ParamNum);
gStr (BufferPtr, Prompt, BufferLen);
if (DataType != SQL_LONGVARCHAR) {
*LenOrInd=strlen (BufferPtr);
}
return;
}
//
// Allocated buffer to either Column or Parameter data
//
void AllocBuffer(SQLUINTEGER buffSize, SQLPOINTER *Ptr, SQLLEN *BufferLen) {
*Ptr=malloc (buffSize);
memset (*Ptr, ' ', buffSize);
if (BufferLen != NULL) {
*BufferLen=buffSize;
}
return;
}
//
// Returns the parameter number associated with the paramId pointer returned
// by SQLParamData()
//
SQLSMALLINT findParam (SQLSMALLINT NumParams, PTR pParamId,
SQLPOINTER PtrArray[]) {
int i;
for (i=0; i<NumParams; i++) {
if (PtrArray[i]==pParamId)
return i;
}
return -1;
}
//
// Program to illustrate SQLDescribeParam() and SQLDescribeCol()
//
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
SQLLEN status;
SQLSMALLINT statuslen;
SQLPOINTER ParamPtrArray[MAXFIELDS];
SQLLEN ParamBufferLenArray[MAXFIELDS];
SQLLEN ParamLenOrIndArray[MAXFIELDS];
SQLPOINTER ColPtrArray[MAXFIELDS];
SQLPOINTER ColNameArray[MAXFIELDS];
SQLLEN ColBufferLenArray[MAXFIELDS];
SQLLEN ColLenOrIndArray[MAXFIELDS];
SQLSMALLINT ColDataTypeArray[MAXFIELDS];
char *Str;
PTR pParamID;
int i, j=0;
SQLCHAR textBatch [MAXBATCHSIZE];
SQLCHAR ColName[255];
SQLSMALLINT ColNameLen;
SQLULEN ColumnSize;
SQLLEN siText;
// initialise pointer arrays so can be freed
for (i=0;i<MAXFIELDS;i++) {
ParamPtrArray[i]=NULL;
}
for (i=0;i<MAXFIELDS;i++) {
ColPtrArray[i]=NULL;
}
// Prompt the user for an SQL statement and prepare it.
gStr (Statement, "SQL Statement", MAXQUERY);
printf ("Statement is : %s\n", Statement);
// Allocate the ODBC environment and save handle.
retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
// Notify ODBC that this is an ODBC 3.0 app.
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 ODBC connection handle and connect.
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
hdbc, SQL_HANDLE_DBC);
// Connect to the driver
retcode=SQLDriverConnect(hdbc, NULL, "DSN=DATASOURCE;", SQL_NTS,
NULL, 0, NULL, SQL_DRIVER_COMPLETE);
CHECK_ERROR(retcode, "SQLDriverConnect(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);
//
// Set cursor type to DYNAMIC.
// To get the full content of 'text' column, we use SQLFetch followed by
// SQLGetData. For this to work however, (unless the text field is the
// last column in the statement) we have to set the cursor to either
// DYMANIC or STATIC because the cursor has to effectively 'go back' to
// the field to get the data again after SQLFetch has retrieved the
// first chunk and moved the cursor forward in the first read.
// Note: If the cursor is left as FORWARD and the 'text' column is the
// last column in the statement, it will work because the cursor still
// moving forward and not advance to the next record.
//
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
(SQLPOINTER) SQL_CURSOR_DYNAMIC, 0);
printf ("Preparing : %s\n", Statement);
retcode = SQLPrepare(hstmt, Statement, SQL_NTS);
CHECK_ERROR(retcode, "SQLPrepare(Statement)",
hstmt, SQL_HANDLE_STMT);
printf("SQLPrepare(hstmt, Statement, SQL_NTS) OK\n");
// dumpDescriptors ("HSTMT", hstmt, 'Y', 'Y', 1);
// Check to see if there are any parameters. If so, process them.
SQLNumParams(hstmt, &NumParams);
if (NumParams) {
printf ("Num Params : %i\n", NumParams);
for (i = 0; i < NumParams; i++) {
// Describe the parameter.
retcode = SQLDescribeParam(hstmt,
i+1,
&DataType,
&bytesRemaining,
&DecimalDigits,
&Nullable);
CHECK_ERROR(retcode, "SQLPrepare(Statement)",
hstmt, SQL_HANDLE_STMT);
printf("\nSQLDescribeParam() OK\n");
printf("Data Type : %i, bytesRemaining : %i, DecimalDigits : %i, Nullable %i\n",
(int)DataType, (int)bytesRemaining,
(int)DecimalDigits, (int)Nullable);
if (DataType==SQL_LONGVARCHAR) {
AllocBuffer(MAXBATCHSIZE, &ParamPtrArray[i],
&ParamBufferLenArray[i]);
} else {
AllocBuffer(bytesRemaining, &ParamPtrArray[i],
&ParamBufferLenArray[i]);
}
printf ("Param Buffer Ptr : %p\n",
(SQLPOINTER *) ParamPtrArray[i]);
printf ("Param Buffer Len : %i\n",
(int)ParamBufferLenArray[i]);
// Text field
// 100 bytes overall total
// indicates data for the parameter will be sent with SQLPutData
if (DataType==SQL_LONGVARCHAR) {
bytesRemaining=(SDWORD) TEXTSIZE;
ParamLenOrIndArray[i]=SQL_LEN_DATA_AT_EXEC(bytesRemaining);
printf ("Binding MEMO field - ");
printf ("Bytes Remaining : %i, ", (int)bytesRemaining);
printf ("ParamLenOrIndArray : %i\n", (int)ParamLenOrIndArray[i]);
retcode = SQLBindParameter(hstmt, // Statment Handle
i+1, // Parameter Number
SQL_PARAM_INPUT, // Type is INPUT
SQL_C_CHAR, // C Data Type
SQL_LONGVARCHAR, // SQL Data Type
bytesRemaining, // Parameter size big
0, // Decimal Digits
ParamPtrArray[i], // Param value Pointer
0, // Buffer Length
&ParamLenOrIndArray[i]);// Len or Indicator
// data for this column is grabbed when asked for
} else {
// Bind the memory to the parameter. Assume that we only have input parameters.
retcode = SQLBindParameter(hstmt,
i+1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
DataType,
bytesRemaining,
DecimalDigits,
ParamPtrArray[i],
ParamBufferLenArray[i],
&ParamLenOrIndArray[i]);
// Can get the data for this column immediately
GetParamValue(ParamPtrArray[i], ParamBufferLenArray[i],
&ParamLenOrIndArray[i], i+1, DataType);
}
CHECK_ERROR(retcode, "SQLBindParameter(SQL_PARAM_INPUT)",
hstmt, SQL_HANDLE_STMT);
printf("SQLBindParameter() OK\n");
}
} else {
printf ("No Params\n");
}
// Check to see if there are any Columns. If so, get their details
SQLNumResultCols(hstmt, &NumColumns);
if (NumColumns) {
printf ("Num Columns : %i\n", NumColumns);
for (i = 0; i < NumColumns; i++) {
// Describe the parameter.
retcode = SQLDescribeCol(hstmt,
i+1,
ColName, 255,
&ColNameLen,
&DataType,
&ColumnSize,
&DecimalDigits,
&Nullable);
CHECK_ERROR(retcode, "SQLDescribeCol()",
hstmt, SQL_HANDLE_STMT);
printf("\nSQLDescribeCol () OK\n");
printf("Data Type : %i, ColName : %s, DecimalDigits : %i, Nullable %i\n",
(int)DataType, ColName, (int)DecimalDigits, (int)Nullable);
AllocBuffer (ColNameLen+1, &ColNameArray[i], NULL);
strcpy (ColNameArray[i], ColName);
ColDataTypeArray[i]=DataType;
if (DataType==SQL_LONGVARCHAR) {
AllocBuffer(MAXBATCHSIZE, &ColPtrArray[i],
&ColBufferLenArray[i]);
} else {
AllocBuffer(ColumnSize, &ColPtrArray[i],
&ColBufferLenArray[i]);
}
printf ("Col Buffer Ptr : %p\n",
(SQLPOINTER *) ColPtrArray[i]);
printf ("Col Buffer Len : %i\n",
(int)ColBufferLenArray[i]);
if (DataType==SQL_LONGVARCHAR) {
//
// For LONGVARCHAR Columns, we get the first 'Buffer Length'
// or chunk of data in the first SQLFetch(). For the rest, we
// can use SQLGetData which will return the whole column (albeit
// in 'Buffer Length' chunks). This means by using SQLFetch
// followed by SQLGetData we actually get the first chuck
// twice. So, we can either NOT BIND, and SQLFetch wont give
// us the first chunk or BIND and ignore the first chunk when
// we use SQLGetData.
//
retcode = SQLBindCol (
hstmt, // Statment Handle
i+1, // Column Number
SQL_C_CHAR, // C Type
ColPtrArray[i], // Column value Pointer
ColBufferLenArray[i], // Buffer Length
&ColLenOrIndArray[i]); // Len or Indicator
} else {
// Bind the memory to the parameter.
retcode = SQLBindCol (
hstmt,
i+1,
SQL_C_CHAR,
ColPtrArray[i],
ColBufferLenArray[i],
&ColLenOrIndArray[i]);
}
CHECK_ERROR(retcode, "SQLBindCol()",
hstmt, SQL_HANDLE_STMT);
printf("SQLBindCol() OK\n");
}
} else {
printf ("No Columns\n");
}
// Execute the statement.
// If SQLExecute encounters a data-at-execution parameter,
// it returns SQL_NEED_DATA. The application sends the data using
// SQLParamData and SQLPutData.
retcode = SQLExecute (hstmt);
if ( (retcode!=SQL_NEED_DATA) &&
(retcode!=SQL_SUCCESS) &&
(retcode!=SQL_SUCCESS_WITH_INFO) ) {
CHECK_ERROR(retcode, "SQLExecute()", hstmt, SQL_HANDLE_STMT);
}
//
// Select/Delete or Insert?
//
// If no columns assume non-select statement
if (NumColumns == 0) {
// Get parameter needing more data
retcode = SQLParamData(hstmt, &pParamID);
while (retcode==SQL_NEED_DATA) {
// Find which parameter
paramNo = findParam (NumParams, pParamID, ParamPtrArray);
if (paramNo==-1) {
printf ("\nParam for more data not found\n");
goto exit;
}
while ((int)bytesRemaining > 0) {
printf ("Bytes still to write %i\n",
(int) bytesRemaining);
memset (ParamPtrArray[paramNo], ' ',
ParamBufferLenArray[paramNo]);
GetParamValue(ParamPtrArray[paramNo],
ParamBufferLenArray[paramNo],
NULL, paramNo+1, SQL_LONGVARCHAR);
printf ("Param %i, next %i bytes\n", paramNo+1,
(int)strlen(ParamPtrArray[paramNo]));
retcode = SQLPutData(hstmt,
(UCHAR *)ParamPtrArray[paramNo],
(SQLLEN) strlen(ParamPtrArray[paramNo]));
CHECK_ERROR(retcode, "SQLPutData()",
hstmt, SQL_HANDLE_STMT);
bytesRemaining-=strlen(ParamPtrArray[paramNo]);
}
bytesRemaining=(SDWORD) TEXTSIZE;
// Make final SQLParamData call.
printf ("Final Call - SQLParamData\n");
retcode = SQLParamData(hstmt, &pParamID);
if ( (retcode!=SQL_NEED_DATA) &&
(retcode!=SQL_SUCCESS) &&
(retcode!=SQL_SUCCESS_WITH_INFO) ) {
CHECK_ERROR(retcode, "SQLParamData()",
hstmt, SQL_HANDLE_STMT);
}
printf ("Code = %i (%i)\n", (int)retcode, SQL_NEED_DATA);
}
} else {
// initialise buffers
for (i=0;i<NumColumns;i++) {
memset( ColPtrArray[i], ' ', ColBufferLenArray[i]);
strcpy (ColPtrArray[i], "");
}
while ( (retcode = SQLFetch(hstmt)) != SQL_NO_DATA ) {
CHECK_ERROR(retcode, "SQLFetch()", hstmt, SQL_HANDLE_STMT);
// Loop round each column, output based on TYPE
printf ("\n\nRecord %i", ++j);
for (i=0;i<NumColumns;i++) {
printf ("\nColumn : %i", i+1);
printf ("\nName : %s", (char *) ColNameArray[i]);
printf ("\nType : %i", (int) ColDataTypeArray[i]);
// As we are BINDING LONGVARCHAR columns in this example, we
// ignore the chunk of data returned by the SQLFetch. We get
// the whole column regardless.
if (ColDataTypeArray[i] == SQL_LONGVARCHAR) {
printf ("\nValue : ");
while ((retcode = SQLGetData(hstmt,
i+1,
SQL_CHAR,
ColPtrArray[i],
ColBufferLenArray[i],
&ColLenOrIndArray[i]))
!= SQL_NO_DATA) {
CHECK_ERROR(retcode, "SQLGetData()",
hstmt, SQL_HANDLE_STMT);
printf ("\n %s",
(char *) ColPtrArray[i]);
}
} else {
printf ("\nValue : %s",
(char *) ColPtrArray[i]);
}
}
// re-initialise buffers
for (i=0;i<NumColumns;i++) {
memset( ColPtrArray[i], ' ', ColBufferLenArray[i]);
strcpy (ColPtrArray[i], "");
}
}
}
exit:
// Free the memory allocated for each parameter and the memory allocated
// for the arrays of pointers, buffer lengths, and length/indicator values.
for (i = 0; i < NumParams; i++) {
if (ParamPtrArray[i]!=NULL) free(ParamPtrArray[i]);
}
for (i = 0; i < NumColumns; i++) {
if (ColPtrArray[i]!=NULL) free(ColPtrArray[i]);
}
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
