[Réso­lu] Com­ment fait-on pour appe­ler une pro­cé­dure sto­ckée dans liste de bibliothèque ?

Bon­jour, 

Voi­ci un lien où tout est expliqué :

http://rpgdeveloper.altervista.org/joomla/articoli-faq-as-400/100-use-libl-for-stored-procedures-and-udfs

Et une copie au cas où le lien ne serait plus valide :

Q : Can an exter­nal user-defi­ned func­tion (UDF) or sto­red pro­ce­dure be cal­led using the libra­ry list ?

Which Thing to Find ?

Most data­base mana­ge­ment soft­ware pro­vides the abi­li­ty to call rou­tines in SQL, inclu­ding both sto­red pro­ce­dures and user-defi­ned func­tions. But DB2 for i pro­vides « exter­nal » pro­ce­dures and func­tions, which is to say that the rou­tines don’t have to be writ­ten in SQL ; they can call an exter­nal pro­gram or ser­vice pro­gram writ­ten in ano­ther lan­guage. On IBM i, we often have busi­ness logic writ­ten in RPG, and we want to make it avai­lable to call from SQL because that exposes that logic to any pro­gram that can run an SQL statement.

When an SQL sta­te­ment calls an exter­nal SQL rou­tine, there are two steps involved :

1. fin­ding the procedure/function defi­ni­tion in the SQL catalogs
2. fin­ding the exter­nal object (*PGM or *SRVPGM) to be called

Start by asking your­self which of these (or both) should be loca­ted using the libra­ry list.

The Effect of the Naming Convention

DB2 for i sup­ports two dif­ferent naming conventions.

1. SQL Naming : objects are refe­ren­ced as LIBRARY.OBJECT
2. Sys­tem Naming : objects are refe­ren­ced as LIBRARY/OBJECT

The naming conven­tion applies to all objects you use in an SQL sta­te­ment. This includes conven­tio­nal file-type objects (tables, views, indexes) as well as rou­tines (sto­red pro­ce­dures and UDFs).

When you’re cal­ling a rou­tine, the naming affects more than just whe­ther you use a dot or a slash. It also affects whe­ther you use the libra­ry list (sys­tem naming) or the SQL PATH variable (SQL naming). If you’re using SQL naming, you can’t use the libra­ry list. The­re­fore, you must use sys­tem naming to use the libra­ry list.

Fin­ding the SQL Defi­ni­tion by Libra­ry List

Ima­gine a sce­na­rio in which you have two libra­ries, one named LIBPROD for pro­duc­tion pro­grams, and one named LIBTEST for tes­ting pro­grams. Users control which libra­ry they use by set­ting their libra­ry list.

Now ima­gine you ran these two SQL statements :

Create Func­tion libprod/MyFunc(ymd Decimal(8,0))
returns char(10) 
lan­guage rpgle 
deter­mi­nis­tic 
no sql 
exter­nal name ‘LIBPROD/DEMO1(MYFUNC)‘
para­me­ter style general ; 

Create Func­tion libtest/MyFunc(ymd Decimal(8,0))
returns char(10) 
lan­guage rpgle 
deter­mi­nis­tic 
no sql 
exter­nal name ‘LIBTEST/DEMO1(MYFUNC)‘
para­me­ter style general ;

The Create Func­tion SQL sta­te­ment is some­thing you run to tell the data­base how to call a UDF. Once I’ve crea­ted the func­tion, I can call it from an SQL sta­te­ment. I run Create Func­tion only once for each func­tion to « ins­tall » it.

With the pre­ce­ding two Create Func­tion com­mands, I can run the SQL sta­te­ment as follows :

select LIBPROD/MYFUNC(someField) from SOMEFILE ;

I have LIBPROD hard-coded, so it calls the copy of MyFunc that’s in LIBPROD. But what would hap­pen if I didn’t hard-code the library ?

select MYFUNC(someField) from SOMEFILE ;

This time, I didn’t spe­ci­fy a libra­ry for MyFunc in the Select sta­te­ment. Because my SQL naming conven­tion has been set to « sys­tem,» the sys­tem searches my libra­ry list for the SQL func­tion defi­ni­tion. If LIBPROD is ahead of LIBTEST in my libra­ry list, the sys­tem finds the func­tion defi­ni­tion (the one I crea­ted with Create Func­tion) in LIBPROD and calls the cor­res­pon­ding ser­vice pro­gram, which is also in LIBPROD. If I had LIBTEST ahead of LIBPROD in my libra­ry list, the sys­tem would call the ver­sion in LIBTEST.

