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, 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 metadata > Extract from database.

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

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 the corresponding section. Once it has been defined, Name, User, Password, URL and/or JNDI fields become filled in the Database Connection wizard.

Then click Next to see a database schema.

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 the following window:

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"

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 (back quote is used as inline CTL special character)

• square brackets
  MSSQL 2008, MSSQL 2000-2005

• without quotes
  When the default (Generic) JDBC specific is 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, MetadataEditor 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.

• By clicking the Finish button (in case of internal metadata), you will get internal metadata in the Outline pane.

• On the other hand, if you wanted to extract external (shared) metadata, you must click the Next button first, after which you will be prompted to decide which project and which subfolder should contain your future metadata file. After expanding the project, selecting the meta subfolder, specifying the name of the metadata file and clicking Finish, it is saved into the selected location.