INSERTIN' INTO TEMPORAL TABLE THE RESULT OF A STORE PROCEDURE

[gacuna]

i want to insert into a temporal table the result of a store procedure.
on sql server the sentence would look like this (already working)

INSERT INTO #SHIPINFO
exec TESTDTA.S59RSH05 @SCBILLTO, @INID, @ADRSTYPE

i tried to do the same on DB2, meaning

INSERT INTO SESSION.SHIPINFO
CALL TESTDTA.S59RSH05 v_SCBILLTO, v_INID, v_ADRSTYPE

but it doesn't work.i have been trying to do this for a couple of days
but i can't and it kinda urgent :-p
does anybody knows hot to do this?

[Serge Rielau]

ga****@gmail.com wrote:

i want to insert into a temporal table the result of a store procedure.
on sql server the sentence would look like this (already working)

INSERT INTO #SHIPINFO
exec TESTDTA.S59RSH05 @SCBILLTO, @INID, @ADRSTYPE

i tried to do the same on DB2, meaning

INSERT INTO SESSION.SHIPINFO
CALL TESTDTA.S59RSH05 v_SCBILLTO, v_INID, v_ADRSTYPE

Well, that's illegal SQL. It was two days ago and it still is ;-)
How does your procedure look like? Before trying to do it the hard way
let's see if it could be written as a table function. E.g.:

CREATE FUNCTION FOO(arg INT) RETURNS TABLE(c1 INT, c2 INT)
RETURN SELECT 1, 2 FROM SYSIBM.SYSDUMMY1;

CREATE TABLE T AS (SELECT * FROM TABLE(FOO(1, 1)) AS F) DEFINITION ONLY;
INSERT INTO T SELECT * FROM TABLE(FOO(1, 1)) AS F;

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[JohnO]

Serge Rielau wrote:

ga****@gmail.com wrote:

i want to insert into a temporal table the result of a store procedure.
on sql server the sentence would look like this (already working)

INSERT INTO #SHIPINFO
exec TESTDTA.S59RSH05 @SCBILLTO, @INID, @ADRSTYPE

i tried to do the same on DB2, meaning

INSERT INTO SESSION.SHIPINFO
CALL TESTDTA.S59RSH05 v_SCBILLTO, v_INID, v_ADRSTYPE

Well, that's illegal SQL. It was two days ago and it still is ;-)
How does your procedure look like? Before trying to do it the hard way
let's see if it could be written as a table function. E.g.:

CREATE FUNCTION FOO(arg INT) RETURNS TABLE(c1 INT, c2 INT)
RETURN SELECT 1, 2 FROM SYSIBM.SYSDUMMY1;

CREATE TABLE T AS (SELECT * FROM TABLE(FOO(1, 1)) AS F) DEFINITION ONLY;
INSERT INTO T SELECT * FROM TABLE(FOO(1, 1)) AS F;

Cheers
Serge

Serge, why the two step insert? Why not just
CREATE TABLE T AS (SELECT * FROM TABLE(FOO(1, 1)) AS F) WITH DATA;

[Serge Rielau]

JohnO wrote:

Serge, why the two step insert? Why not just
CREATE TABLE T AS (SELECT * FROM TABLE(FOO(1, 1)) AS F) WITH DATA;

I for the life of it can't find WITH DATA in the syntax diagram...
Seriously - what's so bad about separating declaration of the table from
filling it? If DB2 were to support WITH DATA it would save you nothing
but parsing the SELECT twice. A teeny, tiny bit of CPU cost, since the
CREATE TABLE will NOT execute the SELECT. It merely derives the data types.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[JohnO]

Serge Rielau wrote:

JohnO wrote:

Serge, why the two step insert? Why not just
CREATE TABLE T AS (SELECT * FROM TABLE(FOO(1, 1)) AS F) WITH DATA;

I for the life of it can't find WITH DATA in the syntax diagram...
Seriously - what's so bad about separating declaration of the table from
filling it? If DB2 were to support WITH DATA it would save you nothing
but parsing the SELECT twice. A teeny, tiny bit of CPU cost, since the
CREATE TABLE will NOT execute the SELECT. It merely derives the data types.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

Hi Serge, can't tell if you are just kidding me but... "WITH DATA" is
in the Syntax diagram for CREATE TABLE in my SQL manual "DB2 UDB for
iSeries SQL Reference V5R3" on page 568.

I *really* like succinct code, and *hate* seeing 2 lines of code when
one will do. For a start, it is less code to maintain; if the table
name is changed, you only have to change it in one place. If the
parameters change, they only change in one place etc. Most of all, it
is just more elegant - to my eye anyway.

Cheers,
JohnO

[Serge Rielau]

JohnO wrote:

Serge Rielau wrote:

JohnO wrote:

Serge, why the two step insert? Why not just
CREATE TABLE T AS (SELECT * FROM TABLE(FOO(1, 1)) AS F) WITH DATA;

