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 : ListTablePrivileges.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 like 'TestTBL'
* For each table
* SQLTablePrivileges() to get table privileges
* SQLBindCol() to bind the 7 columns of the result set (those
* being Catalog, Schema, Table, Grantor, Grantee, Privilege
* and Is Grantable)
* SQLFetch() to get the values
* Display
*/
#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 strGrantor[STR_LEN];
SQLCHAR strGrantee[REM_LEN];
SQLCHAR strPrivilege[STR_LEN];
SQLCHAR strIsGrantable[STR_LEN];
SQLINTEGER BufferLength;
// Declare buffers for bytes available to return
SQLLEN lenCatalog;
SQLLEN lenSchema;
SQLLEN lenTableName;
SQLLEN lenGrantor;
SQLLEN lenGrantee;
SQLLEN lenPrivilege;
SQLLEN lenIsGrantable;
struct DataBinding {
 SQLSMALLINT TargetType;
 SQLPOINTER TargetValuePtr;
 SQLINTEGER BufferLength;
 SQLLEN StrLen_or_Ind;
};
void Cleanup(SQLHSTMT henv, SQLHSTMT hdbc, SQLHSTMT hstmt) {
 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);
}
// Gets list of tables based on iTableStrPtr
int getTheseTables (char *pTableName, SQLCHAR *tableNames[]) {
 int bufferSize = 1024, 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 );
 // Connect to database
 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,
 (SQLCHAR *)(void*)SQL_OV_ODBC3, -1);
 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)10, 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);
 // 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 binding.
 for ( i = 0 ; i < numCols ; i++ ) {
 //printf ("Binding Column %i\n", i+1);
 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_ENV)",
 hstmt, SQL_HANDLE_STMT);
 }
 retcode = SQLTables( hstmt, dbName, SQL_NTS, userName, SQL_NTS, "%",
 SQL_NTS, "TABLE", SQL_NTS );
 CHECK_ERROR(retcode, "SQLTables(dbName)", 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, pTableName) != 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:
 Cleanup(henv, hdbc, hstmt);
 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;
 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);
 tableCount=getTheseTables ("TestTBL", tableNames);
 for (i=0; i<tableCount; i++) {
 printf ("\nTable : %s\n", tableNames[i]);
 retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
 CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
 hstmt, SQL_HANDLE_STMT);
 retcode = SQLTablePrivileges(hstmt, NULL, 0, NULL, 0,
 (SQLCHAR*)tableNames[i], SQL_NTS);
 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,
 strGrantor, STR_LEN, &lenGrantor);
 SQLBindCol(hstmt, 5, SQL_C_CHAR,
 strGrantee, STR_LEN, &lenGrantee);
 SQLBindCol(hstmt, 6, SQL_C_CHAR,
 strPrivilege, STR_LEN, &lenPrivilege);
 SQLBindCol(hstmt, 7, SQL_C_CHAR,
 strIsGrantable, STR_LEN, &lenIsGrantable);
 max=strlen(padding);
 printf ("Catalog Schema Table Grantor ");
 printf ("Grantee Is Grantable\n");
 printf ("----------- ----------- ----------- -----------");
 printf ("----------- ------------\n");
 while (SQL_SUCCESS == retcode) {
 retcode = SQLFetch(hstmt);
 if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
 extract_error("xSQLTablePrivileges Error : ",
 hstmt, SQL_HANDLE_STMT);
 }
 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 (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);
 }
 }
 } else {
 CHECK_ERROR(retcode, "SQLStatistics()", hstmt, SQL_HANDLE_STMT);
 }
 SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
 }
exit:
 printf ("\nComplete.\n");
 // Free statement, connection and environment handle
 Cleanup(henv, hdbc, hstmt);
 return 0;
}

Further information

Download ODBC Drivers for

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

Learn More
Share:

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