[freetds] [BUG] 0.91 SQL Anywhere SQLColumns

[Lionel Elie Mamane]

Hi,

When connecting to SQL Anywhere 10.0.1.3716, SQLColumns() returns
wrong data: the columns are not at their expected
position, don't have the right name, don't contain the right data, ...
http://msdn.microsoft.com/en-us/library/windows/desktop/ms711683(v=vs.85).aspx

E.g. column "ordinal_position" should be at position 17
but it is at position 14 and is called "column_id".
It seems the first 12 columns are correct (these are the ODBC 1.0
columns), and after that, the two extra columns are incorrect, and
most are missing.

The source code says:

/**
* SQLColumns
*
* Return column information for a table or view. This is
* mapped to a call to sp_columns which - lucky for us - returns
* the exact result set we need.
*
* exec sp_columns [ @table_name = ] object
* [ , [ @table_owner = ] owner ]
* [ , [ @table_qualifier = ] qualifier ]
* [ , [ @column_name = ] column ]
* [ , [ @ODBCVer = ] ODBCVer ]
*
*/


Well, that assumption is wrong:

1> exec sp_columns @table_name='COMPANY';
2> go
table_qualifier table_owner table_name column_name type_id type_name precision length scale radix nullable remarks ss_domain_id column_id
compta DBA COMPANY CMPY_CODE 1 char 8 8 0 NULL 0 NULL 47 1
compta DBA COMPANY LEGAL_FORM_CODE 1 char 4 4 0 NULL 0 NULL 47 2
compta DBA COMPANY CURY_CODE 1 char 3 3 0 NULL 0 NULL 47 3
compta DBA COMPANY CTRY_CODE 1 char 2 2 0 NULL 0 NULL 47 4
compta DBA COMPANY CMPY_NAME 12 varchar 250 250 0 NULL 0 NULL 39 5
compta DBA COMPANY CMPY_VAT_NUM 12 T_VAT_NUMBER 20 20 0 NULL 1 NULL 39 6
compta DBA COMPANY CMPY_VAT_ID 12 T_VAT_NUMBER 20 20 0 NULL 1 NULL 39 7
compta DBA COMPANY CMPY_TRADE_REG 12 varchar 20 20 0 NULL 1 NULL 39 8
compta DBA COMPANY CMPY_TAX_BUR 1 char 10 10 0 NULL 1 NULL 47 9
compta DBA COMPANY CMPY_VAT_DECL_VRMODE 1 char 1 1 0 NULL 1 NULL 47 10
compta DBA COMPANY ADDR_NUM 1 char 10 10 0 NULL 1 NULL 47 11
compta DBA COMPANY ADDR_STREET 12 varchar 50 50 0 NULL 1 NULL 39 12
compta DBA COMPANY ADDR_BOX 12 varchar 20 20 0 NULL 1 NULL 39 13
compta DBA COMPANY ADDR_SUPP 12 varchar 50 50 0 NULL 1 NULL 39 14
compta DBA COMPANY ADDR_ZIP_CODE 12 varchar 20 20 0 NULL 1 NULL 39 15
compta DBA COMPANY ADDR_CITY 12 varchar 35 35 0 NULL 1 NULL 39 16
compta DBA COMPANY CURY_CODE2 1 char 3 3 0 NULL 1 NULL 47 17
compta DBA COMPANY cmpy_activity 12 varchar 50 50 0 NULL 1 NULL 39 18
compta DBA COMPANY CMPY_XML_VAT_CODE 12 varchar 5 5 0 NULL 1 NULL 39 19
compta DBA COMPANY CMPY_XML_AUTH_CODE 12 varchar 30 30 0 NULL 1 NULL 39 20
compta DBA COMPANY CMPY_NUM_CETREL 12 varchar 50 50 0 NULL 1 NULL 39 21
compta DBA COMPANY CMPY_DOM_FILE_ID 2 numeric 10 10 0 10 1 NULL 63 22
compta DBA COMPANY SW_DECL_VAT_PAID 2 numeric 1 1 0 10 1 NULL 63 23
compta DBA COMPANY cmpy_debut_exercice 12 varchar 10 10 0 NULL 1 NULL 39 24
compta DBA COMPANY intrastat_declaration_type 2 numeric 1 1 0 10 1 NULL 63 25
compta DBA COMPANY CMPY_COMMENT 12 varchar 1024 1024 0 NULL 1 NULL 39 26
compta DBA COMPANY CMPY_PICTURE -4 image 32767 32767 0 NULL 1 NULL 34 27
compta DBA COMPANY CMPY_XML_ECDF_CODE 12 varchar 6 6 0 NULL 1 NULL 39 28

[James K. Lowden]

On Thu, 2 Aug 2012 10:58:45 +0200

Post by Lionel Elie Mamane
When connecting to SQL Anywhere 10.0.1.3716, SQLColumns() returns
wrong data: the columns are not at their expected
position, don't have the right name, don't contain the right data, ...
http://msdn.microsoft.com/en-us/library/windows/desktop/ms711683
(v=vs.85).aspx

IOW, the column headings returned by SQLColumns in FreeTDS don't conform
to the ODBC documentation.

Thank you for the report. I added a note to the BUGS file. The right
way to fix it is to rewrite the query in terms of INFORMATION_SCHEMA.
If someone would do that, it would go a long way toward fixing the
function.

Sybase supports INFORMATION_SCHEMA nowadays, too. How long has that
been true? Is there any reason to test a server and fall back to
sp_columns?

--jkl

[Lionel Elie Mamane]

Post by James K. Lowden
On Thu, 2 Aug 2012 10:58:45 +0200

Post by Lionel Elie Mamane
When connecting to SQL Anywhere 10.0.1.3716, SQLColumns() returns
wrong data: the columns are not at their expected
position, don't have the right name, don't contain the right data, ...
http://msdn.microsoft.com/en-us/library/windows/desktop/ms711683
(v=vs.85).aspx

IOW, the column headings returned by SQLColumns in FreeTDS don't conform
to the ODBC documentation.

That's annoying and a bug, but not extremely critical, as ODBC
applications usually access the columns by number, right?

Post by James K. Lowden
Sybase supports INFORMATION_SCHEMA nowadays, too. How long has that
been true? Is there any reason to test a server and fall back to
sp_columns?

SQL Anywhere 10 does not, or am I making something wrong?

1> SELECT * FROM INFORMATION_SCHEMA.COLUMNS;
2> go
Msg 509 (severity 11, state 0) from :
"Erreur SQL Anywhere -140 : L'ID utilisateur 'INFORMATION_SCHEMA' n'existe pas"


OTOH, the SYS schema looks interesting; the data will need some
sorting / column reordering / column renaming / post-processing, but
at least some of the information seems to be there.

[James K. Lowden]

On Thu, 2 Aug 2012 18:13:57 +0200

Post by Lionel Elie Mamane

Post by James K. Lowden
Sybase supports INFORMATION_SCHEMA nowadays, too. How long has that
been true? Is there any reason to test a server and fall back to
sp_columns?

SQL Anywhere 10 does not, or am I making something wrong?

I don't know where I got that impression. A more thorough search shows
Sybase doesn't support INFORMATION_SCHEMA.

Post by Lionel Elie Mamane
OTOH, the SYS schema looks interesting; the data will need some
sorting / column reordering / column renaming / post-processing, but
at least some of the information seems to be there.

So, yes, the query would have to be written in terms of the system
tables. I think you'll find in fact that everything you need is
there.

--jkl