I for the life of it can't find WITH DATA in the syntax diagram...
Seriously - what's so bad about separating declaration of the table from
filling it? If DB2 were to support WITH DATA it would save you nothing
but parsing the SELECT twice. A teeny, tiny bit of CPU cost, since the
CREATE TABLE will NOT execute the SELECT. It merely derives the data types.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

Hi Serge, can't tell if you are just kidding me but... "WITH DATA" is
in the Syntax diagram for CREATE TABLE in my SQL manual "DB2 UDB for
iSeries SQL Reference V5R3" on page 568.

I *really* like succinct code, and *hate* seeing 2 lines of code when
one will do. For a start, it is less code to maintain; if the table
name is changed, you only have to change it in one place. If the
parameters change, they only change in one place etc. Most of all, it
is just more elegant - to my eye anyway.

I agree on elegance. Chances that the OP is using DB2 for iSeries are
rather slim though. I didn't know that iSeries has stepped up to WITH
DATA. Good to know :-)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[JohnO]

Serge Rielau wrote:

JohnO wrote:

Serge Rielau wrote:

JohnO wrote:
Serge, why the two step insert? Why not just
CREATE TABLE T AS (SELECT * FROM TABLE(FOO(1, 1)) AS F) WITH DATA;
I for the life of it can't find WITH DATA in the syntax diagram...
Seriously - what's so bad about separating declaration of the table from
filling it? If DB2 were to support WITH DATA it would save you nothing
but parsing the SELECT twice. A teeny, tiny bit of CPU cost, since the
CREATE TABLE will NOT execute the SELECT. It merely derives the data types.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

Hi Serge, can't tell if you are just kidding me but... "WITH DATA" is
in the Syntax diagram for CREATE TABLE in my SQL manual "DB2 UDB for
iSeries SQL Reference V5R3" on page 568.

I *really* like succinct code, and *hate* seeing 2 lines of code when
one will do. For a start, it is less code to maintain; if the table
name is changed, you only have to change it in one place. If the
parameters change, they only change in one place etc. Most of all, it
is just more elegant - to my eye anyway.

I agree on elegance. Chances that the OP is using DB2 for iSeries are
rather slim though. I didn't know that iSeries has stepped up to WITH
DATA. Good to know :-)

Cheers
Serge

Indeed. I just happened to notice it appeared after we upgraded to V5R3!

[gacuna]

the thing is. i have this store procedure (X) wich returns some data.

i have a new store procedure (Y) in wich i need to fill a tepmoral
table with the result of the first procedure (X)

i found something like this

DECLARE result1 RESULT_SET_LOCATOR VARYING;
CALL targetProcedure();
ASSOCIATE RESULT SET LOCATORS(result1, result2, result3)
WITH PROCEDURE targetProcedure;

ALLOCATE rsCur CURSOR FOR RESULT SET result1;

FETCH rsCur INTO ...

but when i try to run the code it says

"oken VARYING was not valid. Valid tokens: NO CURSOR SCROLL. Cause . .
.. . . : A syntax error was detected at token VARYING. Token VARYING
is not a valid token. A partial list of valid tokens is NO CURSOR
SCROLL. This list assumes that the statement is correct up to the
token. The error may be earlier in the statement, but the syntax of
the statement appears to be valid up to this point. Recovery . . . :
Do one or more of the following and try the request again: -- Verify
the SQL statement in the area of the token VARYING. Correct the
statement. The error could be a missing comma or quotation mark, it
could be a misspelled word, or it could be related to the order of
clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL
statement because it does not end with a valid clause."
now it looks something like this
CREATE PROCEDURE SCDV810.S42ROD01

(

)

DYNAMIC RESULT SETS 4

LANGUAGE SQL

MODIFIES SQL DATA

BEGIN

DECLARE procresult RESULT_SET_LOCATOR VARYING;
-- TEMPORAL TABLE FOR THE SHIPTO'S

DECLARE GLOBAL TEMPORARY TABLE SESSION.SHIPINFO

(

ShipToNumber DECIMAL,

CCType DECIMAL,

ALPHNAME CHAR(100),

MLNAM CHAR(100),

ADDL1 CHAR(100),

ADDL2 CHAR(100),

ADDL3 CHAR(100),

ADDL4 CHAR(100),

CITY CHAR(100),

STATE CHAR(100),

ZIPCD CHAR(100),

CNTRY CHAR(100),

SHICTCTID DECIMAL,

FIRSTNAME CHAR(100),

MDLNAME CHAR(100),

LSTNAME CHAR(100),

NKNAME CHAR(100),

ADRSFLAG CHAR(100),

CNYNAME CHAR(100),

PHNNUMBER CHAR(100)

) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;


CALL TESTDTA.S59RSH05();
ASSOCIATE LOCATORS (loc) WITH PROCEDURE TESTDTA.S59RSH05;
ALLOCATE temp_cursor CURSOR FOR RESULT SET loc;

ETC ETC ETC
END;

but it gave me the error that y showed you before..

THANKS

[Serge Rielau]

Which platform and version of DB2 are you on?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/