Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / February 2006

Tip: Looking for answers? Try searching our database.

Passing variable parameters to stored procedures

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
anilcool@gmail.com - 10 Feb 2006 01:57 GMT
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 - 10 Feb 2006 03:40 GMT
> Hi all,
>
[quoted text clipped - 39 lines]
> 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.

Signature

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

--CELKO-- - 10 Feb 2006 19:18 GMT
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 - 14 Feb 2006 18:51 GMT
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 - 15 Feb 2006 07:25 GMT
> 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.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Serge Rielau - 15 Feb 2006 12:26 GMT
>>If your question is how to accept an unconstrained set of parameters,
>>the answer is db2 does not support that.
[quoted text clipped - 5 lines]
> 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.

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Brian Tkatch - 15 Feb 2006 16:28 GMT
>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 - 16 Feb 2006 05:03 GMT
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 - 16 Feb 2006 08:43 GMT
> 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.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Liu Liu - 16 Feb 2006 14:25 GMT
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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.