Discussion:
[freetds] FreeTDS does not work with Sybase SQL Anywhere 10, 11 or 12
Tony Esposito
2012-10-08 18:49:03 UTC
Permalink
Hello all,
Not sure everyone got this thread. If you did, my apologies for the duplicate email.

It appears that the FreeTDS ODBC drivers are converting the SQLGetTypeInfo ODBC call to the Sybase stored procedure sp_datatype_info.
That's ok when running against Sybase Adaptive Server Enterprise but the drivers will fail against Sybase SQL Anywhere because the stored procedure sp_datatype_info has been removed/deprecated.

See trace file from Oracle ODBC gateway below:

Entered hgopoer at 2012/10/02-12:03:44
hgopoer, line 231: got native error 504 and sqlstate 52W09; message follows...
[FreeTDS][SQL Server]SQL Anywhere Error -265: Procedure 'sp_datatype_info' not found {52W09,NativeErr = 504}
Exiting hgopoer, rc=0 at 2012/10/02-12:03:44
hgoulcp, line 1246: calling SQLGetTypeInfo got sqlstate 52W09
Exiting hgoulcp, rc=28500 at 2012/10/02-12:03:44 with error ptr FILE:hgoulcp.c LINE:1246 FUNCTION:hgoulcp() ID:GetTypeInfo: varchar
hostmstr: 0: HOA After hoaulcp

Since there seems to be an issue with FreeTDS using an unsupported stored procedure (in Sybase SQL Anywhere), I thought it might be helpful to list those
stored procedures no longer available/supported in Sybase SQL Anywhere 12.

Support for all the store procedures below has been removed from Sybase SQL Anywhere 12:

Procedure name

Procedure name

sp_addalias

sp_helpindex

sp_addauditrecord

sp_helpjoins

sp_addlanguage

sp_helpkey

sp_addremotelogin

sp_helplanguage

sp_addsegment

sp_helplog

sp_addserver

sp_helpremotelogin

sp_addthreshold

sp_helpprotect

sp_adddumpdevice

sp_helpsegment

sp_auditdatabase

sp_helpserver

sp_auditlogin

sp_helpsort

sp_auditobject

sp_helpthreshold

sp_auditoption

sp_helpuser

sp_auditsproc

sp_indsuspect

sp_bindefault

sp_lock

sp_bindmsg

sp_locklogin

sp_bindrule

sp_logdevice

sp_changedbowner

sp_modifylogin

sp_checknames

sp_modifythreshold

sp_checkreswords

sp_monitor

sp_clearstats

sp_placeobject

sp_commonkey

sp_primarykey

sp_configure

sp_procxmode

sp_cursorinfo

sp_recompile

sp_dboption

sp_remap

sp_dbremap

sp_remoteoption

sp_depends

sp_rename

sp_diskdefault

sp_renamedb

sp_displaylogin

sp_reportstats

sp_dropalias

sp_role

sp_dropdevice

sp_serveroption

sp_dropkey

sp_setlangalias

sp_droplanguage

sp_spaceused

sp_dropremotelogin

sp_syntax

sp_dropsegment

sp_unbindefault

sp_dropserver

sp_unbindmsg

sp_dropthreshold

sp_unbindrule

sp_estspace

sp_volchanged

sp_extendsegment

sp_who

sp_foreignkey

sp_column_privileges

sp_help

sp_databases

sp_helpconstraint

sp_datatype_info

sp_helpdb

sp_server_info

sp_helpdevice

sp_table_privileges

