1select NUMERIC(18,2) "123" SQL_C_NUMERIC """38 0 1 7B""" 2 3# some binary 4# 5select CHAR(7) pippo SQL_C_BINARY 706970706F2020 6select TEXT mickey SQL_C_BINARY 6D69636B6579 7select VARCHAR(20) foo SQL_C_BINARY 666F6F 8 9select TIMESTAMP "abcdefghi" SQL_C_BINARY "6162636465666768" 10 11select BINARY(5) qwer SQL_C_BINARY 7177657200 12select IMAGE cricetone SQL_C_BINARY 6372696365746F6E65 13select VARBINARY(20) teo SQL_C_BINARY 74656F 14 15select_cond bigint BIGINT 87654 SQL_C_CHAR "5 87654" 16 17if bigint 18 select_cond cond1 BIGINT 123456789012345 SQL_C_BINARY 13000179DF0D86487000000000000000000000 19 if not cond1 and bigendian 20 select BIGINT "123456789012345" SQL_C_BINARY "00007048860DDF79" 21 endif 22 if not cond1 and not bigendian 23 select BIGINT "123456789012345" SQL_C_BINARY "79DF0D8648700000" 24 endif 25endif 26 27if bigendian 28 select DATETIME "2004-02-24 15:16:17" SQL_C_BINARY "0000949700FBAA2C" 29 select SMALLDATETIME "2004-02-24 15:16:17" SQL_C_BINARY "94970394" 30 select SMALLINT "4321" SQL_C_BINARY "10E1" 31 select INT "1234567" SQL_C_BINARY "0012D687" 32 select FLOAT "1234.5678" SQL_C_BINARY "40934A456D5CFAAD" 33 select REAL "8765.4321" SQL_C_BINARY "4608F5BA" 34 select SMALLMONEY "765.4321" SQL_C_BINARY "0074CBB1" 35 select MONEY "4321234.5678" SQL_C_BINARY "0000000A0FA8114E" 36 37 select UNIQUEIDENTIFIER "0DDF3B64-E692-11D1-AB06-00AA00BDD685" SQL_C_BINARY "0DDF3B64E69211D1AB0600AA00BDD685" 38else 39 select DATETIME "2004-02-24 15:16:17" SQL_C_BINARY "979400002CAAFB00" 40 select SMALLDATETIME "2004-02-24 15:16:17" SQL_C_BINARY "97949403" 41 select SMALLINT "4321" SQL_C_BINARY "E110" 42 select INT "1234567" SQL_C_BINARY "87D61200" 43 select FLOAT "1234.5678" SQL_C_BINARY "ADFA5C6D454A9340" 44 select REAL "8765.4321" SQL_C_BINARY "BAF50846" 45 select SMALLMONEY "765.4321" SQL_C_BINARY "B1CB7400" 46 select MONEY "4321234.5678" SQL_C_BINARY "0A0000004E11A80F" 47 48 select UNIQUEIDENTIFIER "0DDF3B64-E692-11D1-AB06-00AA00BDD685" SQL_C_BINARY "643BDF0D92E6D111AB0600AA00BDD685" 49endif 50 51select BIT 1 SQL_C_BINARY 01 52select BIT 0 SQL_C_BINARY 00 53select TINYINT 231 SQL_C_BINARY E7 54 55select DECIMAL 1234.5678 SQL_C_BINARY 120001D3040000000000000000000000000000 56select NUMERIC 8765.4321 SQL_C_BINARY 1200013D220000000000000000000000000000 57 58# behavior is different from MS ODBC, Sybase does not handle N types with unicode 59if msdb 60 select NCHAR(7) "donald" SQL_C_BINARY "64006F006E0061006C0064002000" 61 select NTEXT "duck" SQL_C_BINARY "6400750063006B00" 62 select NVARCHAR(20) "daffy" SQL_C_BINARY "64006100660066007900" 63endif 64 65 66# others 67 68select INT -123 SQL_C_CHAR "4 -123" 69select INT 78654 SQL_C_WCHAR "5 78654" 70select VARCHAR(10) " 51245 " SQL_C_LONG 51245 71 72select VARCHAR(20) " 15 " SQL_C_NUMERIC "38 0 1 0F" 73select UNIVARCHAR(10) """u&'\06A4\FBA5'""" SQL_C_WCHAR """2 \u06a4\ufba5""" 74 75select VARCHAR(20) test SQL_C_WCHAR "4 test" 76 77 78# date to char 79# 80select DATETIME "2006-06-09 11:22:44" SQL_C_CHAR "23 2006-06-09 11:22:44.000" 81select DATETIME "2106-06-09 11:22:44" SQL_C_CHAR "23 2106-06-09 11:22:44.000" 82select DATETIME "2206-06-09 11:22:44" SQL_C_CHAR "23 2206-06-09 11:22:44.000" 83select DATETIME "2306-06-09 11:22:44" SQL_C_CHAR "23 2306-06-09 11:22:44.000" 84select DATETIME "3806-06-09 11:22:44" SQL_C_CHAR "23 3806-06-09 11:22:44.000" 85select SMALLDATETIME "2006-06-12 22:37:21" SQL_C_CHAR "19 2006-06-12 22:37:00" 86select DATETIME "2006-06-09 11:22:44" SQL_C_WCHAR "23 2006-06-09 11:22:44.000" 87select SMALLDATETIME "2006-06-12 22:37:21" SQL_C_WCHAR "19 2006-06-12 22:37:00" 88select DATETIME "2006-06-09 11:22:44" SQL_C_TIMESTAMP "2006-06-09 11:22:44.000" 89select SMALLDATETIME "2006-06-12 22:37:21" SQL_C_TIMESTAMP "2006-06-12 22:37:00.000" 90 91if msdb 92 sql_cond cond1 "SELECT CAST('test' AS NVARCHAR(10)) WHERE 0=1" 93 if cond1 94 # nvarchar without extended characters 95 select NVARCHAR(20) "test" SQL_C_CHAR "4 test" 96 # nvarchar with extended characters 97 # don't test with MS which usually have a not compatible encoding 98 if freetds 99 select NVARCHAR(20) 0x830068006900f200 SQL_C_CHAR "4 \x83hi\xf2" 100 endif 101 102 # nvarchar with extended characters 103 select NVARCHAR(20) "0x830068006900f200" SQL_C_WCHAR "4 \x83hi\xf2" 104 select NVARCHAR(20) "0xA406A5FB" SQL_C_WCHAR """2 \u06a4\ufba5""" 105 # NVARCHAR -> SQL_C_LONG 106 select NVARCHAR(20) "-24785 " SQL_C_LONG "-24785" 107 endif 108 # check variant existence 109 sql_cond cond1 "SELECT CAST(123 AS SQL_VARIANT) WHERE 0=1" 110 if cond1 111 select SQL_VARIANT "CAST('123' AS INT)" SQL_C_CHAR "3 123" 112 select SQL_VARIANT "CAST('hello' AS CHAR(6))" SQL_C_CHAR "6 hello " 113 select SQL_VARIANT "CAST('ciao' AS VARCHAR(10))" SQL_C_CHAR "4 ciao" 114 select SQL_VARIANT "CAST('foo' AS NVARCHAR(10))" SQL_C_CHAR "3 foo" 115 select SQL_VARIANT "CAST('Super' AS NCHAR(8))" SQL_C_CHAR "8 Super " 116 # using protocol version 7.0 server returns NVARCHAR instead of NVARBINARY so test it 117 select_cond bug SQL_VARIANT "CAST(0x330032003100 AS VARBINARY(10))" SQL_C_CHAR "3 321" 118 if not bug 119 select SQL_VARIANT "CAST(0x333231 AS VARBINARY(10))" SQL_C_CHAR "6 333231" 120 endif 121 # for some reasons MS ODBC seems to convert -123.4 to -123.40000000000001 122 select SQL_VARIANT "CAST('-123.5' AS FLOAT)" SQL_C_CHAR "6 -123.5" 123 select SQL_VARIANT "CAST('-123.4' AS NUMERIC(10,2))" SQL_C_CHAR "7 -123.40" 124 select SQL_VARIANT "CAST('0DDF3B64-E692-11D1-AB06-00AA00BDD685' AS UNIQUEIDENTIFIER)" SQL_C_CHAR "36 0DDF3B64-E692-11D1-AB06-00AA00BDD685" 125 endif 126 127 # mssql2005 varchar(max) 128 sql_cond cond1 "SELECT CAST('test' AS VARCHAR(MAX)) WHERE 0=1" 129 if cond1 130 select VARCHAR(MAX) "goodbye!" SQL_C_CHAR "8 goodbye!" 131 select NVARCHAR(MAX) "Micio mao" SQL_C_CHAR "9 Micio mao" 132 select VARBINARY(MAX) "ciao" SQL_C_BINARY "6369616F" 133 select XML """<a b="aaa"><b>ciao</b>hi</a>""" SQL_C_CHAR """28 <a b="aaa"><b>ciao</b>hi</a>""" 134 135 # XML with schema 136 sql "IF EXISTS(SELECT * FROM sys.xml_schema_collections WHERE [name] = 'test_schema') DROP XML SCHEMA COLLECTION test_schema" 137 sql """CREATE XML SCHEMA COLLECTION test_schema AS '<schema xmlns="http://www.w3.org/2001/XMLSchema"><element name="test" type="string"/></schema>'""" 138 select XML(test_schema) "<test>ciao</test>" SQL_C_CHAR "17 <test>ciao</test>" 139 sql "DROP XML SCHEMA COLLECTION test_schema" 140 endif 141 142 # mssql 2008 date/time 143 sql_cond cond1 "SELECT CAST('1923-12-02' AS DATE) WHERE 0=1" 144 if cond1 145 select DATE "1923-12-02" SQL_C_CHAR "10 1923-12-02" 146 147 select TIME "12:23:45" SQL_C_CHAR "16 12:23:45.0000000" 148 select TIME(4) "12:23:45.765" SQL_C_CHAR "13 12:23:45.7650" 149 select TIME(0) "12:23:45.765" SQL_C_CHAR "8 12:23:46" 150 151 select DATETIME2 "2011-08-10 12:23:45" SQL_C_CHAR "27 2011-08-10 12:23:45.0000000" 152 select DATETIME2(4) "12:23:45.345888" SQL_C_CHAR "24 1900-01-01 12:23:45.3459" 153 select DATETIME2(0) "2011-08-10 12:23:45.93" SQL_C_CHAR "19 2011-08-10 12:23:46" 154 155 select DATETIMEOFFSET "12:23:45 -02:30" SQL_C_CHAR "34 1900-01-01 12:23:45.0000000 -02:30" 156 select DATETIMEOFFSET(4) "12:23:45" SQL_C_CHAR "31 1900-01-01 12:23:45.0000 +00:00" 157 158 # test we are using mssql2008 protocol (7.3) 159 select_cond cond1 DATE "1923-12-02" SQL_C_BINARY "31003900320033002D00310032002D0030003200" 160 if not cond1 and bigendian 161 select DATE "1923-12-02" SQL_C_BINARY "0783000C0002" 162 select TIME "12:23:45" SQL_C_BINARY "000C0017002D000000000000" 163 select TIME(4) "12:23:45.765" SQL_C_BINARY "000C0017002D00002D98F940" 164 select DATETIME2 "2011-08-10 12:23:45" SQL_C_BINARY "07DB0008000A000C0017002D00000000" 165 select DATETIME2(4) "12:23:45" SQL_C_BINARY "076C00010001000C0017002D00000000" 166 select DATETIMEOFFSET "12:23:45 -08:30" SQL_C_BINARY "076C00010001000C0017002D00000000FFF8FFE2" 167 select DATETIMEOFFSET(4) "12:23:45" SQL_C_BINARY "076C00010001000C0017002D0000000000000000" 168 endif 169 if not cond1 and not bigendian 170 select DATE "1923-12-02" SQL_C_BINARY "83070C000200" 171 select TIME "12:23:45" SQL_C_BINARY "0C0017002D00000000000000" 172 select TIME(4) "12:23:45.765" SQL_C_BINARY "0C0017002D00000040F9982D" 173 select DATETIME2 "2011-08-10 12:23:45" SQL_C_BINARY "DB0708000A000C0017002D0000000000" 174 select DATETIME2(4) "12:23:45" SQL_C_BINARY "6C07010001000C0017002D0000000000" 175 select DATETIMEOFFSET "12:23:45 -08:30" SQL_C_BINARY "6C07010001000C0017002D0000000000F8FFE2FF" 176 select DATETIMEOFFSET(4) "12:23:45" SQL_C_BINARY "6C07010001000C0017002D000000000000000000" 177 endif 178 179 # new date/time types embedded into variant types 180 tds_version_cmp tds71p >= 7.1 181 if tds71p 182 select SQL_VARIANT "CAST('2014-04-15 20:23:56' AS DATETIME2)" SQL_C_CHAR "27 2014-04-15 20:23:56.0000000" 183 select SQL_VARIANT "CAST('2014-04-15 20:23:56' AS DATETIME2(3))" SQL_C_CHAR "23 2014-04-15 20:23:56.000" 184 select SQL_VARIANT "CAST('2014-04-15 20:23:56' AS TIME)" SQL_C_CHAR "16 20:23:56.0000000" 185 select SQL_VARIANT "CAST('2014-04-15 20:23:56' AS TIME(3))" SQL_C_CHAR "12 20:23:56.000" 186 select SQL_VARIANT "CAST('2014-04-15' AS DATE)" SQL_C_CHAR "10 2014-04-15" 187 endif 188 endif 189 190 # mssql 2008 hierarchyid 191 select HIERARCHYID "/" SQL_C_BINARY "" 192 select HIERARCHYID "/1.2/" SQL_C_BINARY "6340" 193endif 194 195# test sybase date/time types 196if not msdb 197 # FIXME sure ?? with date and time always ?? 198 sql_cond cond1 "SELECT CAST('1923-12-02' AS DATE) WHERE 0=1" 199 if cond1 200 select DATE "1923-12-02" SQL_C_CHAR "10 1923-12-02" 201 select TIME "12:23:45" SQL_C_CHAR "12 12:23:45.000" 202 select TIME "12:23:45.983" SQL_C_CHAR "12 12:23:45.983" 203 # TODO binary 204 endif 205endif 206 207if bigint and not msdb 208 select "UNSIGNED BIGINT" "11510302200549295463" SQL_C_CHAR "20 11510302200549295463" 209 select "UNSIGNED INT" "3478444555" SQL_C_CHAR "10 3478444555" 210 select "UNSIGNED SMALLINT" "65432" SQL_C_CHAR "5 65432" 211 if not bigendian 212 select "UNSIGNED BIGINT" "11510302200549295463" SQL_C_BINARY "67452301DECEBC9F" 213 select "UNSIGNED INT" "3478444555" SQL_C_BINARY "0BDA54CF" 214 select "UNSIGNED SMALLINT" "65432" SQL_C_BINARY "98FF" 215 endif 216 if bigendian 217 select "UNSIGNED BIGINT" "11510302200549295463" SQL_C_BINARY "9FBCCEDE01234567" 218 select "UNSIGNED INT" "3478444555" SQL_C_BINARY "CF54DA0B" 219 select "UNSIGNED SMALLINT" "65432" SQL_C_BINARY "FF98" 220 endif 221endif 222 223# check for Sybase big(date)time 224sql_cond cond1 "SELECT CAST('2015-10-10' AS BIGDATETIME) WHERE 0=1" 225if cond1 226 if bigendian 227 select BIGTIME "2004-02-24 15:16:17" SQL_C_BINARY "000F00100011000000000000" 228 select BIGDATETIME "2004-02-24 15:16:17" SQL_C_BINARY "07D400020018000F0010001100000000" 229 else 230 select BIGTIME "2004-02-24 15:16:17" SQL_C_BINARY "0F0010001100000000000000" 231 select BIGDATETIME "2004-02-24 15:16:17" SQL_C_BINARY "D407020018000F001000110000000000" 232 endif 233 select BIGTIME "21:51:38.73973" SQL_C_CHAR "15 21:51:38.739730" 234 select BIGDATETIME "1998-02-17 21:54:38.73973" SQL_C_CHAR "26 1998-02-17 21:54:38.739730" 235 select BIGTIME "2006-06-12 22:37:21.372" SQL_C_TIMESTAMP "1900-01-01 22:37:21.372" 236 select BIGDATETIME "2006-06-09 11:22:44" SQL_C_TIMESTAMP "2006-06-09 11:22:44.000" 237endif 238 239sql_cond cond1 "SELECT CAST('test' AS UNIVARCHAR(10)) WHERE 0=1" 240if cond1 241 select UNICHAR(7) "daffy" SQL_C_BINARY "6400610066006600790020002000" 242 select UNIVARCHAR(20) "daffy" SQL_C_BINARY "64006100660066007900" 243 select UNIVARCHAR(20) "0x830068006900f200" SQL_C_WCHAR "4 \x83hi\xf2" 244 select UNIVARCHAR(20) "0xA406A5FB" SQL_C_WCHAR """2 \u06a4\ufba5""" 245 select UNIVARCHAR(20) "4567129 " SQL_C_LONG "4567129" 246endif 247 248select VARCHAR(20) " 15.0000 " SQL_C_NUMERIC "38 0 1 0F" 249select VARCHAR(20) " 15.0000 " SQL_C_LONG "15" 250if bigint 251 select VARCHAR(20) " 13.0000 " SQL_C_SBIGINT "13" 252endif 253 254# mssql 2008 give a warning for truncation (01004) 255if freetds 256 select VARCHAR(20) " 15.1245 " SQL_C_NUMERIC "38 0 1 0F" 257 select VARCHAR(20) " 15.1234 " SQL_C_LONG "15" 258 if bigint 259 select VARCHAR(20) " 12.98 " SQL_C_SBIGINT "12" 260 endif 261endif 262