Re: FreeTDS does not work with Sybase SQL Anywhere 10, 11 or 12 -- sp_datatype_info()
"James K. Lowden" <[email protected]>
| Newsgroups | gmane.comp.db.tds.freetds |
|---|---|
| Message-ID | <[email protected]> |
On Wed, 6 Feb 2013 17:44:38 +0000 Tony Esposito <[email protected]> wrote: > 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. > 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 _______________________________________________ FreeTDS mailing list [email protected] http://lists.ibiblio.org/mailman/listinfo/freetds
tds_datatype_info.h
(application/octet-stream, 4.9 KB)
struct sp_datatype_info_t
{
struct { size_t server_version, odbc_ver; } key;
char * type_name;
short data_type;
int precision;
char * literal_prefix;
char * literal_suffix;
char * create_params;
short nullable;
short case_sensitive;
short searchable;
char * unsigned_attribute;
short money;
short auto_increment;
char * local_type_name;
short minimum_scale;
short maximum_scale;
short sql_data_type;
short sql_datetime_sub;
int num_prec_radix;
short shorterval_precision;
short usertype;
};
sp_datatype_info_t sp_datatype_info[] =
{
{{ 2005, 3}, "sql_variant", -150, 8000, NULL, NULL, NULL, 1, 0, 2, NULL, 0, NULL, "sql_variant", 0, 0, -150, NULL, 10, NULL, 0, }
, {{ 2005, 3}, "uniqueidentifier", -11, 36, "'", "'", NULL, 1, 0, 2, NULL, 0, NULL, "uniqueidentifier", NULL, NULL, -11, NULL, NULL, NULL, 0}
, {{ 2005, 3}, "ntext", -10, 1073741823, "N'", "'", NULL, 1, 0, 1, NULL, 0, NULL, "ntext", NULL, NULL, -10, NULL, NULL, NULL, 0}
, {{ 2005, 3}, "xml", -10, 1073741823, "N'", "'", NULL, 1, 1, 0, NULL, 0, NULL, "xml", NULL, NULL, -10, NULL, NULL, NULL, 0}
, {{ 2005, 3}, "nvarchar", -9, 4000, "N'", "'", "max", "length", 1, 0, 3, NULL, 0, NULL, "nvarchar", NULL, NULL, -9, NULL, NULL, NULL, 0}
, {{ 2005, 3}, "sysname", -9, 128, "N'", "'", NULL, 0, 0, 3, NULL, 0, NULL, "sysname", NULL, NULL, -9, NULL, NULL, NULL, 18}
, {{ 2005, 3}, "nchar", -8, 4000, "N'", "'", "length", 1, 0, 3, NULL, 0, NULL, "nchar", NULL, NULL, -8, NULL, NULL, NULL, 0}
, {{ 2005, 3}, "bit", -7, 1, NULL, NULL, NULL, 1, 0, 2, NULL, 0, NULL, "bit", 0, 0, -7, NULL, NULL, NULL, 16}
, {{ 2005, 3}, "tinyint", -6, 3, NULL, NULL, NULL, 1, 0, 2, 1, 0, 0, "tinyint", 0, 0, -6, NULL, 10, NULL, 5}
, {{ 2005, 3}, "tinyint identity", -6, 3, NULL, NULL, NULL, 0, 0, 2, 1, 0, 1, "tinyint identity", 0, 0, -6, NULL, 10, NULL, 5}
, {{ 2005, 3}, "bigint", -5, 19, NULL, NULL, NULL, 1, 0, 2, 0, 0, 0, "bigint", 0, 0, -5, NULL, 10, NULL, 0}
, {{ 2005, 3}, "bigint identity", -5, 19, NULL, NULL, NULL, 0, 0, 2, 0, 0, 1, "bigint identity", 0, 0, -5, NULL, 10, NULL, 0}
, {{ 2005, 3}, "image", -4, 2147483647, 0"x", NULL, NULL, 1, 0, 0, NULL, 0, NULL, "image", NULL, NULL, -4, NULL, NULL, NULL, 20}
, {{ 2005, 3}, "varbinary", -3, 8000, 0"x", NULL, "max", "length", 1, 0, 2, NULL, 0, NULL, "varbinary", NULL, NULL, -3, NULL, NULL, NULL, 4}
, {{ 2005, 3}, "binary", -2, 8000, 0"x", NULL, "length", 1, 0, 2, NULL, 0, NULL, "binary", NULL, NULL, -2, NULL, NULL, NULL, 3}
, {{ 2005, 3}, "timestamp", -2, 8, 0"x", NULL, NULL, 0, 0, 2, NULL, 0, NULL, "timestamp", NULL, NULL, -2, NULL, NULL, NULL, 80}
, {{ 2005, 3}, "text", -1, 2147483647, "'", "'", NULL, 1, 0, 1, NULL, 0, NULL, "text", NULL, NULL, -1, NULL, NULL, NULL, 19}
, {{ 2005, 3}, "char", 1, 8000, "'", "'", "length", 1, 0, 3, NULL, 0, NULL, "char", NULL, NULL, 1, NULL, NULL, NULL, 1}
, {{ 2005, 3}, "numeric", 2, 38, NULL, NULL, "precision","scale", 1, 0, 2, 0, 0, 0, "numeric", 0, 38, 2, NULL, 10, NULL, 10}
, {{ 2005, 3}, "numeric identity", 2, 38, NULL, NULL, "precision", 0, 0, 2, 0, 0, 1, "numeric identity", 0, 0, 2, NULL, 10, NULL, 10}
, {{ 2005, 3}, "decimal", 3, 38, NULL, NULL, "precision","scale", 1, 0, 2, 0, 0, 0, "decimal", 0, 38, 3, NULL, 10, NULL, 24}
, {{ 2005, 3}, "money", 3, 19, $, NULL, NULL, 1, 0, 2, 0, 1, 0, "money", 4, 4, 3, NULL, 10, NULL, 11}
, {{ 2005, 3}, "smallmoney", 3, 10, $, NULL, NULL, 1, 0, 2, 0, 1, 0, "smallmoney", 4, 4, 3, NULL, 10, NULL, 21}
, {{ 2005, 3}, "decimal identity", 3, 38, NULL, NULL, "precision", 0, 0, 2, 0, 0, 1, "decimal identity", 0, 0, 3, NULL, 10, NULL, 24}
, {{ 2005, 3}, "int", 4, 10, NULL, NULL, NULL, 1, 0, 2, 0, 0, 0, "int", 0, 0, 4, NULL, 10, NULL, 7}
, {{ 2005, 3}, "int identity", 4, 10, NULL, NULL, NULL, 0, 0, 2, 0, 0, 1, "int identity", 0, 0, 4, NULL, 10, NULL, 7}
, {{ 2005, 3}, "smallint", 5, 5, NULL, NULL, NULL, 1, 0, 2, 0, 0, 0, "smallint", 0, 0, 5, NULL, 10, NULL, 6}
, {{ 2005, 3}, "smallint identity", 5, 5, NULL, NULL, NULL, 0, 0, 2, 0, 0, 1, "smallint identity", 0, 0, 5, NULL, 10, NULL, 6}
, {{ 2005, 3}, "float", 6, 53, NULL, NULL, NULL, 1, 0, 2, 0, 0, 0, "float", NULL, NULL, 6, NULL, 2, NULL, 8}
, {{ 2005, 3}, "real", 7, 24, NULL, NULL, NULL, 1, 0, 2, 0, 0, 0, "real", NULL, NULL, 7, NULL, 2, NULL, 23}
, {{ 2005, 3}, "varchar", 12, 8000, "'", "'", "max", "length", 1, 0, 3, NULL, 0, NULL, "varchar", NULL, NULL, 12, NULL, NULL, NULL, 2}
, {{ 2005, 3}, "datetime", 93, 23, "'", "'", NULL, 1, 0, 3, NULL, 0, NULL, "datetime", 3, 3, 9, 3, NULL, NULL, 12}
, {{ 2005, 3}, "smalldatetime", 93, 16, "'", "'", NULL, 1, 0, 3, NULL, 0, NULL, "smalldatetime", 0, 0, 9, 3, NULL, NULL, 22}
};
sp_datatype_info.ms2005.txt
(text/plain, 19.7 KB)
TYPE_NAME DATA_TYPE PRECISION LITERAL_PREFIX LITERAL_SUFFIX CREATE_PARAMS NULLABLE CASE_SENSITIVE SEARCHABLE UNSIGNED_ATTRIBUTE MONEY AUTO_INCREMENT LOCAL_TYPE_NAME MINIMUM_SCALE MAXIMUM_SCALE SQL_DATA_TYPE SQL_DATETIME_SUB NUM_PREC_RADIX INTERVAL_PRECISION USERTYPE -------------------------------------------------------------------------------------------------------------------------------- --------- ----------- -------------------------------- -------------------------------- -------------------------------- -------- -------------- ---------- ------------------ ------ -------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ---------------- -------------- ------------------ -------- sql_variant -150 8000 NULL NULL NULL 1 0 2 NULL 0 NULL sql_variant 0 0 -150 NULL 10 NULL 0 uniqueidentifier -11 36 ' ' NULL 1 0 2 NULL 0 NULL uniqueidentifier NULL NULL -11 NULL NULL NULL 0 ntext -10 1073741823 N' ' NULL 1 0 1 NULL 0 NULL ntext NULL NULL -10 NULL NULL NULL 0 xml -10 1073741823 N' ' NULL 1 1 0 NULL 0 NULL xml NULL NULL -10 NULL NULL NULL 0 nvarchar -9 4000 N' ' max length 1 0 3 NULL 0 NULL nvarchar NULL NULL -9 NULL NULL NULL 0 sysname -9 128 N' ' NULL 0 0 3 NULL 0 NULL sysname NULL NULL -9 NULL NULL NULL 18 nchar -8 4000 N' ' length 1 0 3 NULL 0 NULL nchar NULL NULL -8 NULL NULL NULL 0 bit -7 1 NULL NULL NULL 1 0 2 NULL 0 NULL bit 0 0 -7 NULL NULL NULL 16 tinyint -6 3 NULL NULL NULL 1 0 2 1 0 0 tinyint 0 0 -6 NULL 10 NULL 5 tinyint identity -6 3 NULL NULL NULL 0 0 2 1 0 1 tinyint identity 0 0 -6 NULL 10 NULL 5 bigint -5 19 NULL NULL NULL 1 0 2 0 0 0 bigint 0 0 -5 NULL 10 NULL 0 bigint identity -5 19 NULL NULL NULL 0 0 2 0 0 1 bigint identity 0 0 -5 NULL 10 NULL 0 image -4 2147483647 0x NULL NULL 1 0 0 NULL 0 NULL image NULL NULL -4 NULL NULL NULL 20 varbinary -3 8000 0x NULL max length 1 0 2 NULL 0 NULL varbinary NULL NULL -3 NULL NULL NULL 4 binary -2 8000 0x NULL length 1 0 2 NULL 0 NULL binary NULL NULL -2 NULL NULL NULL 3 timestamp -2 8 0x NULL NULL 0 0 2 NULL 0 NULL timestamp NULL NULL -2 NULL NULL NULL 80 text -1 2147483647 ' ' NULL 1 0 1 NULL 0 NULL text NULL NULL -1 NULL NULL NULL 19 char 1 8000 ' ' length 1 0 3 NULL 0 NULL char NULL NULL 1 NULL NULL NULL 1 numeric 2 38 NULL NULL precision,scale 1 0 2 0 0 0 numeric 0 38 2 NULL 10 NULL 10 numeric() identity 2 38 NULL NULL precision 0 0 2 0 0 1 numeric() identity 0 0 2 NULL 10 NULL 10 decimal 3 38 NULL NULL precision,scale 1 0 2 0 0 0 decimal 0 38 3 NULL 10 NULL 24 money 3 19 $ NULL NULL 1 0 2 0 1 0 money 4 4 3 NULL 10 NULL 11 smallmoney 3 10 $ NULL NULL 1 0 2 0 1 0 smallmoney 4 4 3 NULL 10 NULL 21 decimal() identity 3 38 NULL NULL precision 0 0 2 0 0 1 decimal() identity 0 0 3 NULL 10 NULL 24 int 4 10 NULL NULL NULL 1 0 2 0 0 0 int 0 0 4 NULL 10 NULL 7 int identity 4 10 NULL NULL NULL 0 0 2 0 0 1 int identity 0 0 4 NULL 10 NULL 7 smallint 5 5 NULL NULL NULL 1 0 2 0 0 0 smallint 0 0 5 NULL 10 NULL 6 smallint identity 5 5 NULL NULL NULL 0 0 2 0 0 1 smallint identity 0 0 5 NULL 10 NULL 6 float 6 53 NULL NULL NULL 1 0 2 0 0 0 float NULL NULL 6 NULL 2 NULL 8 real 7 24 NULL NULL NULL 1 0 2 0 0 0 real NULL NULL 7 NULL 2 NULL 23 varchar 12 8000 ' ' max length 1 0 3 NULL 0 NULL varchar NULL NULL 12 NULL NULL NULL 2 datetime 93 23 ' ' NULL 1 0 3 NULL 0 NULL datetime 3 3 9 3 NULL NULL 12 smalldatetime 93 16 ' ' NULL 1 0 3 NULL 0 NULL smalldatetime 0 0 9 3 NULL NULL 22