Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.11
-
None
-
CentOS7 x86_64 on Google Cloud Platform (standard install) with unixODBC 2.3.1 and freetds v 0.95.19
Description
Using Connect engine with the Free TDS driver a couple of the data types are not mapped correctly.
For example, columns declared as DATE on the remote MS SQL Server come back as character and remote columns declared as DOUBLE or FLOAT are coming back with zero decimal places.
Note remote DATETIME does come back correctly as TIMESTAMP in Connect, and remote NUMERIC and DECIMAL types seem to come back correctly.
In the example below, next_bill_date is a DATE on the remote MS server, discount is a FLOAT on the remote server (a percentage) and my_double is a DOUBLE on the remote MS server.
So, for example, if 0.15 is the value of the discount field, it shows in isql as 0.15 but comes back in CONNECT as 0. Likewise a double as 23.45 comes back as 23.
Remote types explicitly declared as NUMERIC or DECIMAL appear to work correctly.
show columns in ms_cust_info
|
-> ;
|
+----------------+---------------+------+-----+-------------------+-----------------------------+
|
| Field | Type | Null | Key | Default | Extra |
|
+----------------+---------------+------+-----+-------------------+-----------------------------+
|
| id | bigint(19) | NO | | NULL | | |
| company | varchar(40) | YES | | NULL | | |
| created_on | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
|
| next_bill_date | varchar(10) | YES | | NULL | | |
| discount | double(8,0) | YES | | NULL | | |
| sales | decimal(8,2) | YES | | NULL | | |
| percentage | decimal(17,3) | YES | | NULL | | |
| my_double | double(8,0) | YES | | NULL | | |
+----------------+---------------+------+-----+-------------------+-----------------------------+
|
8 rows in set (0.00 sec) |
Attachments
Activity
I'm not sure, but I can see the issue being that floats don't have a specific number of decimal digits (floating point) so the number is 0 in the CATFUNC=columns and the auto-discovery is using that number rather than just mapping to float (or double to be safe).
I will post output from the Simba Salesforece ODBC driver, which fails completely on auto-discovery but works with manually specified columns.
The following file has the output from CATFUNC=columns using the Simba Salesforece ODBC driver.
The auto-discovery just errors out with this driver, but the driver seems to work with manually specified columns.
Here is the CATFUNC output: sfout.txt
Note there are some other issues that I'll post in a different thread (ORDER BY fails on the connect table, but if the connect table is joined with a local table, then ORDER BY works properly).
This was caused by Connect not handling NULL values returned by catalog functions.
Note however that Connect only knows what is returned by the ODBC drivers and many are sometimes returning hazardous values.
Therefore, using discovery is a nice facility with limitations and it should be regarded as normal to edit the Create Table statement produced by it to met the exact requirements of the Connect table based on a remote table.
In particular, in the case of double values, you may want to specify the decimal numbers to be handled by the table.
About sorting the table, please consult the documentation about how the handle this point.
Yes Olivier, we came to the same conclusion about wild column mappings [e.g. varchar(1300) for a Fax number from a Salesforce driver] so we are using the catfunc=columns to auto-map to our own requirements and this is working great! Thank you for your assistance - much appreciated!
Output from CATFUNC=columns
+-----------+--------------+------------+----------------+-----------+-----------+-------------+---------------+----------------+-------+----------+---------+| Table_Cat | Table_Schema | Table_Name | Column_Name | Data_Type | Type_Name | Column_Size | Buffer_Length |Decimal_Digits | Radix | Nullable | Remarks |+-----------+--------------+------------+----------------+-----------+-----------+-------------+---------------+----------------+-------+----------+---------++-----------+--------------+------------+----------------+-----------+-----------+-------------+---------------+----------------+-------+----------+---------+