Using the SQDR Text Driver

The SQDR Text Driver can be used to read from or to create files in the following formats:

CSV or TAB files are commonly used when importing and exporting to/from spreadsheets and some database systems.  The default is to create CSV files with an extension of .TXT.

The Text driver executes SQL statements directly on the text files. The driver supports Insert statements and inserts the record at the end of the file. You can execute Update and Delete statements conditionally.

The Text driver can access files up to 15 GB in size.

Table names can be up to 32 character. When the driver creates the file (e.g. with CREATE TABLE),  the filename itself is truncated to 8 characters (plus the 3 character extension) and folded to upper case.  The mapping between filename and table name is configured in QETXT.INI e.g.:

as created by the driver:

[Defined Tables]
MYTABLE1.TXT=MYTABLE123456

[MYTABLE123456]
FILE=MYTABLE1.TXT

But manipluating QETXT.INI allows for flexibility in naming and location - e.g.:

[Defined Tables]
MySubDir/MyTabInASubDir.txt=MYTABINASUBDIRECTORY

[MYTABINASUBDIRECTORY]
FILE=MySubDir/MyTabInASubDir.txt

The above information about manipulating filename to table name mapping was discovered experimentally and may not apply in all cases.

Path names can be maximum of 128 characters. Setting the Use Long Qualifiers (ULQ) property to 1 allows path names up to 255 characters.

For additional information, refer to the ODBC 7.1.6 User's Guide in the SQDR_ODBC Drivers Program Group.

To use as a destination:

Create a target directory e.g C:\mydest and specify it as the Database.

Enable AllowUpdateAndDelete (unless the desired result is an archive table).

When creating a subscription, specify either Append replicated rows to existing data (for archive purposes) or Truncate table before replication (rather than Delete existing database before replication) on the destination panel.

Sample connection string:

Database=C:\mydest;AllowUpdateAndDelete=1

When creating the source or destination object, specify Integrated Security, or place any value in the User field; this information is ignored.

When you run the baseline or snapshot, the file will be created.  In addition a file named QETXT.INI containing column information for all tables in the directory is created.

If you specify FirstLineNames=1, the column information will appear as the first line of the resulting text file.

If the desired result is an archive table, you do not need to enable AllowUpdateAndDelete; instead, specify "Append replicated rows to existing data" on the Destination panel of the subscription.  This may result in better performance. Since CSV tables cannot be indexed, searched updates and deletes may be slow.

Here is another sample connection string, specifying that the resulting file will have a CSV extension, and its contents delimited with with the pipe character:

Database=L:\Transfer;AllowUpdateAndDelete=1;TableType=Character;Delimiter=|;DataFileExtension=CSV;FirstLineNames=1

To use as a snapshot source:

Sample connection string:

Database=C:\mydest

The directory should contain the file QETXT.INI containing column information. Refer to the Help documentation or, for examples, examine the files created by SQDR when the text driver is used as destination.

Limitations

• Data types:

• Only character, timestamp (dates), and numeric values are supported. However, most data types can be mapped to some kind of character literals.

• Numbers are limited to a precision of 15.

• While binary data types (e.g. BLOB) are not supported, hex and hex64 forms may be used.

• NOT NULL constraints are ignored.

• CSV tables cannot be indexed, so searched updates and deletes may be slow.

• Transactions are not supported.