Example: Creating Java External SQL Procedures - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

The following set of examples shows CREATE PROCEDURE requests and relevant Java external procedure code for a variety of different parameter data types.

The following example is for a BYTEINT parameter data type.

     CREATE PROCEDURE mybyteint
       (IN  b BYTEINT
        OUT c BYTEINT)
     LANGUAGE JAVA
     NO SQL
     PARAMETER STYLE JAVA
     EXTERNAL NAME 'User_jar:UnitTest.mybyteint';
     public static void mybyteint(
        byte a,
        byte[] Result) throws SQLException

The following example is for a SMALLINT parameter data type.

     CREATE PROCEDURE mysmallint
       (IN    b smallint, 
        INOUT c smallint )
     LANGUAGE JAVA
     NO SQL
     PARAMETER STYLE JAVA
     EXTERNAL NAME 'newint_jar:newint.mysmallint';
     public static void mysmallint(
        short a,
        short[] Result ) throws SQLException

The following example is for an INTEGER parameter data type.

     CREATE PROCEDURE myint
       (IN  b INTEGER, 
        OUT c INTEGER )
     LANGUAGE JAVA
     NO SQL
     PARAMETER STYLE JAVA 
     EXTERNAL NAME 'user_jar:UnitTest.myint';
     public static void myint(int i,
        int[] retval) throws SQLException

The following example is for a DECIMAL or NUMERIC parameter data type.

     CREATE PROCEDURE mydecs
       (IN  c DECIMAL(8,2), 
        OUT d DECIMAL(8,2))
     LANGUAGE JAVA
     NO SQL
     PARAMETER STYLE JAVA
     EXTERNAL NAME 'user_jar:UnitTest.mydec';
     public static void mydecs(BigDecimal c, BigDecimal[] d)
         throws SQLException

The following example is for a FLOAT or REAL parameter data type.

     CREATE PROCEDURE myfloat
       (IN  i FLOAT, 
        OUT j FLOAT )
     LANGUAGE JAVA
     NO SQL
     PARAMETER STYLE JAVA
     EXTERNAL NAME 'user_jar:UnitTest.myfloat';
     public static void myfloat(
        Double c,
        Double[] Result) throws SQLException

The following example is for a DATE parameter data type.

     CREATE PROCEDURE mydatediagret
       (IN  d DATE,
        OUT o DATE  )
     LANGUAGE JAVA
     NO SQL
     PARAMETER STYLE JAVA
     EXTERNAL NAME 'user_jar:UnitTest.mydatediagret';
     public static void mydatediagret(
        java.sql.Date date,
        java.sql.Date[] Result) throws SQLException

The following example is for a TIME WITH TIME ZONE parameter data type.

     CREATE PROCEDURE mytimezdiagret
       (IN   t TIME WITH TIME ZONE,
        OUT  d TIME WITH TIME ZONE )
     LANGUAGE JAVA
     NO SQL
     PARAMETER STYLE JAVA
     EXTERNAL NAME 'user_jar:UnitTest.mytimezdiagret';
     public static void mytimezdiagret(
        java.sql.Time time,
        java.sql.Time[] Result) throws SQLException

Because the Java Virtual Machine implementation does not support leap seconds, the maximum value of the SECOND field is 59.999999 rather than 61.999999.

The following example is for a TIMESTAMP WITH TIME ZONE parameter data type.

     CREATE PROCEDURE mytszdiagret
       (IN  t TIMESTAMP WITH TIME ZONE,
        OUT d TIMESTAMP WITH TIME ZONE  )
     LANGUAGE JAVA
     NO SQL
     PARAMETER STYLE JAVA
     EXTERNAL NAME 'user_jar:UnitTest.mytszdiagret';
     public static void mytszdiagret (
        java.sql.Timestamp p1,
        java.sql.Timestamp[] result) throws SQLException

Because the Java Virtual Machine implementation does not support leap seconds, the maximum value of the SECOND field is 59.999999 rather than 61.999999.

The following example is for an INTERVAL YEAR parameter data type.

     CREATE PROCEDURE intcpy
       (IN  parameter_1 INTERVAL YEAR,
        OUT t           INTERVAL YEAR )
     LANGUAGE JAVA
     NO SQL
     PARAMETER STYLE JAVA
     EXTERNAL NAME 'user_jar:UnitTest.intcpy';
     public static void intcpy( String param_1,
        String[] result ) throws SQLException

The following example is for either a CHARACTER or a VARCHAR parameter data type.

     CREATE PROCEDURE strcpy
       (IN  parameter_1 VARCHAR(15),
        OUT t           VARCHAR(15) )
     LANGUAGE JAVA
     NO SQL
     PARAMETER STYLE JAVA
     EXTERNAL NAME 'user_jar:UnitTest.strcpy';
     public static void strcpy( String param_1,
        String[] result ) throws SQLException

The following example is for a CLOB parameter data type.

     CREATE PROCEDURE myclobdiagret2
       (IN  b CLOB)
     LANGUAGE JAVA
     MODIFIES SQL DATA
     PARAMETER STYLE JAVA
     EXTERNAL NAME 'user_jar:UnitTest.myclobdiagret2';
          public static void myclobdiagret2(
                 java.sql.Clob data) throws SQLException
     {
       Connection conn =        DriverManager.getConnection("jdbc:default:connection");
       PreparedStatement p = conn.prepareStatement("ins into tab2
                                                    values(1,?)");
       p.setClob(1, data);
       p.executeUpdate();
     }

The following example is for a VARBYTE parameter data type:

     CREATE PROCEDURE mybvdiagret
       (IN  c  varbyte(30),
        OUT d  varbyte(30))
     LANGUAGE JAVA
     NO SQL
     PARAMETER STYLE JAVA
     EXTERNAL NAME 'user_jar:UnitTest.mybvdiagret';
     public static void mybvdiagret(
        byte[] data,
        byte[][] Result ) throws SQLException

The following example is for a BLOB parameter data type:

     CREATE PROCEDURE myblobdiagret
       (IN b  blob,
        OUT c blob) 
     LANGUAGE JAVA
     NO SQL
     PARAMETER STYLE JAVA
     EXTERNAL NAME 'user_jar:UnitTest.myblobdiagret';
     public static void myblobdiagret(
        java.sql.Blob data,
        java.sql.Blob[] Result) throws SQLException