[freetds] FreeTDS does not work with Sybase SQL Anywhere 10, 11 or 12 -- sp_datatype

Discussion:

[freetds] FreeTDS does not work with Sybase SQL Anywhere 10, 11 or 12 -- sp_datatype_info()

Tony Esposito

2013-02-04 19:54:20 UTC

All,
Has anyone had a chance to look into this issue (referred to in the link below)?

http://permalink.gmane.org/gmane.comp.db.tds.freetds/14717

It appears to be the only issue stopping FreeTDS from working with Sybase SQL Anywhere 10, 11 and 12.

Thanks.

Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics

Frediano Ziglio

2013-02-06 16:15:32 UTC

Permalink

Post by Tony Esposito
All,
Has anyone had a chance to look into this issue (referred to in the link below)?
http://permalink.gmane.org/gmane.comp.db.tds.freetds/14717
It appears to be the only issue stopping FreeTDS from working with Sybase SQL Anywhere 10, 11 and 12.
Thanks.
Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics

Tony Esposito

2013-02-06 17:44:38 UTC

Permalink

Last time I looked this was the only sp that was being used that did not exist in SQL Anywhere 10-12. That's the issue -- the sp for SQLGetTypeInfo does not exist in SQL Anywhere so it needs to be replaced in FreeTDS -- i.e. FreeTDS needs to not be dependent on that sp in order for it to be used with SQL Anywhere. Just need to find out what it does.

Otherwise, FreeTDS only works with Sybase ASE. I would see this as a limitation to the product.

HTH and thanks for your efforts...

-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of Frediano Ziglio
Sent: Wednesday, February 06, 2013 10:16 AM
To: FreeTDS Development Group
Subject: Re: [freetds] FreeTDS does not work with Sybase SQL Anywhere 10, 11 or 12 -- sp_datatype_info()

Hi,
yesterday I tried to install a SQL Anywhere and see what's' the calls but it seems that ODBC version they use do not use TDS protocol but CMDSEQ (and also encrypted!).

So I where no able to understand where SQL Anywhere ODBC driver get the data for SQLGetTypeInfo.

Frediano
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.2897 / Virus Database: 2639/6081 - Release Date: 02/04/13

Frediano Ziglio

2013-02-06 18:58:47 UTC

Permalink

I hope somebody use with Microsoft SQL Server too !!

I'll see if this call lead to some traffic on network.

Frediano

Post by Tony Esposito
Last time I looked this was the only sp that was being used that did not exist in SQL Anywhere 10-12. That's the issue -- the sp for SQLGetTypeInfo does not exist in SQL Anywhere so it needs to be replaced in FreeTDS -- i.e. FreeTDS needs to not be dependent on that sp in order for it to be used with SQL Anywhere. Just need to find out what it does.
Otherwise, FreeTDS only works with Sybase ASE. I would see this as a limitation to the product.
HTH and thanks for your efforts...
-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of Frediano Ziglio
Sent: Wednesday, February 06, 2013 10:16 AM
To: FreeTDS Development Group
Subject: Re: [freetds] FreeTDS does not work with Sybase SQL Anywhere 10, 11 or 12 -- sp_datatype_info()

Hi,
yesterday I tried to install a SQL Anywhere and see what's' the calls but it seems that ODBC version they use do not use TDS protocol but CMDSEQ (and also encrypted!).
So I where no able to understand where SQL Anywhere ODBC driver get the data for SQLGetTypeInfo.
Frediano
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.2897 / Virus Database: 2639/6081 - Release Date: 02/04/13
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

Tony Esposito

2013-02-06 19:55:14 UTC

Permalink

Thanks Frediano! This would be a great help as we are trying to connect to Sybase SQL Anywhere...we use to connect to Sybase ASE and it worked.

-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of Frediano Ziglio
Sent: Wednesday, February 06, 2013 12:59 PM
To: FreeTDS Development Group
Subject: Re: [freetds] FreeTDS does not work with Sybase SQL Anywhere 10, 11 or 12 -- sp_datatype_info()

I hope somebody use with Microsoft SQL Server too !!

I'll see if this call lead to some traffic on network.

Frediano

Hi,
yesterday I tried to install a SQL Anywhere and see what's' the calls but it seems that ODBC version they use do not use TDS protocol but CMDSEQ (and also encrypted!).
So I where no able to understand where SQL Anywhere ODBC driver get the data for SQLGetTypeInfo.
Frediano
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.2897 / Virus Database: 2639/6081 - Release Date: 02/04/13
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.2897 / Virus Database: 2639/6081 - Release Date: 02/04/13

James K. Lowden

2013-02-06 21:12:20 UTC

Permalink

On Wed, 6 Feb 2013 17:44:38 +0000

