Integer data type mapping problem with SQLInsert in DatabaseLink package

Ask Question

Asked 6 years, 6 months ago

Modified 6 years, 6 months ago

Viewed 44 times

I am working with a following SQL table named "ticket". Now let's see the data types for the columns.

SQLColumns[conn,"ticket"]/.SQLColumn[{"ticket",a_},"DataTypeName"->b_,
"DataLength"->c_,"Default"->__,"Nullable"->__]:> a-> {b,c}

{"end_time_stamp" -> {"int8", 19}, "kms" -> {"varchar", 255}}

Now when I use SQLInsert to enter the values in the columns I get no error.

SQLInsert[conn, "ticket", {"end_time_stamp", "kms"}, {20161102135641,"273-A"}]

However fetching the data from table shows wrong integer was inserted.

SQLSelect[conn, "ticket", "end_time_stamp", SQLColumn["kms"] =="273-A"]

{535656579}

where as we should get the integer 20161102135641 back. I have loaded the database driver using JDBCDrivers["PostgreSQL"].

Could not find much in the docs about this strange issue.

asked Nov 25, 2016 at 13:48

PlatoManiac

14.6k2 gold badges41 silver badges74 bronze badges

$\begingroup$ According to the postgresql documentation int8 is not a native datatype but an "extension", whatever that exactly means. For other SQL dialects int8 means an 8 bit integer, which would obviously not be able to hold the number you try to store, but int8 might be cast internally to the next larger native integer type. For other dialects int8 is an 8 byte integer. The value 19 for DataLength looks strange in any case. Are you sure that the value you are storing is OK for the datatype you are using? Can you confirm that you can store that value to the same column with other languages/tools? $\endgroup$
– Albert Retey
Nov 25, 2016 at 14:42

Add a comment |

0 Your Answer

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.