SQLColumnPrivileges - Get privileges associated with the columns of a table
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
with the same functions that are used to process a result set generated
from a query.
SQLColumnPrivilegesW()
. Refer
to Unicode in Db2 for i CLI for more information
about Unicode support forDb2 for
i CLI.Syntax
SQLRETURN SQLColumnPrivileges (
SQLHSTMT StatementHandle,
SQLCHAR *CatalogName,
SQLSMALLINT NameLength1,
SQLCHAR *SchemaName,
SQLSMALLINT NameLength2,
SQLCHAR *TableName
SQLSMALLINT NameLength3,
SQLCHAR *ColumnName,
SQLSMALLINT NameLength4);
Function 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 can 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 Table 2. 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 might
want 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
Because 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. Because Db2 for
i names are always 128
characters or less in length, the application can choose to always
set aside 128 characters (plus the null-terminator) for the output
buffer, or alternatively, call SQLGetInfo()
with
SQL_MAX_CATALOG_NAME_LEN, SQL_MAX_SCHEMA_NAME_LEN, SQL_MAX_TABLE_NAME_LEN,
and SQL_MAX_COLUMN_NAME_LEN. The SQL_MAX_CATALOG_NAME_LEN value determines
the actual length of the TABLE_CAT supported by the connected Database
Management System (DBMS). The SQL_MAX_SCHEMA_NAME_LEN value determines
the actual length of the TABLE_SCHEM supported by the connected DBMS.
The SQL_MAX_TABLE_NAME_LEN value determines the actual length of the
TABLE_NAME supported by the connected DBMS. The SQL_MAX_COLUMN_NAME_LEN
value determines the actual length of the COLUMN_NAME supported by
the connected DBMS.
Note that the ColumnName argument accepts a search pattern.
Column number/name | Data type | Description |
---|---|---|
1 TABLE_CAT | VARCHAR(128) | This is always NULL. |
2 TABLE_SCHEM | VARCHAR(128) | The name of the schema containing TABLE_NAME. |
3 TABLE_NAME | VARCHAR(128) not NULL | Name of the table or view. |
4 COLUMN_NAME | VARCHAR(128) not NULL | Name of the column of the specified table or view. |
5 GRANTOR | VARCHAR(128) | Authorization ID of the user who granted the privilege. |
6 GRANTEE | VARCHAR(128) | Authorization ID of the user to whom the privilege is granted. |
7 PRIVILEGE | VARCHAR(128) | The column privilege. This can be:
|
8 IS_GRANTABLE | VARCHAR(3) | This indicates whether the grantee is permitted to grant the privilege to other users. Either YES or NO. |
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
SQLSTATE | Description | Explanation |
---|---|---|
HY001 | Memory allocation failure | The driver is unable to allocate memory required to support the processing or completion of the function. |
HY009 | String or buffer length that is not valid | The value of one of the name length arguments is less than 0, but not equal to SQL_NTS. |
HY010 | Function sequence error | There is an open cursor for this statement handle, or there is no connection for this statement handle. |
HY021 | Internal descriptor that is not valid | The internal descriptor cannot be addressed or allocated, or it contains a value that is not valid. |
Restrictions
None.
Example
/* 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);