Post by Tony Esposito
FreeTDS needs to not be dependent on that sp in order for it to be
used with SQL Anywhere. Just need to find out what it does.
Otherwise, FreeTDS only works with Sybase ASE.

Tony, if you're willing to do some legwork, I think we can solve this
together.

The attached file implements the resultset returned by sp_datatype_info
in C as an array of struct. The data come from SQL Server 2005. If
you extend the array for SQL Anywhere, we can reference it in
SQLGetTypeInfo().

The inputs to SQLGetTypeInfo() are:

1. server product version (implicitly, from connection)
2. datatype manifest constant
3. ODBC version

We can implement tds_datatype_info() as a binary search of the above
array using those inputs. If the function fails, we can fall back to
calling sp_datatype_info on the server. (The local binary search will
be very fast, microseconds at most.) If both fail, the function
fails.

Post by Tony Esposito
I would see this as a limitation to the product.

I would, too, if FreeTDS were a product. Since it's a project, the
only real limitation is our own time, skill, and imagination.

Regards,

--jkl
-------------- next part --------------
A non-text attachment was scrubbed...
Name: tds_datatype_info.h
Type: application/octet-stream
Size: 4973 bytes
Desc: not available
Url : http://lists.ibiblio.org/pipermail/freetds/attachments/20130206/5ff8e0ef/attachment-0001.obj
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: sp_datatype_info.ms2005.txt
Url: http://lists.ibiblio.org/pipermail/freetds/attachments/20130206/5ff8e0ef/attachment-0001.txt

Tony Esposito

2013-02-06 21:16:41 UTC

Permalink

I'd be more than happy to help us all out and to contribute toward this worthy project. No insult intended by my remarks previous about 'limitation to product'. :-)

-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of James K. Lowden
Sent: Wednesday, February 06, 2013 3:12 PM
To: freetds at lists.ibiblio.org
Subject: Re: [freetds] FreeTDS does not work with Sybase SQL Anywhere 10, 11 or 12 -- sp_datatype_info()

On Wed, 6 Feb 2013 17:44:38 +0000

Post by Tony Esposito
FreeTDS needs to not be dependent on that sp in order for it to be
used with SQL Anywhere. Just need to find out what it does.
Otherwise, FreeTDS only works with Sybase ASE.

Tony, if you're willing to do some legwork, I think we can solve this together.

The attached file implements the resultset returned by sp_datatype_info in C as an array of struct. The data come from SQL Server 2005. If you extend the array for SQL Anywhere, we can reference it in SQLGetTypeInfo().

The inputs to SQLGetTypeInfo() are:

1. server product version (implicitly, from connection) 2. datatype manifest constant 3. ODBC version

We can implement tds_datatype_info() as a binary search of the above array using those inputs. If the function fails, we can fall back to calling sp_datatype_info on the server. (The local binary search will be very fast, microseconds at most.) If both fail, the function fails.

Post by Tony Esposito
I would see this as a limitation to the product.

I would, too, if FreeTDS were a product. Since it's a project, the only real limitation is our own time, skill, and imagination.

Regards,

--jkl

-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.2897 / Virus Database: 2639/6081 - Release Date: 02/04/13

Frediano Ziglio

2013-02-06 21:44:46 UTC

Permalink

Post by James K. Lowden
On Wed, 6 Feb 2013 17:44:38 +0000

Post by Tony Esposito
FreeTDS needs to not be dependent on that sp in order for it to be
used with SQL Anywhere. Just need to find out what it does.
Otherwise, FreeTDS only works with Sybase ASE.

Tony, if you're willing to do some legwork, I think we can solve this
together.
The attached file implements the resultset returned by sp_datatype_info
in C as an array of struct. The data come from SQL Server 2005. If
you extend the array for SQL Anywhere, we can reference it in
SQLGetTypeInfo().
1. server product version (implicitly, from connection)
2. datatype manifest constant
3. ODBC version
We can implement tds_datatype_info() as a binary search of the above
array using those inputs. If the function fails, we can fall back to
calling sp_datatype_info on the server. (The local binary search will
be very fast, microseconds at most.) If both fail, the function
fails.

Post by Tony Esposito
I would see this as a limitation to the product.

I would, too, if FreeTDS were a product. Since it's a project, the
only real limitation is our own time, skill, and imagination.
Regards,
--jkl

I found this

http://www.sqlines.com/sybase-asa-to-oracle/datatypes

types seem quite similar. The "problems" are:
- DATETIMEOFFSET
- VARBIT/LONG VARBIT
- UNIQUEIDENTIFIER

Other types (even XML) have descriptions in TDS protocol.
We can do something: if SQL Anywhere is detected (who to do it??)
return a sort of fixed type (as James suggested) based also from
information from capabilities.

Frediano