Thanks...I will have to rethink the way I'm invoking the stored procedure...
Thank you,
Antonio Mira
Application Developer - Mid-Ohio Division
Time Warner Cable
1015 Olentangy River Road - 2nd Floor
Columbus, OH 43212
http://www.timewarnercable.com
phone: 614 827 7949
-----Original Message-----
From: systemidotnet-bounces@xxxxxxxxxxxx [mailto:systemidotnet-bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
Sent: Thursday, July 09, 2009 5:13 PM
To: '.net use with the System i'
Subject: Re: [SystemiDotNet] Parameters to a stored procedure...
Classic problem and discussed before...
The IN clause in STATIC sql requires that you list individual variables as
elements of the list, i.e. ... AND CEITM IN (I_INEXPRESSION1,
I_INEXPRESSION2, I_INEXPRESSION3) AND...
You are instead specifying the entire list of variables in a single variable
and DB2 is treating it as 1 element list.
One solution is to resort to DYNAMIC sql. Then you can build your sql
statement as a string through concatenation and all would be well. This
doesn't perform as well and is a nuisance to do due to having to cast all of
the other variables to character strings, but definitely a viable option.
Another solution is to stick with STATIC sql and parse the list assuming
some sort of standardized tokenization is possible (i.e. comma separated
list of values). For example, define some maximum number of list values,
say 13. Then declare 13 local variables. When your parse the input string,
place each value in its own variable. If there are less than 13, simply
repeat one (i.e. last) value in the remaining variables. Then change your
sql to look like what I described at the offset, listing all 13 variables in
the IN list. Trick here is that you're allowed to repeat values in the IN
list since only equality test is performed. Redundant equality matches
don't matter.
Of course, third and easiest solution for a stored procedure developer is to
instead require all list elements passed in as separate parameters. This
would make it (much?) harder on the invoker though.
For what's it worth, I think I've read about ARRAY support for stored
procedures in 6.1 version of the DB2 for i, but can't recall the details off
the top of my head and am not sure if it would be helpful in this scenario.
It may be worth investigating if you're on 6.1 though.
Elvis
Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com
-----Original Message-----
Subject: [SystemiDotNet] Parameters to a stored procedure...
Hello,
I have created a stored procedure that receives several parameters. I'm
having problems with one of the parameters. The parameter that I'm
having problems with is a parameter that I want to use as part of an IN
expression. My sp looks like this...
CREATE PROCEDURE COHAMIRA.SP_EQTOT (
IN I_SITEID DECIMAL(3, 0) ,
IN I_FROMDATE DECIMAL(7, 0) ,
IN I_TODATE DECIMAL(7, 0) ,
IN I_EQUIPSTSCODE CHAR(3) ,
IN I_INEXPRESSION CHAR(130) )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC COHAMIRA.SP_EQTOT
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN
DECLARE C1 CURSOR FOR
SELECT CEITM , ITDSC1 , COUNT ( * ) COUNT FROM OHILIVFILE . CONVRPF
INNER JOIN
OHILIVFILE . ITMSTPF ON CENROV = ITNROV AND CEITM = ITPTNO
WHERE CENROV = I_SITEID AND CEITM IN I_INEXPRESSION AND
CESDT BETWEEN I_FROMDATE AND I_TODATE AND CESTC = I_EQUIPSTSCODE
GROUP BY CEITM , ITDSC1 ;
OPEN C1 ;
END ;
The problem I'm having is with the I_INEXPRESSION parameter. I'm
calling this procedure from a .net program. Before I call this
procedure, I build the parameter to look like that way it should, like
so:
('VALUE1','VALUE2','VALUE3')
For some reason, it doesn't like this and the sp returns an empty data
set. When I run it with just one value (that I hard-code), it runs
fine. I pass the value as a string, like so:
"AB2100" for example. I get a data set back. The problem comes up when
I attempt to use several values. How do I build this parameter so that
it effectively works correct in the IN expression?
Any help would be greatly appreciated...
Thank you,
Antonio Mira
midrange.com
