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.

Figure 31.20. Extracting Internal Metadata from a Database

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

Figure 31.21. 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 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 Nextto see a database schema.

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

Figure 31.23. 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

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, 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.