Client driver version 11.x not backward compatible with db server version 10.1.x for complex types

We are using this jar

https://mvnrepository.com/artifact/com.vertica.jdbc/vertica-jdbc/12.0.4-0

could you please share your full java program?

The code is similar to this one : https://www.vertica.com/docs/10.1.x/HTML/Content/Authoring/ConnectingToVertica/ClientJDBC/ExecutingQueriesThroughJDBC.htm?tocpath=Connecting to Vertica|Client Libraries|Programming JDBC Client Applications|_____4

I just tried to run the reproducer and yes.. using getString, you will get unknown characters in the output.. you will need to use getArray() function to get the results via JDBC driver..

https://docs.vertica.com/23.3.x/en/connecting-to/client-libraries/accessing/java/jdbc-data-types/complex-types-jdbc/

complex types support for JDBC was added in 11.0.0. Hence you need to have your db version greater than 11.0.0 to get results via JDBC driver for arrays

https://www.vertica.com/docs/11.0.x/HTML/Content/Authoring/NewFeatures/11.0/11.0.0/ClientDrivers.htm

Complete working example:

create table test_Array_jdbc(custkey INT,prodkey ARRAY[VARCHAR]);

dbadmin=> select * from test_Array_jdbc;

custkey | prodkey

---------+-----------------------

 716 | ["P997","P31","P101"]

 716 | ["P12"]

(2 rows)

dbadmin=> select version();

version

Vertica Analytic Database v11.0.2-18
(1 row)

dbadmin=>

JDBC Code:

package verticaarraytest;

import java.sql.*;
import java.sql.Array;
import java.util.Properties;

public class testjdbc {
public static void main(String[] args) {

    Properties myProp = new Properties();
    myProp.put("user", "dbadmin");
    myProp.put("password", "");
    //myProp.put("LogLevel", "TRACE");
    Connection conn;
    try {
        conn = DriverManager.getConnection(
                "jdbc:vertica://<ip>:5433/testdb", myProp);
        System.out.println("Connected!");
        String sql = "select prodkey FROM test_Array_jdbc";
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = null;
        rs = stmt.executeQuery();
        int targetColumnId = rs.findColumn ("prodkey");

        while (rs.next()) {
            java.sql.Array prodkeys = rs.getArray(targetColumnId);
            String[] pTypes = (String[]) prodkeys.getArray();
            for (int i = 0; i< pTypes.length;i++)
            {
                System.out.println(pTypes[i]+"");
            }
        }
        conn.close();
    } catch (SQLTransientConnectionException connException) {
        // There was a potentially temporary network error
        // Could automatically retry a number of times here, but
        // instead just report error and exit.
        System.out.print("Network connection issue: ");
        System.out.print(connException.getMessage());
        System.out.println(" Try again later!");
        return;
    } catch (SQLInvalidAuthorizationSpecException authException) {
        // Either the username or password was wrong
        System.out.print("Could not log into database: ");
        System.out.print(authException.getMessage());
        System.out.println(" Check the login credentials and try again.");
        return;
    } catch (SQLException e) {
        // Catch-all for other exceptions
        e.printStackTrace();
    }
}

}

Complex types are supported in 10.x and it works as per this document : https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/DataTypes/SQLDataTypes.htm?tocpath=SQL Reference Manual|SQL Data Types|_____0

We have tried using getArray and getString as per this document : https://www.vertica.com/docs/11.0.x/HTML/Content/Authoring/NewFeatures/11.0/11.0.0/ClientDrivers.htm

"To get JSON string representation of these types, call getString() on java.sql.ResultSet, or call toString() on the returned array."

@shefeekj : yes complex types was supported at server level in 10.x. JDBC client driver support was was added in 11.0. so your database version must be alteast 11.0 to use complex types feature in JDBC

Understood . But we could use 10.1.x client version to store and retrieve complex types in 10.1.x server version . It was using a json parser and we were getting it as long varchar

https://www.vertica.com/docs/11.0.x/HTML/Content/Authoring/NewFeatures/11.0/11.0.0/ClientDrivers.htm?TocPath=Vertica 11.0.x New Features and Changes|New and Changed in Vertica 11.0.0|_____3

"The Vertica JDBC client driver now supports complex types using the standard JDBC API. Previously, users needed a JSON parser for complex types, which were then treated by the JDBC driver as a LONG VARCHAR."

So there was a way in 10.1.x drivers to retrieve complex types . My question is 10.1.x driver was working fine with 10.1.x server version , however 11.x driver is not working with the same 10.1.x server . Is nt it a break in backward compatiblity ?

So in short , how do we use the same behaviour mentioned below with 11.x client driver
"Previously, users needed a JSON parser for complex types, which were then treated by the JDBC driver as a LONG VARCHAR."

it is not possible to get the behavior of 10.1.x with 11.0 and above client driver since we introduced direct support for complex types for JDBC directly. generally, when a new feature gets introduced., one needs to use new feature and new features are not backward compatible. so it is not a break in backward compatibility.

Thanks for the update .
So we cannot retrieve complex types from 10.1.x db server using client driver version 11.x or higher . Is this correct understanding ?

yes. you need to either use a 10.x client driver and use old workaround or upgrade database and use 11.x client driver and use getArray() function.

So in short , how do we use the same behaviour mentioned below with 11.x client driver

krnl download
hdstreamz.uno

@weekstonz Please see the documentation and examples to retrieve complex types with JDBC at https://docs.vertica.com/12.0.x/en/connecting-to/client-libraries/accessing/java/jdbc-data-types/complex-types-jdbc/