Re: JAVA Stored Procedure -- JAVA400-L

Jay

Send us your Java source and your SQL CREATE statement. Maybe we can find the problem.

By the way it seemed like I had to sign-off and on after making changes to my Java class to see my changes during development.

Thanx, PLA

Himes, Jay wrote:

I tried creating a function based on the ones Patrick sent; however, now I get the error below. I suspect there is a log somewhere that would give me more details but I have no idea where to look. Does anyone have any ideas?

Message ID . . . . . . : CPF503E Severity . . . . . . . : 30

Message type . . . . . : Diagnostic

Date sent . . . . . . : 04/02/04 Time sent . . . . . . : 13:44:01

Message . . . . : User-defined function error on member NAMMSP.

Cause . . . . . : An error occurred while invoking user-defined function

SYSDOWN in library LBUCMUSR. The error occurred while invoking the

associated external program or service program s!getsysst in library

storedproc, program entry point or external name ate. The error occurred on member NAMMSP file NAMMSP in library LBUCMFIL. The error code is 1. The

error codes and their meanings follow:

1 -- The external program or service program returned SQLSTATE 42724. The text message returned from the program is: LBUCMUSR.SYSDOWNSYSDOWNget .

2 -- The external program failed before it completed.
    3 -- The database timed out waiting for the program to return. The
timeout
More...
Message ID . . . . . . :   CPF503E       Severity . . . . . . . :   30
Message type . . . . . : Diagnostic

value used by the database was 0 minutes and 30 seconds.

4 -- The external program no longer exists or is not found.
   5 -- One of the input parameters of the function had a data mapping
error.
   For an external program, the program entry point displayed will be *N.
Recovery . . . : For error codes 1 and 2, determine the cause of the error from either the SQLSTATE or a previously listed message.
   For error code 3, either increase the timeout limit using the QAQQINI
file
 setting or determine why the external program or service program did not
return in the time allotted.

For error code 4, ensure the program or service program exists for the

duration of the query.

For error code 5, determine the cause of the data mapping error.

Try the request again.
More...
Message ID . . . . . . :   CPF503E       Severity . . . . . . . :   30
Message type . . . . . : Diagnostic

Refer to the DB2 UDB for iSeries SQL Programming topic in the Information Center book, http://www.iseries.ibm.com/infocenter for more information on

user-defined functions.

Possible choices for replying to message . . . . . . . . . . . . . . . :

C -- The request is canceled.

I -- The request is ignored.
-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx [mailto:java400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Patrick L Archibald
Sent: Thursday, April 01, 2004 10:40 AM
To: Java Programming on and around the iSeries / AS400
Subject: Re: JAVA Stored Procedure
Hey Jay
I've recently created a few Java SQL functions. I haven't run into the error
you did. Here is my Java code maybe it will help.
Thanx, PLA

import java.math.BigDecimal; import java.net.InetAddress; import java.net.UnknownHostException; import java.text.SimpleDateFormat; /** * @author Patrick L Archibald * @version 1.0 March 23, 2004 * * Various static methods for SQL functions. * * The class must reside in the following directory on the iSeries. * /QIBM/UserData/OS400/SQLLib/Function * * You can use this AS/400 command to compile the class. * qsh cmd('cd /java;javac SqlFunctions.java -d /QIBM/UserData/OS400/SQLLib/Function') */ public class SqlFunctions { private final static SimpleDateFormat dateAndTimeFormatFull = new SimpleDateFormat("EEE MMM dd, yyyy hh:mm:ss a"); private final static SimpleDateFormat dateFormatFull = new SimpleDateFormat("EEE MMM dd, yyyy"); private final static SimpleDateFormat dateFormatYyyymmdd = new SimpleDateFormat("yyyyMMdd"); public static void main(String[] args) { System.out.println(getHostName(args[0])); System.out.println(getFullDate(args[0])); } private SqlFunctions() {} /** * This SQL statement was used to register this function: * * CREATE FUNCTION QGPL.GETHOSTJ ( * VARCHAR(132) ) * RETURNS VARCHAR(132) * LANGUAGE JAVA * SPECIFIC QGPL.GETHOSTJ * DETERMINISTIC * READS SQL DATA * RETURNS NULL ON NULL INPUT * NO EXTERNAL ACTION * EXTERNAL NAME 'SqlFunctions.getHostName' * PARAMETER STYLE JAVA ; * * Named the function GETHOSTJ to differentiate * it from the GETHOST function that uses a C * program. I was comparing the two for performance. * Surprisingly I didn't see much of a difference. * I thought the C program would smoke the java class. * */ public static String getHostName(String ipAddress) { if (ipAddress == null) { return ipAddress; } try { InetAddress inetAddress = InetAddress.getByName(ipAddress.trim()); return inetAddress.getHostName(); } catch (UnknownHostException e) { return ipAddress.trim(); } } /** * This SQL statement was used to register this function: * * CREATE FUNCTION QGPL.GETFULLDATEANDTIME ( * TIMESTAMP ) * RETURNS VARCHAR(30) * LANGUAGE JAVA * SPECIFIC QGPL.GETFULLDATEANDTIME * DETERMINISTIC * READS SQL DATA * RETURNS NULL ON NULL INPUT * NO EXTERNAL ACTION * EXTERNAL NAME 'SqlFunctions.getFullDateAndTime' * PARAMETER STYLE JAVA ; */ public static String getFullDateAndTime(java.sql.Timestamp timestamp) { if (timestamp == null) { return null; } return dateAndTimeFormatFull.format((java.util.Date) timestamp); } /** * This SQL statement was used to register this function * * CREATE FUNCTION QGPL.GETFULLDATE ( * CHAR(8) ) * RETURNS VARCHAR(132) * LANGUAGE JAVA * SPECIFIC QGPL.GETFULLDATECHAR * DETERMINISTIC * READS SQL DATA * RETURNS NULL ON NULL INPUT * NO EXTERNAL ACTION * EXTERNAL NAME 'SqlFunctions.getFullDate' * PARAMETER STYLE JAVA ; */ public static String getFullDate(String yyyymmdd) { try { java.util.Date date = dateFormatYyyymmdd.parse(yyyymmdd); System.out.println(date); return dateFormatFull.format(date); } catch (Exception e) { return null; } } /** * This SQL statement was used to register this function: * * CREATE FUNCTION QGPL.GETFULLDATE( * NUMERIC(8, 0)) * RETURNS VARCHAR(132) * LANGUAGE JAVA * SPECIFIC QGPL.GETFULLDATENUMERIC * DETERMINISTIC * READS SQL DATA * RETURNS NULL ON NULL INPUT * NO EXTERNAL ACTION * EXTERNAL NAME 'SqlFunctions.getFullDate ' * PARAMETER STYLE JAVA; */ public static String getFullDate(BigDecimal yyyymmdd) { try { java.util.Date date = dateFormatYyyymmdd.parse(yyyymmdd.toString()); System.out.println(date); return dateFormatFull.format(date); } catch (Exception e) { return null; } } }

Himes, Jay wrote:

I am trying to create a java stored procedure; however, when I run it I get an error. Has anyone created a stored procedure in java?
_______________________________________________
This is the Java Programming on and around the iSeries / AS400 (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.
_______________________________________________
This is the Java Programming on and around the iSeries / AS400 (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.