RE: SQL stored procedure with list

["hotmail" <craigjacobsen@xxxxxxxxxxx>]

Charles,

It didn't like concat.  
The stored proc got created but during testing (I'm on v5r4) I kept getting
some numeric error.
I changed it to use || instead of concat and it worked fine.  
(The .net programmer will :) when he comes in)

Thank you again,

Craig 

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Monday, April 12, 2010 1:18 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL stored procedure with list

Sorry, the i doesn't like the literal on the prepare.  This works:

CREATE PROCEDURE GetItemNot
(in NotInclude varchar(800))
RESULT SETS 1
LANGUAGE SQL
BEGIN
declare stmt varchar(1000);
DECLARE c1 CURSOR with hold with return to client for S1; set stmt = 'SELECT
imdsc,impn,pscmrn,imlots,psqtyn,cost1,cost2,cost3,cost4,cost5,cost6,
cost7
 FROM PNOTEBOOK/NBKSUM
  WHERE impn NOT IN (' concat NotInclude concat ')'; prepare S1 from stmt;
OPEN c1; SET RESULT SETS CURSOR c1; END

HTH,
Charles

On Mon, Apr 12, 2010 at 1:03 PM, hotmail <craigjacobsen@xxxxxxxxxxx> wrote:
> Charles,
>
> Thank you.  I had a feeling it was through a prepared statement
>
> I am having trouble with the S1 and select:
>
> Token 'SELECT was not valid. Valid tokens: : <IDENTIFIER> <PLI_STRIN
>
> CREATE PROCEDURE GetItemNot
> (in NotInclude varchar(800))
> RESULT SETS 1
> LANGUAGE SQL
> BEGIN
> DECLARE c1 CURSOR with hold with return to client for S1; prepare S1 
> from 'SELECT 
> imdsc,impn,pscmrn,imlots,psqtyn,cost1,cost2,cost3,cost4,cost5,cost6,
> cost7
>  FROM PNOTEBOOK/NBKSUM
>   WHERE impn NOT IN (' + NotInclude +')'; OPEN c1; SET RESULT SETS 
> CURSOR c1; END
>
>
> Craig
>
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
> Sent: Monday, April 12, 2010 12:35 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: SQL stored procedure with list
>
> The same way he's doing it in MS SQL server, by using a dynamic 
> statement instead of the static statement you currently have.
>
> declare C1 cursor
>      with hold
>      with return to client
>      for S1;
>
> prepare S1 from 'SELECT
> imdsc,impn,pscmrn,imlots,psqtyn,cost1,cost2,cost3,cost4,cost5,cost6,co
> st7 FROM PNOTEBOOK.NBKSUM WHERE impn NOT IN ' + '(' + NotInclude + 
> ')';
>
> open C1;
>
> Note that if NotInclude's value is from user input, then you will have 
> a huge security hold in either MS SQL Server or DB2 on the i.  Google 
> "SQL Injection" for more info.
>
> HTH,
> Charles
>
> On Mon, Apr 12, 2010 at 12:03 PM, hotmail <craigjacobsen@xxxxxxxxxxx>
wrote:
> > I am trying to create an SQL stored procedure for a .net programmer 
> > that may pass a list in one parameter.
> > It works if I only pass in 1 item part number.  The .net programmer 
> > said he had it working in sequel server and he could pass
item1,item2,etc.
> > Here is what he gave me from the sequel server:
> >
> > CREATE PROCEDURE some_name
> >
> > @productNumberString  VARCHAR(750)='''000000'''
> >
> > AS
> >
> >            DECLARE @select        VARCHAR(150)
> >
> >            DECLARE @where        VARCHAR(800)
> >
> >            SET @select = 'SELECT
> > imdsc,impn,pscmrn,imlots,psqtyn,cost1,cost2,cost3,cost4,cost5,cost6,c
> > o
> > st7 FROM PNOTEBOOK.NBKSUM WHERE impn NOT IN '
> >
> >            SET @where = '(' + @productNumberString + ')'
> >
> >            EXEC(@select+@where)
> >
> > GO
> >
> > This is what I created.  I can't figure out how to pass this as one 
> > string for a DB2 stored proc.
> >
> > CREATE PROCEDURE GetItemNot
> > (in NotInclude varchar(800))
> > RESULT SETS 1
> > LANGUAGE SQL
> > BEGIN
> > DECLARE c1 CURSOR FOR   SELECT
> > imdsc,impn,pscmrn,imlots,psqtyn,cost1,cost2,cost3,cost4,cost5,cost6,
> > cost7
> >  FROM PNOTEBOOK/NBKSUM
> >   WHERE impn NOT IN (NotInclude);
> > OPEN c1;
> > SET RESULT SETS CURSOR c1;
> > END
> >
> > Thank you,
> >
> > Craig
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) 
> > mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To 
> > subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take 
> > a moment to review the archives at 
> > http://archive.midrange.com/midrange-l.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, 
> unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take 
> a moment to review the archives at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, 
> unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take 
> a moment to review the archives at 
> http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.