In addition to what Bruce says, the DELIMITED BY caused a problem in the first slightly-modified version of your code:
EXCEPTION in BEGIN block at 2010-03-15 11:13:27.284:
SQLCODE = -638, SQLSTATE = 22001,
ERRORMSG() = Right truncation of string data
Here's the first modified version; note the EXCEPTION handler and the @here variable for pinpointing problems...
-- version 1
BEGIN
DROP TABLE t1;
EXCEPTION WHEN OTHERS THEN
END;
CREATE TABLE t1 (
fd_entity VARCHAR ( 1 ),
data1 integer,
data2 integer );
INSERT t1 VALUES ( '1', 1, 1 );
INSERT t1 VALUES ( '1', 2, 2 );
COMMIT;
UNLOAD TABLE t1 TO 'c:/temp/1/history/t1.txt' DELIMITED BY '''|''';
BEGIN
DROP TABLE zz_t1;
EXCEPTION WHEN OTHERS THEN
END;
CREATE TABLE zz_t1 (
fd_entity VARCHAR ( 1 ),
data1 integer,
data2 integer );
BEGIN
DROP TABLE sy_data_upload;
EXCEPTION WHEN OTHERS THEN
END;
CREATE TABLE sy_data_upload (
fd_entity VARCHAR ( 1 ),
sy_table_name char(100),
sy_file long varchar,
sy_demo_yn char(1),
use_zz_yn char(1) );
INSERT sy_data_upload VALUES ( '1', 't1', 't1.txt', 'Y', 'Y' );
COMMIT;
BEGIN
declare cmdLoadZZ long varchar;
declare WriteLine long varchar;
declare Delimiter char(20);
declare cmdDeleteZZ long varchar;
declare TableName char(100);
declare DataFile long varchar;
declare DemoYN char(1);
declare Entity char(10);
declare zzTableYN char(1);
declare LogFile long varchar;
declare @loopcounter integer;
DECLARE cEntity cursor FOR
SELECT sy_table_name, sy_file, sy_demo_yn, use_zz_yn
FROM sy_data_upload
WHERE fd_entity = @Entity;
DECLARE @Entity VARCHAR ( 1 );
DECLARE @sqlcode INTEGER;
DECLARE @sqlstate VARCHAR ( 5 );
DECLARE @errormsg VARCHAR ( 32767 );
DECLARE @here VARCHAR ( 1 );
SET @here = '1';
SET @Entity = '1';
SET @loopcounter = 0;
SET LogFile = '';
SET Delimiter = ' DELIMITED BY ' + Char(39) + '|' + Char(39);
OPEN cEntity;
SET @here = '2';
FETCH cEntity INTO TableName, DataFile, DemoYN, zzTableYN;
WHILE SQLCode = 0 LOOP
SET @loopcounter = @loopcounter + 1;
SET cmdDeleteZZ = 'DELETE FROM zz_' + TableName + ' WHERE fd_entity = ' + Char(39) + @Entity + Char(39);
SET cmdLoadZZ = 'LOAD TABLE ' || 'zz_' || TableName || ' FROM ' || '''c:\\temp\\' || @Entity || '\\history\\' || DataFile || '''' || Delimiter;
SET LogFile = LogFile + str(@loopcounter) + ' ' + cmdDeleteZZ + ' ' + cmdLoadZZ;
SET @here = '3';
EXECUTE IMMEDIATE cmdDeleteZZ;
MESSAGE STRING(str(@loopcounter) + ' cmdDeleteZZ: ', cmdDeleteZZ, str(SQLSTATE)) TO CONSOLE;
SET @here = '4';
EXECUTE IMMEDIATE cmdLoadZZ;
MESSAGE STRING(str(@loopcounter) + ' cmdLoadZZ: ', cmdLoadZZ, str(SQLSTATE)) TO CONSOLE;
FETCH cEntity INTO TableName, DataFile, DemoYN, zzTableYN;
END LOOP ;
SET @here = '5';
Close cEntity;
SET @here = '6';
UNLOAD SELECT LogFile To 'c:/temp/loaddata_log.txt' ;
EXCEPTION
WHEN OTHERS THEN
SELECT SQLCODE, SQLSTATE, ERRORMSG()
INTO @sqlcode, @sqlstate, @errormsg;
MESSAGE STRING (
'EXCEPTION in BEGIN block at ',
CURRENT TIMESTAMP,
': @here = ', @here,
', SQLCODE = ', @sqlcode,
', SQLSTATE = ', @sqlstate,
', ERRORMSG() = ', @errormsg )
TO CONSOLE;
END;
SELECT * FROM zz_t1;
The second modified version shows the problem Bruce mentions:
Starting checkpoint of "ddd11" (ddd11.db) at Mon Mar 15 2010 11:29
Finished checkpoint of "ddd11" (ddd11.db) at Mon Mar 15 2010 11:29
1 cmdDeleteZZ: DELETE FROM zz_t1 WHERE fd_entity = '1' 2000
Starting checkpoint of "ddd11" (ddd11.db) at Mon Mar 15 2010 11:29
Finished checkpoint of "ddd11" (ddd11.db) at Mon Mar 15 2010 11:29
1 cmdLoadZZ: LOAD TABLE zz_t1 FROM 'c:\temp\1\history\t1.txt' 0
EXCEPTION in BEGIN block at 2010-03-15 11:29:51.165:
@here = 4, SQLCODE = -180, SQLSTATE = 24501,
ERRORMSG() = Cursor not open
Here's the second modified version:
-- version 2
BEGIN
DROP TABLE t1;
EXCEPTION WHEN OTHERS THEN
END;
CREATE TABLE t1 (
fd_entity VARCHAR ( 1 ),
data1 integer,
data2 integer );
INSERT t1 VALUES ( '1', 1, 1 );
INSERT t1 VALUES ( '1', 2, 2 );
COMMIT;
UNLOAD TABLE t1 TO 'c:/temp/1/history/t1.txt'; -- DELIMITED BY '''|''';
BEGIN
DROP TABLE zz_t1;
EXCEPTION WHEN OTHERS THEN
END;
CREATE TABLE zz_t1 (
fd_entity VARCHAR ( 1 ),
data1 integer,
data2 integer );
BEGIN
DROP TABLE sy_data_upload;
EXCEPTION WHEN OTHERS THEN
END;
CREATE TABLE sy_data_upload (
fd_entity VARCHAR ( 1 ),
sy_table_name char(100),
sy_file long varchar,
sy_demo_yn char(1),
use_zz_yn char(1) );
INSERT sy_data_upload VALUES ( '1', 't1', 't1.txt', 'Y', 'Y' );
COMMIT;
BEGIN
declare cmdLoadZZ long varchar;
declare WriteLine long varchar;
declare Delimiter char(20);
declare cmdDeleteZZ long varchar;
declare TableName char(100);
declare DataFile long varchar;
declare DemoYN char(1);
declare Entity char(10);
declare zzTableYN char(1);
declare LogFile long varchar;
declare @loopcounter integer;
DECLARE cEntity cursor FOR
SELECT sy_table_name, sy_file, sy_demo_yn, use_zz_yn
FROM sy_data_upload
WHERE fd_entity = @Entity;
DECLARE @Entity VARCHAR ( 1 );
DECLARE @sqlcode INTEGER;
DECLARE @sqlstate VARCHAR ( 5 );
DECLARE @errormsg VARCHAR ( 32767 );
DECLARE @here VARCHAR ( 1 );
SET @here = '1';
SET @Entity = '1';
SET @loopcounter = 0;
SET LogFile = '';
SET Delimiter = ''; -- ' DELIMITED BY ' + Char(39) + '|' + Char(39);
OPEN cEntity;
SET @here = '2';
FETCH cEntity INTO TableName, DataFile, DemoYN, zzTableYN;
WHILE SQLCode = 0 LOOP
SET @loopcounter = @loopcounter + 1;
SET cmdDeleteZZ = 'DELETE FROM zz_' + TableName + ' WHERE fd_entity = ' + Char(39) + @Entity + Char(39);
SET cmdLoadZZ = 'LOAD TABLE ' || 'zz_' || TableName || ' FROM ' || '''c:\\temp\\' || @Entity || '\\history\\' || DataFile || '''' || Delimiter;
SET LogFile = LogFile + str(@loopcounter) + ' ' + cmdDeleteZZ + ' ' + cmdLoadZZ;
SET @here = '3';
EXECUTE IMMEDIATE cmdDeleteZZ;
MESSAGE STRING(str(@loopcounter) + ' cmdDeleteZZ: ', cmdDeleteZZ, str(SQLSTATE)) TO CONSOLE;
SET @here = '4';
EXECUTE IMMEDIATE cmdLoadZZ;
MESSAGE STRING(str(@loopcounter) + ' cmdLoadZZ: ', cmdLoadZZ, str(SQLSTATE)) TO CONSOLE;
FETCH cEntity INTO TableName, DataFile, DemoYN, zzTableYN;
END LOOP ;
SET @here = '5';
Close cEntity;
SET @here = '6';
UNLOAD SELECT LogFile To 'c:/temp/loaddata_log.txt' ;
EXCEPTION
WHEN OTHERS THEN
SELECT SQLCODE, SQLSTATE, ERRORMSG()
INTO @sqlcode, @sqlstate, @errormsg;
MESSAGE STRING (
'EXCEPTION in BEGIN block at ',
CURRENT TIMESTAMP,
': @here = ', @here,
', SQLCODE = ', @sqlcode,
', SQLSTATE = ', @sqlstate,
', ERRORMSG() = ', @errormsg )
TO CONSOLE;
END;
SELECT * FROM zz_t1;
As Bruce says, adding WITH HOLD to the OPEN makes gets rid of the second exception. Here is version 3...
-- version 3
BEGIN
DROP TABLE t1;
EXCEPTION WHEN OTHERS THEN
END;
CREATE TABLE t1 (
fd_entity VARCHAR ( 1 ),
data1 integer,
data2 integer );
INSERT t1 VALUES ( '1', 1, 1 );
INSERT t1 VALUES ( '1', 2, 2 );
COMMIT;
UNLOAD TABLE t1 TO 'c:/temp/1/history/t1.txt'; -- DELIMITED BY '''|''';
BEGIN
DROP TABLE zz_t1;
EXCEPTION WHEN OTHERS THEN
END;
CREATE TABLE zz_t1 (
fd_entity VARCHAR ( 1 ),
data1 integer,
data2 integer );
BEGIN
DROP TABLE sy_data_upload;
EXCEPTION WHEN OTHERS THEN
END;
CREATE TABLE sy_data_upload (
fd_entity VARCHAR ( 1 ),
sy_table_name char(100),
sy_file long varchar,
sy_demo_yn char(1),
use_zz_yn char(1) );
INSERT sy_data_upload VALUES ( '1', 't1', 't1.txt', 'Y', 'Y' );
COMMIT;
BEGIN
declare cmdLoadZZ long varchar;
declare WriteLine long varchar;
declare Delimiter char(20);
declare cmdDeleteZZ long varchar;
declare TableName char(100);
declare DataFile long varchar;
declare DemoYN char(1);
declare Entity char(10);
declare zzTableYN char(1);
declare LogFile long varchar;
declare @loopcounter integer;
DECLARE cEntity cursor FOR
SELECT sy_table_name, sy_file, sy_demo_yn, use_zz_yn
FROM sy_data_upload
WHERE fd_entity = @Entity;
DECLARE @Entity VARCHAR ( 1 );
DECLARE @sqlcode INTEGER;
DECLARE @sqlstate VARCHAR ( 5 );
DECLARE @errormsg VARCHAR ( 32767 );
DECLARE @here VARCHAR ( 1 );
SET @here = '1';
SET @Entity = '1';
SET @loopcounter = 0;
SET LogFile = '';
SET Delimiter = ''; -- ' DELIMITED BY ' + Char(39) + '|' + Char(39);
OPEN cEntity WITH HOLD;
SET @here = '2';
FETCH cEntity INTO TableName, DataFile, DemoYN, zzTableYN;
WHILE SQLCode = 0 LOOP
SET @loopcounter = @loopcounter + 1;
SET cmdDeleteZZ = 'DELETE FROM zz_' + TableName + ' WHERE fd_entity = ' + Char(39) + @Entity + Char(39);
SET cmdLoadZZ = 'LOAD TABLE ' || 'zz_' || TableName || ' FROM ' || '''c:\\temp\\' || @Entity || '\\history\\' || DataFile || '''' || Delimiter;
SET LogFile = LogFile + str(@loopcounter) + ' ' + cmdDeleteZZ + ' ' + cmdLoadZZ;
SET @here = '3';
EXECUTE IMMEDIATE cmdDeleteZZ;
MESSAGE STRING(str(@loopcounter) + ' cmdDeleteZZ: ', cmdDeleteZZ, str(SQLSTATE)) TO CONSOLE;
SET @here = '4';
EXECUTE IMMEDIATE cmdLoadZZ;
MESSAGE STRING(str(@loopcounter) + ' cmdLoadZZ: ', cmdLoadZZ, str(SQLSTATE)) TO CONSOLE;
FETCH cEntity INTO TableName, DataFile, DemoYN, zzTableYN;
END LOOP ;
SET @here = '5';
Close cEntity;
SET @here = '6';
UNLOAD SELECT LogFile To 'c:/temp/loaddata_log.txt' ;
EXCEPTION
WHEN OTHERS THEN
SELECT SQLCODE, SQLSTATE, ERRORMSG()
INTO @sqlcode, @sqlstate, @errormsg;
MESSAGE STRING (
'EXCEPTION in BEGIN block at ',
CURRENT TIMESTAMP,
': @here = ', @here,
', SQLCODE = ', @sqlcode,
', SQLSTATE = ', @sqlstate,
', ERRORMSG() = ', @errormsg )
TO CONSOLE;
END;
SELECT * FROM zz_t1;
What is @Entity?
It is a parameter I am passing to the procedure. Different entities (our clients) could have different tables that need to be processed. I should point out that the first time through the loop, it works fine. The DELETE and LOAD TABLE commands are successful. The problem is that the loop just ends right then and there and no other fetches occur. Tom
@Tom: I hope you don't mind the rewrite of your title to match the question you're asking... you can change it back if you want, but EXECUTE IMMEDIATE was not directly implicated. ( Yes, it creeps me out editing other people's stuff :)