Geek asks and answers

Local variables in informix script

I need to make a big update to the script - not the SPL (stored procedure). This should be written for Informix db.

It includes inserting rows into several tables, each of which depends on the insertion sequence in the previous table.

I know that I can access the series by doing this:

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

but I can’t define a local variable to save it before pasting into the following 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 parses the definition string.

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

+3
source share
2 answers

, SPL , :

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

, , .

, concurrency, MAX(), , DBINFO('sessionid') 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');
...

Table3 TEMP:

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 (...);
+2

Informix " " , . , , :

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

, 2. , . , , :

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');

... 3 concurrency MAX().

0

More articles:


All Articles