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

Hi Vern,

I don't think this is a problem of reading the manual (I think both Alan and I 
do a lot of that) <g>

Unfortunately, CURRENT PATH is not what is used when you have an unqualifed 
table or view (Alans original problem) - it is CURRENT SCHEMA that is used 
which means you are restricted to one default library as opposed to a library 
list.

Compile the following program with OPTION *SYS and, with SQLSTAND in the 
library list, it display the number of rows and an SQLSTT of 00000.
Compile it with OPTION *SQL and, still with SQLSTAND in the library list, it 
displays 0 rows and an SQLSTT of 42704 (An undefined object or constraint name 
was detected).

DCount            S             10I 0           
C/Exec SQL                                      
C+ SET PATH = QSYS, QSYS2, SQLSTAND            
C/end-exec                                      
C/Exec SQL                                      
C+   select count(*) into :count from employee  
C/end-exec                                      
C     Count         Dsply                       
C     SQLSTT        Dsply                       
C                   Eval      *Inlr = *on       

Maybe I have not looked hard enough but I have to seen the difference between 
the use of PATH and SCHEMA "clearly" defined anywhere in the documentation :-)

Paul




----- Original Message ----- 
From: "Vernon Hamberg" <vhamberg@xxxxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Sent: Wednesday, July 27, 2005 1:59 PM
Subject: Re: Library List in SQL Stored Procedures - Was Re: (no subject)


> In the SQL Reference, chapter 2, are 2 sections, "Naming Conventions" & 
> "Schemas and the SQL Path". You can get to the manual at InfoCenter. These 
> supposedly explain everything need to answer this question. It is not 
> always clear, it seems, as evidenced by posts to this list. It also is not 
> obvious how specific situations are working. But it is the official 
> starting point.
> 
> There is also a special register - there are several, such as CURRENT DATE 
> - that contains the SQL path in force at the time. You can see it using
> 
> SELECT CURRENT PATH FROM SYSIBM/SYSDUMMY1
> 
> in STRSQL.
> 
>  From the explanation of CURRENT PATH:
> 
> The initial value of the CURRENT PATH special register in an activation 
> group is established by the first
> SQL statement that is executed.
> - If the first SQL statement in an activation group is executed from an SQL 
> program or SQL package and
> the SQLPATH parameter was specified on the CRTSQLxxx command, the path is 
> the value specified in
> the SQLPATH parameter. The SQLPATH value can also be specified using the 
> SET OPTION statement.
> - Otherwise,
>    - For SQL naming, QSYS, QSYS2, the value of the authorization ID of the 
> statement
>    - For system naming, *LIBL
> 
> There is much more - check it out!
> 
> The truth is out there!
> 
> Vern
> 
> At 05:46 AM 7/27/2005, you wrote:
> 
> >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;