SQL Queries of ESP

There are several methods of querying ESP using SQL. All SQL statements must follow these rules for dealing with the specified datatypes in ESP.

Inserting Date and Time Data

To insert date, timestamp, and bigdatetime values into an SQL statement, use the the undate, untimestamp, and unbigdatetime functions to convert the input values into the correct datatype.

insert into win
(c_key,date,timestamp,bigdatetime)
values(21478,undate('2013-02-11 17:35:45'),untimestamp('2013-02-11 17:35:45'),
unbigdatetime('2013-02-11 17:35:45'))

Specifying Money Data

When specifying the value for money data, you must append either d or D.

insert into win
(c_key,c_money)
values(111770,8.12345d)

There are fifteen money datatypes with a fixed number of digits following the decimal point: money1 with one digit, money2 with two digits, ... money15 with fifteen digits. When specifying a value for one of these datatypes, you must append either d or D and the number following money (for example d7 for money7.

insert into win
(c_key,c_money1,c_money2,c_money3,c_money4,c_money5,c_money10,c_money15)
values(111770,8.12345d1,8.12345d2,8.12345d3,8.12345d4,8.12345d5,8.123456789012345d10,8.123456789012345d15)

Reading and Writing Binary Data

To write binary data to ESP in an SQL statement, use the hex_binary() function to convert the string representation of the hexadecimal value to binary.

insert into win
(c_key,c_binary)
values(111770,hex_binary('9140ACA0361856334DD319F05'))

To read binary data from ESP in an SQL statement, use the hex_string() function to convert the binary value to a string representation of the hexadecimal value.

select c_key,hex_string(c_binary)from win