Creating a Java Table UDF

Create, compile, and install a Java row generator and create the Java table UDF function definition.

1. Place this code in a file named RowGenerator.java:

   package example;
   
   import java.sql.*;
   
   public class RowGenerator {
     
     public static void rowGenerator( int numRows, ResultSet rset[] ) {
       // Create the meta data needed for the result set
       ResultSetMetaDataImpl rsmd = new ResultSetMetaDataImpl(2);
   
       //The first column is the SQL type INTEGER.
       rsmd.setColumnType(1, Types.INTEGER);
       rsmd.setColumnName(1,"c1");
       rsmd.setColumnLabel(1,"c1");
       rsmd.setTableName(1,"MyTable");
   
       // The second column is the SQL type VARCHAR length 255
       rsmd.setColumnType(2, Types.VARCHAR);
       rsmd.setColumnName(2,"c2");
       rsmd.setColumnLabel(2,"c2");
       rsmd.setColumnDisplaySize(2, 255);
       rsmd.setTableName(2,"MyTable");
           
       // Create result set using the ResultSetMetaData
       ResultSetImpl rs = null;
       try {
         rs = new ResultSetImpl( (ResultSetMetaData)rsmd );
         rs.beforeFirst();  // Make sure we are at the beginning.
       } catch( Exception e ) {
         System.out.println( "Error: couldn't create result set." );
         System.out.println( e.toString() );
       }
       
       // Add the rows to the result set and populate them
       for( int i = 0; i < numRows; i++ ) {
         try {
           rs.insertRow();   // insert a new row.
           rs.updateInt( 1, i );   // put the integer value in the first column
           rs.updateString( 2, ("Str" + i) );  // put the VARCHAR/String value in the second column
         } catch( Exception e ) {
           System.out.println( "Error: couldn't insert row/data on row " + i );
           System.out.println( e.toString() );
         }        
       }
   
       try {
           rs.beforeFirst();  // rewind the result set so that the server gets it from the beginning.
       } catch( Exception e ) {
           System.out.println( e.toString() );
       }
       rset[0] = rs;  // assign the result set to the 1st of the passed in array.
     }
   }
   

2. Compile RowGenerator.java, ResultSetImpl.java, and ResultSetMetaData.java. The Windows directory %ALLUSERSPROFILE%\samples\java ($IQDIR15/samples/java on UNIX) contains ResultSetImpl.java and ResultSetMetaData.java.

   javac <pathtojavafile>/ResultSetMetaDataImpl.java

   javac <pathtojavafile>/ResultSetImpl.java

   javac <pathtojavafile>/RowGenerator.java

3. In Interactive SQL, connect to the iqdemo database.
4. In Interactive SQL, install the three class files:

   INSTALL JAVA NEW FROM FILE '<pathtofile>/ResultSetMataDataImpl.class'

   INSTALL JAVA NEW FROM FILE '<pathtofile>/ResultSetImpl.class'

   INSTALL JAVA NEW FROM FILE '<pathtofile>/RowGenerator.class'

5. In Interactive SQL, create the Java Table function definition.
   Be ready to provide this information:

   • The Java package, class, and method names
   • The Java data types of your function arguments, and their corresponding SQL data types
   • The SQL name to assign to the Java UDF

   CREATE or REPLACE PROCEDURE rowgenerator( IN numRows INTEGER )
   	RESULT ( c1 INTEGER , c2 VARCHAR(255) )
   	EXTERNAL NAME
   		'example.RowGenerator.rowGenerator(I[Ljava/sql/ResultSet;)V'
   	LANGUAGE JAVA

   Note: The RESULT set has two columns; one INTEGER and the other VARCHAR(255). The Java prototype has two arguments; one INT (I) and the other an array of java.sql.ResultSets ([Ljava/sql/ResultSet;). The Java prototype shows the function returning Void(V).
6. In Interactive SQL, use the Java table UDF in a query against the iqdemo database:

   SELECT * from rowGenerator(5);

   The query returns five rows of two columns.