sp_helpgroup


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
2012-10-08 20:17:26 UTC
Permalink
Post by Tony Esposito
Hello all,
Not sure everyone got this thread. If you did, my apologies for the duplicate email.
It appears that the FreeTDS ODBC drivers are converting the SQLGetTypeInfo ODBC call to the Sybase stored procedure sp_datatype_info.
That's ok when running against Sybase Adaptive Server Enterprise but the drivers will fail against Sybase SQL Anywhere because the stored procedure sp_datatype_info has been removed/deprecated.
Entered hgopoer at 2012/10/02-12:03:44
hgopoer, line 231: got native error 504 and sqlstate 52W09; message follows...
[FreeTDS][SQL Server]SQL Anywhere Error -265: Procedure 'sp_datatype_info' not found {52W09,NativeErr = 504}
Exiting hgopoer, rc=0 at 2012/10/02-12:03:44
hgoulcp, line 1246: calling SQLGetTypeInfo got sqlstate 52W09
Exiting hgoulcp, rc=28500 at 2012/10/02-12:03:44 with error ptr FILE:hgoulcp.c LINE:1246 FUNCTION:hgoulcp() ID:GetTypeInfo: varchar
hostmstr: 0: HOA After hoaulcp
Since there seems to be an issue with FreeTDS using an unsupported stored procedure (in Sybase SQL Anywhere), I thought it might be helpful to list those
stored procedures no longer available/supported in Sybase SQL Anywhere 12.
Procedure name
Procedure name
....

Thanks for the list.

It seems that the only one we use that they removed is
sp_datatype_info. Do they say which should replace this store
procedure? Or a view or whatever ?? This would help a lot.

Regards,
Frediano
Tony Esposito
2012-10-08 20:29:36 UTC
Permalink
I have queried Sybase about your request (What replaces sp_datatype_info?). No word yet ...

-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of Frediano Ziglio
Sent: Monday, October 08, 2012 3:17 PM
To: FreeTDS Development Group
Subject: Re: [freetds] FreeTDS does not work with Sybase SQL Anywhere 10, 11 or 12
Post by Tony Esposito
Hello all,
Not sure everyone got this thread. If you did, my apologies for the duplicate email.
It appears that the FreeTDS ODBC drivers are converting the SQLGetTypeInfo ODBC call to the Sybase stored procedure sp_datatype_info.
That's ok when running against Sybase Adaptive Server Enterprise but the drivers will fail against Sybase SQL Anywhere because the stored procedure sp_datatype_info has been removed/deprecated.
Entered hgopoer at 2012/10/02-12:03:44 hgopoer, line 231: got native
error 504 and sqlstate 52W09; message follows...
[FreeTDS][SQL Server]SQL Anywhere Error -265: Procedure
'sp_datatype_info' not found {52W09,NativeErr = 504} Exiting hgopoer,
rc=0 at 2012/10/02-12:03:44 hgoulcp, line 1246: calling SQLGetTypeInfo
got sqlstate 52W09 Exiting hgoulcp, rc=28500 at 2012/10/02-12:03:44
with error ptr FILE:hgoulcp.c LINE:1246 FUNCTION:hgoulcp()
ID:GetTypeInfo: varchar
hostmstr: 0: HOA After hoaulcp
Since there seems to be an issue with FreeTDS using an
unsupported stored procedure (in Sybase SQL Anywhere), I thought it might be helpful to list those stored procedures no longer available/supported in Sybase SQL Anywhere 12.
Procedure name
Procedure name
....

Thanks for the list.

It seems that the only one we use that they removed is sp_datatype_info. Do they say which should replace this store procedure? Or a view or whatever ?? This would help a lot.

Regards,
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: 2012.0.2221 / Virus Database: 2441/5317 - Release Date: 10/08/12
Tony Esposito
2012-10-10 16:18:44 UTC
Permalink
No word from Sybase about a replacement stored procedure.



Below is a description of what the stored procedure does (as per the Sybase documentation at http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00170.1540/doc/html/san1283555043391.html ):
sp_datatype_info
Description
Returns information about a particular ODBC datatype or about all ODBC datatypes.
Syntax
sp_datatype_info [data_type]
Parameters
data_type
is the code number for the specified ODBC datatype about which information is returned. Datatype codes are listed in Table 2-2<http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36273.1550/html/sprocs/CIHHGDBC.htm#X70260> and Table 2-3<http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36273.1550/html/sprocs/CIHHGDBC.htm#X81537>.
Usage

