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

shefeekj

We are facing an issue with complex types . A complex type stored in the db 10.1.x server is getting corrupted while retrieving with client driver version 11.x or above
For ex : if we store [ ‘test1’,’test2’ ] , while retrieving we get something like “%nullnullnullnullnullnullnullnullnullnull-test1test2”
As per the documentation all versions of client drivers and fully backward compatible . Can you please help on this . Are we missing something .
We are trying to retrieve the data with
resultSet.getString(columnIndex)

shefeekj

We are using this jar

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

SruthiA

could you please share your full java program?

shefeekj

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

SruthiA

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/

SruthiA

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

SruthiA

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();
    }
}

}

shefeekj

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."

SruthiA

@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

shefeekj

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 ?

shefeekj

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."

SruthiA

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.

shefeekj

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 ?

SruthiA

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.

weekstonz

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

Bryan_H

@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/