CODEWITHSUNDEEP
informix
Bernard
Bernard

Reputation: 2043

Local variables in an Informix script

I have to do a big update script - not an SPL (stored procedure). It's to be written for an Informix db.

It involves inserting rows into multiple tables, each of which relies on the serial of the insert into the previous table.

I know I can get access to the serial by doing this:

SELECT DISTINCT dbinfo('sqlca.sqlerrd1') FROM systables

but I can't seem to define a local variable to store this before the insert into the next table.

I want to do this:

insert into table1 (serial, data1, data2) values (0, 'newdata1', 'newdata2');
define serial1 as int;
let serial1 = SELECT DISTINCT dbinfo('sqlca.sqlerrd1') FROM systables;
insert into table2 (serial, data1, data2) values (0, serial1, 'newdata3');

But of course Informix chokes on the define line.

Is there a way to do this without having to create this as a stored procedure, run it once and then delete the procedure?

Upvotes: 4

Views: 6501

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 754490

Informix does not provide a mechanism outside of stored procedures for 'local variables' of the type you want. However, in the limited example you provide, this works:

CREATE TABLE Table1
(
    serial SERIAL(123) NOT NULL,
    data1  VARCHAR(32) NOT NULL,
    data2  VARCHAR(32) NOT NULL
);
CREATE TABLE Table2
(
    serial SERIAL      NOT NULL,
    data1  INTEGER     NOT NULL,
    data2  VARCHAR(32) NOT NULL
);

INSERT INTO Table1(Serial, Data1, Data2)
    VALUES(0, 'newdata1', 'newdata2');
INSERT INTO Table2(Serial, Data1, Data2)
    VALUES(0, DBINFO('sqlca.sqlerrd1'), 'newdata3');

SELECT * FROM Table1;

123   newdata1     newdata2

SELECT * FROM Table2;

1     123          newdata3

However, this works only because you need to insert one row into Table2. If you needed to insert more, the technique would not work well. You could, I suppose, use:

CREATE TEMP TABLE Table3
(
    value   INTEGER NOT NULL
);

INSERT INTO Table1(Serial, Data1, Data2)
    VALUES(0, 'newdata1', 'newdata2');
INSERT INTO Table3(Value)
    VALUES(DBINFO('sqlca.sqlerrd1'));
INSERT INTO Table2(Serial, Data1, Data2)
    VALUES(0, (SELECT MAX(value) FROM Table3), 'newdata3');
INSERT INTO Table2(Serial, Data1, Data2)
    VALUES(0, (SELECT MAX(value) FROM Table3), 'newdata4');

And so on...the temporary table for Table3 avoids problems with concurrency and MAX().

Upvotes: 0

RET
RET

Reputation: 9188

If the number of columns in the tables involved is as few as your example, then you could make the SPL permanent, and use it to insert your data, ie:

EXECUTE PROCEDURE insert_related_tables('newdata1','newdata2','newdata3');

Obviously that doesn't scale terribly well, but is OK for your example.

Another thought that expands on Jonathan's example and solves any concurrency issues that might arise from the use of MAX() would be to include DBINFO('sessionid') in Table3:

DELETE FROM Table3 WHERE sessionid = DBINFO('sessionid');
INSERT INTO Table1 (...);
INSERT INTO Table3 (sessionid, value)
  VALUES (DBINFO('sessionid'), DBINFO('sqlca.sqlerrd1'));
INSERT INTO Table2 
  VALUES (0, (SELECT value FROM Table3
              WHERE sessionid = DBINFO('sessionid'), 'newdata3');
...

You could also make Table3 a TEMP table:

INSERT INTO Table1 (...);
SELECT DISTINCT DBINFO('sqlca.sqlerrd1') AS serial_value
  FROM some_dummy_table_like_systables
INTO TEMP Table3 WITH NO LOG;
INSERT INTO Table2 (...);

Upvotes: 3

Related Questions