Examples: Using java.sql.Array Interface For ARRAY Parameter - Analytics Database - Teradata Vantage

SQL External Routine Programming

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
2023-07-11
dita:mapPath
iiv1628111441820.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1147
lifecycle
latest
Product Category
Teradata Vantageā„¢

Example: Using java.sql.Array Interface For 1-D ARRAY 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 java.sql.Array Interface For n-D ARRAY 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);
      }
}