How to specify the number of precision digits for columns using DatabaseLink functions and options

1

Some databases (e.g. MySQL) does not store decimal places for seconds unless the number of digits needed is directly specified (e.g. DATETIME(4) for 4 digits) when creating the table in the database. Using SQLCreateTable the number of characters in a VarChar column is specified using "DataLength"->k for a SQL interpretation of VARCHAR(k). I would like to know if there is a similar option to "DataLength" that would work to specify the number of precision digits required for the seconds. Similarly, REAL, NUMERIC, and other data types share analog problems to specify the number of precision digits.

Ariel Sepulveda

Posted 2015-06-05T00:01:46.070

Reputation: 1 617

@ilian: That solution also worked for NUMERIC data type. So, apparently "DataLength" would work to supply a second parameter to data types. So your comment seems to be the answer. Thanks. – Ariel Sepulveda – 2015-06-05T02:43:14.930

Comment reposted as an answer. – ilian – 2015-06-05T14:17:24.880

@ilian: Apparently there is still the need of a second parameter. For example, to get DECIMAL(5,2) in MySQL "DataLength"->{5,2} does not work. – Ariel Sepulveda – 2015-06-06T14:53:07.570

Yes, it only seems to accept integers. Perhaps use SQLExecute with your desired CREATE TABLE statement?

– ilian – 2015-06-06T15:17:06.757

Answers

1

Yes, the value of the DataLength option will get appended to any type.

For example, to obtain DATETIME(4) just set the SQLColumn options "DataTypeName" -> "DATETIME" and "DataLength" -> 4.

ilian

Posted 2015-06-05T00:01:46.070

Reputation: 24 492