SAND CDBMS Tools Reference Guide
Data Loader (ndlm)

Performing Database Updates and Deletions Using the EXECSQL Command

The EXECSQL keyword, when it appears at the beginning of the load specification script, instructs ndlm to update, insert, or delete records in a database table using the standard SQL commands. When the specified operation is complete, ndlm implicitly executes a COMMIT WORK command to make the changes permanent.

Consult the SAND CDBMS SQL Reference Guide for the proper syntax for UPDATE, INSERT, and DELETE statements. Each SQL statement included in the load specification script must be terminated by a semicolon ( ; ).

The EXECSQL statement has the following general form:

EXECSQL @update-file-name
{
  [ options ]

RECORD
{
<record specification>
}

SQL | UPSERT
{
<UPDATE statement> ; | <DELETE statement> ;
}

[ ALTERNATESQL | ELSE
{
<INSERT statement> ;
} ]

}

Note:
Comment lines (beginning with //, or surrounded by /*... */ ) can be included anywhere within the load specification script, except within the PIC formatting string (see above).

EXECSQL
Instructs ndlm to open a data file that will be treated as an update file, according to the specifications in the script. Make sure that a space separates the EXECSQL keyword from the @ symbol.

@update-file-name

Identifies the ASCII file containing the data to be used to update the database. The "at" symbol ( @ ) must prefix the file name. The file name may include a full path specification.

{ }

Braces are used to group components of the specification file. They must appear in the relative positions indicated in the EXECSQL statement outline. In the outline, braces are placed on separate lines to make the file more readable. Tabs, spaces, and line feed characters are all treated by ndlm as "white space" and may be used to make the file easier to read.

Options

Processing options may be entered before the record specification. In the event of a conflict between a flag entered in the ndlm invocation and an option in the specification file, the flag in the invocation takes precedence. The IGNORE, SKIP, PROCESS, MAXERR, ERRINC, and NULL options (as described in the IMPORT section above) are available with the EXECSQL command.

Record-specification

The EXECSQL record specification section follows the syntax of the IMPORT record specification, and allows for all of the scripting features of the IMPORT function as described above.

SQL...ALTERNATESQL and UPSERT...ELSE Keywords

These keyword pairs specify SQL commands to be executed during import. They operate identically and are interchangeable. If the command specified after the SQL or UPSERT keyword fails, the command following the ALTERNATESQL or ELSE keyword will be executed. Generally, the UPDATE statement will be specified first; if the UPDATE does not succeed (probably because the record does not already exist), an INSERT can then be performed. Note that specification of an alternative command (preceded by the ALTERNATESQL or ELSE keyword) is optional.

The commands following the SQL...ALTERNATESQL or UPSERT...ELSE keywords must be syntactically valid SAND CDBMS SQL statements. NDL++ expressions are not permitted in SQL statements.

Examples:

EXECSQL @update_file.txt
{

RECORD
{
field1 (1:15)
field2 (16:15)\r\n
}
SQL
{
UPDATE schema1.table1 SET column2='field2' WHERE key_column='field1';
}
ALTERNATESQL
{
INSERT INTO schema1.table1 VALUES ('field1','field2');
}

}

-or-

EXECSQL @update_file.txt
{

RECORD
{
field1 (1:15)
field2 (16:15)\r\n
}
UPSERT
{
UPDATE schema1.table1 SET column2='field2' WHERE key_column='field1';
}
ELSE
{
INSERT INTO schema1.table1 VALUES ('field1','field2');
}

}

Note that each SQL statement ends with a semicolon. The names of fields containing character-type values must be enclosed in single quotation marks; numeric type fields do not require quotation marks.

The DELETE statement is normally used on its own, with no alternative (however, it is possible to specify an alternative if desired). For example:

EXECSQL @update_file.txt
{

RECORD
{
field1 (1:15)
field2 (16:15)\r\n
}
SQL
{
DELETE FROM schema1.table1 WHERE column1='field1' AND column2='field2';
}

}