Database metadata

Database metadata is data about a database. In this article, we also cover information about the database connection/server/client.

SQLDB

Server & client information

In FPC development versions as of November 2012, you can use GetConnectionInfo with the following parameters:

• citServerType: returns type of server (example: IBConnection returns 'Interbase' or 'Firebird' depending on what server it connected to)
• citServerVersionString: returns complete identification/version string, formatted the way the server sends it
• citServerVersion: returns a string formatted as a number (e.g. 0205 representing 2.5)
• citClientName: returns the name of the client/driver DLL/library (e.g. fbembed.dll for Firebird)
• citClientVersion: returns version information returned by the client

Not all SQLDB T*Connections support this; if there is no support the functions will return empty values

Firebird/Interbase specifics

• citServerVersion returns major and minor numbers only for Firebird (e.g. 0205 for version 2.5.1); it returns 3 numbers for Interbase
• citClientVersion is not supported and probably will never be supported for Firebird on Linux/*Nix due to missing functionality

In addition to the functions above, as of December 2012, the Firebird connector provides the GetODS function that shows the ODS major version of the database you are connected to. This ODS version determines what features are available for use - as you can e.g. use a database created with Firebird 2.1 with a Firebird 2.5 server, but the features will be limited to 2.1 functionality.

Schema information

Also known as a database's DDL (Data Definition Language), a schema is the representation of the tables, views, procedures and other objects that make up a database.

You can use the GetSchemaInfoSQL function to get an SQL string that you can run in a query to get information about metadata. Use the SchemaType parameters to request information about certain objects:

• stTables: user tables (created by the application/database user)
• stSysTables: system tables (internal tables used by the database system)
• stProcedures: stored procedures
• stColumns: columns in e.g. a table. Supply the name of the table etc in the SchemaObjectName parameter

GetSchemaInfoSQL is not implemented equally for every connector; the Firebird connector currently has most of the functionality implemented; the others (e.g. MS SQL, Sybase, Oracle) have less functionality. Patches are welcome.

Lazarus TSQLQuery metadata tool

GetSchemaInfoSQL is used in Lazarus: in design mode, if you use the Object Inspector to edit the SQL property of a TSQLQuery where the database connection is connected, you can not only edit the text of the SQL query in the SQL Code tab, but also retrieve information in the Metadata tab by filling in some details and pressing on the Play/Go key:

Data dictionary schema information

There is a separate FPC data dicitionary package that has support for schema information. This package is used in the LazDataDesktop project supplied with Lazarus.

Database specific information

Many databases have either

• system tables/views (which you can select from) or
• stored procedures

that show information about database objects as well as server version etc.

Examples are the @@VERSION variables in MS SQL Server and Sybase servers.

If your database connection library does not support other means of getting this information, you can use this (and submit a patch/improvement request on the relevant bug tracker for incorporation into SQLDB/Zeos etc)

See also

• Working With TSQLQuery More information about queries