What Am i missing?

Posted by: Robert Gilsdorf

qpgmr

Wayne, Michigan

What Am i missing?
has no ratings.

Published: 03 Nov 2011
Revised: 23 Jan 2013 - 4111 days ago
Last viewed on: 26 Apr 2024 (6235 views)

Main URL:

What Am i missing? Published by: Robert Gilsdorf on 03 Nov 2011 view comments(5)

I have an SQL function that the system cannot seem to find.

I create the procedure with the following statement:

CREATE FUNCTION RSHPROBJ/ONTIM (            
 CM CHAR(4),                                
 TRAC CHAR(14),                             
 STDATE DECIMAL(8,0),                       
 ENDATE DECIMAL(8,0),                       
 CNTTYP CHAR(2),                            
 STPTYP CHAR(2) )                           
 RETURNS DECIMAL(7,0)                       
 LANGUAGE RPGLE                             
 SPECIFIC RSHPROBJ/ONTIM                    
 DETERMINISTIC                              
 NO SQL                                     
 CALLED ON NULL INPUT                       
 NO EXTERNAL ACTION                         
 EXTERNAL NAME 'RSHPROBJ/SQLSRVPGM3(ONTIM)' 
 PARAMETER STYLE SQL

I call it with the following:

SELECT DUMMY1 , ONTIM('01 ', '8640 ', 20111101, 20111102, 'LT', 'AL') FROM dummy

and it is not found. I see it with the proper 6 parameters in iSeries navigator

The RPGLE program has the following entries:

    DONTIM            PR
     D* * Input Vars
     D  CM                                 Like(BSCM)
     D  Tractor                            Like(BSTRA)
     D  StDate                        8  0
     D  EnDate                        8  0
     D  CntType                       2A
     D  StpType                            Like(BSSTYP)
.....D* * Return Var
     D  OTCOUNT                       7  0
     D* * Input Vars Null Indicators
     D  CMi                           5I 0
     D  Tri                           5I 0
     D  SDi                           5I 0
     D  EDi                           5I 0
     D  CTi                           5I 0
     D  STi                           5I 0
     D* * Output Var Null Indicator
     D  Rtni                          5I 0
     D* * SQL status Vars
     D  SQLState                      5
     D  SQLFuncName                 139
     D  SQLSpecName                 128
     D  SQLMsg                       70    Varying

BSCM is 4A

BSTRA is 14A

BSSTYP is 2A

The module compiles and is in SQLSRVPGM3 and the ONTIM procedure is exported.

Everything seems to agree, but it is not found.

Error is "ONTIM in *LIBL type *N not found." and RSHPROBJ is in my *LIBL

Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)

Posted by: abc4000

Premium member *

Comment on: What Am i missing?
Posted: 12 years 5 months 23 days 19 hours 46 minutes ago

Are you using SQLPKG ?

IF YES find User-profile-level Job-de!--script--ion *LIBL when you connect then add RSHPROBJ in *LIBL.

IF NO, I am not sure !

Posted by: bobcozzi

Site Admin ****

Chicagoland

www.cozTools.com/qryfile

Comment on: What Am i missing?
Posted: 12 years 5 months 23 days 13 hours 38 minutes ago
Edited: Thu, 03 Nov, 2011 at 20:08:19 (4558 days ago)

Maybe this...

http://www.midrangenews.com/view?id=1458

Parameter style GENERAL

Posted by: BrianR

Premium member *

Green Bay, WI

Comment on: What Am i missing?
Posted: 12 years 5 months 22 days 22 hours 31 minutes ago

SQL functions are located by the name and the parameter definition list. When you use a character literal, the system casts that as a varchar field, so its looking for the function with parameters defined as varchar. Try casting your character literals as type char:

SELECT DUMMY1 , ONTIM(char('01'), char('8640'), 20111101, 20111102, char('LT'), char('AL')) FROM dummy

You may also have to cast your numeric literals into another data type, I'm not sure what type the system defaults numeric literals to.

Posted by: Ringer

Premium member *

Comment on: What Am i missing?
Posted: 12 years 5 months 22 days 21 hours 50 minutes ago

SQL is complaining because it can't find that function "signature" (count and type of parms).

However, SQL will internally / automatically cast integers and characters (your literals) to decimal and varchar but not the other way around when trying to find a matching function signature. So I usually try to define the parms as decimal and varchar.

Chris Ringer

Posted by: rgilsdor

Premium member *

Wayne, Michigan

Comment on: What Am i missing?
Posted: 12 years 5 months 12 days 22 hours 21 minutes ago

What I was missing was the varchar to char. Once I cast the constant to Char(2), it started working. Never showed up before because my other functions are called from within stored procedures which get parameters passed to them as char(2) and thus the parameters can be imported to the function as a char(2)