Using LONG VARCHAR and LONG VARBINARY data types with JDBC

Using LONG VARCHAR and LONG VARBINARY data types in a JDBC client application is similar to using VARCHAR and VARBINARY data types.

Using LONG VARCHAR and LONG VARBINARY data types in a JDBC client application is similar to using VARCHAR and VARBINARY data types. The JDBC driver transparently handles the conversion (for example, between a Java String object and a LONG VARCHAR).

The following example code demonstrates inserting and retrieving a LONG VARCHAR string. It uses the JDBC Types class to determine the data type of the string returned by Vertica, although it does not actually need to know whether the database column is a LONG VARCHAR or just a VARCHAR in order to retrieve the value.

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

public class LongVarcharExample {
    public static void main(String[] args) {
        try {
        } catch (ClassNotFoundException e) {
            System.err.println("Could not find the JDBC driver class.");
        Properties myProp = new Properties();
        myProp.put("user", "ExampleUser");
        myProp.put("password", "password123");
        Connection conn;
        try {
            conn = DriverManager.getConnection(
            // establish connection and make a table for the data.
            Statement stmt = conn.createStatement();

            // How long we want the example string to be. This is
            // larger than can fit into a traditional VARCHAR (which is limited
            // to 65000.
            int length = 100000;

            // Create a table with a LONG VARCHAR column that can store
            // the string we want to insert.
            stmt.execute("DROP TABLE IF EXISTS longtable CASCADE");
            stmt.execute("CREATE TABLE longtable (text LONG VARCHAR(" + length
                            + "))");
            // Build a long string by appending an integer to a string builder
            // until we hit the size limit. Will result in a string
            // containing 01234567890123....
            StringBuilder sb = new StringBuilder(length);
            for (int i = 0; i < length; i++)
                sb.append(i % 10);
            String value = sb.toString();

            System.out.println("String value is " + value.length() +
                            " characters long.");

            // Create the prepared statement
            PreparedStatement pstmt = conn.prepareStatement(
                            "INSERT INTO longtable (text)" +
                            " VALUES(?)");
            try {
                // Insert LONG VARCHAR value
                System.out.println("Inserting LONG VARCHAR value");
                pstmt.setString(1, value);

                // Query the table we created to get the value back.
                ResultSet rs = null;
                rs = stmt.executeQuery("SELECT * FROM longtable");

                // Get metadata about the result set.
                ResultSetMetaData rsmd = rs.getMetaData();
                // Print the type of the first column. Should be
                // LONG VARCHAR. Also check it against the Types class, to
                // recognize it programmatically.
                System.out.println("Column #1 data type is: " +
                if (rsmd.getColumnType(1) == Types.LONGVARCHAR) {
                    System.out.println("It is a LONG VARCHAR");
                } else {
                    System.out.println("It is NOT a LONG VARCHAR");

                // Print out the string length of the returned value.
                while ( {
                    // Use the same getString method to get the value that you
                    // use to get the value of a VARCHAR.
                    System.out.println("Returned string length: " +
            } catch (SQLException e) {
                System.out.println("Error message: " + e.getMessage());
                return; // Exit if there was an error
            // Cleanup
        } catch (SQLException e) {