select NUMERIC(18,2) "123" SQL_C_NUMERIC """38 0 1 7B"""

# some binary
#
select CHAR(7)     pippo  SQL_C_BINARY 706970706F2020
select TEXT        mickey SQL_C_BINARY 6D69636B6579
select VARCHAR(20) foo    SQL_C_BINARY 666F6F

select TIMESTAMP "abcdefghi" SQL_C_BINARY "6162636465666768"

select BINARY(5)     qwer      SQL_C_BINARY 7177657200
select IMAGE         cricetone SQL_C_BINARY 6372696365746F6E65
select VARBINARY(20) teo       SQL_C_BINARY 74656F

select_cond bigint BIGINT 87654 SQL_C_CHAR "5 87654"

if bigint
	select_cond cond1 BIGINT 123456789012345 SQL_C_BINARY 13000179DF0D86487000000000000000000000
	if not cond1 and bigendian
		select BIGINT "123456789012345" SQL_C_BINARY "00007048860DDF79"
	endif
	if not cond1 and not bigendian
		select BIGINT "123456789012345" SQL_C_BINARY "79DF0D8648700000"
	endif
endif

if bigendian
	select DATETIME      "2004-02-24 15:16:17" SQL_C_BINARY "0000949700FBAA2C"
	select SMALLDATETIME "2004-02-24 15:16:17" SQL_C_BINARY "94970394"
	select SMALLINT "4321"    SQL_C_BINARY "10E1"
	select INT      "1234567" SQL_C_BINARY "0012D687"
	select FLOAT "1234.5678" SQL_C_BINARY "40934A456D5CFAAD"
	select REAL  "8765.4321" SQL_C_BINARY "4608F5BA"
	select SMALLMONEY "765.4321"     SQL_C_BINARY "0074CBB1"
	select MONEY      "4321234.5678" SQL_C_BINARY "0000000A0FA8114E"

	select UNIQUEIDENTIFIER "0DDF3B64-E692-11D1-AB06-00AA00BDD685" SQL_C_BINARY "0DDF3B64E69211D1AB0600AA00BDD685"
else
	select DATETIME      "2004-02-24 15:16:17" SQL_C_BINARY "979400002CAAFB00"
	select SMALLDATETIME "2004-02-24 15:16:17" SQL_C_BINARY "97949403"
	select SMALLINT "4321"    SQL_C_BINARY "E110"
	select INT      "1234567" SQL_C_BINARY "87D61200"
	select FLOAT "1234.5678" SQL_C_BINARY "ADFA5C6D454A9340"
	select REAL  "8765.4321" SQL_C_BINARY "BAF50846"
	select SMALLMONEY "765.4321"     SQL_C_BINARY "B1CB7400"
	select MONEY      "4321234.5678" SQL_C_BINARY "0A0000004E11A80F"

	select UNIQUEIDENTIFIER "0DDF3B64-E692-11D1-AB06-00AA00BDD685" SQL_C_BINARY "643BDF0D92E6D111AB0600AA00BDD685"
endif

select BIT 1 SQL_C_BINARY 01
select BIT 0 SQL_C_BINARY 00
select TINYINT 231 SQL_C_BINARY E7

select DECIMAL 1234.5678 SQL_C_BINARY 120001D3040000000000000000000000000000
select NUMERIC 8765.4321 SQL_C_BINARY 1200013D220000000000000000000000000000

# behavior is different from MS ODBC, Sybase does not handle N types with unicode
if msdb
	select NCHAR(7)     "donald" SQL_C_BINARY "64006F006E0061006C0064002000"
	select NTEXT        "duck"   SQL_C_BINARY "6400750063006B00"
	select NVARCHAR(20) "daffy"  SQL_C_BINARY "64006100660066007900"
endif


# others

select INT -123  SQL_C_CHAR "4 -123"
select INT 78654 SQL_C_WCHAR "5 78654"
select VARCHAR(10) "  51245  " SQL_C_LONG 51245

