ODBC drivers >

JDBC drivers >

Bridges, gateways >

Other >

All products

Connect JDBC to any ODBC driver

Connect JDBC to Microsoft Access

Cross-platform or cross-architecture access to any ODBC driver

ODBC access to any JDBC driver

Connect dbExpress applications to any ODBC driver

Connect XML applications to any ODBC driver

Access mutiple ODBC data sources from one SQL statement

Create custom ODBC drivers

Bespoke data access requirements

In the pipeline

Support

Resources

Quick start

Licensing

Knowledge Base

User Guides

Company

About Us

Careers & Partners

Download ODBC Drivers for

Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase, DB2, & more.

Learn More 
/**********************************************************************
* FILENAME : ListProcedureColumns.c
*
* DESCRIPTION :
* Example uses SQLProcedures to return the list of input and
* output parameters, as well as the column details for the
* procedures visible to a datasource.
*
* ODBC USAGE :
* selectDSN() to get data source name
* SQLProcedures() - with statement handle and NULL for SchemaName,
* ProcName and ColumnName. This retrieves all procedure
* names.
* SQLProcedureColumns - for each procedure name returned by
* SQLProcedures, retrieved column names, types and data types.
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include "util.c"
// Arbitary sizes for char buffs
#define BUFF_SIZE 255
// Arbitary sizes for number of procedures and
#define MAX_PROCS 100
// Declare buffers for result set data
SQLCHAR strProcedureName[BUFF_SIZE];
struct DataBinding {
 SQLSMALLINT TargetType;
 SQLPOINTER TargetValuePtr;
 SQLINTEGER BufferLength;
 SQLLEN StrLen_or_Ind;
};
//
// Fill an array of procedure names associated with a datasource
//
SQLRETURN getProcedures (SQLCHAR *procNames[], // procedure names array
 SQLLEN *noProcNames, // no of procedures found
 SQLCHAR *dsn) { // data source handle
 SQLHENV henv = SQL_NULL_HENV; // Environment
 SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
 SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
 SQLRETURN retcode;
 SQLCHAR strProcName[BUFF_SIZE];
 SQLLEN lenProcName;
 char confirm='N';
 char reply=' ';
 int i;
 retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
 CHECK_ERROR(retcode, "SQLAllocHandle (SQL_HANDLE_ENV)",
 henv, SQL_HANDLE_ENV);
 retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
 (SQLPOINTER*)SQL_OV_ODBC3, 0);
 CHECK_ERROR(retcode, "SQLSetEnvAttr (SQL_ATTR_ODBC_VERSION)",
 henv, SQL_HANDLE_ENV);
 retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
 CHECK_ERROR(retcode, "SQLAllocHandle (SQL_HANDLE_DBC)",
 hdbc, SQL_HANDLE_DBC);
 retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT,
 (SQLPOINTER)5, 0);
 CHECK_ERROR(retcode, "SQLSetConnectAttr (SQL_LOGIN_TIMEOUT)",
 hdbc, SQL_HANDLE_DBC);
 retcode = SQLConnect(hdbc, (SQLCHAR*) dsn, SQL_NTS,
 (SQLCHAR*) NULL, 0, NULL, 0);
 CHECK_ERROR(retcode, "SQLConnect (SQL_HANDLE_DBC)",
 hdbc, SQL_HANDLE_DBC);
 retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
 CHECK_ERROR(retcode, "SQLAllocHandle (SQL_HANDLE_STMT)",
 hstmt, SQL_HANDLE_STMT);
 // Request all procedures
 retcode = SQLProcedures (hstmt,
 NULL, 0,
 NULL, 0,
 NULL, 0);
 if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
 // Bind Procedure Name column of result set to buffers
 retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR, strProcName,
 sizeof(strProcName), &lenProcName);
 while (SQL_SUCCESS == retcode && i < MAX_PROCS) {
 retcode = SQLFetch(hstmt);
 CHECK_ERROR(retcode, "SQLFetch (Procedures)",
 hstmt, SQL_HANDLE_STMT);
 procNames[i]=(char *)malloc((strlen(strProcName)+1)*sizeof(char));
 strcpy (procNames[i++], strProcName);
 }
 *noProcNames=i;
 }
exit:
 if (hstmt != SQL_NULL_HSTMT)
 SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
 if (hdbc != SQL_NULL_HDBC) {
 SQLDisconnect(hdbc);
 SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
 }
 if (henv != SQL_NULL_HENV)
 SQLFreeHandle(SQL_HANDLE_ENV, henv);
 return retcode;
}
int main () {
 SQLHENV henv = SQL_NULL_HENV; // Environment
 SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
 SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
 SQLRETURN retcode;
 SQLCHAR *procNames[MAX_PROCS];
 SQLLEN noProcNames;
 SQLCHAR colName[BUFF_SIZE];
 // vars for full result set
 SQLCHAR strProcedureCat[BUFF_SIZE];
 SQLCHAR strProcedureSchem[BUFF_SIZE];
 SQLCHAR strProcedureName[BUFF_SIZE];
 SQLCHAR strColumnName[BUFF_SIZE];
 SQLSMALLINT ColumnType;
 SQLSMALLINT DataType;
 SQLCHAR strTypeName[BUFF_SIZE];
 SQLLEN ColumnSize;
 SQLLEN BufferLength;
 SQLLEN DecimalDigits;
 SQLSMALLINT NumPrevRadix;
 SQLSMALLINT Nullable;
 SQLCHAR strRemarks[BUFF_SIZE];
 SQLCHAR strColumnDef[BUFF_SIZE];
 SQLSMALLINT SQLDataType;
 SQLSMALLINT SQLDateTimeSub;
 SQLLEN CharOctetLength;
 SQLLEN OrdinalPosition;
 SQLCHAR strIsNullable[BUFF_SIZE];
 // ind/len vars for full result set
 SQLLEN lenProcedureCat;
 SQLLEN lenProcedureSchem;
 SQLLEN lenProcedureName;
 SQLLEN lenColumnName;
 SQLLEN lenColumnType;
 SQLLEN lenDataType;
 SQLLEN lenTypeName;
 SQLLEN lenColumnSize;
 SQLLEN lenBufferLength;
 SQLLEN lenDecimalDigits;
 SQLLEN lenNumPrevRadix;
 SQLLEN lenNullable;
 SQLLEN lenRemarks;
 SQLLEN lenColumnDef;
 SQLLEN lenSQLDataType;
 SQLLEN lenSQLDateTimeSub;
 SQLLEN lenCharOctetLength;
 SQLLEN lenOrdinalPosition;
 SQLLEN lenIsNullable;
 char procName[BUFF_SIZE];
 char dsn[BUFF_SIZE];
 char confirm='N';
 char reply=' ';
 int i, header;
 // Allcoate environment handle
 retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
 CHECK_ERROR(retcode, "SQLAllocHandle (SQL_HANDLE_ENV)",
 henv, SQL_HANDLE_ENV);
 // Set ODBC Version
 retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
 (SQLPOINTER*)SQL_OV_ODBC3, 0);
 CHECK_ERROR(retcode, "SQLSetEnvAttr (SQL_HANDLE_ENV)",
 henv, SQL_HANDLE_ENV);
 // Prompt for DSN
 retcode = selectDSN (henv, dsn, "Select DSN : ");
 if (retcode != SQL_SUCCESS) {
 printf ("DSN not selected, exiting.");
 goto exit;
 }
 // Allocate connection handle
 retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
 CHECK_ERROR(retcode, "SQLAllocHandle (SQL_HANDLE_DBC)",
 hdbc, SQL_HANDLE_DBC);
 // Set connection timeout
 retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
 CHECK_ERROR(retcode, "SQLSetConnectAttr (SQL_LOGIN_TIMEOUT)",
 hdbc, SQL_HANDLE_DBC);
 // Connect to DSN
 retcode = SQLConnect(hdbc, (SQLCHAR*) dsn, SQL_NTS,
 (SQLCHAR*) NULL, 0, NULL, 0);
 CHECK_ERROR(retcode, "SQLConnect (dsn)", 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);
 // Get list of procedure names
 retcode = getProcedures (procNames, &noProcNames, dsn);
 if (retcode==SQL_SUCCESS) {
 for (i=0;i<noProcNames;i++) {
 // Get columns associated with each pocedure name
 header=0;
 retcode = SQLProcedureColumns (hstmt,
 NULL, 0,
 NULL, 0,
 procNames[i], SQL_NTS,
 NULL, 0);
 CHECK_ERROR(retcode, "SQLProcedureColumns ()",
 hstmt, SQL_HANDLE_STMT);
 // Bind all 19 columns
 SQLBindCol (hstmt, 1, SQL_C_CHAR, strProcedureCat,
 sizeof(strProcedureCat), &lenProcedureCat);
 SQLBindCol (hstmt, 2, SQL_C_CHAR, strProcedureSchem,
 sizeof(strProcedureSchem),&lenProcedureSchem);
 SQLBindCol (hstmt, 3, SQL_C_CHAR, strProcedureName,
 sizeof(strProcedureName), &lenProcedureName);
 SQLBindCol (hstmt, 4, SQL_C_CHAR, strColumnName,
 sizeof(strColumnName), &lenColumnName);
 SQLBindCol (hstmt, 5, SQL_C_SHORT, &ColumnType,
 sizeof(ColumnType), &lenColumnType);
 SQLBindCol (hstmt, 6, SQL_C_SHORT, &DataType,
 sizeof(DataType), &lenDataType);
 SQLBindCol (hstmt, 7, SQL_C_CHAR, strTypeName,
 sizeof(strTypeName), &lenTypeName);
 SQLBindCol (hstmt, 8, SQL_C_LONG, &ColumnSize,
 sizeof(ColumnSize), &lenColumnSize);
 SQLBindCol (hstmt, 9, SQL_C_LONG, &BufferLength,
 sizeof(BufferLength), &lenBufferLength);
 SQLBindCol (hstmt, 10, SQL_C_SHORT, &DecimalDigits,
 sizeof(DecimalDigits), &lenDecimalDigits);
 SQLBindCol (hstmt, 11, SQL_C_SHORT, &NumPrevRadix,
 sizeof(NumPrevRadix), &lenNumPrevRadix);
 SQLBindCol (hstmt, 12, SQL_C_SHORT, &Nullable,
 sizeof(Nullable), &lenNullable);
 SQLBindCol (hstmt, 13, SQL_C_CHAR, strRemarks,
 sizeof(strRemarks), &lenRemarks);
 SQLBindCol (hstmt, 14, SQL_C_CHAR, strColumnDef,
 sizeof(strColumnDef), &lenColumnDef);
 SQLBindCol (hstmt, 15, SQL_C_SHORT, &SQLDataType,
 sizeof(SQLDataType), &lenSQLDataType);
 SQLBindCol (hstmt, 16, SQL_C_SHORT, &SQLDateTimeSub,
 sizeof(SQLDateTimeSub), &lenSQLDateTimeSub);
 SQLBindCol (hstmt, 17, SQL_C_LONG, &CharOctetLength,
 sizeof(CharOctetLength), &lenCharOctetLength);
 SQLBindCol (hstmt, 18, SQL_C_LONG, &OrdinalPosition,
 sizeof(OrdinalPosition), &lenOrdinalPosition);
 SQLBindCol (hstmt, 19, SQL_C_CHAR, strIsNullable,
 sizeof(strIsNullable), &lenIsNullable);
 // Get column data.
 while (retcode == SQL_SUCCESS) {
 retcode = SQLFetch(hstmt);
 CHECK_ERROR(retcode, "SQLFetch (ProcedureColumns)",
 hstmt, SQL_HANDLE_STMT);
 if (header++==0) {
 printf ("\nProcedure Name : %s\n",strProcedureName);
 }
 printf (" Column Name : %s\n", strColumnName);
 printf (" Column Type : %i\n", ColumnType);
 printf (" Data Type : %i\n", DataType);
 if (retcode==SQL_NO_DATA && header==1) {
 printf ("(NO DATA)\n");
 }
 }
 }
 }
 printf ("\nThe End.\n");
exit:
 if (hstmt != SQL_NULL_HSTMT)
 SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
 if (hdbc != SQL_NULL_HDBC) {
 SQLDisconnect(hdbc);
 SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
 }
 if (henv != SQL_NULL_HENV)
 SQLFreeHandle(SQL_HANDLE_ENV, henv);
}

Further information

Download ODBC Drivers for

Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase, DB2, & more.

Learn More
Share:

AltStyle によって変換されたページ (->オリジナル) /