Version

    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 metadataExtract from database.

    Extracting Internal Metadata from a Database

    Figure 32.20. Extracting Internal Metadata from a Database


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

    Database Connection Properties Dialog

    Figure 32.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 you must click Next. After that, you can see a database schema.

    Selecting Columns for Metadata

    Figure 32.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:

    Generating a Query

    Figure 32.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]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 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

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