Passing variable parameters to stored procedures

[anilcool]

Hi all,

Another novice question.

I want to insert multiple records into my DB2 database using stored
procedure but I do not know how many I would like to insert at any
given time. Each record has 6 columns (fields).

Here's an example of a stored procedure that accepts 6 parameters and
inserts one record. I would like to modify this stored procedure such
that it accepts 6, 12 or 18 or more parameters and inserts 1, 2 3 ..or
more rows into the DB - depending on how many parameters it receives.
So, as an example I want to be able to execute something like:

- Call db2.sp_add_log ("1", "2", "3", "4", "5",
"6", "7", "8", "9", "10","11", "12" ) and
have this call insert 2 records into the DB.

At other times I might want to pass 60 parameters and insert maybe 10
records at a time. Is there a way to decide at run-time the number of
parameters I want to pass to a stored procedure.

=============================================
create PROCEDURE db2.sp_add_log
(
IN PLAYEROID_in CHAR(36),
IN PLAYTIME_in CHAR(23),
IN CONTENTNAME_in VARCHAR(1024),
IN CONTENTOID_in CHAR(36),
IN REASON_in VARCHAR(256),
IN DATAFIELD_in VARCHAR(256)
)
LANGUAGE SQL
BEGIN
insert into DB2.PLAYLOGS
(PLAYEROID,
PLAYTIME,CONTENTNAME,CONTENTOID,REASON, DATAFIELD) values
(PLAYEROID_in, PLAYTIME_in, CONTENTNAME_in,
CONTENTOID_in, REASON_in, DATAFIELD_in);

END
==============================================

Thanks a lot.

-Anil

[Serge Rielau]

an******@gmail.com wrote:

Hi all,

Another novice question.

I want to insert multiple records into my DB2 database using stored
procedure but I do not know how many I would like to insert at any
given time. Each record has 6 columns (fields).

Here's an example of a stored procedure that accepts 6 parameters and
inserts one record. I would like to modify this stored procedure such
that it accepts 6, 12 or 18 or more parameters and inserts 1, 2 3 ..or
more rows into the DB - depending on how many parameters it receives.
So, as an example I want to be able to execute something like:

- Call db2.sp_add_log ("1", "2", "3", "4", "5",
"6", "7", "8", "9", "10","11", "12" ) and
have this call insert 2 records into the DB.

At other times I might want to pass 60 parameters and insert maybe 10
records at a time. Is there a way to decide at run-time the number of
parameters I want to pass to a stored procedure.

=============================================
create PROCEDURE db2.sp_add_log
(
IN PLAYEROID_in CHAR(36),
IN PLAYTIME_in CHAR(23),
IN CONTENTNAME_in VARCHAR(1024),
IN CONTENTOID_in CHAR(36),
IN REASON_in VARCHAR(256),
IN DATAFIELD_in VARCHAR(256)
)
LANGUAGE SQL
BEGIN
insert into DB2.PLAYLOGS
(PLAYEROID,
PLAYTIME,CONTENTNAME,CONTENTOID,REASON, DATAFIELD) values
(PLAYEROID_in, PLAYTIME_in, CONTENTNAME_in,
CONTENTOID_in, REASON_in, DATAFIELD_in);

END
==============================================

Well, they say if all you have is a hammer everything looks like a nail.
Try the same trick again.
Since INSERT is not a cursor you can use EXECUTE IMMEDIATE txt
DB2 supports INSERT INTO T VALUES (....), (.....)
for multi row insert.
Oh and remember ".." is for identifiers, '...' is for strings.

Cheers
Serge

PS: Gluing together statements on the fly and compiling them one of is
not DB2's preferred mode of operation.. It eats CPU, but it gets the job
done. Good learning experience any day.

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[--CELKO--]

Why do you have OIDs in SQL? Why huge column sizes that invite bad
data? Why did you violate ISO-11179 naming rules? How usable and
descriptive do you think that "data_field" is as a name? Do you know
that a column and field are nothing alike? What is "reason"? A better
name would "vague_reason" for the guy who has to maintain it :) Didyou
pick VARCHAR(256) after actuall research, or is it a "magic number"
that was easy write on the fly?

