DBInfo

Retrieve database metadata for a given datasource.

Component Signature

<bx:DBInfo type=[string]
name=[string]
datasource=[string]
table=[string]
pattern=[string]
dbname=[string]
filter=[string] />

Attributes

Atrribute	Type	Required	Description	Default
type	string	true	Type of metadata to retrieve. One of: columns, dbnames, tables, foreignkeys, index, procedures, or version.
name	string	true	Name of the variable to which the result will be assigned. Required.
datasource	string	false	Name of the datasource to check metadata on. If not provided, the default datasource will be used.
table	string	false	Table name for which to retrieve metadata. Required for columns, foreignkeys, and index types.
pattern	string	false	Table name pattern to filter by. Can use wildcards or any LIKE-compatible pattern such as tbl_%. Can use schemaName.tableName syntax to additionally filter by schema.
dbname	string	false	Name of the database to check for tables. If not provided, the database name from the connection will be used.
filter	string	false	This is a string value that must match a table type in your database implementation. Each database is different. Some common filter types are: TABLE - This is the default value and will return only tables. VIEW - This will return only views. SYSTEM TABLE - This will return only system tables. GLOBAL TEMPORARY - This will return only global temporary tables. LOCAL TEMPORARY - This will return only local temporary tables. ALIAS - This will return only aliases. SYNONYM - This will return only synonyms.

Examples

Check JDBC Version

bx:dbinfo( type='version', name='result' );

The result variable is now populated with a query looking something like this:

DATABASE_PRODUCTNAME	DATABASE_VERSION	DRIVER_NAME	DRIVER_VERSION	JDBC_MAJOR_VERSION	JDBC_MINOR_VERSION
MySQL	8.0.33	MySQL Connector/J	8.0.33	4	2

Read All Database Views

Use type=tables to read all database tables, or filter for a specific type of table using filter=TABLE|VIEW|SYSTEM TABLE|GLOBAL TEMPORARY|LOCAL TEMPORARY:

bx:dbinfo( type='tables', name='result', filter='VIEW' );

TABLE_CAT	TABLE_SCHEM	TABLE_NAME	TABLE_TYPE	REMARKS	TYPE_CAT	TYPE_SCHEM	TYPE_NAME	SELF_REFERENCING_COL_NAME	REF_GENERATION
mydb	public	my_view	VIEW	Example view	NULL	NULL	NULL	NULL	NULL

Read Table Columns

bx:dbinfo( type='columns', name='result', table='admins' );

The admins table column information is now populated into the result variable. The result columns are:

• TABLE_CAT

• TABLE_SCHEM

• TABLE_NAME

• COLUMN_NAME

• DATA_TYPE

• TYPE_NAME

• COLUMN_SIZE

• BUFFER_LENGTH

• DECIMAL_DIGITS

• NUM_PREC_RADIX

• NULLABLE

• REMARKS

• COLUMN_DEF

• SQL_DATA_TYPE

• SQL_DATETIME_SUB

• CHAR_OCTET_LENGTH

• ORDINAL_POSITION

• IS_NULLABLE

• SCOPE_CATALOG

• SCOPE_SCHEMA

• SCOPE_TABLE

• SOURCE_DATA_TYPE

• IS_AUTOINCREMENT

• IS_GENERATEDCOLUMN

• SCOPE_CATLOG

• IS_PRIMARYKEY

• IS_FOREIGNKEY

• REFERENCED_PRIMARYKEY

• REFERENCED_PRIMARYKEY_TABLE

Output Column Names

Along with the data type and size

<bx:dbinfo type="columns" name="cols" table="tester">
<bx:output query="cols">
        #cols.COLUMN_NAME# #cols.TYPE_NAME#(#cols.COLUMN_SIZE#)<br>
</bx:output>