Re: Library List in SQL Stored Procedures - Was Re: (no subject)

Hi Alan,

My apologies - I did not mean to imply that the problem would be fixed by
changing the naming convention.

I don't think "true" SQL supports the concept of a library list for
unqualified tables or views. You can only have one default library which you
specify with SET SCHEMA. SQL does support the concept of a library list for
unqualifed stored procedures or UDFs which you specify with SET PATH (but
this does not apply to tables or views).

Interactive SQL (STRSQL), Run SQL Scripts, embedded SQL and RUNSQLSTM will
use the library list if you specify a naming convention of *SYS. You can
test this in STRSQL by using F13 to change session attributes (no point in
doing it on the STRSQL command if it is retrieving an existing session).

For RUNSQLSTM I tried the following simple test. I have a source with

UPDATE EMPLOYEE SET COMM = COMM + 0 WHERE EMPNO = '000010';

When I RUNSQLSTM with a naming convention of *SYS it runs just fine and the
spool file has the messages

 SQL7957   0       1  Position 1 1 rows updated in EMPLOYEE in SQLSTAND.
 SQL7960   0          Commit completed.

When I RUNSQLSTM with a naming convention of *SQL it does not run and the
spool file has the messages

 SQL0204  30       1  Position 1 EMPLOYEE in TUOHYP type *FILE not found.
 SQL7961   0          Rollback completed.

Must have been a different error you were getting on the RUNSQLSTM. BTW,
RUNSQLSTM does not support SET or DECLARE statements.

I think you are hosed when you are using "true" SQL in that you are confined
to one schema or you must use qualified names for tables and views.

HTH

Paul




----- Original Message -----
From: "Alan Campin" <Alan.Campin@xxxxxxx>
To: <midrange-l@xxxxxxxxxxxx>
Sent: Tuesday, July 26, 2005 11:05 PM
Subject: (no subject)


> Using RUNSQLSTM and using option *SYS for naming convention. Makes no
difference. Tried to use *SQL but same thing.
> >> Hi Alan,
>
> >> My understanding is that PATH is applied when calling an unquailified
> >> procedure or UDF. You need to specify a SCHEMA for unquailifed tables
or
> >> views - which, of course, restricts you to one default library.
>
> >> The library list is only used if you are using a system naming
convention
> >> (as on STRSQL, JDBC connection, pre-compiler options) but I don't think
you
> >> have that option when you are creating a true SQL procedure.
>
> >> HTH
>
> >> Paul
>
> >> ----- Original Message -----
> >>From: "Alan Campin" Alan.Campin@xxxxxxx>
> >>To: midrange-l@xxxxxxxxxxxx>
> >> Sent: Monday, July 25, 2005 9:59 PM
> >> Subject: Library List in SQL Stored Procedures
>
>
> > I am trying to write a SQL Stored Procedure but am having no luck
getting
> to work. I have written them before and this smells like another complier
> bug.
> >
> > Each time that I try to run this stored procedure, I keep getting a
> message saying that everyone of the tables is not found in QGPL if I use
> QGPL as the default collection and my library MP1CAMPIA if I do not
specify
> a default collection. All the libraries are on the library list as well as
> being added to Set Path statement but it keeps trying to find them in the
> default collection. What gives? This is driving me crazy. If you don't
> specify a collection, doesn't it use the library list?
> >
> > Anyway thanks.
> >
> >     SET DTA_LIBRARY = 'A1' CONCAT INCOMPANYCODE CONCAT 'DTA' ;
> >     SET AMO_LIBRARY = 'A1AMODTA';
> >
> >     SET PATH DTA_LIBRARY, AMO_LIBRARY;
> >
> >     SET STMT = 'CRTDUPOBJ OBJ(QAQQINI) FROMLIB(MP1CAMPIA)
> >                   OBJTYPE(*FILE) TOLIB(QTEMP) DATA(*YES) ' ;
> >     SET STMTLENGTH = LENGTH ( STMT ) ;
> >     CALL QSYS/QCMDEXC ( STMT , STMTLENGTH ) ;
> >
> >     Update QTEMP/QAQQINI
> >        SET QQVAL = '*YES' WHERE QQPARM = 'FORCE_JOIN_ORDER' ;
> >
> >     SET STMT = 'CHGQRYA QRYOPTLIB(QTEMP)' ;
> >     SET STMTLENGTH = LENGTH ( STMT ) ;
> >     CALL QSYS/QCMDEXC ( STMT , STMTLENGTH ) ;
> >
> >     Sel: Begin
> >       Declare c1 Cursor With Return For
> >         Select RFPAYC,
> >                RFRFNN,
> >                RFDATE,
> >                RFAMT,
> >                CMPCMPY,
> >                CMPNAME
> >         From OPMRFPF1
> >           Inner Join OPMHDRF1
> >             On RFORDN = HDORDN
> >           Inner JOIN MFMDIVF1
> >             On HDODIV = DIVDIV
> >           Inner JOIN MFMCMPF1
> >             On DIVCMPY = CMPCMPY
> >         Where RFRFNN <> 0 And
> >               RFDATE Between InFromDate and InToDate
> >         Order By CMPCMPY;
> >         Open c1;
> >       End Sel;