ODBC setup

In the Color Factory Settings app, expand the relevant channel and select Metadata. Open the ODBC (Open Database Connectivity) tab.

This functionality makes it possible to extract text from a database and store it in the files processed in the channel or to store metadata from the files in the connected database.

This is done by setting up a data source and mapping database fields to XMP metadata fields:

1. First, select Activate ODBC connection, then enter a Data Source Name (DSN) or choose one by selecting Browse (...).

Note: For this to function correctly, a system DSN must be configured on the server; a User DSN will not work. You can optionally enter the username and password required to access the DSN.

2. Choose how Color Factory should interact with the database. You have three options:
   
   

• Read from database, write to file - searches the database for a matching record, extracts content from it, and stores the information in the file processed in the channel.
• Read from file, update to database - updates the matching record in the database with the content in the files processed in the channel.
• Read from file, update and insert to database - updates existing records in the database with metadata from the files, and inserts new records in the database if new files are processed that don't have an existing record.

3. Configure the mapping of column contents to metadata fields. Select Configure and choose a table from the DSN in the Select table dropdown list.
   
   
    
4. Choose the metadata field to which the content should be mapped and the action to perform in this field. You can replace existing content in the chosen metadata field or append the new data before or after any existing text. You can add a further query to the base SQL query (primary key) using AND.
5. Choose which metadata field you want to use as the Primary key field. Color Factory will try to match the content of that metadata field to an existing record in the database. When a match has been found, the action specified for each field will be performed (replace or insert before/after).
   
   If you choose File name in the Primary key field, Color Factory can search for a part of the name, the full name, or the full name with an extension. Choose Use part of name to specify the number of characters Color Factory should compare in the file name and the database column content.

• Append to ‘WHERE’ clause: Add additional queries to the base SQL query (primary key) using AND. 
• Search for exact expression. Use ‘=’ instead of ‘LIKE’ - The primary key search is usually a search for an exact match. You can replace this with a like search. 
• Primary Key is ‘Auto number’- Do not set this column on INSERT -  If the primary key is an auto-increment value, it must be removed from inserts in SQL, as you don't define a value in the insert statement. Instead, the database is auto-assigned a new unique value.