Site navigation
Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase, DB2, & more.
/**********************************************************************
* FILENAME : DescribeColumn.c
*
* DESCRIPTION :
* Example illustrates use of SQLDescribeCol and SQLColAttribute to
* obtain column information from a prepared statement and then use
* that information to bind parameters in the prepared statement
* before executing it.
*
* ODBC USAGE :
* SQLPrepare - prepare basic 'SELECT * FROM' statement
* SQLNumResultCols- get number of columns
* SQLDescribeCol - obtain column information (name, type, etc) and
* save
* SQLColAttribute - obtain column max size information
* SQLBindCol - binds the column data saved by SQLDescribeCol
* to the statement
* SQLExecute - execute the 'SELECT * FROM'
* SQLFetch - return the records until SQL_NO_DATA returned
*
*/
#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 MAXCOLS 10
#define TRUE 1
SQLHENV henv = SQL_NULL_HENV; // Environment
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLRETURN retcode;
SQLRETURN display_results(SQLHSTMT hstmt)
{
SQLCHAR colname[32]; // column name
SQLSMALLINT coltype; // column type
SQLSMALLINT colnamelen; // length of column name
SQLSMALLINT nullable; // whether column can have NULL value
SQLLEN collen[MAXCOLS]; // array of column lengths
SQLSMALLINT decimaldigits; // no of digits if column is numeric
SQLLEN outlen[MAXCOLS]; // lengths of column values returned
SQLCHAR * data[MAXCOLS]; // buffers for retrieving column values
SQLRETURN retcode; // general return code
SQLLEN displaysize; // drivers column display size
SQLINTEGER i,j;
SQLSMALLINT columns;
// Initialise data array
for (i=0;i<MAXCOLS;i++) {
data[i]=NULL;
}
// Get number of columns from prepared statement
retcode=SQLNumResultCols(hstmt, &columns);
CHECK_ERROR(retcode, "SQLNumResultCols()",
hstmt, SQL_HANDLE_STMT);
printf ("Number of columns is %i\n", (int) columns);
printf ("Use SQLDescribeCol to obtain column details\n");
for (i = 0; i < columns; i++)
{
// for each column from the prepared statement in hstmt, get the
// column name, type, column size, decimal digits, and nullability
retcode = SQLDescribeCol (hstmt,
(SQLUSMALLINT)i+1,
colname,
sizeof (colname),
&colnamelen,
&coltype,
&collen[i],
&decimaldigits,
&nullable);
CHECK_ERROR(retcode, "SQLDescribeCol()",
hstmt, SQL_HANDLE_STMT);
displaysize=0;
// get Maximum number of characters required to display data
// from the column.
retcode = SQLColAttribute (hstmt,
(SQLUSMALLINT)i+1,
SQL_COLUMN_DISPLAY_SIZE,
NULL, 0, NULL, &displaysize);
CHECK_ERROR(retcode, "SQLColAttribute()",
hstmt, SQL_HANDLE_STMT);
/* set column length to max of display length, and column name
length. Plus one byte for null terminator */
if (collen[i] > displaysize) {
collen[i]=displaysize+1;
} else {
collen[i]=collen[i]+1;
}
// allocate memory to bind column
data[i] = (SQLCHAR *) malloc (collen[i]);
}
//Bind columns to data array
printf ("Bind columns to statement\n");
for (i = 0; i < columns; i++) {
outlen[i]=0;
retcode = SQLBindCol (hstmt,
(SQLUSMALLINT)i+1,
SQL_C_CHAR,
(SQLPOINTER) data[i],
collen[i],
&outlen[i]);
CHECK_ERROR(retcode, "SQLBindCol()", hstmt, SQL_HANDLE_STMT);
}
/* execute the now fully prepared statement */
printf ("Execute Statement\n");
retcode=SQLExecute(hstmt);
CHECK_ERROR(retcode, "SQLExecute()", hstmt, SQL_HANDLE_STMT);
printf ("Fetch Results\n");
/* display the result rows */
for (i=0; ; i++) {
retcode = SQLFetch(hstmt);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
printf("Record %i : ", i+1);
for (j=0;j<columns;j++) {
printf ("%s", rtrim(data[j], ' '));
if (j!=columns-1) {printf (", ");}
}
printf ("\n");
} else {
if (retcode != SQL_NO_DATA) {
CHECK_ERROR(retcode, "SQLFetch()",
hstmt, SQL_HANDLE_STMT);
}
break;
}
}
exit:
/* free data buffers */
for (i = 0; i < columns; i++)
{
if (data[i]!=NULL) free (data[i]);
}
return;
}
int main () {
SQLSMALLINT sCustID;
char sqlstr[] = "SELECT * from TestTBL1Copy order by PersonID";
// 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 DSN
retcode=SQLDriverConnect(hdbc, NULL, "DSN=DATASOURCE;", SQL_NTS,
NULL, 0, NULL, SQL_DRIVER_COMPLETE);
CHECK_ERROR(retcode, "SQLDriverConnect(DATASOURCE 0)",
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);
// Prepare the statement
retcode = SQLPrepare(hstmt, (SQLCHAR*) sqlstr, SQL_NTS);
CHECK_ERROR(retcode, "SQLPrepare(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
retcode = display_results(hstmt);
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;
}
Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase, DB2, & more.