LucidDbCreateForeignServer

Syntax

CREATE SERVER server-name
[ TYPE 'server-type' ]
[ VERSION 'server-version' ]
FOREIGN DATA WRAPPER wrapper-name
[ OPTIONS ( server-option, ... ) ]
[ DESCRIPTION server-description ]

server-option ::= option-name 'option-value-as-literal-string'

Examples

NOTE: If you're using JDBC drivers to connect other database (such as MySQL / Oracle) like the examples below, you'll need to add these drivers to the CLASSPATH. Easiest way to do this is to add the JDBC jar (ie, ojdbc16.jar) to $LUCIDDB_HOME/bin/classpath.gen and that will get in your classpath. --Ngoodman 14:55, 10 July 2011 (EDT) Does CREATE JAR? allow the driver to work properly given the JDBC issues with DriverManager? If so, you can also do a CREATE JAR statement.

Hypersonic

CREATE SERVER jdbc_link
FOREIGN DATA WRAPPER sys_jdbc
OPTIONS(
    driver_class 'org.hsqldb.jdbcDriver',
    url 'jdbc:hsqldb:examples/etl/jdbcdata/scott',
    user_name 'SA',
    table_types 'TABLE');

MySQL

(for use with Connector/J 5.x)

CREATE SERVER mysql 
FOREIGN DATA WRAPPER sys_jdbc 
OPTIONS ( 
    driver_class 'com.mysql.jdbc.Driver', 
    url 'jdbc:mysql://hk/TESTING?useCursorFetch=true', 
    user_name 'root', 
    password 'root', 
    login_timeout '10',
    fetch_size '1000',
    validation_query 'select 1',
    table_types 'TABLE', 
    schema_name 'TESTING');

(for use with Connector/J 3.x)

CREATE SERVER mysql 
FOREIGN DATA WRAPPER sys_jdbc 
OPTIONS ( 
    driver_class 'com.mysql.jdbc.Driver', 
    url 'jdbc:mysql://hk/TESTING', 
    user_name 'root', 
    password 'root', 
    login_timeout '10',
    validation_query 'select 1',
    fetch_size '-2147483648',
    table_types 'TABLE', 
    schema_name 'TESTING');

If you get "Error: Support for foreign schema import not available in data", try adding the qualifying_catalog_name option, matching the MySQL database name; for the example above, it would be

    ...
    url 'jdbc:mysql://hk/TESTING', 
    qualifying_catalog_name 'TESTING,
    ...

More information on this issue is available in eigenjira:LDB-186.

If you get an error about an improper character encoding, you need to specify what encoding your MySQL database is using in the URL. For example, if you're using UTF-8, you would add to your URL: ?characterEncoding=utf-8 (replacing ? with & if you've already added something else as well).

PostgreSQL

create server postgres_psrv
foreign data wrapper sys_jdbc
options(
    driver_class 'org.postgresql.Driver',
    url 'jdbc:postgresql://psrv:11000/abc',
    user_name 'postgres',
    table_types 'TABLE',
    fetch_size '10000',
    autocommit 'false'
);

CSVJDBC

The csvjdbc driver is a JDBC driver for .csv files. Normally, you should use LucidDB's native flatfile reader instead for .csv files; however, the native reader does not yet support anything other than ISO-8859-1 encoding, so use the csvjdbc driver for accessing other encodings such as UTF-8.

create server utf8_csv_server
foreign data wrapper sys_jdbc
options(
    driver_class 'org.relique.jdbc.csv.CsvDriver',
    url 'jdbc:relique:csv:/path/to/unicode/files',
    schema_name 'TESTDATA',
    extended_options 'TRUE',
    "charset" 'UTF-8');

Salesforce

CREATE SERVER sfdc_server
FOREIGN DATA WRAPPER SALESFORCE
OPTIONS (
  USER_NAME 'joe@mac.com',
  PASSWORD 'sekritjcD8RmGCoXDk6gjmA8Zt3EgX'
);

For more information, see FarragoMedSalesforcePlugin.

Additional Resources

• Design documentation on the JDBC foreign data wrapper (including documentation of OPTIONS).
• Specifications for flat file foreign data access
• Examples for retrieving available extended options for a driver