Re: SQL stored procedure with list

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.


--
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.