Extraction Limitations

Restrictions and limitations affect the data extraction facility.

Extract works only with data stored in the IQ store.
Extract does not work on system tables or cross database joins.
Extract does not work with queries that use user-defined functions or system functions, except for the system functions suser_id() and suser_name().
A binary LOAD TABLE always trims blanks from VARCHAR data. If you have VARCHAR data with trailing blanks, they are not preserved on insert by a binary load.
Trailing zeros are padded onto VARBINARY data during the extract. For example, a field declared as varbinary(6), which contains the data 0x1234, is padded with zeros during extraction and is loaded after extraction as 0x123400.
To reproduce floating point data exactly, use the binary option.
Tape devices are not currently supported.
If you run dbisql (Interactive SQL) with the -q (quiet mode) option and the data extraction commands are in a command file, you must first set and make permanent the dbisql option Show multiple result sets. If this option is not set, the output file is not created.

To set the Show multiple result sets option, click Tools > Options in the dbisql window, then select Show multiple result sets and click Make permanent.

When Temp_Extract_Name1 is set, you cannot perform these operations:

INSERT...SELECT

Events do not support execution of statements that return result sets. The server log returns an error similar to:

Handler for event 'test_ev' caused SQLSTATE '09W03'
Result set not permitted in 'test_ev'

To execute a query through an event, create an event that calls a stored procedure and insert the stored procedure results into a temporary table. If extract is used, the temporary table is always empty and requires little overhead.

For example:

CREATE PROCEDURE proc1()
BEGIN
	SET TEMPORARY OPTION temp_extract_name1 = 'testproc.out';
	SELECT * FROM iq_table;
END;

CREATE EVENT "test_ev" ENABLE HANDLER
BEGIN
	SELECT * INTO #tmp FROM proc1();
END;

TRIGGER EVENT test_ev;