223 lines
13 KiB
HTML
223 lines
13 KiB
HTML
<?xml version="1.0" encoding="UTF-8"?>
|
|
<!DOCTYPE html
|
|
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
|
<html lang="en-us" xml:lang="en-us">
|
|
<head>
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
|
<meta name="security" content="public" />
|
|
<meta name="Robots" content="index,follow" />
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
|
<meta name="DC.Type" content="reference" />
|
|
<meta name="DC.Title" content="SQLTables - Get table information" />
|
|
<meta name="DC.Relation" scheme="URI" content="rzadphdapi.htm" />
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1999, 2006" />
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1999, 2006" />
|
|
<meta name="DC.Format" content="XHTML" />
|
|
<meta name="DC.Identifier" content="rzadpfntabls" />
|
|
<meta name="DC.Language" content="en-us" />
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
|
<!-- US Government Users Restricted Rights -->
|
|
<!-- Use, duplication or disclosure restricted by -->
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
|
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
|
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
|
<title>SQLTables</title>
|
|
</head>
|
|
<body id="rzadpfntabls"><a name="rzadpfntabls"><!-- --></a>
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
|
<h1 class="topictitle1">SQLTables - Get table information</h1>
|
|
<div><div class="section"></div>
|
|
<div class="section"><h4 class="sectiontitle">Purpose</h4> <p><samp class="codeph">SQLTables()</samp> returns
|
|
a list of table names and associated information stored in the system catalogs
|
|
of the connected data source. The list of table names is returned as a result
|
|
set, which can be retrieved using the same functions that are used to retrieve
|
|
a result set generated by a SELECT statement.</p>
|
|
</div>
|
|
<div class="section"><h4 class="sectiontitle">Syntax</h4><pre>SQLRETURN SQLTables (SQLHSTMT hstmt,
|
|
SQLCHAR *szCatalogName,
|
|
SQLSMALLINT cbCatalogName,
|
|
SQLCHAR *szSchemaName,
|
|
SQLSMALLINT cbSchemaName,
|
|
SQLCHAR *szTableName,
|
|
SQLSMALLINT cbTableName,
|
|
SQLCHAR *szTableType,
|
|
SQLSMALLINT cbTableType);</pre>
|
|
</div>
|
|
<div class="section"><h4 class="sectiontitle">Function arguments</h4>
|
|
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" width="100%" frame="hsides" border="1" rules="all"><caption>Table 1. SQLTables arguments</caption><thead align="left"><tr><th align="left" valign="top" width="20%" id="d0e48">Data type</th>
|
|
<th align="left" valign="top" width="22.037037037037038%" id="d0e50">Argument</th>
|
|
<th align="left" valign="top" width="17.962962962962965%" id="d0e52">Use</th>
|
|
<th align="left" valign="top" width="40%" id="d0e54">Description</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody><tr><td align="left" valign="top" width="20%" headers="d0e48 ">SQLCHAR *</td>
|
|
<td align="left" valign="top" width="22.037037037037038%" headers="d0e50 "><em>szCatalogName</em></td>
|
|
<td align="left" valign="top" width="17.962962962962965%" headers="d0e52 ">Input</td>
|
|
<td align="left" valign="top" width="40%" headers="d0e54 ">Buffer that might contain a <cite>pattern-value</cite> to
|
|
qualify the result set. <cite>Catalog</cite> is the first part of a three-part
|
|
table name. <p>This must be a NULL pointer or a zero length string.</p>
|
|
</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="20%" headers="d0e48 ">SQLCHAR *</td>
|
|
<td align="left" valign="top" width="22.037037037037038%" headers="d0e50 "><em>szSchemaName</em></td>
|
|
<td align="left" valign="top" width="17.962962962962965%" headers="d0e52 ">Input</td>
|
|
<td align="left" valign="top" width="40%" headers="d0e54 ">Buffer that might contain a <cite>pattern-value</cite> to
|
|
qualify the result set by schema name.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="20%" headers="d0e48 ">SQLCHAR *</td>
|
|
<td align="left" valign="top" width="22.037037037037038%" headers="d0e50 "><em>szTableName</em></td>
|
|
<td align="left" valign="top" width="17.962962962962965%" headers="d0e52 ">Input</td>
|
|
<td align="left" valign="top" width="40%" headers="d0e54 ">Buffer that might contain a <cite>pattern-value</cite> to
|
|
qualify the result set by table name.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="20%" headers="d0e48 ">SQLCHAR *</td>
|
|
<td align="left" valign="top" width="22.037037037037038%" headers="d0e50 "><em>szTableType</em></td>
|
|
<td align="left" valign="top" width="17.962962962962965%" headers="d0e52 ">Input</td>
|
|
<td align="left" valign="top" width="40%" headers="d0e54 ">Buffer that might contain a <em>value list</em> to
|
|
qualify the result set by table type. <p>The value list is a list of values
|
|
separated by commas for the types of interest. Valid table type identifiers
|
|
might include: ALL, ALIAS, BASE TABLE, MATERIALIZED QUERY TABLE, SYSTEM TABLE,
|
|
TABLE, VIEW. If <em>szTableType</em> argument is a NULL pointer or a zero length
|
|
string, then this is equivalent to specifying all of the possibilities for
|
|
the table type identifier.</p>
|
|
<p>If SYSTEM TABLE is specified, then both
|
|
system tables and system views (if there are any) are returned.</p>
|
|
<p>The
|
|
table types can be specified with or without quotation marks.</p>
|
|
</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="20%" headers="d0e48 ">SQLHSTMT</td>
|
|
<td align="left" valign="top" width="22.037037037037038%" headers="d0e50 "><em>hstmt</em></td>
|
|
<td align="left" valign="top" width="17.962962962962965%" headers="d0e52 ">Input</td>
|
|
<td align="left" valign="top" width="40%" headers="d0e54 ">Statement handle.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="20%" headers="d0e48 ">SQLSMALLINT</td>
|
|
<td align="left" valign="top" width="22.037037037037038%" headers="d0e50 "><em>cbCatalogName</em></td>
|
|
<td align="left" valign="top" width="17.962962962962965%" headers="d0e52 ">Input</td>
|
|
<td align="left" valign="top" width="40%" headers="d0e54 ">Length of <em>szCatalogName</em>. This must
|
|
be set to 0.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="20%" headers="d0e48 ">SQLSMALLINT</td>
|
|
<td align="left" valign="top" width="22.037037037037038%" headers="d0e50 "><em>cbSchemaName</em></td>
|
|
<td align="left" valign="top" width="17.962962962962965%" headers="d0e52 ">Input</td>
|
|
<td align="left" valign="top" width="40%" headers="d0e54 ">Length of <em>szSchemaName</em>.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="20%" headers="d0e48 ">SQLSMALLINT</td>
|
|
<td align="left" valign="top" width="22.037037037037038%" headers="d0e50 "><em>cbTableName</em></td>
|
|
<td align="left" valign="top" width="17.962962962962965%" headers="d0e52 ">Input</td>
|
|
<td align="left" valign="top" width="40%" headers="d0e54 ">Length of <em>szTableName</em>.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="20%" headers="d0e48 ">SQLSMALLINT</td>
|
|
<td align="left" valign="top" width="22.037037037037038%" headers="d0e50 "><em>cbTableType</em></td>
|
|
<td align="left" valign="top" width="17.962962962962965%" headers="d0e52 ">Input</td>
|
|
<td align="left" valign="top" width="40%" headers="d0e54 ">Size of <em>szTableType</em></td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
<div class="note"><span class="notetitle">Note:</span> The <em>szCatalogName, szSchemaName</em>, and <em>szTableName</em> arguments
|
|
accept search patterns.</div>
|
|
<p>An escape character can be specified in conjunction
|
|
with a wildcard character to allow that actual character to be used in the
|
|
search pattern. The escape character is specified on the SQL_ATTR_ESCAPE_CHAR
|
|
environment attribute.</p>
|
|
</div>
|
|
<div class="section"><h4 class="sectiontitle">Usage</h4><p>Table information is returned in a result
|
|
set where each table is represented by one row of the result set.</p>
|
|
<p>To
|
|
support obtaining just a list of schemas, the following special semantics
|
|
for the <em>szSchemaName</em> argument can be applied: if <em>szSchemaName</em> is
|
|
a string containing a single percent (%) character, and <em>cbCatalogName</em>, <em>szTableName</em>,
|
|
and <em>szTableType</em> are empty strings, then the result set contains a list
|
|
of non-duplicate schemas in the data source.</p>
|
|
<div class="p">The result set returned
|
|
by <samp class="codeph">SQLTables()</samp> contains the columns listed in the following
|
|
table in the order given.
|
|
<div class="tablenoborder"><a name="rzadpfntabls__rstabls"><!-- --></a><table cellpadding="4" cellspacing="0" summary="" id="rzadpfntabls__rstabls" width="100%" frame="hsides" border="1" rules="all"><caption>Table 2. Columns returned by SQLTables</caption><thead align="left"><tr><th align="left" valign="top" width="25%" id="d0e233">Column name</th>
|
|
<th align="left" valign="top" width="25%" id="d0e235">Data type</th>
|
|
<th align="left" valign="top" width="50%" id="d0e237">Description</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody><tr><td align="left" valign="top" width="25%" headers="d0e233 ">TABLE_CAT</td>
|
|
<td align="left" valign="top" width="25%" headers="d0e235 ">VARCHAR(128)</td>
|
|
<td align="left" valign="top" width="50%" headers="d0e237 ">The current server.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="25%" headers="d0e233 ">TABLE_SCHEM</td>
|
|
<td align="left" valign="top" width="25%" headers="d0e235 ">VARCHAR(128)</td>
|
|
<td align="left" valign="top" width="50%" headers="d0e237 ">The name of the schema containing TABLE_NAME.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="25%" headers="d0e233 ">TABLE_NAME</td>
|
|
<td align="left" valign="top" width="25%" headers="d0e235 ">VARCHAR(128)</td>
|
|
<td align="left" valign="top" width="50%" headers="d0e237 ">The name of the table, view, alias,
|
|
or synonym.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="25%" headers="d0e233 ">TABLE_TYPE</td>
|
|
<td align="left" valign="top" width="25%" headers="d0e235 ">VARCHAR(128)</td>
|
|
<td align="left" valign="top" width="50%" headers="d0e237 ">Identifies the type given by the name in
|
|
the TABLE_NAME column. It can have the string values ALIAS, BASE TABLE, MATERIALIZED
|
|
QUERY TABLE, SYSTEM TABLE, TABLE, or VIEW.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="25%" headers="d0e233 ">REMARKS</td>
|
|
<td align="left" valign="top" width="25%" headers="d0e235 ">VARCHAR(254)</td>
|
|
<td align="left" valign="top" width="50%" headers="d0e237 ">Contains the descriptive information about
|
|
the table.</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
<div class="section"><h4 class="sectiontitle">Return codes</h4><ul><li>SQL_SUCCESS</li>
|
|
<li>SQL_SUCCESS_WITH_INFO</li>
|
|
<li>SQL_ERROR</li>
|
|
<li>SQL_INVALID_HANDLE</li>
|
|
</ul>
|
|
</div>
|
|
<div class="section"><h4 class="sectiontitle">Diagnostics</h4>
|
|
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" width="100%" frame="hsides" border="1" rules="rows"><caption>Table 3. SQLTables SQLSTATEs</caption><thead align="left"><tr><th align="left" valign="top" width="25%" id="d0e301">SQLSTATE</th>
|
|
<th align="left" valign="top" width="25%" id="d0e303">Description</th>
|
|
<th align="left" valign="top" width="50%" id="d0e305">Explanation</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody><tr><td align="left" valign="top" width="25%" headers="d0e301 "><strong>24</strong>000</td>
|
|
<td valign="top" width="25%" headers="d0e303 ">Cursor state that is not valid</td>
|
|
<td align="left" valign="top" width="50%" headers="d0e305 ">Cursor-related information is requested,
|
|
but no cursor is open.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="25%" headers="d0e301 "><strong>40</strong>003 <strong>*</strong></td>
|
|
<td align="left" valign="top" width="25%" headers="d0e303 ">Statement completion unknown</td>
|
|
<td align="left" valign="top" width="50%" headers="d0e305 ">The communication link between the CLI and
|
|
the data source fails before the function completes processing.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="25%" headers="d0e301 "><strong>HY</strong>001</td>
|
|
<td align="left" valign="top" width="25%" headers="d0e303 ">Memory allocation failure</td>
|
|
<td align="left" valign="top" width="50%" headers="d0e305 ">The driver is unable to allocate memory required
|
|
to support the processing or completion of the function.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="25%" headers="d0e301 "><strong>HY</strong>009</td>
|
|
<td align="left" valign="top" width="25%" headers="d0e303 ">Argument or buffer length that is not valid</td>
|
|
<td align="left" valign="top" width="50%" headers="d0e305 ">The value of one of the name length arguments
|
|
is less than 0, but not equal to SQL_NTS.</td>
|
|
</tr>
|
|
<tr><td valign="top" width="25%" headers="d0e301 "><strong>HY</strong>021</td>
|
|
<td valign="top" width="25%" headers="d0e303 ">Internal descriptor that is not valid </td>
|
|
<td valign="top" width="50%" headers="d0e305 ">The internal descriptor cannot be addressed or allocated,
|
|
or it contains a value that is not valid.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="25%" headers="d0e301 "><strong>HY</strong>C00</td>
|
|
<td align="left" valign="top" width="25%" headers="d0e303 ">Driver not capable</td>
|
|
<td align="left" valign="top" width="50%" headers="d0e305 ">The catalog part (the first part) of a three-part
|
|
table name is not supported by the data source.</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
<div>
|
|
<div class="familylinks">
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzadphdapi.htm" title="This topic provides a description of each CLI function.">DB2 UDB CLI functions</a></div>
|
|
</div>
|
|
</div>
|
|
</body>
|
|
</html> |