Re: SQL question (that seems to be JDBC-specific): Can I do this UDF better?

What date format is squirrel using? If not ISO, that might explain your
problem.

Charles



On Wed, Feb 1, 2017 at 4:25 PM, James H. H. Lampert <
jamesl@xxxxxxxxxxxxxxxxx> wrote:

We have (in a legacy database) a date that is stored as an 8-digit zoned
decimal number, YYYYMMDD.

And we have, in order to present that date as an actual date field, an SQL
VIEW that calls a UDF defined thusly:

DROP FUNCTION FOO/DISPLAYDAT;

create function FOO/DISPLAYDAT(i_date NUMERIC(8))
returns DATE
language sql deterministic not fenced set option datfmt=*ISO
begin
declare continue handler for sqlexception
return date('99991231000000');
if i_date = 0 then return date('00010101000000'); end if;
return date(digits(i_date) concat '000000');
END

Now, this works great. Unless (1) we're using JDBC, and (2) the underlying
numeric date is 0.

Now, if I'm doing a STRSQL session, I either get ++++++++ (if the date
format is *MDY), or I get the correct value of January 1, 0001 (if the date
format is *ISO or *USA).

But if I'm accessing the database via Squirrel (which uses JDBC), then
every time it gets the zeroed-out raw value that produces January 1, 0001,
there's an SQL Exception 181, we take a significant performance hit, and
the displayed result set shows a year of 2001 instead of 0001.

Now, I'm already being proactive about a raw value of 00000000. But can I
be *more* proactive?

--
JHHL
--
This is the Java Programming on and around the IBM i (JAVA400-L) mailing
list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.