SQLTables Description
There are multiple considerations when using IBM® i Access ODBC driver SQLTables API.
- The CatalogName parameter is ignored, with or without wildcards,
since the catalog name is always the relational database name. The
only time the catalog name value matters is when it must be an empty
string to generate a list of libraries for the server.
You must specify table names for the TableName parameter exactly as you would when creating a SQL statement. In other words, you must capitalize the table name unless you created the table name with double quotes around the table name. If you created the table with double quotes around the table name, you need to specify the TableName parameter as it appears in quotes, matching the case of the letters.
- The "Library view" option on the Catalog tab of the DSN setup GUI only affects this API when you choose the combination that attempts to retrieve the list of libraries for that server. It does not allow you to generate a result set based on a search through multiple libraries for specific tables.
- The "Object description type" option on the Catalog tab of the DSN setup GUI affects the output you get in the "RESULTS" column of the result set when getting a list of tables.
- If you have a string with mixed '\_' and '_' then if SQL_ATTR_METADATA_ID is SQL_FALSE then we'll treat the first '\_' as an actual '_', but the '_' will be treated as the wildcard. If SQL_ATTR_METADATA_ID is SQL_TRUE then the first '\_' will be treated like an actual '_' and the '_' will also be treated like an actual '_'. The driver will internally convert the second '_' to a '\_'.
- In order to use the wildcard character underscore (_) as a literal
precede it with a backlash (\). For example, to search for only MY_TABLE
(not MYATABLE, MYBTABLE, etc...) you need to specify the search string
as MY\_TABLE.
Specifiying '\%' in a name is invalid, as the IBM i operating system does not allow an actual '%' in a library or table name.
When queried for the list of libraries, the driver returns the TABLE_CAT and REMARKS fields as meaningful data.
The ODBC specification says to return everything, except the TABLE_SCHEM as nulls.