select VARCHAR(20) "  15  " SQL_C_NUMERIC "38 0 1 0F"
select UNIVARCHAR(10) """u&'\06A4\FBA5'""" SQL_C_WCHAR """2 \u06a4\ufba5"""

select VARCHAR(20) test SQL_C_WCHAR "4 test"


# date to char
#
select DATETIME      "2006-06-09 11:22:44" SQL_C_CHAR  "23 2006-06-09 11:22:44.000"
select DATETIME      "2106-06-09 11:22:44" SQL_C_CHAR  "23 2106-06-09 11:22:44.000"
select DATETIME      "2206-06-09 11:22:44" SQL_C_CHAR  "23 2206-06-09 11:22:44.000"
select DATETIME      "2306-06-09 11:22:44" SQL_C_CHAR  "23 2306-06-09 11:22:44.000"
select DATETIME      "3806-06-09 11:22:44" SQL_C_CHAR  "23 3806-06-09 11:22:44.000"
select SMALLDATETIME "2006-06-12 22:37:21" SQL_C_CHAR  "19 2006-06-12 22:37:00"
select DATETIME      "2006-06-09 11:22:44" SQL_C_WCHAR "23 2006-06-09 11:22:44.000"
select SMALLDATETIME "2006-06-12 22:37:21" SQL_C_WCHAR "19 2006-06-12 22:37:00"
select DATETIME      "2006-06-09 11:22:44" SQL_C_TIMESTAMP "2006-06-09 11:22:44.000"
select SMALLDATETIME "2006-06-12 22:37:21" SQL_C_TIMESTAMP "2006-06-12 22:37:00.000"

