Example: Creating Java External SQL Procedures - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
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