Call Level Interface Guide and Reference

SQLColumnPrivileges - Get Privileges Associated With The Columns of A Table

Purpose

Specification: DB2 CLI 2.1 ODBC 1.0

SQLColumnPrivileges() returns a list of columns and associated privileges for the specified table. The information is returned in an SQL result set, which can be retrieved using the same functions that are used to process a result set generated from a query.

Syntax

SQLRETURN SQLColumnPrivileges(
                SQLHSTMT          StatementHandle,   /* hstmt */
                SQLCHAR      *FAR CatalogName,       /* szCatalogName */
                SQLSMALLINT       NameLength1,       /* cbCatalogName */
                SQLCHAR      *FAR SchemaName,        /* szSchemaName */
                SQLSMALLINT       NameLength2,       /* cbSchemaName */
                SQLCHAR      *FAR TableName          /* szTableName */
                SQLSMALLINT       NameLength3,       /* cbTableName */
                SQLCHAR      *FAR ColumnName,        /* szColumnName */
                SQLSMALLINT       NameLength4);      /* cbColumnName */

Function Arguments

Table 36. SQLColumnPrivileges Arguments

Data Type Argument Use Description
SQLHSTMT StatementHandle input Statement handle.
SQLCHAR * CatalogName input Catalog qualifier of a 3 part table name. This must be a NULL pointer or a zero length string.
SQLSMALLINT NameLength1 input Length of CatalogName. This must be set to 0.
SQLCHAR * SchemaName input Schema qualifier of table name.
SQLSMALLINT NameLength2 input Length of SchemaName.
SQLCHAR * TableName input Table name.
SQLSMALLINT NameLength3 input Length of TableName
SQLCHAR * ColumnName input Buffer that may contain a pattern-value to qualify the result set by column name.
SQLSMALLINT NameLength4 input Length of ColumnName

Data Type	Argument	Use	Description
SQLHSTMT	StatementHandle	input	Statement handle.
SQLCHAR *	CatalogName	input	Catalog qualifier of a 3 part table name. This must be a NULL pointer or a zero length string.
SQLSMALLINT	NameLength1	input	Length of `CatalogName`. This must be set to 0.
SQLCHAR *	SchemaName	input	Schema qualifier of table name.
SQLSMALLINT	NameLength2	input	Length of `SchemaName`.
SQLCHAR *	TableName	input	Table name.
SQLSMALLINT	NameLength3	input	Length of `TableName`
SQLCHAR *	ColumnName	input	Buffer that may contain a `pattern-value` to qualify the result set by column name.
SQLSMALLINT	NameLength4	input	Length of `ColumnName`

Usage

The results are returned as a standard result set containing the columns listed in Columns Returned by SQLColumnPrivileges. The result set is ordered by TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, and PRIVILEGE. If multiple privileges are associated with any given column, each privilege is returned as a separate row. A typical application may wish to call this function after a call to SQLColumns() to determine column privilege information. The application should use the character strings returned in the TABLE_SCHEM, TABLE_NAME, COLUMN_NAME columns of the SQLColumns() result set as input arguments to this function.

Since calls to SQLColumnPrivileges() in many cases map to a complex and thus expensive query against the system catalog, they should be used sparingly, and the results saved rather than repeating the calls.

The VARCHAR columns of the catalog functions result set have been declared with a maximum length attribute of 128 to be consistent with SQL92 limits. Since DB2 names are less than 128, the application can choose to always set aside 128 characters (plus the null-terminator) for the output buffer, or alternatively, call SQLGetInfo() with the SQL_MAX_CATALOG_NAME_LEN, SQL_MAX_SCHEMA_NAME_LEN, SQL_MAX_TABLE_NAME_LEN, and SQL_MAX_COLUMN_NAME_LEN to determine respectively the actual lengths of the TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME columns supported by the connected DBMS.

Note that the ColumnName argument accepts a search pattern. For more information about valid search patterns, refer to Input Arguments on Catalog Functions.

Although new columns may be added and the names of the existing columns changed in future releases, the position of the current columns will not change.

Columns Returned by SQLColumnPrivileges

Column 1 TABLE_CAT (VARCHAR(128) Data type)

This is always NULL.

Column 2 TABLE_SCHEM (VARCHAR(128) Data type)

The name of the schema containing TABLE_NAME.

Column 3 TABLE_NAME (VARCHAR(128) not NULL Data type)

Name of the table or view.

Column 4 COLUMN_NAME (VARCHAR(128) not NULL Data type)

Name of the column of the specified table or view.

Column 5 GRANTOR (VARCHAR(128) Data type)

