Example: Using the java.sql.Array Interface For an ARRAY Type Parameter - Advanced SQL Engine - Teradata Database

SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
qwr1571437338192.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1147
lifecycle
previous
Product Category
Teradata Vantage™

Example: Using the java.sql.Array Interface For a 1-D ARRAY Type Parameter

CREATE TYPE INTARRAY AS INTEGER ARRAY[5];

CREATE PROCEDURE GET_ARRAY(IN A1 INTARRAY)
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA 
EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.get_array';

public static void get_array(java.sql.Array A1) throws SQLEXception
{
     System.out.println ("Array is of type "+A1.getBaseTypeName());
     System.out.println ("Array element type:" + A1.getBaseType()); 

      // get Array elements            
      int[] values = (Int[]) A1.getArray();
      for (int i=0; i<values.length; i++) 
      {
        System.out.println("element " + i + " = "+values[i]);
      }
}

Example: Using the java.sql.Array Interface For an n-D ARRAY Type Parameter

This example uses the java.sql.Array interface for an n-D ARRAY type parameter and gets elements as a result set.

CREATE TYPE SHOTS AS INTEGER ARRAY[1:3][1:3][1:3];

CREATE PROCEDURE GET_ND_ARRAY(IN A1 SHOTS)
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA 
EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.get_nd_array';

public static void get_nd_array(java.sql.Array A1) throws SQLException
{
     System.out.println ("Array is of type "+A1.getBaseTypeName());
     System.out.println ("Array element type:" + A1.getBaseType()); 
     System.out.println 
     ("Array num dimensions:" + A1.getNumDimensions()); 

      // get Array elements as a result set            
      ResultSet nDArrayElements;
      List<Integer> lowerBounds = new ArrayList<Integer>();
      List<Integer> upperBounds = new ArrayList<Integer>();
      lowerBounds.add(1);
      lowerBounds.add(1);
      lowerBounds.add(1);
      upperBounds.add(3);
      upperBounds.add(3);
      upperBounds.add(3);

      nDArrayElements = A1.getResultSet_nD(lowerBounds, upperBounds);
      
      while (nDArrayElements.next()) {
          List<Integer> elementNum = nDArrayElements.getObject(1);
          int elementItem = nDArrayElements.getInt(2);
          System.out.println ("Array element index: " + elementNum + 
                              "element value: " + elementItem);
      }
}