RE: SQL Procedure - Set Schema ignored?

It is a stored procedure for a PHP app to use. When you writing a stored
procedure that returns data, this is one way to do it. Saw an article
about it years ago, and I use this method it several places and it works
fine. The issue is that I am not getting the data from the correct schema.
It is returning data though.


|------------>
| From: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|"Needles,Stephen J" <SNEEDLES@xxxxxxxxxxxxx> |
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| To: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|"RPG programming on the IBM i / System i" <rpg400-l@xxxxxxxxxxxx> |
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Date: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|02/04/2010 05:49 PM |
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Subject: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|RE: SQL Procedure - Set Schema ignored? |
>--------------------------------------------------------------------------------------------------------------------------------------------------|





Is this all of the code? All the declare/open combination does is setup an
access path to the data. Now you must FETCH it into the program in order
to process it.

What is your end-game here?

Steve Needles
Northland Insurance
(651) 310-4203


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of MattLavinder@xxxxxxxxxxxxxxxxxxx
Sent: Thursday, February 04, 2010 3:57 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQL Procedure - Set Schema ignored?



Not sure where the best group is for this, but it is embedded SQL in an RPG
program, so I am putting it here.

Trying to write an external stored procedure to retrieve data. First
parameter of the procedure is a customer identifier. Based on that
parameter, I use exec sql SET SCHEMA :custlib; to set the library to use,
Then I declare my cursor and proceed with the SQL Statement. Problem is,
it doesn't work. I have verified the custlib is correct, and even verified
the schema changed (see below). Regardless of what is written to my log,
it still seems to use the default schema when it runs the statement. Are
there options I need to change when I compile the RPG program, or is this
something that can't be done?

exec sql SET SCHEMA :custlib;

exec sql VALUES(CURRENT SCHEMA) INTO :CURSCHEMA;

WriteLog('CRMTASKLD3':curSchema);

exec sql DECLARE c1 CURSOR FOR
SELECT t.*, ifnull(typdesc,'') typdesc,.
ifnull(catdesc,'') catdesc, ifnull(dispdsc,'') dispdsc
FROM crmtask t
LEFT JOIN dmilibr.crmtypes st on t.tsktype = st.type
LEFT JOIN crmcats ut
on t.tskusrtype = ut.category
LEFT JOIN crmdisp ud
on t.tskdisp = ud.dispid
WHERE tskpgm = :pTskPgm
and tskpgmid = :pTskPgmid;

exec sql OPEN c1;