Retrieving column metadata

The JDBC Metadata Explorer enables the retrieval of different column metadata (data type, nullable, autoincrement etc.) to one or multiple table columns; you can narrow the search by specifying catalog, schema, and table name.

Input

Add the element <tablecolumns resulttag="ResultElementName"/> to an input XML document with any root element, and specify a valid XML name within the attribute resulttag. The specification of catalog, schema, table and column within the elements <catalog>, <schema>, <table> and <column> are optional here; if these are not specified, all available metadata will be retrieved.

HTML/XML

<Root>
  <tablemetadata resulttag="ResultElementName">
     <catalog>Katalog</catalog>
     <schema>DB-Schema</schema>
     <table>DB-Tabelle</table>
     <column>Spalte</column>
  </tablemetadata>
</Root>

Output

The output XML structure contains the root element <Data> with a child element whose name has been specified in the input structure within the attribute resulttag. It contains column metadata. If there are multiple result sets (i. e. more than one column), each will be output in a separate element.

HTML/XML

<Data>
   <ResultElementName>
      <Column property> value </Column property>
      ...
   </ResultElementName>
</Data>

Further information on each table property can be found here.

Example 1

An input XML document contains the following XML structure to output metadata for a column X4_PID in the table HISTORY in a database schema APP:

HTML/XML

<Root>
  <tablecolumns resulttag="DBColumn">
     <catalog></catalog>
     <schema>APP</schema>
     <table>HISTORY</table>
     <column>X4_PID</column>
  </tablecolumns>
</Root>

Then, the JDBC Metadata Explorer outputs e. g. the following result XML document:

HTML/XML

<Data>
   <DBColumn>
      <TABLE_CAT />
      <TABLE_SCHEM>APP</TABLE_SCHEM>
      <TABLE_NAME>HISTORY</TABLE_NAME>
      <COLUMN_NAME>X4_PID</COLUMN_NAME>
      <DATA_TYPE>12</DATA_TYPE>
      <TYPE_NAME>VARCHAR</TYPE_NAME>
      <COLUMN_SIZE>20</COLUMN_SIZE>
      <BUFFER_LENGTH isNull="true" />
      <DECIMAL_DIGITS isNull="true" />
      <NUM_PREC_RADIX isNull="true" />
      <NULLABLE>0</NULLABLE>
      <REMARKS />
      <COLUMN_DEF isNull="true" />
      <SQL_DATA_TYPE isNull="true" />
      <SQL_DATETIME_SUB isNull="true" />
      <CHAR_OCTET_LENGTH>40</CHAR_OCTET_LENGTH>
      <ORDINAL_POSITION>1</ORDINAL_POSITION>
      <IS_NULLABLE>NO</IS_NULLABLE>
      <SCOPE_CATLOG isNull="true" />
      <SCOPE_SCHEMA isNull="true" />
      <SCOPE_TABLE isNull="true" />
      <SOURCE_DATA_TYPE isNull="true" />
      <IS_AUTOINCREMENT>NO</IS_AUTOINCREMENT>
   </DBColumn>
</Data>

Example 2

An input XML document contains the following XML structure to output metadata for a column X4_PID in the table HISTORY in a database schema APP. The element <column> with its attribute label within <resultsetRestriction> restricts the output data::

HTML/XML

<Root>
  <tablecolumns resulttag="DBColumn">
     <catalog></catalog>
     <schema>APP</schema>
     <table>HISTORY</table>
     <column>X4_PID</column>
  	 <resultsetRestriction>
		<column label="TABLE_NAME" />
		<column label="DATA_TYPE" />
		<column label="NULLABLE" />
		<column label="TYPE_NAME" />
	 </resultsetRestriction>
	</tablecolumns>
</Root>

The JDBC Metadata Explorer outputs e. g. the following result XML document:

HTML/XML

<Data>
   <DBColumn>
      <TABLE_NAME>HISTORY</TABLE_NAME>
      <COLUMN_NAME>X4_PID</COLUMN_NAME>
      <DATA_TYPE>12</DATA_TYPE>
      <TYPE_NAME>VARCHAR</TYPE_NAME>
   </DBColumn>
</Data>