Re: SQL Procedure - Set Schema ignored?

Hi Matt,

Not sure if this is of assistance but I had come across something similar where a CL program was being called to change a library list (as opposed to using a SET SCHEMA). It would work on the first call but all subsequent calls would used the original library list.

It was corrected by PTF SI35332

Regards

Paul Tuohy
ComCon
www.comconadvisor.com www.systemideveloper.com




MattLavinder@xxxxxxxxxxxxxxxxxxx wrote:

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;

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
______________________________________________________________________
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l..



==============================================================================

This communication, including attachments, is confidential, may be subject
to legal privileges, and is intended for the sole use of the addressee. Any
use, duplication, disclosure or dissemination of this communication, other
than by the addressee, is prohibited. If you have received this
communication in error, please notify the sender immediately and delete or
destroy this communication and all copies.

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l..


______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
______________________________________________________________________



______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
______________________________________________________________________