Re: JAVA Stored Procedure -- JAVA400-L

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?