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