Appendix F. VoltDB Datatype Compatibility

Documentation

VoltDB Home » Documentation » Using VoltDB
< previoustable of contentsnext >

Appendix F. VoltDB Datatype Compatibility

VoltDB supports eleven datatypes. When invoking stored procedures from different programming languages or queuing SQL statements within a Java stored procedure, you must use an appropriate language-specific value and datatype for arguments corresponding to placeholders in the query. This appendix provides the mapping of language-specific datatypes to the corresponding VoltDB datatype.

In several cases, there are multiple possible language-specific datatypes that can be used. The following tables highlight the best possible matches in bold.

F.1. Java and VoltDB Datatype Compatibility

Table F.1, “Java and VoltDB Datatype Compatibility” shows the compatible Java datatypes for each VoltDB datatype when:

  • Calling simple stored procedures defined using the CREATE PROCEDURE AS statement

  • Calling default stored procedures created for each table in the schema

In reverse, the table also shows which SQL datatype is used for the arguments and return values of user-defined functions written in Java. The highlighted Java datatype listed in the second column results in the corresponding SQL datatype being accepted or returned at runtime.

Note that when calling user-defined stored procedures written in Java, you can use additional datatypes, including arrays and the VoltTable object, as arguments to the stored procedure, as long as the actual query invocations within the stored procedure use the following datatypes. Within the stored procedure, when queuing SQL statements using the voltdbQueueSql method, implicit type casting is not guaranteed so using the highlighted Java type is recommended.

VoltDB accepts both primitive numeric types (byte, short, int, and so on) and their reference type equivalents (Byte, Short, Integer, etc.). The reference types can be useful, especially when passing null values, where you can send a Java null. In most cases when using the primitive types, you must pass the largest possible negative value for the type in place of null.

Table F.1. Java and VoltDB Datatype Compatibility

SQL DatatypeCompatible Java DatatypesNotes
TINYINT

byte/Byte
short/Short
int/Integer
long/Long
BigDecimal
String

Larger datatypes (short, int, long, and BigDecimal) are valid input types. However, VoltDB throws a runtime error if the value exceeds the allowable range of a TINYINT.

String input must be a properly formatted text representation of an integer value in the correct range.

SMALLINT

byte/Byte
short/Short
int/Integer
long/Long
BigDecimal
String

Larger datatypes (int, long, and BigDecimal) are valid input types. However, VoltDB throws a runtime error if the value exceeds the allowable range of a SMALLINT.

String input must be a properly formatted text representation of an integer value in the correct range.

INTEGER

byte/Byte
short/Short
int/Integer
long/Long
BigDecimal
String

Larger datatypes (long and BigDecimal) are valid input type. However, VoltDB throws a runtime error if the value exceeds the allowable range of an INTEGER.

String input must be a properly formatted text representation of an integer value in the correct range.

BIGINT

byte/Byte
short/Short
int/Integer
long/Long
BigDecimal
String

String input must be a properly formatted text representation of an integer value in the correct range.

FLOAT

double/Double
byte/Byte
short/Short
int/Integer
long/Long
BigDecimal
String

Because of the difference in how numbers are represented in the two types, there can be a loss of precision when using BigDecimal as input to a FLOAT value.

String input must be a properly formatted text representation of a floating point value.

DECIMAL

BigDecimal
double/Double
byte/Byte
short/Short
int/Integer
long/Long
String

String input must be a properly formatted text representation of a decimal number.

GEOGRAPHY

(none)

Geospatial input should be converted from Well Known Text (WKT) to a VoltDB native format either using the GeographyValue.fromWKT() method or by passing a String and using the POLYGONFROMTEXT function within the SQL statement.

GEOGRAPHY_POINT

(none)

Geospatial input should be converted from Well Known Text (WKT) to a VoltDB native format either using the GeographyPointValue.fromWKT() method or by passing a String and using the POINTFROMTEXT function within the SQL statement.

VARCHAR()

String
byte[]
byte/Byte
short/Short
int/Integer
long/Long
BigDecimal
VoltDB TimestampType

VoltDB stores VARCHAR text data as UTF-8 encoded strings. String and byte array input from Java programs is interpreted as UTF-8. However, for interactive input through the sqlcmd utility, VoltDB uses the character set encoding for the current user session to interpret and map the data to UTF-8 for processing. Similarly, for file input (such as CSV files) you can use the --charset qualifier to the sqlcmd or csvloader command to specify which character set to use when interpreting input.

Numeric and timestamp values are converted to their string representation. For example, the double value 13.25 is interpreted as "13.25" when converted to a VARCHAR.

VARBINARY()

String
byte[]
ByteBuffer

String input is interpreted as a hex-encoded binary value.

For ByteBuffers, the starting position and limit properties are ignored and the entire buffer is interpreted as a byte array starting in position zero.

TIMESTAMP

VoltDB TimestampType
int/Integer
long/Long
String

For String variables, the text must be formatted as either YYYY-MM-DD hh.mm.ss.nnnnnn or just the date portion YYYY-MM-DD.

< previoustable of contentsnext >