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

No offense meant, and I hope none taken. My hoped-for light tone did not come 
through the ether.

I had seen only the reference to a stored procedure, not that files referenced 
inside the procedure were not found - sorry for my sloppy tech support chops!
-------------- Original message -------------- 

> 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) 
> 
> 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" 
> To: "Midrange Systems Technical Discussion" 
> 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" 
> > >To: 
> > >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; 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
>