if msdb
	sql_cond cond1 "SELECT CAST('test' AS NVARCHAR(10)) WHERE 0=1"
	if cond1
		# nvarchar without extended characters
		select NVARCHAR(20) "test" SQL_C_CHAR "4 test"
		# nvarchar with extended characters
		# don't test with MS which usually have a not compatible encoding
		if freetds
			select NVARCHAR(20) 0x830068006900f200 SQL_C_CHAR "4 \x83hi\xf2"
		endif

		# nvarchar with extended characters
		select NVARCHAR(20) "0x830068006900f200" SQL_C_WCHAR "4 \x83hi\xf2"
		select NVARCHAR(20) "0xA406A5FB" SQL_C_WCHAR """2 \u06a4\ufba5"""
		# NVARCHAR -> SQL_C_LONG
		select NVARCHAR(20) "-24785  " SQL_C_LONG "-24785"
	endif
	# check variant existence
	sql_cond cond1 "SELECT CAST(123 AS SQL_VARIANT) WHERE 0=1"
	if cond1
		select SQL_VARIANT "CAST('123' AS INT)" SQL_C_CHAR "3 123"
		select SQL_VARIANT "CAST('hello' AS CHAR(6))" SQL_C_CHAR "6 hello "
		select SQL_VARIANT "CAST('ciao' AS VARCHAR(10))" SQL_C_CHAR "4 ciao"
		select SQL_VARIANT "CAST('foo' AS NVARCHAR(10))" SQL_C_CHAR "3 foo"
		select SQL_VARIANT "CAST('Super' AS NCHAR(8))" SQL_C_CHAR "8 Super   "
		# using protocol version 7.0 server returns NVARCHAR instead of NVARBINARY so test it
		select_cond bug SQL_VARIANT "CAST(0x330032003100 AS VARBINARY(10))" SQL_C_CHAR "3 321"
		if not bug
			select SQL_VARIANT "CAST(0x333231 AS VARBINARY(10))" SQL_C_CHAR "6 333231"
		endif
		# for some reasons MS ODBC seems to convert -123.4 to -123.40000000000001
		select SQL_VARIANT "CAST('-123.5' AS FLOAT)" SQL_C_CHAR "6 -123.5"
		select SQL_VARIANT "CAST('-123.4' AS NUMERIC(10,2))" SQL_C_CHAR "7 -123.40"
		select SQL_VARIANT "CAST('0DDF3B64-E692-11D1-AB06-00AA00BDD685' AS UNIQUEIDENTIFIER)" SQL_C_CHAR "36 0DDF3B64-E692-11D1-AB06-00AA00BDD685"
	endif

	# mssql2005 varchar(max)
	sql_cond cond1 "SELECT CAST('test' AS VARCHAR(MAX)) WHERE 0=1"
	if cond1
		select VARCHAR(MAX)   "goodbye!"  SQL_C_CHAR   "8 goodbye!"
		select NVARCHAR(MAX)  "Micio mao" SQL_C_CHAR   "9 Micio mao"
		select VARBINARY(MAX) "ciao"      SQL_C_BINARY "6369616F"
		select XML """<a b="aaa"><b>ciao</b>hi</a>""" SQL_C_CHAR """28 <a b="aaa"><b>ciao</b>hi</a>"""

		# XML with schema
		sql "IF EXISTS(SELECT * FROM sys.xml_schema_collections WHERE [name] = 'test_schema') DROP XML SCHEMA COLLECTION test_schema"
		sql """CREATE XML SCHEMA COLLECTION test_schema AS '<schema xmlns="http://www.w3.org/2001/XMLSchema"><element name="test" type="string"/></schema>'"""
		select XML(test_schema) "<test>ciao</test>" SQL_C_CHAR "17 <test>ciao</test>"
		sql "DROP XML SCHEMA COLLECTION test_schema"
	endif

	# mssql 2008 date/time
	sql_cond cond1 "SELECT CAST('1923-12-02' AS DATE) WHERE 0=1"
	if cond1
		select DATE "1923-12-02" SQL_C_CHAR "10 1923-12-02"

		select TIME "12:23:45" SQL_C_CHAR "16 12:23:45.0000000"
		select TIME(4) "12:23:45.765" SQL_C_CHAR "13 12:23:45.7650"
		select TIME(0) "12:23:45.765" SQL_C_CHAR "8 12:23:46"

		select DATETIME2 "2011-08-10 12:23:45" SQL_C_CHAR "27 2011-08-10 12:23:45.0000000"
		select DATETIME2(4) "12:23:45.345888" SQL_C_CHAR "24 1900-01-01 12:23:45.3459"
		select DATETIME2(0) "2011-08-10 12:23:45.93" SQL_C_CHAR "19 2011-08-10 12:23:46"

		select DATETIMEOFFSET "12:23:45 -02:30" SQL_C_CHAR "34 1900-01-01 12:23:45.0000000 -02:30"
		select DATETIMEOFFSET(4) "12:23:45" SQL_C_CHAR "31 1900-01-01 12:23:45.0000 +00:00"

		# test we are using mssql2008 protocol (7.3)
		select_cond cond1 DATE "1923-12-02" SQL_C_BINARY "31003900320033002D00310032002D0030003200"
		if not cond1 and bigendian
			select DATE "1923-12-02" SQL_C_BINARY "0783000C0002"
			select TIME "12:23:45" SQL_C_BINARY "000C0017002D000000000000"
			select TIME(4) "12:23:45.765" SQL_C_BINARY "000C0017002D00002D98F940"
			select DATETIME2 "2011-08-10 12:23:45" SQL_C_BINARY "07DB0008000A000C0017002D00000000"
			select DATETIME2(4) "12:23:45" SQL_C_BINARY "076C00010001000C0017002D00000000"
			select DATETIMEOFFSET "12:23:45 -08:30" SQL_C_BINARY "076C00010001000C0017002D00000000FFF8FFE2"
			select DATETIMEOFFSET(4) "12:23:45" SQL_C_BINARY "076C00010001000C0017002D0000000000000000"
		endif
		if not cond1 and not bigendian
			select DATE "1923-12-02" SQL_C_BINARY "83070C000200"
			select TIME "12:23:45" SQL_C_BINARY "0C0017002D00000000000000"
			select TIME(4) "12:23:45.765" SQL_C_BINARY "0C0017002D00000040F9982D"
			select DATETIME2 "2011-08-10 12:23:45" SQL_C_BINARY "DB0708000A000C0017002D0000000000"
			select DATETIME2(4) "12:23:45" SQL_C_BINARY "6C07010001000C0017002D0000000000"
			select DATETIMEOFFSET "12:23:45 -08:30" SQL_C_BINARY "6C07010001000C0017002D0000000000F8FFE2FF"
			select DATETIMEOFFSET(4) "12:23:45" SQL_C_BINARY "6C07010001000C0017002D000000000000000000"
		endif

		# new date/time types embedded into variant types
		tds_version_cmp tds71p >= 7.1
		if tds71p
			select SQL_VARIANT "CAST('2014-04-15 20:23:56' AS DATETIME2)" SQL_C_CHAR "27 2014-04-15 20:23:56.0000000"
			select SQL_VARIANT "CAST('2014-04-15 20:23:56' AS DATETIME2(3))" SQL_C_CHAR "23 2014-04-15 20:23:56.000"
			select SQL_VARIANT "CAST('2014-04-15 20:23:56' AS TIME)" SQL_C_CHAR "16 20:23:56.0000000"
			select SQL_VARIANT "CAST('2014-04-15 20:23:56' AS TIME(3))" SQL_C_CHAR "12 20:23:56.000"
			select SQL_VARIANT "CAST('2014-04-15' AS DATE)" SQL_C_CHAR "10 2014-04-15"
		endif
	endif

	# mssql 2008 hierarchyid
	select HIERARCHYID "/" SQL_C_BINARY ""
	select HIERARCHYID "/1.2/" SQL_C_BINARY "6340"
