SAND CDBMS Tools Reference Guide
Data Loader (ndlm)

Importing from an ODBC Data Source

Ndlm supports the importing of data from any ODBC source (DSN) into database tables and/or SCT Files. Load specification scripts for importing from ODBC sources are similar to standard specification scripts, with the following exceptions:

• Instead of a RECORD section, ODBC import scripts specify either a DSNSTRING (containing ODBC connection information) or a DSNFILE (the location of the file DSN). The DSN section precedes the TYPE and VAR sections, if these are included.
• An SQL SELECT statement must be included after the DSN section, and before the TYPE and VAR sections if these are present. This indicates which data are to be retrieved from the data source (to retrieve all data from a table, use the statement "SELECT * FROM schema-name.table-name;"
  
• The IGNORE n option is not available with ODBCIMPORT.
• The column names in the script must exactly match those in the source table. Note that if derived columns are produced by the SELECT statement for example, by aggregate functions), an alias must be declared for each.

Some databases require a relatively long ODBC connection string (refer to the Nucleus ODBC Setup Guide for Windows Clients for further information). In these cases, it may be preferable to create a File DSN and refer to it using the DSNFILE option. Note that when the DSNFILE option is used, the specification must include the complete path to the ODBC File DSN.

On Windows systems, when ndlm is invoked without the -b option and DSNFILE is specified in the ODBCIMPORT script, the Microsoft ODBC Manager File DSN dialog box will appear, prompting for selection of the appropriate file.

Syntax:

ODBCIMPORT
{
  [ SCTFILE:@<SCT output file> ]
  [ LOAD:<SCT load option> ]

  <options>

DSNSTR { <connection string> } | DSNFILE @path\dsn-file-name

<SELECT statement> ;

[ TYPE
{
<data type declaration>
} ]

[ VAR
{
<variable declaration>
} ]

[<SKIPIF clause>]

[schema-name.]Table-name
{
source-column-name1 target-column-name1
source-column-name2 target-column-name2
...
source-column-nameN target-column-nameN
}

}

Examples:

Using an ODBC Connection String

-- importing from a SAND database

ODBCIMPORT
{
DSNSTR
{
DRIVER={NUCLEUS ODBC Driver};CONNECTION=SERV1_TEST093A;DATABASE=TEST093A;UID=JOE;PWD=AAA
}
SELECT * FROM SCHEMA1.S;
SCHEMA1.S
{
SN SN
SNAME SNAME
STATUS STATUS
CITY CITY
}
}

-- importing from an Microsoft Access database

ODBCIMPORT
{
DSNSTR
{
DBQ=H:\qatest\ndlscript\tests\TESTDB.mdb;
DefaultDir=H:\qatest\ndlscript\tests;Driver={Microsoft AccessDriver(*.mdb)};DriverId=281;
FIL=MSAccess;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;
UID=admin; UserCommitSync=Yes;
}
SELECT * FROM SCHEMA1.P;
SCHEMA1.P
{
PN PN
PNAME PNAME
COLOR COLOR
WEIGHT WEIGHT
CITY CITY
}
}

Using a File DSN

ODBCIMPORT
{
DSNFILE @C:\Progra~1\Common~1\ODBC\DataSo~1\testdb.dsn
SELECT * FROM SCHEMA1.P;
SCHEMA1.P
{
pn PN
pname PNAME
color COLOR
weight WEIGHT
city CITY
}
}