Extracting Metadata from a Database

If you want to extract metadata from a database (when you select the Extract from database option), you must have some database connection defined prior to extracting metadata.

In addition to this, if you want to extract internal metadata from a database, you can also right-click any connection item in the Outline pane and select New metadataExtract from database.

Extracting Internal Metadata from a Database

Figure 32.24. Extracting Internal Metadata from a Database


After each of these three options, a Database Connection properties dialog opens.

Database Connection Properties Dialog

Figure 32.25. Database Connection Properties Dialog


In order to extract metadata, you must first select database connection from the existing ones (using the Connection menu) or load a database connection using the Load from file button or create a new connection as shown in corresponding section. Once it has been defined, Name, User, Password, URL and/or JNDI fields become filled in the Database Connection wizard.

Then you must click Next. After that, you can see a database schema.

Selecting Columns for Metadata

Figure 32.26. Selecting Columns for Metadata


Now you have two possibilities:

Either you write a query directly, or you generate the query by selecting individual columns of database tables.

If you want to generate the query, hold Ctrl on the keyboard, highlight individual columns from individual tables by clicking the mouse button and click the Generate button. The query will be generated automatically.

See following window:

Generating a Query

Figure 32.27. Generating a Query


If you check the Prefix table names with schema checkbox, it will have the following form: schema.table.column. If you check the Quote identifiers checkbox, it will look like one of this: "schema"."table"."column" (Prefix table names with schema is checked) or "table"."column" only (the mentioned checkbox is not checked). This query is also generated using the default (Generic) JDBC specific. Only it does not include quotes.

Remember that Sybase has another type of query which is prefixed by schema. It looks like this:

"schema"."dbowner"."table"."column"

[Important]Important

Remember that quoted identifiers may differ for different databases. They are:

  • double quotes

    DB2, Informix (for Informix, the DELIMIDENT variable must be set to yes otherwise no quoted identifiers will be used), Oracle, PostgreSQL, SQLite, Sybase

  • back quotes

    Infobright

  • backslash with back quotes

    MySQL (backquote is used as inline CTL special character)

  • square brackets

    MSSQL 2008, MSSQL 2000-2005

  • without quotes

    When the default (Generic) JDBC specific or Derby specific are selected for corresponding database, the generated query will not be quoted at all.

Once you have written or generated the query, you can check its validity by clicking the Validate button.

Then you must click Next. After that, Metadata Editor opens. In it, you must finish the extraction of metadata. If you wish to store the original database field length constraints (especially for strings/varchars), choose the fixed length or mixed record type. Such metadata provide the exact database field definition when used for creating (generating) table in a database, see Create Database Table from Metadata