endif

# test sybase date/time types
if not msdb
	# FIXME sure ?? with date and time always ??
	sql_cond cond1 "SELECT CAST('1923-12-02' AS DATE) WHERE 0=1"
	if cond1
		select DATE "1923-12-02" SQL_C_CHAR "10 1923-12-02"
		select TIME "12:23:45" SQL_C_CHAR "12 12:23:45.000"
		select TIME "12:23:45.983" SQL_C_CHAR "12 12:23:45.983"
		# TODO binary
	endif
endif

# check for Sybase big(date)time
sql_cond cond1 "SELECT CAST('2015-10-10' AS BIGDATETIME) WHERE 0=1"
if cond1
	if bigendian
		select BIGTIME "2004-02-24 15:16:17" SQL_C_BINARY "000F00100011000000000000"
		select BIGDATETIME  "2004-02-24 15:16:17" SQL_C_BINARY "07D400020018000F0010001100000000"
	else
		select BIGTIME "2004-02-24 15:16:17" SQL_C_BINARY "0F0010001100000000000000"
		select BIGDATETIME  "2004-02-24 15:16:17" SQL_C_BINARY "D407020018000F001000110000000000"
	endif
	select BIGTIME     "21:51:38.73973" SQL_C_CHAR "15 21:51:38.739730"
	select BIGDATETIME "1998-02-17 21:54:38.73973" SQL_C_CHAR "26 1998-02-17 21:54:38.739730"
	select BIGTIME     "2006-06-12 22:37:21.372" SQL_C_TIMESTAMP "1900-01-01 22:37:21.372"
	select BIGDATETIME "2006-06-09 11:22:44" SQL_C_TIMESTAMP "2006-06-09 11:22:44.000"
endif

select VARCHAR(20) "  15.0000  " SQL_C_NUMERIC "38 0 1 0F"
select VARCHAR(20) "  15.0000  " SQL_C_LONG "15"
if bigint
	select VARCHAR(20) "  13.0000  " SQL_C_SBIGINT "13"
endif

# mssql 2008 give a warning for truncation (01004)
if freetds
	select VARCHAR(20) "  15.1245  " SQL_C_NUMERIC "38 0 1 0F"
	select VARCHAR(20) "  15.1234  " SQL_C_LONG "15"
	if bigint
		select VARCHAR(20) "  12.98  " SQL_C_SBIGINT "12"
	endif
endif