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 : SQLColumnPrivileges.c
*
* DESCRIPTION :
* Example finds tables starting with 'TestTBL' and for each one
* outputs the table privileges.
*
* ODBC USAGE :
* SQLGetInfo() to get the DB and USER names
* SQLBindCol and SQLTables to obtain list of tables starting with
* 'TestTBL'.
*
* For each table, call :
*
* SQLColumnPrivileges() to get column privileges
* SQLBindCol() to bind the 8 columns of the result set (those being
* Catalog, Schema, Table, Column, Grantor, Grantee and Is Grantable)
* SQLFetch() to get the values
* Displays information returned
*
*/
#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
// Number of columns in SQLTables
#define NUMCOLS 5
// Declare buffers for result set data
SQLCHAR strCatalog[STR_LEN];
SQLCHAR strSchema[STR_LEN];
SQLCHAR strTableName[STR_LEN];
SQLCHAR strColumnName[STR_LEN];
SQLCHAR strGrantor[STR_LEN];
SQLCHAR strGrantee[REM_LEN];
SQLCHAR strPrivilege[STR_LEN];
SQLCHAR strIsGrantable[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 lenGrantor;
SQLLEN lenGrantee;
SQLLEN lenPrivilege;
SQLLEN lenIsGrantable;
struct DataBinding {
 SQLSMALLINT TargetType;
 SQLPOINTER TargetValuePtr;
 SQLINTEGER BufferLength;
 SQLLEN StrLen_or_Ind;
};
// Gets list of tables based on iTableStrPtr
int getTheseTables (char *iTableName, SQLCHAR *tableNames[]) {
 SQLHENV henv = SQL_NULL_HENV; // Environment
 SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
 SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
 SQLRETURN retcode;
 int bufferSize = 1024, i, j, count = 1;
 SQLCHAR * dbName = (SQLCHAR *)malloc( sizeof(SQLCHAR)*bufferSize );
 SQLCHAR * userName = (SQLCHAR *)malloc( sizeof(SQLCHAR)*bufferSize );
 SQLCHAR connStrbuffer[1024];
 SQLSMALLINT connStrBufferLen, bufferLen;
 struct DataBinding* catalogResult = (struct DataBinding*)
 malloc( NUMCOLS * sizeof(struct DataBinding) );
 SQLCHAR* selectAllQuery = (SQLCHAR *)
 malloc( sizeof(SQLCHAR) * bufferSize );
 // Allocate 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);
 // Allocate Connection Handle
 retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
 CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
 hdbc, SQL_HANDLE_DBC);
 // Set timeout
 retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)10, 0);
 CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)",
 hdbc, SQL_HANDLE_DBC);
 // Connect to a datasource
 retcode = SQLConnect(hdbc, (SQLCHAR*) "DATASOURCE", SQL_NTS,
 (SQLCHAR*) NULL, 0, NULL, 0);
 CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
 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 and 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);
 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 );
 }
 // Set up the column bindings
 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, "SQLBindCol(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(SQL_HANDLE_STMT)",
 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; // OSBC function return status
 char padding[] = " ";
 SQLCHAR *tableNames[MAX_TABLES];
 SQLINTEGER tableCount, i, max=strlen(padding);
 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*) "DATASOURCE",
 SQL_NTS, (SQLCHAR*) NULL, 0, NULL, 0);
 CHECK_ERROR(retcode, "SQLConnect(DATASOURCE)",
 hdbc, SQL_HANDLE_DBC);
 retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
 CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
 hstmt, SQL_HANDLE_STMT);
 tableCount=getTheseTables ("TestTBL", tableNames);
 for (i=0; i<tableCount; i++) {
 printf ("\nTable : %s\n", tableNames[i]);
 retcode = SQLColumnPrivileges(hstmt, NULL, 0, NULL, 0,
 (SQLCHAR*)tableNames[i],
 SQL_NTS, NULL, 0);
 if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
 // Bind columns in result set to buffers
 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_CHAR, strGrantor,
 STR_LEN, &lenGrantor);
 SQLBindCol(hstmt, 6, SQL_C_CHAR, strGrantee,
 STR_LEN, &lenGrantee);
 SQLBindCol(hstmt, 7, SQL_C_CHAR, strPrivilege,
 STR_LEN, &lenPrivilege);
 SQLBindCol(hstmt, 8, SQL_C_CHAR, strIsGrantable,
 STR_LEN, &lenIsGrantable);
 max=strlen(padding);
 printf ("Catalog Schema Table Column Grantor"
 "Grantee Is Grantable\n");
 printf ("---------- ---------- ---------- ---------- ----------"
 "---------- --------------\n");
 while (SQL_SUCCESS == retcode) {
 retcode = SQLFetch(hstmt);
 if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
 extract_error("Error : ", hstmt, SQL_HANDLE_STMT);
 }
 // Display results in tabular form
 if (retcode == SQL_SUCCESS ||
 retcode == SQL_SUCCESS_WITH_INFO) {
 padOut (strCatalog, padding, max);
 printf ("%s%s", strCatalog, padding);
 padOut (strSchema, padding, max);
 printf ("%s%s", strSchema, padding);
 padOut (strTableName, padding, max);
 printf ("%s%s", strTableName, padding);
 padOut (strColumnName, padding, max);
 printf ("%s%s", strColumnName, padding);
 padOut (strGrantor, padding, max);
 printf ("%s%s", strGrantor, padding);
 padOut (strGrantee, padding, max);
 printf ("%s%s", strGrantee, padding);
 padOut (strPrivilege, padding, max);
 printf ("%s%s", strPrivilege, padding);
 padOut ("", padding, max);
 printf ("%s\n", strIsGrantable);
 }
 }
 }
 retcode = SQLFreeStmt(hstmt, SQL_CLOSE);
 }
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 によって変換されたページ (->オリジナル) /