* The results set for sp_datatype_info is:
Column

Datatype

Description

type_name

varchar(30)

A DBMS-dependent datatype name (the same as the type_name column in the sp_columns results set).

data_type

smallint

A code for the ODBC type to which all columns of this type are mapped.

precision

int

The maximum precision for the datatype on the data source. Zero is returned for datatypes where precision is not applicable.

literal_prefix

varchar(32)

Character(s) used to prefix a literal. For example, a single quotation mark (') for character types and 0x for binary.

literal_suffix

varchar(32)

Character(s) used to terminate a literal. For example, a single quotation mark (') for character types and nothing for binary.

create_params

varchar(32)

A description of the creation parameters for this datatype.

nullable

smallint

The value 1 means this datatype can be created allowing null values; 0 means it cannot.

case_sensitive

smallint

The value 1 means all columns of this type are case sensitive (for collations); 0 means they are not.

searchable

smallint

The value 1 means columns of this type can be used in a where clause.

unsigned_attribute

smallint

The value 1 means the datatype is unsigned; 0 means the datatype is signed.

money

smallint

The value 1 means it is a money datatype; 0 means it is not.

auto_increment

smallint

The value 1 means the datatype is automatically incremented; 0 means it is not.

local_type_name

varchar(128)

Localized version of the data source dependent name of the datatype.

Permissions
Any user can execute sp_datatype_info.
+++++++++++++++++++++++
HTH

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



-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of Frediano Ziglio
Sent: Monday, October 08, 2012 3:17 PM
To: FreeTDS Development Group
Subject: Re: [freetds] FreeTDS does not work with Sybase SQL Anywhere 10, 11 or 12
Post by Tony Esposito
Hello all,
Not sure everyone got this thread. If you did, my apologies for the duplicate email.
It appears that the FreeTDS ODBC drivers are converting the SQLGetTypeInfo ODBC call to the Sybase stored procedure sp_datatype_info.
That's ok when running against Sybase Adaptive Server Enterprise but the drivers will fail against Sybase SQL Anywhere because the stored procedure sp_datatype_info has been removed/deprecated.
Entered hgopoer at 2012/10/02-12:03:44 hgopoer, line 231: got native
error 504 and sqlstate 52W09; message follows...
[FreeTDS][SQL Server]SQL Anywhere Error -265: Procedure
'sp_datatype_info' not found {52W09,NativeErr = 504} Exiting hgopoer,
rc=0 at 2012/10/02-12:03:44 hgoulcp, line 1246: calling SQLGetTypeInfo
got sqlstate 52W09 Exiting hgoulcp, rc=28500 at 2012/10/02-12:03:44
with error ptr FILE:hgoulcp.c LINE:1246 FUNCTION:hgoulcp()
ID:GetTypeInfo: varchar
hostmstr: 0: HOA After hoaulcp
Since there seems to be an issue with FreeTDS using an
unsupported stored procedure (in Sybase SQL Anywhere), I thought it might be helpful to list those stored procedures no longer available/supported in Sybase SQL Anywhere 12.
Procedure name
Procedure name
....



Thanks for the list.



It seems that the only one we use that they removed is sp_datatype_info. Do they say which should replace this store procedure? Or a view or whatever ?? This would help a lot.



Regards,

Frediano

_______________________________________________

FreeTDS mailing list

FreeTDS at lists.ibiblio.org<mailto:FreeTDS at lists.ibiblio.org>

http://lists.ibiblio.org/mailman/listinfo/freetds





-----

No virus found in this message.

Checked by AVG - www.avg.com<http://www.avg.com>

Version: 2012.0.2221 / Virus Database: 2441/5317 - Release Date: 10/08/12
James K. Lowden
2012-10-09 02:29:40 UTC
Permalink
On Mon, 8 Oct 2012 18:49:03 +0000
Post by Tony Esposito
It appears that the FreeTDS ODBC drivers are converting the
SQLGetTypeInfo ODBC call to the Sybase stored procedure
sp_datatype_info. That's ok when running against Sybase Adaptive
Server Enterprise but the drivers will fail against Sybase SQL
Anywhere because the stored procedure sp_datatype_info has been
removed/deprecated.
I wonder how many customers were happy about that?

Patches welcome. The query below would be a start.

--jkl

http://infocenter.sybase.com/help/topic/com.sybase.help.ase_15.0.sprocs/html/sprocs/sprocs229.htm
http://manuals.sybase.com:80/onlinebooks/group-pbarc/conn5/sqlug/@Generic__BookTextView/71268;pt=69960#X
http://manuals.sybase.com:80/onlinebooks/group-pbarc/conn5/sqlug/@Generic__BookTextView/69960;pt=69960#X

select domain_name as type_name
, type_id as data_type
, precision as precision
, cast(NULL as varchar(32)) as literal_prefix
, cast(NULL as varchar(32)) as literal_suffix
, cast(NULL as varchar(32)) as create_params
, cast(NULL as smallint) as nullable
, cast(NULL as smallint) as case_sensitive
, cast(NULL as smallint) as searchable
, cast(NULL as smallint) as unsigned_attribute
, cast(NULL as smallint) as money
, cast(NULL as smallint) as auto_increment
, cast(NULL as smallint) as local_type_name
from master.dbo.SYSDOMAIN
UNION
select type_name as type_name
, type_id as data_type
, width as precision
, cast(NULL as varchar(32)) as literal_prefix
, cast(NULL as varchar(32)) as literal_suffix
, cast(NULL as varchar(32)) as create_params
, nulls as nullable
, cast(NULL as smallint) as case_sensitive
, cast(NULL as smallint) as searchable
, cast(NULL as smallint) as unsigned_attribute
, cast(NULL as smallint) as money
, cast(NULL as smallint) as auto_increment
, cast(NULL as smallint) as local_type_name
from master.dbo.SYSUSERTYPE
Frediano Ziglio
2012-10-09 17:30:28 UTC
Permalink
Post by James K. Lowden
On Mon, 8 Oct 2012 18:49:03 +0000
Post by Tony Esposito
It appears that the FreeTDS ODBC drivers are converting the
SQLGetTypeInfo ODBC call to the Sybase stored procedure
sp_datatype_info. That's ok when running against Sybase Adaptive
Server Enterprise but the drivers will fail against Sybase SQL
Anywhere because the stored procedure sp_datatype_info has been
removed/deprecated.
I wonder how many customers were happy about that?
Patches welcome. The query below would be a start.
Tony, I think that a

select * from master.dbo.sysdomain

and a

select * from master.dbo.sysusertype

would be helpful

Frediano
Post by James K. Lowden
--jkl
http://infocenter.sybase.com/help/topic/com.sybase.help.ase_15.0.sprocs/html/sprocs/sprocs229.htm
select domain_name as type_name
, type_id as data_type
, precision as precision
, cast(NULL as varchar(32)) as literal_prefix
, cast(NULL as varchar(32)) as literal_suffix
, cast(NULL as varchar(32)) as create_params
, cast(NULL as smallint) as nullable
, cast(NULL as smallint) as case_sensitive
, cast(NULL as smallint) as searchable
, cast(NULL as smallint) as unsigned_attribute
, cast(NULL as smallint) as money
, cast(NULL as smallint) as auto_increment
, cast(NULL as smallint) as local_type_name
from master.dbo.SYSDOMAIN
UNION
select type_name as type_name
, type_id as data_type
, width as precision
, cast(NULL as varchar(32)) as literal_prefix
, cast(NULL as varchar(32)) as literal_suffix
, cast(NULL as varchar(32)) as create_params
, nulls as nullable
, cast(NULL as smallint) as case_sensitive
, cast(NULL as smallint) as searchable
, cast(NULL as smallint) as unsigned_attribute
, cast(NULL as smallint) as money
, cast(NULL as smallint) as auto_increment
, cast(NULL as smallint) as local_type_name
from master.dbo.SYSUSERTYPE
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
Loading...