HANA Array type and JDBC

[markmumy]

Working on a project where we have the need for an array type in HANA.  Data will be loaded and queried via Java and JDBC. 

What I can't find are examples on how to write back to HANA using the JDBC array stuff.  We can easily query an array type and process it using JAVA array types.

Any thoughts on if this should work?   Of course, we can insert a new row with a statement, but prepared statements seem to break.

Here's the sample code tea re starting with to write:

import java.sql.*;

import java.util.*;

import java.io.*;

public class arraytest {

  static String USERNAME="user";

  static String PWD="pass";

  public static void main(String[] argv) {

  //create a connection and a statement object to be reused in this program

  Connection connection = null;

      Statement stmt = null;

      PreparedStatement ps=null;

      java.sql.Array ar;

      ResultSet resultSet;

      int i;

      Integer[] arrint={1,5,7};

  try {                 

      connection = DriverManager.getConnection(

            "jdbc:sap://192.168.0.1:30015?autocommit=true",USERNAME,PWD);                 

        stmt=connection.createStatement();

        ps=connection.prepareStatement("INSERT INTO TEST1 (a, b) values (?, ?)");

        ps.setInt(1,100);

        ar=connection.createArrayOf("INT",arrint);

        ps.setArray(2,ar);

        i=ps.executeUpdate();

        System.err.println(i+" rows inserted");

      } catch (SQLException e) {

         System.err.println("Connection Failed. ");

         System.err.println(e);

         e.printStackTrace();        

         return;

      }  

  }

}

Mark

[lbreddemann]

Ok,given the state of the HANA JDBC programming documentation, this is what I can add here:

To retrieve the values from ARRAY columns in JDBC, the ResultSet.getArray() method is used. That works for SAP HANA JDBC driver, too.

However to INSERT/UPDATE an ARRAY column, you either need to provide a SQLScript ARRAY object (which is not available outside SQLScript or you need to use the ARRAY () function:

INSERT INTO <table> VALUES (1, ARRAY ( a1, a2, a3...) );

Personally I haven't been able to figure out how to use this in a batch statement, as simply putting a parameter (?) into the ARRAY function and using .setArray fails with a type mismatch error.

Maybe has some more ideas?