15.10 - PARAMETER STYLE - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

The parameter passing convention to be used when passing parameters to the procedure.

This clause is mandatory for all external procedures. The specified parameter style must match the parameter passing convention of the external procedure.

If you do not specify a parameter style at this point, you can specify one with the external body reference.

You cannot specify parameter styles more than once in the same CREATE PROCEDURE or REPLACE PROCEDURE request.

SQL
Uses indicator variables to pass arguments.
As a result, you can always pass nulls as iinputs and return them in results.
SQL is the default parameter style.
TD_GENERAL
Uses parameters to pass arguments.
Can neither be passed nor return nulls.
JAVA
Mandatory for all Java procedures.
If the Java procedure must accept null arguments, then the EXTERNAL NAME clause must include the list of parameters and specify data types that map to Java objects.

Example: Creating a Procedure Using PARAMETER STYLE SQL

The following CREATE PROCEDURE request installs the external SQL procedure named GetRegionXSP on the Teradata platform:

   CREATE PROCEDURE GetRegionXSP
     (INOUT region VARCHAR(64))
   LANGUAGE C
   NO SQL
   EXTERNAL NAME 'CS!getregion!xspsrc/getregion.c!F!xsp_getregion'
   PARAMETER STYLE SQL;

The only difference between this definition and the definition provided in Example: Creating a Procedure Using PARAMETER STYLE TD_GENERAL is the PARAMETER STYLE declaration: this procedure uses parameter style SQL.

The following excerpt shows the fragment of the C procedure code that declares a parameter style of SQL:

   /*****  C source file name: getregion.c  *****/
   
   #define  SQL_TEXT Latin_Text
   #include "sqltypes_td.h"
   #include <string.h>
   
   void xsp_getregion( VARCHAR_LATIN *region,
                       int           *region_isnull,
                       char           sqlstate[6],
                       SQL_TEXT       extname[129],
                       SQL_TEXT       specific_name[129],
                       SQL_TEXT       error_message[257] )
   {
        …
   }

Example: Creating a Procedure Using PARAMETER STYLE TD_GENERAL

The following CREATE PROCEDURE request installs the external SQL procedure named GetRegionXSP on the Teradata platform:

   CREATE PROCEDURE GetRegionXSP
     (INOUT region VARCHAR(64))
   LANGUAGE C
   NO SQL
   EXTERNAL NAME 'CS!getregion!xspsrc/getregion.c!F!xsp_getregion'
   PARAMETER STYLE TD_GENERAL;

The only difference between this definition and the definition provided in Example: Creating a Procedure Using PARAMETER STYLE SQL is the PARAMETER STYLE declaration: this procedure uses parameter style TD_GENERAL.

The following excerpt shows the fragment of the C procedure code that declares a parameter style of TD_GENERAL:

   /*****  C source file name: getregion.c  *****/
   
   #define SQL_TEXT Latin_Text
   #include "sqltypes_td.h"
   #include <string.h>
   
   void xsp_getregion( VARCHAR_LATIN *region,
                       char           sqlstate[6])
   {
        …
   }

Example: Creating Java External SQL Procedures

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