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 : ListTableColumns.c
*
* DESCRIPTION :
* Example finds tables starting with 'TestTBL' and for each one
* outputs the column names, size and data type.
*
* ODBC USAGE :
* 		SQLGetInfo - with SQL_DATABASE_NAME info type - to get name
* of the current database in use. e.g msdb
* 		SQLGetInfo - with SQL_USER_NAME info type - to get the name
* used in a particular database. e.g. dbo
* Creates a catalogue table with 5 elements table details, namely
* TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE and
* REMARKS (see MSDN SQLTables).
* SQLBindCol - to bind the columns from the catalogue table before
* calling SQLTables
* SQLTables - with database, user and TABLE to initiate retrieval
* of tables from msdb/dbo
* SQLFetch - loops until all all tables returned, saviing ones
* starting with 'TestTBL' for later
* SQLColumns - called for each table saves, displaying Column Name,
* Column Size and Data Type
*
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include "util.c"
#define STR_LEN 128 + 1
#define REM_LEN 254 + 1
// Arbitary sizes for number of tables and
#define MAX_TABLES 100
#define BUFFERSIZE 1024
// Declare buffers for result set data
SQLCHAR strSchema[STR_LEN];
SQLCHAR strCatalog[STR_LEN];
SQLCHAR strColumnName[STR_LEN];
SQLCHAR strTableName[STR_LEN];
SQLCHAR strTypeName[STR_LEN];
SQLCHAR strRemarks[REM_LEN];
SQLCHAR strColumnDefault[STR_LEN];
SQLCHAR strIsNullable[STR_LEN];
SQLINTEGER ColumnSize;
SQLINTEGER BufferLength;
SQLINTEGER CharOctetLength;
SQLINTEGER OrdinalPosition;
SQLSMALLINT DataType;
SQLSMALLINT DecimalDigits;
SQLSMALLINT NumPrecRadix;
SQLSMALLINT Nullable;
SQLSMALLINT SQLDataType;
SQLSMALLINT DatetimeSubtypeCode;
SQLHSTMT hstmt = NULL;
// Declare buffers for bytes available to return
SQLLEN lenCatalog;
SQLLEN lenSchema;
SQLLEN lenTableName;
SQLLEN lenColumnName;
SQLLEN lenDataType;
SQLLEN lenTypeName;
SQLLEN lenColumnSize;
SQLLEN lenBufferLength;
SQLLEN lenDecimalDigits;
SQLLEN lenNumPrecRadix;
SQLLEN lenNullable;
SQLLEN lenRemarks;
SQLLEN lenColumnDefault;
SQLLEN lenSQLDataType;
SQLLEN lenDatetimeSubtypeCode;
SQLLEN lenCharOctetLength;
SQLLEN lenOrdinalPosition;
SQLLEN lenIsNullable;
struct DataBinding {
 SQLSMALLINT TargetType;
 SQLPOINTER TargetValuePtr;
 SQLINTEGER BufferLength;
 SQLLEN StrLen_or_Ind;
};
//
// Retrieves list of tables based on iTableStrPtr
// Example is self contained, using own handles
//
int getTheseTables (char *iTableName, SQLCHAR *tableNames[]) {
 int i, j, count = 1, numCols = 5;
 SQLCHAR * dbName = (SQLCHAR *)malloc( sizeof(SQLCHAR)*BUFFERSIZE );
 SQLCHAR * userName = (SQLCHAR *)malloc( sizeof(SQLCHAR)*BUFFERSIZE );
 SQLCHAR connStrbuffer[1024];
 SQLSMALLINT connStrBufferLen, bufferLen;
 SQLHENV henv = SQL_NULL_HENV; // Environment
 SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
 SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
 SQLRETURN retcode;
 struct DataBinding* catalogResult = (struct DataBinding*)
 malloc( numCols * sizeof(struct DataBinding) );
 SQLCHAR* selectAllQuery = (SQLCHAR *)
 malloc( sizeof(SQLCHAR) * BUFFERSIZE );
 // Create 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,
 (SQLCHAR *)(void*)SQL_OV_ODBC3, -1);
 CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
 henv, SQL_HANDLE_ENV);
 // Create Connection Handle
 retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
 CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
 hdbc, SQL_HANDLE_DBC);
 // Set Login Timeout
 retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)10, 0);
 CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)",
 hdbc, SQL_HANDLE_DBC);
 // Connect to data source
 retcode = SQLConnect(hdbc, (SQLCHAR*) "DATASOURCE", SQL_NTS,
 (SQLCHAR*) NULL, 0, NULL, 0);
 CHECK_ERROR(retcode, "SQLConnect(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);
 // Display the database information
 retcode = SQLGetInfo(hdbc, SQL_DATABASE_NAME, dbName,
 (SQLSMALLINT)BUFFERSIZE,
 (SQLSMALLINT *)&bufferLen);
 CHECK_ERROR(retcode, "SQLGetInfo(SQL_DATABASE_NAME)",
 hdbc, SQL_HANDLE_DBC);
 retcode = SQLGetInfo(hdbc, SQL_USER_NAME, userName,
 (SQLSMALLINT)BUFFERSIZE, &bufferLen);
 CHECK_ERROR(retcode, "SQLGetInfo(SQL_USER_NAME)",
 hdbc, SQL_HANDLE_DBC);
 printf ("Current DB Name : %s\n", dbName);
 printf ("Current User Name : %s\n", userName);
 // Buffers for table names results
 for ( i = 0 ; i < numCols ; i++ ) {
 catalogResult[i].TargetType = SQL_C_CHAR;
 catalogResult[i].BufferLength = (BUFFERSIZE + 1);
 catalogResult[i].TargetValuePtr =
 malloc( sizeof(unsigned char)*catalogResult[i].BufferLength );
 }
 // Bind results set columns to table names
 for ( i = 0 ; i < numCols ; i++ ) {
 retcode = SQLBindCol(hstmt, (SQLUSMALLINT)i+1,
 catalogResult[i].TargetType,
 catalogResult[i].TargetValuePtr,
 catalogResult[i].BufferLength,
 &(catalogResult[i].StrLen_or_Ind));
 CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
 hstmt, SQL_HANDLE_STMT);
 }
 retcode = SQLTables( hstmt, dbName, SQL_NTS, userName, SQL_NTS, "%",
 SQL_NTS, "TABLE", SQL_NTS );
 CHECK_ERROR(retcode, "SQLTables()", hstmt, SQL_HANDLE_STMT);
 // Create array of my tables starting with 'TestTBL'
 i=0;
 for ( retcode = SQLFetch(hstmt) ;
 retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO ;
 retcode = SQLFetch(hstmt), ++count ) {
 if ( strstr (catalogResult[2].TargetValuePtr, "TestTBL") != 0 ) {
 tableNames[i]=(char *)
 malloc((strlen(catalogResult[2].TargetValuePtr)+1)*sizeof(char));
 strcpy (tableNames[i], catalogResult[2].TargetValuePtr);
 printf( " Found Table %s\n", tableNames[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 i;
}
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 *tableNames[MAX_TABLES];
 SQLINTEGER tableCount, i;
 // Create 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_ATTR_ODBC_VERSION)",
 henv, SQL_HANDLE_ENV);
 // Create Connection Handle
 retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
 CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
 hdbc, 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);
 // Connect to data source
 retcode = SQLConnect(hdbc, (SQLCHAR*) "DATASOURCE", SQL_NTS,
 (SQLCHAR*) NULL, 0, NULL, 0);
 CHECK_ERROR(retcode, "SQLConnect(DATASOURCE)",
 hdbc, SQL_HANDLE_DBC);
 // Create Statement Handle
 retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
 CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
 hstmt, SQL_HANDLE_STMT);
 // Get list of tables and columns in each table
 tableCount=getTheseTables ("TestTBL", tableNames);
 for (i=0; i<tableCount; i++) {
 printf ("\nTable : %s\n", tableNames[i]);
 retcode = SQLColumns(hstmt, NULL, 0, NULL, 0,
 (SQLCHAR*)tableNames[i], SQL_NTS, NULL, 0);
 CHECK_ERROR(retcode, "SQLColumns(SQL_HANDLE_STMT)",
 hstmt, SQL_HANDLE_STMT);
 // Bind columns in result set to buffers
 if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
 SQLBindCol(hstmt, 1, SQL_C_CHAR, strCatalog,
 STR_LEN, &lenCatalog);
 SQLBindCol(hstmt, 2, SQL_C_CHAR, strSchema,
 STR_LEN, &lenSchema);
 SQLBindCol(hstmt, 3, SQL_C_CHAR, strTableName,
 STR_LEN,&lenTableName);
 SQLBindCol(hstmt, 4, SQL_C_CHAR, strColumnName,
 STR_LEN, &lenColumnName);
 SQLBindCol(hstmt, 5, SQL_C_SSHORT, &DataType,
 0, &lenDataType);
 SQLBindCol(hstmt, 6, SQL_C_CHAR, strTypeName,
 STR_LEN, &lenTypeName);
 SQLBindCol(hstmt, 7, SQL_C_SLONG, &ColumnSize,
 0, &lenColumnSize);
 SQLBindCol(hstmt, 8, SQL_C_SLONG, &BufferLength,
 0, &lenBufferLength);
 SQLBindCol(hstmt, 9, SQL_C_SSHORT, &DecimalDigits,
 0, &lenDecimalDigits);
 SQLBindCol(hstmt, 10, SQL_C_SSHORT, &NumPrecRadix,
 0, &lenNumPrecRadix);
 SQLBindCol(hstmt, 11, SQL_C_SSHORT, &Nullable,
 0, &lenNullable);
 SQLBindCol(hstmt, 12, SQL_C_CHAR, strRemarks,
 REM_LEN, &lenRemarks);
 SQLBindCol(hstmt, 13, SQL_C_CHAR, strColumnDefault,
 STR_LEN, &lenColumnDefault);
 SQLBindCol(hstmt, 14, SQL_C_SSHORT, &SQLDataType,
 0, &lenSQLDataType);
 SQLBindCol(hstmt, 15, SQL_C_SSHORT, &DatetimeSubtypeCode,
 0, &lenDatetimeSubtypeCode);
 SQLBindCol(hstmt, 16, SQL_C_SLONG, &CharOctetLength,
 0, &lenCharOctetLength);
 SQLBindCol(hstmt, 17, SQL_C_SLONG, &OrdinalPosition,
 0, &lenOrdinalPosition);
 SQLBindCol(hstmt, 18, SQL_C_CHAR, strIsNullable,
 STR_LEN, &lenIsNullable);
 // retrieve column data
 while (SQL_SUCCESS == retcode) {
 retcode = SQLFetch(hstmt);
 CHECK_ERROR(retcode, "SQLFetch(SQLColumns)",
 hstmt, SQL_HANDLE_STMT);
 // Display column name, size and type
 printf (" Column Name : %s, ", strColumnName);
 printf ("Column Size : %i, ", ColumnSize);
 printf ("Data Type : %i\n", SQLDataType);
 }
 }
 }
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

Download ODBC Drivers for

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

Learn More
Share:

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