Robert Dyas added a comment - 2016-02-09 23:40 isql result vs. connect result: SQL> select my_double from cust_info +-------------------------+ | my_double | +-------------------------+ | 5000.0100000000002 | | | | | +-------------------------+ SQLRowCount returns 3 3 rows fetched   MariaDB [db11003]> select my_double from ms_cust_info; +-----------+ | my_double | +-----------+ | 5000 | | NULL | | NULL | +-----------+ 3 rows in set ( 0.44 sec)

Olivier Bertrand added a comment - 2016-02-11 01:30 CONNECT tries to translate the SQL types to MariaDB types using the function: /***********************************************************************/ /* TranslateSQLType: translate a SQL Type to a PLG type. */ /***********************************************************************/ int TranslateSQLType(int stp, int prec, int& len, char& v, bool& w) { int type;   switch (stp) { case SQL_WVARCHAR: // (-9) w = true; case SQL_VARCHAR: // 12 v = 'V'; type = TYPE_STRING; break; case SQL_WCHAR: // (-8) w = true; case SQL_CHAR: // 1 type = TYPE_STRING; break; case SQL_WLONGVARCHAR: // (-10) w = true; case SQL_LONGVARCHAR: // (-1) v = 'V'; type = TYPE_STRING; len = MY_MIN(abs(len), GetConvSize()); break; case SQL_NUMERIC: // 2 case SQL_DECIMAL: // 3 // type = (prec || len > 20) ? TYPE_DOUBLE // : (len > 10) ? TYPE_BIGINT : TYPE_INT; type = TYPE_DECIM; break; case SQL_INTEGER: // 4 type = TYPE_INT; break; case SQL_SMALLINT: // 5 type = TYPE_SHORT; break; case SQL_TINYINT: // (-6) case SQL_BIT: // (-7) type = TYPE_TINY; break; case SQL_FLOAT: // 6 case SQL_REAL: // 7 case SQL_DOUBLE: // 8 type = TYPE_DOUBLE; break; case SQL_DATETIME: // 9 type = TYPE_DATE; len = 19; break; case SQL_TYPE_DATE: // 91 type = TYPE_DATE; len = 10; v = 'D'; break; case SQL_INTERVAL: // 10 case SQL_TYPE_TIME: // 92 type = TYPE_STRING; len = 8 + ((prec) ? (prec+1) : 0); v = 'T'; break; case SQL_TIMESTAMP: // 11 case SQL_TYPE_TIMESTAMP: // 93 type = TYPE_DATE; len = 19 + ((prec) ? (prec+1) : 0); v = 'S'; break; case SQL_BIGINT: // (-5) type = TYPE_BIGINT; break; case SQL_UNKNOWN_TYPE: // 0 case SQL_BINARY: // (-2) case SQL_VARBINARY: // (-3) case SQL_LONGVARBINARY: // (-4) case SQL_GUID: // (-11) default: type = TYPE_ERROR; len = 0; } // endswitch type   return type; } // end of TranslateSQLType Of course, CONNECT does not directly knows the types that have been assigned to columns, but the types returned by the ODBC driver used to access the table. For instance, if a column is of type DATE, the driver should say its type is SQL_TYPE_DATE and CONNECT will make a DATE column for it. If not, it could be a bug or just because the ODBC driver returned a wrong SQL type for it. I cannot tell what happens in your case not having the information about your source table, and not able to test with your environment and driver. Could you execute your test in ODBC test mode to check what the driver returns when asking for the column description? Note also that this occurs when creating the CONNECT table using discovery, which means not specifying the columns. In the case of wrong column definition, an easy fix is to manually define the columns with the proper type.

Robert Dyas added a comment - 2016-02-11 02:04 RE "Could you execute your test in ODBC test mode to check what the driver returns when asking for the column description?" I'd be happy to - how do I do that?

Olivier Bertrand added a comment - 2016-02-11 13:41 I know how to do it on Windows but not on Linux. However, instead of this, could you get more information by retrieving the remote table columns definition: Firstly by executing the function SQLColumns with isql and showing the result. This should show what the TDS driver returns. Then by doing it with CONNECT by creating a table based on the remote table with the additional option CATFUNC=columns. By the way, does this behaviour occur only with the TDS driver or also with other ODBC drivers? I would be interrested also to see the CREATE TABLE you use with CONNECT.

Robert Dyas added a comment - 2016-02-12 03:34 The remote table column definitions in MS SQL Server are: ID bigint not null company varchar(40) null created_on datetime null next_bill_date date null discount float null sales numeric(6,2) null percentage numeric(15,3) null my_double float null The above is from the MS SQL Server Management Studio. Note also that we have just started using the Simba Salesforce ODBC driver, and the auto discovery there also declares double(8,0) rather than double. A fix for both drivers that would be workable I think is for all floats/doubles created via auto discovery to be declared as double (rather than double(8,0)).... as one can always manually declare. Also, I think it would be much better for the default behavior of auto-discovery to simply skip and binary and unknown types, and possibly varchar columns exceeding a certain length (configurable) as they can always be manually declared... this option could be configurable via my.cnf. output from the following command is attached out.txt echo "help cust_info" | iusql -v TDS sa pass > out.txt

Robert Dyas added a comment - 2016-02-12 03:42 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 | +-----------+--------------+------------+----------------+-----------+-----------+-------------+---------------+ ----------------+-------+----------+---------+ | test1 | dbo | cust_info | id | - 5 | bigint | 19 | 8 | 0 | 10 | 0 | | | test1 | dbo | cust_info | company | 12 | varchar | 40 | 40 | 0 | 0 | 1 | | | test1 | dbo | cust_info | created_on | 11 | datetime | 23 | 16 | 3 | 0 | 1 | | | test1 | dbo | cust_info | next_bill_date | - 9 | date | 10 | 20 | 0 | 0 | 1 | | | test1 | dbo | cust_info | discount | 6 | float | 15 | 8 | 0 | 10 | 1 | | | test1 | dbo | cust_info | sales | 2 | numeric | 6 | 8 | 2 | 10 | 1 | | | test1 | dbo | cust_info | percentage | 2 | numeric | 15 | 17 | 3 | 10 | 1 | | | test1 | dbo | cust_info | my_double | 6 | float | 15 | 8 | 0 | 10 | 1 | | +-----------+--------------+------------+----------------+-----------+-----------+-------------+---------------+ ----------------+-------+----------+---------+

Robert Dyas added a comment - 2016-02-12 03:56 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.

Robert Dyas added a comment - 2016-02-12 04:15 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).

Olivier Bertrand added a comment - 2016-02-16 13:18 - edited 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.

Robert Dyas added a comment - 2016-02-17 03:06 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!