Integer data type mapping problem with SQLInsert in DatabaseLink package



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

"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"]


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.


Posted 2016-11-25T13:48:12.763

Reputation: 13 888

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? – Albert Retey – 2016-11-25T14:42:00.080

No answers