RE: SQL Procedure - Set Schema ignored?

["Needles,Stephen J" <SNEEDLES@xxxxxxxxxxxxx>]

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;