Authorization ID of the user who granted the privilege.

Column 6 GRANTEE (VARCHAR(128) Data type)

Authorization ID of the user to whom the privilege is granted.

Column 7 PRIVILEGE (VARCHAR(128) Data type)

The column privilege. This can be:

INSERT
REFERENCES
SELECT
UPDATE

Note: Some IBM RDBMSs do not offer column level privileges at the column level. DB2 Universal Database, DB2 for MVS/ESA and DB2 for VSE & VM support the UPDATE column privilege; there is one row in this result set for each updateable column. For all other privileges for DB2 Universal Database, DB2 for MVS/ESA and DB2 for VSE & VM, and for all privileges for other IBM RDBMSs, if a privilege has been granted at the table level, a row is present in this result set.

Column 8 IS_GRANTABLE (VARCHAR(3) Data type)

Indicates whether the grantee is permitted to grant the privilege to other users.

Either "YES" or "NO".

Note: The column names used by DB2 CLI follow the X/Open CLI CAE specification style. The column types, contents and order are identical to those defined for the SQLColumnPrivileges() result set in ODBC.

If there is more than one privilege associated with a column, then each privilege is returned as a separate row in the result set.

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 37. SQLColumnPrivileges SQLSTATEs

SQLSTATE Description Explanation
24000 Invalid cursor state. A cursor was already opened on the statement handle.
40001 Serialization failure The transaction was rolled back due to a resource deadlock with another transaction.
40003 08S01 Communication link failure. The communication link between the application and data source failed before the function completed.
HY001 Memory allocation failure. DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY008 Operation canceled.
Asynchronous processing was enabled for the StatementHandle. The function was called and before it completed execution, SQLCancel() was called on the StatementHandle. Then the function was called again on the StatementHandle.
The function was called and, before it completed execution, SQLCancel() was called on the StatementHandle from a different thread in a multithread application.
HY010 Function sequence error
An asynchronously executing function (not this one) was called for the StatementHandle and was still executing when this function was called.
SQLExecute(), SQLExecDirect(), or SQLSetPos() was called for the StatementHandle and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns.
HY009 Invalid argument value. TableName is NULL.
HY014 No more handles. DB2 CLI was unable to allocate a handle due to internal resources.
HY090 Invalid string or buffer length. The value of one of the name length arguments was less than 0, but not equal to SQL_NTS.
HYC00 Driver not capable. DB2 CLI does not support catalog as a qualifier for table name.
HYT00 Timeout expired. The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetConnectAttr().

SQLSTATE	Description	Explanation
24000	Invalid cursor state.	A cursor was already opened on the statement handle.
40001	Serialization failure	The transaction was rolled back due to a resource deadlock with another transaction.
40003 08S01	Communication link failure.	The communication link between the application and data source failed before the function completed.
HY001	Memory allocation failure.	DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY008	Operation canceled.	Asynchronous processing was enabled for the `StatementHandle`. The function was called and before it completed execution, `SQLCancel()` was called on the `StatementHandle`. Then the function was called again on the `StatementHandle`. The function was called and, before it completed execution, `SQLCancel()` was called on the `StatementHandle` from a different thread in a multithread application.
HY010	Function sequence error	An asynchronously executing function (not this one) was called for the `StatementHandle` and was still executing when this function was called. `SQLExecute()`, `SQLExecDirect()`, or `SQLSetPos()` was called for the `StatementHandle` and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns.
HY009	Invalid argument value.	`TableName` is NULL.
HY014	No more handles.	DB2 CLI was unable to allocate a handle due to internal resources.
HY090	Invalid string or buffer length.	The value of one of the name length arguments was less than 0, but not equal to SQL_NTS.
HYC00	Driver not capable.	DB2 CLI does not support `catalog` as a qualifier for table name.
HYT00	Timeout expired.	The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for `SQLSetConnectAttr()`.

Restrictions

None.

CLI Sample tbinfo.c

(The complete sample tbinfo.c is also available here .)

 
/* From the CLI sample TBINFO.C */
/* ... */
 
    /* call SQLColumnPrivileges */ 
    printf("\n    Call SQLColumnPrivileges for:\n");
    printf("        tbSchema = %s\n", tbSchema);    
    printf("        tbName = %s\n", tbName);    
    sqlrc = SQLColumnPrivileges( hstmt, NULL, 0,
                                 tbSchema, SQL_NTS,
                                 tbName, SQL_NTS,
                                 colNamePattern, SQL_NTS);

References

[ Top of Page | Previous Page | Next Page ]