Numeric Data Alias Conversion

The Vertica server supports data type aliases for integer, float and numeric types. The JDBC driver reports these as its basic data types (BIGINT, DOUBLE PRECISION, and NUMERIC), as follows:

Vertica Server Types and Aliases Vertica JDBC Type

INTEGER

INT

INT8

BIGINT

SMALLINT

TINYINT

BIGINT

DOUBLE PRECISION

FLOAT5

FLOAT8

REAL

DOUBLE PRECISION

DECIMAL

NUMERIC

NUMBER

MONEY

NUMERIC

If a client application retrieves the values into smaller data types, Vertica JDBC driver does not check for overflows. The following example demonstrates the results of this overflow.

import java.sql.*;
import java.util.Properties;
 
public class JDBCDataTypes {
    public static void main(String[] args) {
    	// If running under a Java 5 JVM, use you need to load the JDBC driver
    	// using Class.forname here
    	
        Properties myProp = new Properties();
        myProp.put("user", "ExampleUser");
        myProp.put("password", "password123");
        Connection conn;
        try {
            conn = DriverManager.getConnection(
                            "jdbc:vertica://VerticaHost:5433/VMart",
                             myProp);
            Statement statement = conn.createStatement();
            // Create a table that will hold a row of different types of
            // numeric data.
            statement.executeUpdate(
            		"DROP TABLE IF EXISTS test_all_types cascade");
            statement.executeUpdate("CREATE TABLE test_all_types ("
                            + "c0 INTEGER, c1 TINYINT, c2 DECIMAL, "
                            + "c3 MONEY, c4 DOUBLE PRECISION, c5 REAL)");
            // Add a row of values to it.
            statement.executeUpdate("INSERT INTO test_all_types VALUES("
                            + "111111111111, 444, 55555555555.5555, "
                            + "77777777.77,  88888888888888888.88, " 
                            + "10101010.10101010101010)");
            // Query the new table to get the row back as a result set.
            ResultSet rs = statement
                            .executeQuery("SELECT * FROM test_all_types");
            // Get the metadata about the row, including its data type.
            ResultSetMetaData md = rs.getMetaData();
            // Loop should only run once...
            while (rs.next()) {
                // Print out the data type used to defined the column, followed
                // by the values retrieved using several different retrieval
                // methods.
            	
            	String[] vertTypes = new String[] {"INTEGER", "TINYINT",
            			 "DECIMAL", "MONEY", "DOUBLE PRECISION", "REAL"};
            	
            	for (int x=1; x<7; x++) { 
            		System.out.println("\n\nColumn " + x + " (" + vertTypes[x-1]
            				+ ")");
            		System.out.println("\tgetColumnType()\t\t"
                            + md.getColumnType(x));
            		System.out.println("\tgetColumnTypeName()\t"
                            + md.getColumnTypeName(x));
                    System.out.println("\tgetShort()\t\t"
                            + rs.getShort(x)); 
                    System.out.println("\tgetLong()\t\t" + rs.getLong(x));
                    System.out.println("\tgetInt()\t\t" + rs.getInt(x));
                    System.out.println("\tgetByte()\t\t" + rs.getByte(x));                    
            	}
            }
            rs.close();
            statement.executeUpdate("drop table test_all_types cascade");
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The above example prints the following on the console when run:

Column 1 (INTEGER)
       getColumnType()		-5
	getColumnTypeName()	BIGINT
	getShort()		455
	getLong()		111111111111
	getInt()		-558038585
	getByte()		-57
Column 2 (TINYINT)
	getColumnType()		-5
	getColumnTypeName()	BIGINT
	getShort()		444
	getLong()		444
	getInt()		444
	getByte()		-68
Column 3 (DECIMAL)
	getColumnType()		2
	getColumnTypeName()	NUMERIC
	getShort()		-1
	getLong()		55555555555
	getInt()		2147483647
	getByte()		-1
Column 4 (MONEY)
	getColumnType()		2
	getColumnTypeName()	NUMERIC
	getShort()		-13455
	getLong()		77777777
	getInt()		77777777
	getByte()		113
Column 5 (DOUBLE PRECISION)
	getColumnType()		8
	getColumnTypeName()	DOUBLE PRECISION
	getShort()		-1
	getLong()		88888888888888900
	getInt()		2147483647
	getByte()		-1
Column 6 (REAL)
	getColumnType()		8
	getColumnTypeName()	DOUBLE PRECISION
	getShort()		8466
	getLong()		10101010
	getInt()		10101010
	getByte()		18