This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.

Calling a Catalog function is equivalent to calling SQLExecDirect with an argument containing a SELECT statement. That means that data is returned in a result set. You may traverse the rows in the result set using SQLFetch or SQLFetchScroll. You should call SQLCloseCursor when there is nothing more to fetch.

For most Catalog functions, you pass (character string) input parameters to specify which rows should be selected for the result set. There are several rules concerning these input parameters. There is no use trying to figure out what the rationale is behind these rules. You'll simply have to learn them if you want Catalog functions to work reliably. Here they are:

  • Accessible tables. You may recall that there is an option to the SQLGetInfo function, namely SQL_ACCESSIBLE_TABLES, which returns as follows:
    • 'Y': the DBMS only returns information about Tables to users who have SELECT Privileges on the Tables.
    • 'N': the DBMS returns information about Tables based on some other, implementation-defined, criterion.
  • In fact, all standard DBMSs should return 'N' because information is available to users who have any Privileges on the Tables, not necessarily just SELECT Privileges. In all that follows, we will just assume that the INFORMATION_SCHEMA rows are the rows that would be available in standard SQL.
  • Catalogs. Not every DBMS supports Catalogs. For the cases where we say that a <Catalog name> is retrieved, it is possible that the actual retrieval will be
    1. NULL. Once again, this is a case where NULL means "not applicable".
  • Length. All input-string parameters are accompanied by a SMALLINT parameter the "length". This length should be the number of octets in the input string. The special value SQL_NTS is permissible. The special value 0 (zero) is permissible, and a zero-length string means "don't care" for example, if you pass a zero-length string for a parameter named *SchemaName, the DBMS will accept all <Schema name>s in the Catalog.
  • Metadata ID. You may recall that there is an option to the SQLGetStmtAttr function, namely SQL_ATTR_METADATA_ID, which returns either TRUE or FALSE.
    • If METADATA ID is TRUE:
      • If there is such a thing as a <Catalog name> (which is the case in all standard DBMSs), then you must not pass a null pointer for any Catalog function parameter which is labelled *CatalogName. Passing a null pointer will result in the error: HY009 CLI-specific condition-invalid use of null pointer.
      • You must not pass a null pointer for any Catalog function parameter which is labelled *SchemaName. Passing a null pointer will result in the error: HY009 CLI-specific condition-invalid use of null pointer.
      • You may pass a string which begins and ends with quotes, as is the custom for <delimited identifier>s. If you do pass a quoted string, the quotes are stripped and the string is not converted to upper case. If you don't pass a quoted string, the string is converted to upper case.
    • If METADATA ID is FALSE:
      • You may pass a null pointer for any string parameter. Doing so is equivalent to passing a string with zero length.
      • The string may be treated as a search pattern; that is, wild cards are allowed as they are in LIKE predicates. If you need to find out what the value is for the escape character, call SQLGetInfo(hstmt,SQL_SEARCH_PATTERN_ESCAPE,...).

TIP: you'll only have to learn one set of rules if METADATA ID is always TRUE. Therefore, as soon as you allocate a stmt, execute this function:

SQLSetStmtAttr(hstmt,SQL_ATTR_METADATA_ID,&1,NULL);

and leave it that way. Henceforward, we'll forget about the possibility that METADATA ID could be FALSE.

In ODBC, searching is different in significant ways: quotes are not stripped, <identifier>s are always converted to upper case, regardless of the value of METADATA ID. If quotes are not present, then trail spaces are trimmed. The character used for <delimited identifier>s may be something other than a quote mark.

TIP: There's no way to remove the incompatibilities between standard SQL and ODBC here, but they won't matter if you follow two policies. One: avoid <delimited identifier>s. Two: pass all string values in upper case.

Comments

Comments loading...