Even though I’ve hard-coded the libra­ry name in the Exter­nal Name clause of my SQL sta­te­ment, I can still effec­ti­ve­ly use the libra­ry list, since it’ll be used to find my defi­ni­tion. You control which defi­ni­tion is used by choo­sing to spe­ci­fy (or not) the libra­ry on the SQL sta­te­ment. In this example, the Select sta­te­ment didn’t spe­ci­fy a libra­ry, so the sys­tem used the libra­ry list.

Although I used Create Func­tion in this example, the same holds true for the Create Pro­ce­dure SQL statement.

Fin­ding the Exter­nal Pro­gram by Libra­ry List

Per­haps I don’t want to hard-code a libra­ry, even on the Create Func­tion (or Create Pro­ce­dure) SQL sta­te­ment. I want to rely on the libra­ry list throu­ghout. This seems like the most intui­tive thing to try :

Create Func­tion MyFunc(ymd Decimal(8,0))
returns char(10) 
lan­guage rpgle 
deter­mi­nis­tic 
no sql 
exter­nal name ‘*LIBL/DEMO1(MYFUNC)‘
para­me­ter style gene­ral ; 
<span style=«color:red»>SQL0113 – Name *LIBL not allowed.</h3>

It didn’t work ! I can’t spe­ci­fy *LIBL on an exter­nal name clause. Howe­ver, I can still use the libra­ry list if I want to. I just have to leave the libra­ry off the exter­nal name. To my sur­prise, I have to omit the quotes to make it work.

Create Func­tion LibProd/MyFunc(ymd Decimal(8,0))
returns char(10) 
lan­guage rpgle 
deter­mi­nis­tic 
no sql 
exter­nal name DEMO1(MYFUNC)
para­me­ter style general ; 

Create Func­tion LibTest/MyFunc(ymd Decimal(8,0))
returns char(10) 
lan­guage rpgle 
deter­mi­nis­tic 
no sql 
exter­nal name DEMO1(MYFUNC)
para­me­ter style general ;

The SQL refe­rence manual states that when using the sys­tem naming conven­tion, and the exter­nal name is not qua­li­fied, it will use *LIBL to search for the pro­gram or ser­vice pro­gram. You can’t put quotes around the exter­nal name when you use the libra­ry list, or you get an error.

Once again, if I have a LIBPROD libra­ry for pro­duc­tion objects, and a LIBTEST for test copies, I can run the fol­lo­wing SQL statement :

select MYFUNC(someField) from SOMEFILE ;

If LIBPROD is ahead of LIBTEST in my libra­ry list, I will find the defi­ni­tion in LIBPROD, and it will call the DEMO1 ser­vice pro­gram also in LIBPROD if it exists, because it’s using my libra­ry list for both the defi­ni­tion and the exter­nal object. The libra­ry list is used for everything !

But, now consi­der this :

select LIBPROD/MYFUNC(someField) from SOMEFILE ;

I’ve hard-coded LIBPROD on the Select sta­te­ment. It will doubt­less find the func­tion defi­ni­tion (crea­ted with Create Func­tion) in LIBPROD. But which exter­nal object will it use ? The one in LIBPROD or the one in LIBTEST ? It depends on my libra­ry list. So even though I hard-coded the libra­ry in the Select sta­te­ment, it’s still going to use *LIBL. What if I don’t want it to ? What if I have nei­ther LIBPROD nor LIBTEST in my libra­ry list, and I want to expli­cit­ly spe­ci­fy the libra­ry on the Select sta­te­ment so that I don’t have to modi­fy the libra­ry list ? The libra­ry spe­ci­fied in the SQL sta­te­ment doesn’t work, because *LIBL is hard-coded in the defi­ni­tion I crea­ted with Create Function.

Again, I used Create Func­tion pure­ly as an example. The same logic also applies to crea­ting a pro­ce­dure defi­ni­tion with the Create Pro­ce­dure SQL statement.

Conclu­sion

Most of us in the IBM i com­mu­ni­ty are used to using *LIBL for eve­ry­thing. We don’t ever want to hard-code a libra­ry into any­thing. In that case, spe­ci­fying an exter­nal name without a libra­ry causes the rou­tine to use *LIBL, which is what we want. But if you ever want to control the libra­ry at run­time, it may make more sense to hard-code the libra­ry in the Exter­nal Name clause. After all, this does not pre­clude you from using the *LIBL to find the definition !

La solu­tion que nous avons trou­vée est de deman­der à modi­fier le para­mètre NAMING CONVENTION du jdbc en SYSTEM. Puis au pro­fil uti­li­sé par l’ap­pli­ca­tion tierce a été asso­cié une JOBD para­mé­trant la liste de bibliothèque.