RE: UDF problems in STRSQL

Thanks for the responses.

Elvis: No joy.

Rob:
In STRSQL it doesn't matter what the date format is. From watching
_many_ debugging sessions over the last two days with differing STRSQL
and CREATE FUNCTION options in effect, it appears the service program
always receives the dates in *ISO format--except when I tried to use the
value keyword on the service program parameters, then I got garbage. I
did get the selection error message to go away by creating a duplicate
file without the DATFMT(*USA) keyword on the fields. Now I'm not getting
any errors in STRSQL or the job log, but I'm not getting anything in the
result set either.

In our stuck together with chewing gum, duct tape and baling wire
system, changing attributes of fields in the production file is not an
option. It's okay though--I have an easy work around. I'm just using
this as a test bed for building a more important UDF that I don't have a
work around for. And I've learned that if you want to use date fields,
stick with the default until you're producing output for users.
Hopefully I'll remember that if/when we get to design a new system with
a normalized database.

Thanks again,
Roger Mackie


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Thursday, May 05, 2005 14:18
To: Midrange Systems Technical Discussion
Subject: RE: UDF problems in STRSQL

If, when in STRSQL, you hit F13=Services, 1. Change session attributes;
what is your date format?

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
05/05/2005 01:44 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
cc

Subject
RE: UDF problems in STRSQL






Roger, I see you return DECIMAL(3,0) from the UDF.  Not sure but what
happens if you try:

SELECT * FROM MYFILE WHERE INVYR = 2005 and INVNO = 6557 and
CLCDAYSHPLAT(ODDDAT,ODSDAT) > 0.0

Elvis 

-----Original Message-----
 Subject: UDF problems in STRSQL

Hi all,

I searched the archives and read through the SQL Programming Concepts
manual sections on UDFs, but can't seem to solve this problem. This is
my first attempt at creating a UDF (System is at V5R2, soon to be
upgraded to V5R3). The UDF calls a *SRVPGM function written in RPGLE and
compiled in a named activation group (instead of the *CALLER we usually
use). After much trial and error yesterday, I determined that the
service program function completed successfully (in a STRSRVJOB
debugging session it had the proper value in return and did not give any
error), but STRSQL returned this error:

SELECT * FROM MYFILE WHERE INVYR = 2005 and INVNO = 6557 and
CLCDAYSHPLAT(ODDDAT,ODSDAT) > 0 
Selection error involving field ODDDAT.
 

 Message ID . . . . . . :   QRY2283       Severity . . . . . . . :   30

 Message type . . . . . :   Information

 

 Message . . . . :   Selection error involving field ODDDAT.

 Cause . . . . . :   The query did not run because of a comparison
operand 
   involving data from field ODDDAT (in record 299307 of file MYFILE in

   PRODLIB if other than *N). The ODDDAT data is not necessarily
incorrect, 
   since the operand could be an expression involving other fields and

   constants as well as ODDDAT, or it could require some sort of
conversion 
   that could not be performed successfully.

The job log has the expected query optimization messages, but no error
messages. Field ODDDAT is defined with the DATFMT(*USA) keyword and has
a valid date value stored in it. I tried various registration options
and locations but have not stumbled across a combination that does not
give the above error. Here is the latest statement I used to register
the UDF (in my *CURLIB, not the library the data is in).
 CREATE FUNCTION CLCDAYSHPLAT(DATE, DATE) 
    RETURNS DEC(3,0) 
    EXTERNAL NAME 'PGMLIB/FTIMCLC(CLCDAYSHPLAT)'
    LANGUAGE RPGLE 
    NOT FENCED 
    NO SQL
    DETERMINISTIC 

If I can get it working, I want to use this UDF and others in SQLRPGLE
dynamic selects. I appreciate any advice the list can provide as I enter
this brave new world of UDFs.

Thanks,
Roger Mackie