Release 10.1A: OpenEdge Data Management:
SQL Development

> ## Using the SQLLOAD utility

> The `SQLLOAD` utility loads user data from a formatted file into an SQL database. Typically, the source file for the load is created by executing the `SQLDUMP` utility. The `SQLLOAD` utility can process a source file created by another application or utility, if the format of the file conforms to `SQLLOAD` requirements. The file extension made available to `SQLLOAD` for processing must be `.dsql`. See the entry on `SQLDUMP` for a description of the required file format.

> Before you can execute `SQLLOAD` against a database server, the server must be configured to accept SQL connections and must be running

> Use the following syntax for the `SQLLOAD` utility:

> ##### Syntax

> Operating system | Syntax
> --- | ---
> UNIX Windows |
>     sqlload -u user_name [ -a password ] [ -C code-page-name ]
>         -t [ owner_name.]table_name1
>         [ [,owner_name.]table_name2, ... ]
>         [ -l log_file_name ]
>         [ -b badfile_name ]
>         [ -e max_errors ]
>         [ -s skipcount ]
>         [ -m maxrows ]
>         [ -F comma | quote ]
>         database_name

> The `SQLLOAD` utility reads application data from variable-length text-formatted files and writes the data into the specified database. The column order is identical to the table column order. `SQLLOAD` reads format and content header records from the dump file. You can load multiple tables in a single execution by specifying multiple table names, separated by commas. Data for one table is from a single dump file. Every source file corresponds to one database table. For example, if you specify 200 tables in the `SQLLOAD` command, you will load 200 database tables.

> The format for the records in the input files is similar to the Progress 4GL `.d` file dump format. The maximum record length `SQLLOAD` can process is 32K.

> Each database record read is share-locked, for consistency. You must ensure that the SQL Server has a lock table large enough to contain one lock for every record in the table. The default lock table size is 10,000 locks.

> `SQLLOAD` writes any errors to standard output and halts the loading process for any error so that data integrity is not compromised.

> Example 5–30 directs the `SQLLOAD` utility to load the data from two dump files into the `salesdb` database. The input files to `SQLLOAD` must be `tucker`.`customers.dsql` and `tucker.products.dsql`.

> Example 5–30: SQLLOAD of two dump files
>     sqlload -u tucker -a sulky -t tucker.customers,tucker.products
>     progress:T:thunder:4077:salesdb

> Example 5–31 directs `SQLLOAD` to load the data from all appropriately named dump files into the specified tables in the `salesdb` database.

> Example 5–31: SQLLOAD from appropriately named files
>     sqlload -u tucker -a sulky -t %.cust%,%.invent%,%.sales%
>     progress:T:thunder:4077:salesdb

> The database_name must be the last parameter given.

> The character set used by `SQLLOAD` must match the character set information recorded in each dump file. If the character sets do not match, the load is rejected. You can use the `SQL_CLIENT_CHARSET` environment variable to specify a character set.

> Each dump file you create with `SQLDUMP` contains character set information about that file. The character set recorded in the dump file is the client character set. The default character set for all non-JDBC clients is taken from the local operating system through the operating system APIs. JDBC clients use the Unicode UTF-8 character set.

> To use a character set different than that used by the operating system, set the `SQL_CLIENT_CHARSET` environment variable to the name of the preferred character set. You can define any Progress 4GL-supported character set name. The name is not case-sensitive.

> At run time, `SQLLOAD` reports an error if it detects a mismatch between the code page of the dump file being loaded and the code page of the client running `SQLLOAD`.

> By default, `SQLLOAD` displays `promsgs` messages using the code page corresponding to code-page-name. That is, if you are restoring a Russian database and code-page-name specifies the name of a Russian code page, the client displays `promsgs` messages using the Russian code page (unless you specify a different code page by setting the client’s `SQL_CLIENT_CHARSET_PROMSGS` environment variable).

> `SQLLOAD` does not support the following characters in schema names:

> - Double quote (")

> - Forward slash (/)

> - Backslash (\)

> `SQLLOAD`, however, does support schema names that contain special characters, such as a blank space, a hyphen (-), or a pound sign (#). These names must be used as delimited identifiers. Therefore, when specifying names with special characters on a UNIX command line, follow these rules:

> - Use double quotes to delimit identifiers.

> - So that the command line does not strip the quotes, use a backslash (\) to escape the double quotes used for delimited identifiers.

> - Use double quotes to enclose any names with embedded spaces, commas, or characters special to a command shell (such as the Bourne shell). This use of quotes is in addition to quoting delimited identifiers.

> To load the table `Yearly Profits`, use the UNIX command-line syntax, as shown in Example 5–32.

> Example 5–32: SQLLOAD of files with delimited identifiers
>     sqlload -u xxx -a yyy -t "\"Yearly Profits\"" database_name

> In Windows NT, the command interpreter rules for the use of double quotation marks varies from UNIX.

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095