Re: JAVA Stored Procedure

[Patrick L Archibald <Patrick.Archibald@xxxxxxxxxxxxx>]

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? 
>