I do dnot know what DB2's limit on parameters is, but you can fake an
array in SQL/PSM, soemthing like this:
CREATE PROCEDURE AddLog
(IN player_oid_1 CHAR(36), -- OIDs in SQL?
IN play_time_1 CHAR(23), -- temporal data in string?
IN content_name_1 VARCHAR(1024), -- really that big?
IN content_oid_1 CHAR(36), -- OIDs in SQL?
IN vague_reason_1 VARCHAR(256), -- for what??
IN data_field_1 VARCHAR(256), -- fields in SQL? what kind of data

IN player_oid_2 CHAR(36),
IN play_time_2 CHAR(23),
IN content_name_2 VARCHAR(1024),
IN content_oid_2 CHAR(36),
IN vague_reason_2 VARCHAR(256),
IN data_field_2 VARCHAR(256),

etc.

IN player_oid_6 CHAR(36),
IN play_time_6 CHAR(23),
IN content_name_6 VARCHAR(1024),
IN content_oid_6 CHAR(36),
IN vague_reason_6 VARCHAR(256),
IN data_field_6 VARCHAR(256),

)
LANGUAGE SQL
BEGIN

INSERT INTO PlayLogs (player_oid, play_time, content_name,
content_oid, reason, data_field)
VALUES (player_oid_1, play_time_1, content_name_1, content_oid_1,
vague_reason_1, data_field_1);

--skip the insert if the data is missing
IF player_oid_2 IS NOT NULL
THEN
INSERT INTO PlayLogs (player_oid, play_time, content_name,
content_oid, reason, data_field)
VALUES (player_oid_2, play_time_2, content_name_2, content_oid_2,
vague_reason_2, data_field_2);
END IF;

etc.

IF player_oid_6 IS NOT NULL
THEN
INSERT INTO PlayLogs (player_oid, play_time, content_name,
content_oid, reason, data_field)
VALUES (player_oid_6, play_time_6, content_name_6, content_oid_6,
vague_reason_6, data_field_6);
END IF;

END;

The right answer is to split the data in the host program and make
calls the stored procedure, but you knew that from basic Software
Engineering and how a teired architecture works.

[Brian Tkatch]

If your question is how to accept an unconstrained set of parameters,
the answer is db2 does not support that. However, db2 does allow
PROCEDURE overloading. That is, while the PROCEDURE must have a name,
more than one procedure with the same name (but not the same SPECIFIC
name) can be CREATEd, and each can be given a different set of
parameters. With that, you can CREATE a number of PROCEDUREs, each one
for each multiple of 6.

Ideally, however, this is not the best solution, and the PROCEDURE
should just INSERT one record at a time, and have it called one after
the other.

B.

[Knut Stolze]

Brian Tkatch wrote:

If your question is how to accept an unconstrained set of parameters,
the answer is db2 does not support that.

Actually, it does. You could use DB2DARI style procedures (must be written
in C/C++). There you get a SQLDA as input structure and the SQLDA will
contain all the parameters that were provided to the procedure.

However, using DB2DARI procedures is discouraged. And I would say that
there is always an easy work-around, for example by employing temp tables.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Serge Rielau]

Knut Stolze wrote:

Brian Tkatch wrote:

If your question is how to accept an unconstrained set of parameters,
the answer is db2 does not support that.

Actually, it does. You could use DB2DARI style procedures (must be written
in C/C++). There you get a SQLDA as input structure and the SQLDA will
contain all the parameters that were provided to the procedure.

However, using DB2DARI procedures is discouraged. And I would say that
there is always an easy work-around, for example by employing temp tables.

The word you were looking for is DEPRECATED.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Brian Tkatch]

>Actually, it does. You could use DB2DARI style procedures (must be written

in C/C++). There you get a SQLDA as input structure and the SQLDA will
contain all the parameters that were provided to the procedure.

Ooh, i didn't know that. I guess that's because i do all the routines
here in SQL.

B.

[W Gemini]

I don't think you can even do that anymore. Cataloged dari style stored
procedure still need to define all its parameters at creation time or
the compiler simply can't do procedure resolution (sqlcode -440). You
can, however, call the stored procedure uncataloged. As Serge pointed
out, that functionality has been deprecated and sqleproc itself was
removed from the application development header file.

[Knut Stolze]

W Gemini wrote:

I don't think you can even do that anymore. Cataloged dari style stored
procedure still need to define all its parameters at creation time or
the compiler simply can't do procedure resolution (sqlcode -440). You
can, however, call the stored procedure uncataloged. As Serge pointed
out, that functionality has been deprecated and sqleproc itself was
removed from the application development header file.

I fully agree. We are talking about things shouldn't be used any longer.
Related to that, you won't find this documented in the V8 manuals any
longer (only up to V7).

Sometimes, however, it is still necessary to call an uncataloged procedure
(see the Spatial Extender's "st_enable_db"). And there you have an
arbitrary number of parameters as you point out. The procedure has to be
invoked with the "call library!function" notion.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Liu Liu]

That's good point. I would really push for a formal support of variable
number of parameters with IBM though if there's an requirement. I
personally think it will be a useful feature at times.