9.9  Importing from Databases

One simple way to import data from a database into Catalyzer is to save the data from the tables in "CSV" format, and use the "Import Files" option. You can also go via a spreadsheet, and manually copy and paste the tables into Catalyzer. But if you do this, then you'll need to patch up the links and references between tables yourself (e.g. by changing the field types from "Text" to "Reference"), and also convert the field data types to Date and Number as appropriate.

The Database Import Module built into Catalyzer allows a more sophisticated import of a database, preserving the types and links between tables. However, as with most things to do with databases it requires you to set up a number of parameters for the connection, so is only really suitable for database administrators to run.

The ability to import from databases is available for Catalyzer as an additional plugin. Once installed, the plugin can be accessed either from the main menu (File>Import>Other...) or from the context sensitive menu in the catalog record tree (Import>Other...). Select SQL Database as the data source, then click on Profile properties. A range of configuration options are available.

The connection to the database - "Java DataBase Connector" or JDBC

The database import module uses the standard "JDBC" connection to talk to the database (JDBC is the Java version of "ODBC" on Windows). The JDBC parameters are required to connect to the data source. Please refer to your database vendor documentation on obtaining a JDBC connection to the database. You can specify:

• JDBC Driver
• JDBC URL
• JDBC Username
• JDBC Password

The Data storage options allow you to specify what types of data will be retrieved from the database. The options are as follows:

• Store data - store the data contained in the source database tables
• Store structure - create metadata records describing the structure of the source database tables.
• Store JDBC Parameters - create records storing the JDBC parameters used to extract the data.
• Foreign key map file - some DBMS (notably MS Access) do not allow the export of foreign key relations. The user can specify a file in which they have created these mappings in XML. The importer will use this file in lieu of attempting to retrieve the mapping data from the database.

Once the properties have been entered, click 'Import'. The database should be accessed and retrieved data added to the catalog.

Note on JDBC Drivers - Catalyzer (and the database import plugin) does not include any third party JDBC drivers. Some databases are accessible without any additional drivers (ODBC databases). Others require a driver to be made available to Catalyzer. To do this: first obtain a JDBC driver from the database vendor. This will usually be available as a jar file. You should add the driver to the installed java extensions by copying the jar file to $JAVA_HOME/lib/ext. See
http://java.sun.com/j2se/1.4.2/docs/guide/extensions/spec.html#installed
for more details on installed extensions.