iSeries SQL function in a SQL procedure – error: Function Name “in *LIBL type *N not found.”

1760 pts.
Tags:
IBM iSeries
iSeries RPG programming
RPG ILE
SQL
Stored Procedures
A while back, Seeker1 posted this question.  Unfortuantely, he didn't provide enough information for Tom to help.  Well - I have the same problem so hopefully, this will be enough.

This Select statement generates the error:
SELECT UNUNIT, ITRVEHLOC(UNUNIT  , 'CITY ')  FROM Units


Here are the supporting commands and code:
Create Function                                        ITRVEHLOC(                                                   UNIT        Char(  6),                               PROXTO      Char(  5))                       Returns CHAR                                         Language            RPGLE                            Specific            ITRVEHLOC                        Deterministic                                        No SQL                                               Returns Null on Null Input                           No External Action                                   Not Fenced                                           External Name       'OMNISITE/ITRVEHLOC(ITRVEHLOC)'   Parameter Style     SQL             
 

CRTRPGMOD MODULE(OMNISITE/ITRVEHLOC) SRCFILE(OMNISITE/QRPGLESRC) 
          

CRTSRVPGM SRVPGM(OMNISITE/ITRVEHLOC) MODULE(OMNISITE/ITRVEHLOC) EXPORT(*ALL) TEXT('Service Program for mod ITRVEHLOC')
                            

H NOMAIN                                                                                         *                                                                                              D ITRVEHLOC       PR            50A                                        Prototype            D   Unit                         6A                                                             D   ProxType                     5A                                                              *                                                                                              P ITRVEHLOC       B                   EXPORT                               Begin Procedure       *                                                                                              D ITRVEHLOC       PI            50A                                        Procedure Interface  D Unit                           6A                                        Input Unit           D ProxType                       5A                                        Input Proximity Type  *                                                                                              D Location        S             50A                                        Internal Variable     *                                                                                              C                   Call      'OMGETLCR'                                   Call RPG             C                   Parm                    Unit                           Pass Parameters      C                   Parm                    ProxType                                            C                   Parm                    Location                                             *                                                                                              C                   Return                  Location                       Return Location       *                                                                                      P ITRVEHLOC       E                                                        End Procedure


I have no CREATE PROCEDURE statement.



Software/Hardware used:
iSeries, AS/400

Answer Wiki

Thanks. We'll let you know when a new response is added.

BTW, you might also succeed by CASTing ‘CITY ‘ as CHAR in your function call. I’d prefer having the work done in my definitions rather than forcing every potential SELECT to have to code for it.

If I can get time, I’ll try to generate an actual test of your code. But I’m going to be busy for a few days.

Tom

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Craig Hatmaker
    Well the copy paste on the code didn't work so well and I don't see an edit option...
    H NOMAIN
    *
    D ITRVEHLOC       PR            50A
    D   Unit                         6A
    D   ProxType                     5A
     *
    P ITRVEHLOC       B                   EXPORT
     *
    D ITRVEHLOC       PI            50A
    D Unit                           6A
    D ProxType                       5A
     *
    D Location        S             50A
     *
    C                   Call      'OMGETLCR'            
    C                   Parm                    Unit    
    C                   Parm                    ProxType
    C                   Parm                    Location
     *                                                  
    C                   Return                  Location
     *
    P ITRVEHLOC       E
    
    1,760 pointsBadges:
    report
  • Craig Hatmaker
    CRTSRVPGM SRVPGM(OMNISITE/ITRVEHLOC)
    MODULE(OMNISITE/ITRVEHLOC)
    EXPORT(*ALL)
    TEXT('Service Program for mod ITRVEHLOC')
    Create Function
      ITRVEHLOC(
              UNIT        Char(  6),
              PROXTO      Char(  5))
      Returns CHAR
      Language            RPGLE
      Specific            ITRVEHLOC
      Deterministic
      No SQL
      Returns Null on Null Input
      No External Action
      Not Fenced
      External Name       'OMNISITE/ITRVEHLOC(ITRVEHLOC)'
      Parameter Style     SQL
    1,760 pointsBadges:
    report
  • Craig Hatmaker
    So I found this in another forum and it actual makes the Select Statement work. But I really don't like "cast"ing parameters in the Select to fix this. Other suggestions? "This is the classic issue with functions & stored procedures - you defined the parameters as character but are passing literals. Literals are cast to varying char type by SQL, so the prototype does not match. You can do something like char('L') or cast('L' as char(1)) - check the syntax there, I don't use cast very much - usually use the char function itself. I also don't know if there is any benefit to the CONST - or even if those will work - i seem to remember something to the contrary. You could define all the parameters as varying, too."
    1,760 pointsBadges:
    report
  • TomLiotta
    It'll be a while before I can create a test that uses your code, but I can make suggestions. First:
    D   ProxType                     5A
    I might change that to:
    D   ProxType                     5A   varying const
    Along with that:
             PROXTO      Char(  5))
    ...becomes:
             PROXTO      Varchar(  5))
    I'm pretty sure that SQL isn't going to generate a CHAR parm from a literal string like 'CITY '. I would expect it to create a VARCHAR parameter instead. I don't know about UNUNIT, but that would reasonably be a CHAR. With those above changes, the function call in your SELECT statement prepares a CHAR and a VARCHAR parameter, then DB2 goes searching for a function named ITRVEHLOC() that has one CHAR and one VARCHAR parm. It manages to find such a definition and executes the CALL to your program. Without those changes, it does manage to find ITRVEHLOC(), but the parms don't match. (Note that functions and stored procs may overload parm definitions.) It looks for another ITRVEHLOC() with matching parms, but none exist. The quoted text you supplied notes that "You could define all the parameters as varying, too." That's essentially what it was getting at. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    BTW, you might also succeed by CASTing 'CITY ' as CHAR in your function call. I'd prefer having the work done in my definitions rather than forcing every potential SELECT to have to code for it. If I can get time, I'll try to generate an actual test of your code. But I'm going to be busy for a few days. Tom
    125,585 pointsBadges:
    report
  • Craig Hatmaker
    Great suggestions Tom. I think you've pointed me in the right direction. Thanks. I puzzled why the error message was "not found". When you pointed out the overloading bit it all made sense. I hope IBM can improve this error handling and logging to provide better insight into the matter.
    1,760 pointsBadges:
    report
  • TomLiotta
    When you pointed out the overloading bit it all made sense. When you "know" that a function or proc exists but it is reported as 'Not found', you should be able to do some testing by CASTing each parameter to match explicitly with the definition. If explicit CASTing then finds the target, you can be sure of the type of problem. That's about as easy of a test as can be done. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following