Tweet
Why? Why is Java acting funny on me today? I spent all day trying to find out what went wrong with my Java program and finally traced it to the JDBC driver. The other day I put some optimization code in my program to switch to the OS/400 native JDBC driver when run on the iSeries and switch back to the toolbox JDBC driver when run on a PC. I'm using a SQL statement that uses two UDFs (User defined Functions). Everything works fine when run with the toolbox JDBC driver. It gets all jacked up when I switch to the native driver. Is there some sort of escape syntax or something that I'm missing when referencing UDFs? Here's a snippet of the select statement that I'm running: SELECT QGPL.SEQ() AS "SAMPLE #", DATE (QGPL.CHARDATE(CHKRDT)) AS "RELEASE DATE", CHKCOL AS "COLLATOR", QGPL.CHARDATE( CHKSDT ) || ' ' || QGPL.CHARTIME( CHKSTM ) AS "START TIMESTAMP", QGPL.CHARDATE ( CHKEDT ) || ' ' || QGPL.CHARTIME( CHKETM ) AS "END TIMESTAMP", CHKCHK AS "QC EMPLOYEE", CHKLIN AS "LINE ITEMS", CHKQTY AS "PIECES IN ORDER", CHKERR AS "ERROR CODE", ERRDSC AS "ERROR DESCRIPTION" FROM RRDCOMMON.CHKMST1 JOIN RRDCOMMON.ERRMST1 ON CHKERR = ERRCOD I'm using the UDFs for formatting purposes Here is the source for the UDFs: --CHARDATE --Takes a date stored in standard packed decimal CCYYMMDD format CREATE FUNCTION CHARDATE(INDATE DEC(8, 0)) RETURNS CHAR(10) LANGUAGE SQL BEGIN DECLARE WRKDATE DECIMAL(8,0); DECLARE CCYY CHAR(4); DECLARE YY CHAR(2); DECLARE MM CHAR(2); DECLARE DD CHAR(2); SET WRKDATE = IFNULL(INDATE,0); SET WRKDATE = CASE WHEN WRKDATE < 10000000 THEN 10010101 ELSE WRKDATE END; SET CCYY = SUBSTR(CHAR(WRKDATE),1,4); SET YY = SUBSTR(CHAR(WRKDATE),3,2); SET MM = SUBSTR(CHAR(WRKDATE),5,2); SET DD = SUBSTR(CHAR(WRKDATE),7,2); RETURN(CHAR(CCYY || '-' || MM || '-' || DD,10)); END --CHARTIME --Takes a time stored in standard packed decimal HHMMSS format CREATE FUNCTION CHARTIME(INTIME DEC(6, 0)) RETURNS CHAR(8) LANGUAGE SQL BEGIN DECLARE WRKTIME DECIMAL(6,0); DECLARE HH CHAR(2); DECLARE MM CHAR(2); DECLARE SS CHAR(2); SET WRKTIME = IFNULL(INTIME,0); SET HH = SUBSTR(CHAR(WRKTIME),1,2); SET MM = SUBSTR(CHAR(WRKTIME),3,2); SET SS = SUBSTR(CHAR(WRKTIME),5,2); RETURN(CHAR(HH || ':' || MM || ':' || SS, 8)); END Any ideas? Cliff