search

FreeTDS / freetds

Official FreeTDS repository
http://www.freetds.org/
GNU General Public License v2.0
462 stars 161 forks source link

Sybase TDS login failure #550

Closed s7clarke10 closed 4 months ago

s7clarke10 commented 6 months ago

Hi,

I am having to ingest data from an old version of Sybase - Adaptive Server Enterprise/12.5.0.3.

There is code that was introduced in more recent versions of TDS which checks (I believe) whether the code is TDS 5.0 compatible.

https://github.com/FreeTDS/freetds/blob/4aa275c59ff845bd57ed3f0be6e85ff1799aba84/src/tds/login.c#L419-L422

If I switch back to TDS 4.2 this condition 5.0 code is not run and I can connect okay.

I was successful running prior to this change with TDS 5.0. I am concerned that the maximum varchar length is 255 characters under TDS 4.2. ChoosingTdsProtocol and it would lead to truncation.

I don't believe that cast is a valid keyword Sybase 12.5 and for that reason I am getting an error message.

Is it possible to adjust these lines of code so we can run TDS 5.0 against older versions of Sybase i.e. 12.5?

freddy77 commented 6 months ago

Can you try if SELECT CONVERT(NVARCHAR(3), 'abc') AS nvc, CONVERT(UNIVARCHAR(3), 'xyz') AS uvc works ?

s7clarke10 commented 6 months ago

Hi,

Thanks for looking into this.The first convert statement successfully as expected.

The second convert statement for univarchar as xyz fails with the following error message. SQL Error [11740] [S1000]: UNICHAR and UNIVARCHAR datatypes are only supported when server’s default character set is UTF-8.

The characterset is iso_1 on this server (unfortunately common for older servers):

Description - ISO 8859-1 (Latin-1) - Western European 8-bit character set.

Code to find out the characterset.

select cs.name as server_character_set, cs.description as character_set_description from master..syscharsets cs left outer join master..sysconfigures cfg on cs.id = cfg.value where cfg.config = 131

freddy77 commented 6 months ago

Can you send a TDSDUMP? You can strip the part with the login, more curious about query and results.

s7clarke10 commented 6 months ago

Here is a link to a pymssql issue I raised when we were trying to work out was happening.pymssql no longer works for Sybase databases post versions >=2.2.9 · Issue #890 · pymssql/pymssqlgithub.com

freddy77 commented 6 months ago

Found https://github.com/pymssql/pymssql/issues/890

s7clarke10 commented 6 months ago

Here is the error message when the TDS Version is set to None i.e. it defaults to the highest version for Sybase.

(test_pymssql) [myuser@myserver pymssql_testing]$ python pymssql_testing.py 
log.c:187:Starting log file for FreeTDS 1.4.10
        on 2024-04-30 07:52:39 with debug flags 0x4fff.
dblib.c:1187:tdsdbopen(0x55a1616fef60, MYSERVER:3025, [microsoft])
dblib.c:1213:tdsdbopen: dbproc->dbopts = 0x55a161842ef0
dblib.c:1224:tdsdbopen: tds_set_server(0x55a161737e70, "MYSERVER:3025")
dblib.c:251:dblib_get_tds_ctx(void)
dblib.c:1241:tdsdbopen: About to call tds_read_config_info...
config.c:170:Getting connection information for [MYSERVER:3025].
config.c:174:Attempting to read conf files.
config.c:362:... $FREETDSCONF not set.  Trying $FREETDS/etc.
config.c:375:... $FREETDS not set.  Trying $HOME.
config.c:301:Could not open '/home/myuser/.freetds.conf' ((.freetds.conf)).
config.c:301:Could not open '/usr/local/etc/freetds.conf' ((default)).
config.c:362:... $FREETDSCONF not set.  Trying $FREETDS/etc.
config.c:375:... $FREETDS not set.  Trying $HOME.
config.c:301:Could not open '/home/myuser/.freetds.conf' ((.freetds.conf)).
config.c:301:Could not open '/usr/local/etc/freetds.conf' ((default)).
config.c:839:Setting 'dump_file' to 'stdout' from $TDSDUMP.
config.c:725:tds_config_login: client_charset is utf8.
config.c:734:tds_config_login: database_name is nmds.
config.c:839:Setting 'dump_file' to 'stdout' from $TDSDUMP.
dblib.c:1268:tdsdbopen: Calling tds_connect_and_login(0x55a16172a940, 0x55a1616f6750)
iconv.c:371:tds_iconv_open(0x55a161789a30, utf8, 1)
iconv.c:202:local name for ISO-8859-1 is ISO-8859-1
iconv.c:202:local name for UTF-8 is UTF-8
iconv.c:202:local name for UCS-2LE is UCS-2LE
iconv.c:202:local name for UCS-2BE is UCS-2BE
iconv.c:390:setting up conversions for client charset "utf8"
iconv.c:392:preparing iconv for "utf8" <-> "UCS-2LE" conversion
iconv.c:431:tds_iconv_open: done
net.c:391:Connecting with protocol version 7.4
net.c:318:Connecting to 10.131.4.107 port 3025
net.c:340:tds_setup_socket: connect(2) returned "Operation now in progress"
net.c:528:tds_open_socket() succeeded
packet.c:852:Sending packet
0000 12 01 00 3a 00 00 00 00-00 00 1a 00 06 01 00 20 |...:.... ....... |
0010 00 01 02 00 21 00 0c 03-00 2d 00 04 04 00 31 00 |....!... .-....1.|
0020 01 ff 09 00 00 00 00 00-00 4d 53 53 51 4c 53 65 |........ .MSSQLSe|
0030 72 76 65 72 00 5f 3c 17-00 00                   |rver._<. ..|

util.c:179:Changed query state from IDLE to DEAD
util.c:333:tdserror(0x7ffc0abd5c60, 0x55a16172a940, 20017, 0)
util.c:363:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:386:tdserror: returning TDS_INT_CANCEL(2)
packet.c:539:Read attempt when state is TDS_DEAD

REMOVED LOGIN

util.c:179:Changed query state from IDLE to WRITING
packet.c:852:Sending packet
0000 0f 01 00 7a 00 00 00 00-21 6d 00 00 00 00 53 45 |...z.... !m....SE|
0010 4c 45 43 54 20 40 40 73-70 69 64 20 41 53 20 73 |LECT @@s pid AS s|
0020 70 69 64 20 55 53 45 20-6e 6d 64 73 20 53 45 4c |pid USE  nmds SEL|
0030 45 43 54 20 43 41 53 54-28 27 61 62 63 27 20 41 |ECT CAST ('abc' A|
0040 53 20 4e 56 41 52 43 48-41 52 28 33 29 29 20 41 |S NVARCH AR(3)) A|
0050 53 20 6e 76 63 2c 20 43-41 53 54 28 27 78 79 7a |S nvc, C AST('xyz|
0060 27 20 41 53 20 55 4e 49-56 41 52 43 48 41 52 28 |' AS UNI VARCHAR(|
0070 33 29 29 20 41 53 20 75-76 63                   |3)) AS u vc|

util.c:179:Changed query state from WRITING to PENDING
token.c:555:tds_process_tokens(0x55a16172a940, 0x7ffc0abd57cc, 0x7ffc0abd57c8, 0x500)
util.c:179:Changed query state from PENDING to READING
packet.c:410:Received packet
0000 04 01 00 58 00 00 00 00-e5 44 00 9c 00 00 00 02 |...X.... .D......|
0010 0f 05 5a 5a 5a 5a 5a 00-01 00 28 00 49 6e 63 6f |..ZZZZZ. ..(.Inco|
0020 72 72 65 63 74 20 73 79-6e 74 61 78 20 6e 65 61 |rrect sy ntax nea|
0030 72 20 74 68 65 20 6b 65-79 77 6f 72 64 20 27 41 |r the ke yword 'A|
0040 53 27 2e 0a 07 53 59 42-44 57 48 32 00 01 00 fd |S'...SYB DWH2....|
0050 02 00 02 00 28 00 00 00-                        |....(...|

token.c:570:processing result tokens.  marker is  e5(EED)
token.c:131:tds_process_default_tokens() marker is e5(EED)
mem.c:665:tds_free_all_results()
token.c:2402:tds_process_info() reading message 156 from server
token.c:2497:tds_process_info() calling client msg handler
token.c:2514:tds_process_info() returning TDS_SUCCESS
token.c:570:processing result tokens.  marker is  fd(DONE)
token.c:2100:tds_process_end: more_results = 0
                was_cancelled = 0
                error = 1
                done_count_valid = 0
token.c:2119:                rows_affected = 40
token.c:2122:tds_process_end() state set to TDS_IDLE
util.c:179:Changed query state from READING to IDLE
util.c:83:logic error: cannot change query state from IDLE to PENDING
util.c:179:Changed query state from IDLE to IDLE
mem.c:665:tds_free_all_results()
util.c:179:Changed query state from IDLE to WRITING
packet.c:852:Sending packet
0000 0f 01 00 64 00 00 00 00-21 57 00 00 00 00 55 53 |...d.... !W....US|
0010 45 20 6e 6d 64 73 20 53-45 4c 45 43 54 20 43 41 |E nmds S ELECT CA|
0020 53 54 28 27 61 62 63 27-20 41 53 20 4e 56 41 52 |ST('abc'  AS NVAR|
0030 43 48 41 52 28 33 29 29-20 41 53 20 6e 76 63 2c |CHAR(3))  AS nvc,|
0040 20 43 41 53 54 28 27 78-79 7a 27 20 41 53 20 55 | CAST('x yz' AS U|
0050 4e 49 56 41 52 43 48 41-52 28 33 29 29 20 41 53 |NIVARCHA R(3)) AS|
0060 20 75 76 63            -                        | uvc|

util.c:179:Changed query state from WRITING to PENDING
token.c:555:tds_process_tokens(0x55a16172a940, 0x7ffc0abd57cc, 0x7ffc0abd57c8, 0x500)
util.c:179:Changed query state from PENDING to READING
packet.c:410:Received packet
0000 04 01 00 58 00 00 00 00-e5 44 00 9c 00 00 00 02 |...X.... .D......|
0010 0f 05 5a 5a 5a 5a 5a 00-01 00 28 00 49 6e 63 6f |..ZZZZZ. ..(.Inco|
0020 72 72 65 63 74 20 73 79-6e 74 61 78 20 6e 65 61 |rrect sy ntax nea|
0030 72 20 74 68 65 20 6b 65-79 77 6f 72 64 20 27 41 |r the ke yword 'A|
0040 53 27 2e 0a 07 53 59 42-44 57 48 32 00 01 00 fd |S'...SYB DWH2....|
0050 02 00 02 00 28 00 00 00-                        |....(...|

token.c:570:processing result tokens.  marker is  e5(EED)
token.c:131:tds_process_default_tokens() marker is e5(EED)
mem.c:665:tds_free_all_results()
token.c:2402:tds_process_info() reading message 156 from server
token.c:2497:tds_process_info() calling client msg handler
token.c:2514:tds_process_info() returning TDS_SUCCESS
token.c:570:processing result tokens.  marker is  fd(DONE)
token.c:2100:tds_process_end: more_results = 0
                was_cancelled = 0
                error = 1
                done_count_valid = 0
token.c:2119:                rows_affected = 40
token.c:2122:tds_process_end() state set to TDS_IDLE
util.c:179:Changed query state from READING to IDLE
util.c:83:logic error: cannot change query state from IDLE to PENDING
util.c:179:Changed query state from IDLE to IDLE
query.c:3804:tds_disconnect() 
mem.c:665:tds_free_all_results()
util.c:179:Changed query state from IDLE to WRITING
packet.c:852:Sending packet
0000 0f 01 00 0a 00 00 00 00-71 00                   |........ q.|

util.c:179:Changed query state from WRITING to PENDING
token.c:555:tds_process_tokens(0x55a16172a940, 0x7ffc0abd5bc4, 0x7ffc0abd5bc0, 0x100)
util.c:179:Changed query state from PENDING to READING
packet.c:410:Received packet
0000 04 01 00 11 00 00 00 00-fd 00 00 02 00 28 00 00 |........ .....(..|
0010 00                     -                        |.|

token.c:570:processing result tokens.  marker is  fd(DONE)
token.c:2100:tds_process_end: more_results = 0
                was_cancelled = 0
                error = 0
                done_count_valid = 0
token.c:2119:                rows_affected = 40
token.c:2122:tds_process_end() state set to TDS_IDLE
util.c:179:Changed query state from READING to IDLE
util.c:83:logic error: cannot change query state from IDLE to PENDING
util.c:179:Changed query state from IDLE to IDLE
token.c:555:tds_process_tokens(0x55a16172a940, 0x7ffc0abd5bc4, 0x7ffc0abd5bc0, 0x100)
token.c:558:tds_process_tokens() state is COMPLETED
util.c:179:Changed query state from IDLE to DEAD
dbutil.c:75:_dblib_handle_info_message(0x55a16161f7e0, 0x55a16172a940, 0x7ffc0abd5c98)
dbutil.c:76:msgno 5701: "Changed database context to 'nmds'.
"
dbutil.c:75:_dblib_handle_info_message(0x55a16161f7e0, 0x55a16172a940, 0x7ffc0abd5cd0)
dbutil.c:76:msgno 156: "Incorrect syntax near the keyword 'AS'.
"
dbutil.c:75:_dblib_handle_info_message(0x55a16161f7e0, 0x55a16172a940, 0x7ffc0abd5d08)
dbutil.c:76:msgno 156: "Incorrect syntax near the keyword 'AS'.
"
dblib.c:300:db_env_chg(0x55a16172a940, 1, master, nmds)
dblib.c:300:db_env_chg(0x55a16172a940, 4, , 512)
util.c:333:tdserror(0x55a16161f7e0, 0x55a16172a940, 1, 0)
dblib.c:8167:dbperror(0x55a161842b20, 1, 0)
dblib.c:8235:dbperror: Calling dblib_err_handler with msgno = 1; msg->msgtext = "unrecognized msgno (MYSERVER)"
dblib.c:8257:dbperror: dblib_err_handler for msgno = 1; msg->msgtext = "unrecognized msgno (MYSERVER)" -- returns 2 (INT_CANCEL)
util.c:363:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:386:tdserror: returning TDS_INT_CANCEL(2)
dblib.c:1277:tdsdbopen: tds_connect_and_login failed for "MYSERVER"!
dblib.c:1491:dbclose(0x55a161842b20)
dblib.c:236:dblib_del_connection(0x7fc4cee94aa0, 0x55a16172a940)
mem.c:665:tds_free_all_results()
dblib.c:278:dblib_release_tds_ctx(1)
dblib.c:6088:dbfreebuf(0x55a161842b20)
dblib.c:739:dbloginfree(0x55a1616fef60)
Traceback (most recent call last):
  File "src/pymssql/_pymssql.pyx", line 650, in pymssql._pymssql.connect
  File "src/pymssql/_mssql.pyx", line 2155, in pymssql._mssql.connect
  File "src/pymssql/_mssql.pyx", line 712, in pymssql._mssql.MSSQLConnection.__init__
  File "src/pymssql/_mssql.pyx", line 1881, in pymssql._mssql.maybe_raise_MSSQLDatabaseException
  File "src/pymssql/_mssql.pyx", line 1898, in pymssql._mssql.raise_MSSQLDatabaseException
pymssql._mssql.MSSQLDatabaseException: (156, b"Incorrect syntax near the keyword 'AS'.\nDB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 1, severity 11:\nunrecognized msgno (MYSERVER)\n")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "pymssql_testing.py", line 43, in <module>
    conn = pymssql.connect(**args)
  File "src/pymssql/_pymssql.pyx", line 659, in pymssql._pymssql.connect
pymssql.exceptions.OperationalError: (156, b"Incorrect syntax near the keyword 'AS'.\nDB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 1, severity 11:\nunrecognized msgno (MYSERVER)\n")
s7clarke10 commented 6 months ago

Here is the error message when I set the TDS version to 4.2. This allows me to run the query that you supplied with the convert statements.

(test_pymssql) [myuser@lxrssts1 pymssql_testing]$ python pymssql_testing.py 
log.c:187:Starting log file for FreeTDS 1.4.10
        on 2024-04-30 08:01:48 with debug flags 0x4fff.
dblib.c:1187:tdsdbopen(0x55664379bf60, MYSERVER:3025, [microsoft])
dblib.c:1213:tdsdbopen: dbproc->dbopts = 0x5566438dfef0
dblib.c:1224:tdsdbopen: tds_set_server(0x5566437d4e70, "MYSERVER:3025")
dblib.c:251:dblib_get_tds_ctx(void)
dblib.c:1241:tdsdbopen: About to call tds_read_config_info...
config.c:170:Getting connection information for [MYSERVER:3025].
config.c:174:Attempting to read conf files.
config.c:362:... $FREETDSCONF not set.  Trying $FREETDS/etc.
config.c:375:... $FREETDS not set.  Trying $HOME.
config.c:301:Could not open '/home/myuser/.freetds.conf' ((.freetds.conf)).
config.c:301:Could not open '/usr/local/etc/freetds.conf' ((default)).
config.c:362:... $FREETDSCONF not set.  Trying $FREETDS/etc.
config.c:375:... $FREETDS not set.  Trying $HOME.
config.c:301:Could not open '/home/myuser/.freetds.conf' ((.freetds.conf)).
config.c:301:Could not open '/usr/local/etc/freetds.conf' ((default)).
config.c:839:Setting 'dump_file' to 'stdout' from $TDSDUMP.
config.c:725:tds_config_login: client_charset is utf8.
config.c:734:tds_config_login: database_name is nmds.
config.c:839:Setting 'dump_file' to 'stdout' from $TDSDUMP.
dblib.c:1268:tdsdbopen: Calling tds_connect_and_login(0x5566437c7940, 0x556643793750)
iconv.c:371:tds_iconv_open(0x556643826a30, utf8, 1)
iconv.c:202:local name for ISO-8859-1 is ISO-8859-1
iconv.c:202:local name for UTF-8 is UTF-8
iconv.c:202:local name for UCS-2LE is UCS-2LE
iconv.c:202:local name for UCS-2BE is UCS-2BE
iconv.c:390:setting up conversions for client charset "utf8"
iconv.c:392:preparing iconv for "utf8" <-> "UCS-2LE" conversion
iconv.c:431:tds_iconv_open: done
net.c:391:Connecting with protocol version 4.2
net.c:318:Connecting to 10.131.4.107 port 3025
net.c:340:tds_setup_socket: connect(2) returned "Operation now in progress"
net.c:528:tds_open_socket() succeeded
packet.c:852:Sending packet

REMOVED CREDENTIALS

00e0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
00f0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0100 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0110 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0120 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0130 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0140 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0150 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0160 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0170 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0180 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0190 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
01a0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
01b0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
01c0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
01d0 00 07 04 02 00 00 44 42-2d 4c 69 62 72 61 72 79 |......DB -Library|
01e0 0a 00 00 00 00 00 0d 11-75 73 5f 65 6e 67 6c 69 |........ us_engli|
01f0 73 68 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |sh...... ........|

packet.c:852:Sending packet
0000 02 01 00 4c 00 00 00 00-00 00 00 00 00 00 0a 00 |...L.... ........|
0010 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0020 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0030 00 00 00 00 00 00 00 00-00 00 00 00 01 35 31 32 |........ .....512|
0040 00 00 00 03 00 00 00 00-00 00 00 00             |........ ....|

token.c:418:tds_process_login_tokens()
packet.c:410:Received packet
0000 04 01 00 72 00 00 00 00-e3 0d 00 01 04 6e 6d 64 |...r.... .....nmd|
0010 73 06 6d 61 73 74 65 72-ab 37 00 45 16 00 00 02 |s.master .7.E....|
0020 00 24 00 43 68 61 6e 67-65 64 20 64 61 74 61 62 |.$.Chang ed datab|
0030 61 73 65 20 63 6f 6e 74-65 78 74 20 74 6f 20 27 |ase cont ext to '|
0040 6e 6d 64 73 27 2e 0a 07-XX XX XX XX XX XX XX 00 |nmds'... XXXXXXX.|
0050 00 00 ad 14 00 01 04 02-00 00 0a 73 71 6c 20 73 |........ ...sql s|
0060 65 72 76 65 72 0c 05 00-00 fd 00 00 02 00 ba 01 |erver... ........|
0070 00 00                  -                        |..|

token.c:422:looking for login token, got  e3(ENVCHANGE)
token.c:131:tds_process_default_tokens() marker is e3(ENVCHANGE)
dblib.c:300:db_env_chg(0x5566437c7940, 1, master, nmds)
token.c:422:looking for login token, got  ab(INFO)
token.c:131:tds_process_default_tokens() marker is ab(INFO)
mem.c:665:tds_free_all_results()
token.c:2402:tds_process_info() reading message 5701 from server
token.c:2497:tds_process_info() calling client msg handler
dbutil.c:75:_dblib_handle_info_message(0x5566436bc7e0, 0x5566437c7940, 0x7fffbb3b46f0)
dbutil.c:76:msgno 5701: "Changed database context to 'nmds'.
"
token.c:2514:tds_process_info() returning TDS_SUCCESS
token.c:422:looking for login token, got  ad(LOGINACK)
token.c:348:server reports TDS version 4.2.0.0
token.c:350:Product name for 0x4020000 is unknown
token.c:384:Product version C050000
token.c:422:looking for login token, got  fd(DONE)
token.c:131:tds_process_default_tokens() marker is fd(DONE)
token.c:2100:tds_process_end: more_results = 0
                was_cancelled = 0
                error = 0
                done_count_valid = 0
token.c:2119:                rows_affected = 442
token.c:2122:tds_process_end() state set to TDS_IDLE
token.c:445:tds_process_login_tokens() returning TDS_SUCCESS
mem.c:665:tds_free_all_results()
util.c:179:Changed query state from IDLE to WRITING
packet.c:852:Sending packet
0000 01 01 00 26 00 00 00 00-53 45 4c 45 43 54 20 40 |...&.... SELECT @|
0010 40 73 70 69 64 20 41 53-20 73 70 69 64 20 55 53 |@spid AS  spid US|
0020 45 20 6e 6d 64 73      -                        |E nmds|

util.c:179:Changed query state from WRITING to PENDING
token.c:555:tds_process_tokens(0x5566437c7940, 0x7fffbb3b477c, 0x7fffbb3b4778, 0x500)
util.c:179:Changed query state from PENDING to READING
packet.c:410:Received packet
0000 04 01 00 7b 00 00 00 00-a0 05 00 04 73 70 69 64 |...{.... ....spid|
0010 a1 06 00 06 00 00 00 26-02 ae 01 00 00 d1 02 ba |.......& ........|
0020 01 fd 11 00 02 00 01 00-00 00 e3 0b 00 01 04 6e |........ .......n|
0030 6d 64 73 04 6e 6d 64 73-ab 37 00 45 16 00 00 01 |mds.nmds .7.E....|
0040 00 24 00 43 68 61 6e 67-65 64 20 64 61 74 61 62 |.$.Chang ed datab|
0050 61 73 65 20 63 6f 6e 74-65 78 74 20 74 6f 20 27 |ase cont ext to '|
0060 6e 6d 64 73 27 2e 0a 07-XX XX XX XX XX XX XX 00 |nmds'... XXXXXXX.|
0070 01 00 fd 00 00 02 00 00-00 00 00                |........ ...|

token.c:570:processing result tokens.  marker is  a0(COLNAME)
mem.c:665:tds_free_all_results()
token.c:570:processing result tokens.  marker is  a1(COLFMT)
token.c:1082:processing result. type = 38(integer-null), varint_size 1
token.c:570:processing result tokens.  marker is  ae(CONTROL/FEATUREEXTACK)
token.c:131:tds_process_default_tokens() marker is ae(CONTROL/FEATUREEXTACK)
token.c:250:Eating CONTROL/FEATUREEXTACK token
token.c:570:processing result tokens.  marker is  d1(ROW)
token.c:1955:tds_process_row(): reading column 0 
data.c:723:tds_get_data: type 38, varint size 1
data.c:767:tds_get_data(): wire column size is 2 
util.c:179:Changed query state from READING to PENDING
token.c:555:tds_process_tokens(0x5566437c7940, 0x7fffbb3b477c, 0x7fffbb3b4778, 0x500)
util.c:179:Changed query state from PENDING to READING
token.c:570:processing result tokens.  marker is  fd(DONE)
token.c:2100:tds_process_end: more_results = 1
                was_cancelled = 0
                error = 0
                done_count_valid = 1
token.c:2119:                rows_affected = 1
util.c:179:Changed query state from READING to PENDING
token.c:555:tds_process_tokens(0x5566437c7940, 0x7fffbb3b477c, 0x7fffbb3b4778, 0x500)
util.c:179:Changed query state from PENDING to READING
token.c:570:processing result tokens.  marker is  e3(ENVCHANGE)
token.c:131:tds_process_default_tokens() marker is e3(ENVCHANGE)
dblib.c:300:db_env_chg(0x5566437c7940, 1, nmds, nmds)
token.c:570:processing result tokens.  marker is  ab(INFO)
token.c:131:tds_process_default_tokens() marker is ab(INFO)
mem.c:665:tds_free_all_results()
token.c:2402:tds_process_info() reading message 5701 from server
token.c:2497:tds_process_info() calling client msg handler
dbutil.c:75:_dblib_handle_info_message(0x5566436bc7e0, 0x5566437c7940, 0x7fffbb3b4610)
dbutil.c:76:msgno 5701: "Changed database context to 'nmds'.
"
token.c:2514:tds_process_info() returning TDS_SUCCESS
token.c:570:processing result tokens.  marker is  fd(DONE)
token.c:2100:tds_process_end: more_results = 0
                was_cancelled = 0
                error = 0
                done_count_valid = 0
token.c:2119:                rows_affected = 0
token.c:2122:tds_process_end() state set to TDS_IDLE
util.c:179:Changed query state from READING to IDLE
util.c:83:logic error: cannot change query state from IDLE to PENDING
util.c:179:Changed query state from IDLE to IDLE
token.c:555:tds_process_tokens(0x5566437c7940, 0x7fffbb3b477c, 0x7fffbb3b4778, 0x500)
token.c:558:tds_process_tokens() state is COMPLETED
dblib.c:217:dblib_add_connection(0x7efd14cfaaa0, 0x5566437c7940)
dblib.c:1317:tdsdbopen: Returning dbproc = 0x5566438dfb20
dblib.c:739:dbloginfree(0x55664379bf60)
dblib.c:3401:dbcancel(0x5566438dfb20)
query.c:2081:tds_send_cancel: not in_cancel and idle
dblib.c:1450:dbuse(0x5566438dfb20, nmds)
dblib.c:1377:dbcmd(0x5566438dfb20, use nmds)
dblib.c:1383:dbcmd() bufsz = 0
dblib.c:1423:dbsqlexec(0x5566438dfb20)
dblib.c:7077:dbsqlsend(0x5566438dfb20)
mem.c:665:tds_free_all_results()
util.c:179:Changed query state from IDLE to WRITING
packet.c:852:Sending packet
0000 01 01 00 10 00 00 00 00-75 73 65 20 6e 6d 64 73 |........ use nmds|

util.c:179:Changed query state from WRITING to PENDING
dblib.c:4846:dbsqlok(0x5566438dfb20)
dblib.c:4875:dbsqlok() not done, calling tds_process_tokens()
token.c:555:tds_process_tokens(0x5566437c7940, 0x7fffbb3b4c78, 0x7fffbb3b4c74, 0x6914)
util.c:179:Changed query state from PENDING to READING
packet.c:410:Received packet
0000 04 01 00 59 00 00 00 00-e3 0b 00 01 04 6e 6d 64 |...Y.... .....nmd|
0010 73 04 6e 6d 64 73 ab 37-00 45 16 00 00 01 00 24 |s.nmds.7 .E.....$|
0020 00 43 68 61 6e 67 65 64-20 64 61 74 61 62 61 73 |.Changed  databas|
0030 65 20 63 6f 6e 74 65 78-74 20 74 6f 20 27 6e 6d |e contex t to 'nm|
0040 64 73 27 2e 0a 07 XX XX-XX XX XX XX XX 00 01 00 |ds'...XX XXXXX...|
0050 fd 00 00 02 00 00 00 00-00                      |........ .|

token.c:570:processing result tokens.  marker is  e3(ENVCHANGE)
token.c:131:tds_process_default_tokens() marker is e3(ENVCHANGE)
dblib.c:300:db_env_chg(0x5566437c7940, 1, nmds, nmds)
token.c:570:processing result tokens.  marker is  ab(INFO)
token.c:131:tds_process_default_tokens() marker is ab(INFO)
mem.c:665:tds_free_all_results()
token.c:2402:tds_process_info() reading message 5701 from server
token.c:2497:tds_process_info() calling client msg handler
dbutil.c:75:_dblib_handle_info_message(0x5566436bc7e0, 0x5566437c7940, 0x7fffbb3b4b10)
dbutil.c:76:msgno 5701: "Changed database context to 'nmds'.
"
token.c:2514:tds_process_info() returning TDS_SUCCESS
token.c:570:processing result tokens.  marker is  fd(DONE)
token.c:2100:tds_process_end: more_results = 0
                was_cancelled = 0
                error = 0
                done_count_valid = 0
token.c:2119:                rows_affected = 0
token.c:2122:tds_process_end() state set to TDS_IDLE
util.c:179:Changed query state from READING to IDLE
util.c:83:logic error: cannot change query state from IDLE to PENDING
util.c:179:Changed query state from IDLE to IDLE
dblib.c:4908:dbsqlok() end status is 1 (SUCCEED)
dblib.c:4919:dbsqlok() end status was success
dblib.c:1708:dbresults(0x5566438dfb20)
dblib.c:1713:dbresults: dbresults_state is 5 (_DB_RES_SUCCEED)
dblib.c:1698:dbresults returning 1 (SUCCEED)
dblib.c:6062:dbcanquery(0x5566438dfb20)
token.c:555:tds_process_tokens(0x5566437c7940, 0x7fffbb3b4cb8, (nil), 0x108)
token.c:558:tds_process_tokens() state is COMPLETED
dblib.c:3401:dbcancel(0x5566438dfb20)
query.c:2081:tds_send_cancel: not in_cancel and idle
dblib.c:1377:dbcmd(0x5566438dfb20, BEGIN TRAN)
dblib.c:1383:dbcmd() bufsz = 9
dblib.c:6088:dbfreebuf(0x5566438dfb20)
dblib.c:7077:dbsqlsend(0x5566438dfb20)
mem.c:665:tds_free_all_results()
util.c:179:Changed query state from IDLE to WRITING
packet.c:852:Sending packet
0000 01 01 00 12 00 00 00 00-42 45 47 49 4e 20 54 52 |........ BEGIN TR|
0010 41 4e                  -                        |AN|

util.c:179:Changed query state from WRITING to PENDING
dblib.c:4846:dbsqlok(0x5566438dfb20)
dblib.c:4875:dbsqlok() not done, calling tds_process_tokens()
token.c:555:tds_process_tokens(0x5566437c7940, 0x7fffbb3b4c58, 0x7fffbb3b4c54, 0x6914)
util.c:179:Changed query state from PENDING to READING
packet.c:410:Received packet
0000 04 01 00 11 00 00 00 00-fd 04 00 01 00 00 00 00 |........ ........|
0010 00                     -                        |.|

token.c:570:processing result tokens.  marker is  fd(DONE)
token.c:2100:tds_process_end: more_results = 0
                was_cancelled = 0
                error = 0
                done_count_valid = 0
token.c:2119:                rows_affected = 0
token.c:2122:tds_process_end() state set to TDS_IDLE
util.c:179:Changed query state from READING to IDLE
util.c:83:logic error: cannot change query state from IDLE to PENDING
util.c:179:Changed query state from IDLE to IDLE
dblib.c:4908:dbsqlok() end status is 1 (SUCCEED)
dblib.c:4919:dbsqlok() end status was success
dblib.c:1708:dbresults(0x5566438dfb20)
dblib.c:1713:dbresults: dbresults_state is 5 (_DB_RES_SUCCEED)
dblib.c:1698:dbresults returning 1 (SUCCEED)
dblib.c:2826:dbcount(0x5566438dfb20)
dblib.c:3401:dbcancel(0x5566438dfb20)
query.c:2081:tds_send_cancel: not in_cancel and idle
dblib.c:3401:dbcancel(0x5566438dfb20)
query.c:2081:tds_send_cancel: not in_cancel and idle
dblib.c:1377:dbcmd(0x5566438dfb20, 
SELECT CONVERT(NVARCHAR(3), 'abc') AS nvc, CONVERT(UNIVARCHAR(3), 'xyz') AS uvc
)
dblib.c:1383:dbcmd() bufsz = 11
dblib.c:6088:dbfreebuf(0x5566438dfb20)
dblib.c:7077:dbsqlsend(0x5566438dfb20)
mem.c:665:tds_free_all_results()
util.c:179:Changed query state from IDLE to WRITING
packet.c:852:Sending packet
0000 01 01 00 59 00 00 00 00-0a 53 45 4c 45 43 54 20 |...Y.... .SELECT |
0010 43 4f 4e 56 45 52 54 28-4e 56 41 52 43 48 41 52 |CONVERT( NVARCHAR|
0020 28 33 29 2c 20 27 61 62-63 27 29 20 41 53 20 6e |(3), 'ab c') AS n|
0030 76 63 2c 20 43 4f 4e 56-45 52 54 28 55 4e 49 56 |vc, CONV ERT(UNIV|
0040 41 52 43 48 41 52 28 33-29 2c 20 27 78 79 7a 27 |ARCHAR(3 ), 'xyz'|
0050 29 20 41 53 20 75 76 63-0a                      |) AS uvc .|

util.c:179:Changed query state from WRITING to PENDING
dblib.c:4846:dbsqlok(0x5566438dfb20)
dblib.c:4875:dbsqlok() not done, calling tds_process_tokens()
token.c:555:tds_process_tokens(0x5566437c7940, 0x7fffbb3b4f58, 0x7fffbb3b4f54, 0x6914)
util.c:179:Changed query state from PENDING to READING
packet.c:410:Received packet
0000 04 01 00 89 00 00 00 00-aa 75 00 dc 2d 00 00 01 |........ .u..-...|
0010 0f 62 00 55 4e 49 43 48-41 52 20 61 6e 64 20 55 |.b.UNICH AR and U|
0020 4e 49 56 41 52 43 48 41-52 20 64 61 74 61 74 79 |NIVARCHA R dataty|
0030 70 65 73 20 61 72 65 20-6f 6e 6c 79 20 73 75 70 |pes are  only sup|
0040 70 6f 72 74 65 64 20 77-68 65 6e 20 73 65 72 76 |ported w hen serv|
0050 65 72 27 73 20 64 65 66-61 75 6c 74 20 63 68 61 |er's def ault cha|
0060 72 61 63 74 65 72 20 73-65 74 20 69 73 20 55 54 |racter s et is UT|
0070 46 2d 38 2e 0a 07 53 59-42 44 57 48 32 00 02 00 |F-8...SY BDWH2...|
0080 fd 06 00 01 00 00 00 00-00                      |........ .|

token.c:570:processing result tokens.  marker is  aa(ERROR)
token.c:131:tds_process_default_tokens() marker is aa(ERROR)
mem.c:665:tds_free_all_results()
token.c:2402:tds_process_info() reading message 11740 from server
token.c:2497:tds_process_info() calling client msg handler
dbutil.c:75:_dblib_handle_info_message(0x5566436bc7e0, 0x5566437c7940, 0x7fffbb3b4df0)
dbutil.c:76:msgno 11740: "UNICHAR and UNIVARCHAR datatypes are only supported when server's default character set is UTF-8.
"
dblib.c:5086:dbdead(0x5566438dfb20) [alive]
token.c:2514:tds_process_info() returning TDS_SUCCESS
token.c:570:processing result tokens.  marker is  fd(DONE)
token.c:2100:tds_process_end: more_results = 0
                was_cancelled = 0
                error = 1
                done_count_valid = 0
token.c:2119:                rows_affected = 0
token.c:2122:tds_process_end() state set to TDS_IDLE
util.c:179:Changed query state from READING to IDLE
util.c:83:logic error: cannot change query state from IDLE to PENDING
util.c:179:Changed query state from IDLE to IDLE
dblib.c:4908:dbsqlok() end status is 0 (FAIL)
dblib.c:3401:dbcancel(0x5566438dfb20)
query.c:2081:tds_send_cancel: not in_cancel and idle
dblib.c:3401:dbcancel(0x5566438dfb20)
query.c:2081:tds_send_cancel: not in_cancel and idle
Traceback (most recent call last):
  File "src/pymssql/_pymssql.pyx", line 447, in pymssql._pymssql.Cursor.execute
  File "src/pymssql/_mssql.pyx", line 1125, in pymssql._mssql.MSSQLConnection.execute_query
  File "src/pymssql/_mssql.pyx", line 1156, in pymssql._mssql.MSSQLConnection.execute_query
  File "src/pymssql/_mssql.pyx", line 1289, in pymssql._mssql.MSSQLConnection.format_and_run_query
  File "src/pymssql/_mssql.pyx", line 1852, in pymssql._mssql.check_cancel_and_raise
  File "src/pymssql/_mssql.pyx", line 1898, in pymssql._mssql.raise_MSSQLDatabaseException
pymssql._mssql.MSSQLDatabaseException: (11740, b"UNICHAR and UNIVARCHAR datatypes are only supported when server's default character set is UTF-8.\nDB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "pymssql_testing.py", line 46, in <module>
    cursor.execute(select_sql,params)
  File "src/pymssql/_pymssql.pyx", line 465, in pymssql._pymssql.Cursor.execute
pymssql.exceptions.OperationalError: (11740, b"UNICHAR and UNIVARCHAR datatypes are only supported when server's default character set is UTF-8.\nDB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")
dblib.c:1491:dbclose(0x5566438dfb20)
dblib.c:236:dblib_del_connection(0x7efd14cfaaa0, 0x5566437c7940)
query.c:3804:tds_disconnect() 
util.c:179:Changed query state from IDLE to DEAD
mem.c:665:tds_free_all_results()
dblib.c:278:dblib_release_tds_ctx(1)
dblib.c:6088:dbfreebuf(0x5566438dfb20)
s7clarke10 commented 6 months ago

Finally here is the code we have used for testing this process.

import pymssql
import pendulum
from datetime import datetime
from dotenv import load_dotenv
from os import getenv, environ

load_dotenv()

config = {
"host" : getenv("TAP_SYBASE__NMD_HOST"),
"port" : getenv("TAP_SYBASE__NMD_PORT"),
"user" : getenv("TAP_SYBASE__NMD_USER"),
"password" : getenv("TAP_SYBASE__NMD_PASSWORD"),
"database" : getenv("TAP_SYBASE__NMD_DATABASE"),
"tds_version": "4.2",
}

environ['TDSDUMP'] = 'stdout'

select_sql = f"""
SELECT CONVERT(NVARCHAR(3), 'abc') AS nvc, CONVERT(UNIVARCHAR(3), 'xyz') AS uvc
"""
replication_key_value = '2024-03-29 01:02:03.123145+13:00'
replication_key_value = pendulum.parse(replication_key_value)
# Converting to datetime.datetime works...
# replication_key_value = datetime.fromtimestamp(replication_key_value.timestamp())

params = {}
params ["replication_key_value"] = replication_key_value

args = {
    "user": config["user"],
    "password": config["password"],
    "server": config["host"],
    "database": config["database"],
    "charset": config.get("characterset", "utf8"),
    "port": config.get("port", "2638"),
    "tds_version": config.get("tds_version", None),
    "conn_properties": '',
}
conn = pymssql.connect(**args)

cursor = conn.cursor()
cursor.execute(select_sql,params)
row = cursor.fetchone()
while row:
    print("%s" % (row[0],
))
    row = cursor.fetchone()

conn.close()
s7clarke10 commented 6 months ago

@freddy77 , what are your thoughts on this? Do we need the second test select CONVERT(UNIVARCHAR(3), 'xyz') AS uvc piece of code?

It is quite common for older databases to be running on a non UTF-8 characterset.

cobiadigital commented 5 months ago

Maybe this will be helpful

 1> SELECT @@VERSION;
 2> GO
 @@VERSION
 10.0.1.4310
 (1 row affected)
 1> SELECT CONVERT(NVARCHAR(3), 'abc') AS nvc, CONVERT(UNIVARCHAR(3), 'xyz') AS uvc
 2> go
 Msg 102 (severity 15, state 0) from :
         "SQL Anywhere Error -131: Syntax error near 'UNIVARCHAR' on line 1"

I can successfully connect with version 1.2.3 (and I imagine 1.3.x looking at the code). But V 1.4.10 gives me the error. I've included logs from both versions.

log.c:187:Starting log file for FreeTDS 1.4.10
    on 2024-05-01 17:21:06 with debug flags 0x4fff.
iconv.c:371:tds_iconv_open(0x145f04080, UTF-8, 1)
iconv.c:202:local name for ISO-8859-1 is ISO-8859-1
iconv.c:202:local name for UTF-8 is UTF-8
iconv.c:202:local name for UCS-2LE is UCS-2LE
iconv.c:202:local name for UCS-2BE is UCS-2BE
iconv.c:390:setting up conversions for client charset "UTF-8"
iconv.c:392:preparing iconv for "UTF-8" <-> "UCS-2LE" conversion
iconv.c:431:tds_iconv_open: done
net.c:391:Connecting with protocol version 5.0
net.c:318:Connecting to 192.168.1.31 port 2638
net.c:340:tds_setup_socket: connect(2) returned "Operation now in progress"
net.c:528:tds_open_socket() succeeded
packet.c:852:Sending packet
0000 02 00 02 00 00 00 00 00-42 65 6e 73 2d 4d 61 63 |........ Bens-Mac|
0010 2d 53 74 75 64 69 6f 2e-6c 6f 63 61 6c 00 00 00 |-Studio. local...|
0020 00 00 00 00 00 00 15 64-62 61 00 00 00 00 00 00 |.......d ba......|
0030 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0040 00 00 00 00 00 03 73 71-6c 00 00 00 00 00 00 00 |......sq l.......|
0050 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0060 00 00 00 00 03 34 35 36-34 39 00 00 00 00 00 00 |.....456 49......|
0070 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0080 00 00 00 05 03 01 06 0a-09 01 00 00 00 00 00 00 |........ ........|
0090 00 00 00 00 54 53 51 4c-00 00 00 00 00 00 00 00 |....TSQL ........|
00a0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
00b0 00 00 04 6e 65 65 64 6c-65 73 00 00 00 00 00 00 |...needl es......|
00c0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
00d0 00 07 00 03 73 71 6c 00-00 00 00 00 00 00 00 00 |....sql. ........|
00e0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
00f0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0100 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0110 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0120 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0130 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0140 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0150 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0160 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0170 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0180 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0190 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
01a0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
01b0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
01c0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
01d0 00 05 05 00 00 00 54 44-53 2d 4c 69 62 72 61 72 |......TD S-Librar|
01e0 0a 05 00 00 00 00 0d 11-75 73 5f 65 6e 67 6c 69 |........ us_engli|
01f0 73 68 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |sh...... ........|

packet.c:852:Sending packet
0000 02 01 00 6b 00 00 00 00-00 00 00 00 00 00 0a 00 |...k.... ........|
0010 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0020 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0030 00 00 00 00 00 00 00 00-00 00 00 00 01 35 31 32 |........ .....512|
0040 00 00 00 03 00 00 00 00-e2 20 00 01 0e 00 00 60 |........ � .....`|
0050 08 81 81 e8 0f 6d 7f ff-ff ff fe 02 0e 00 00 00 |...�.m.� ���.....|
0060 00 00 00 00 00 00 02 68-00 00 00                |.......h ...|

token.c:418:tds_process_login_tokens()
packet.c:410:Received packet
0000 04 01 00 65 00 00 00 00-e3 09 00 03 06 63 70 31 |...e.... �....cp1|
0010 32 35 32 00 e3 09 00 04-03 35 31 32 03 35 31 32 |252.�... .512.512|
0020 ad 16 00 05 05 00 00 00-0c 53 51 4c 20 41 6e 79 |�....... .SQL Any|
0030 77 68 65 72 65 0a 00 00-00 e2 20 00 01 0e 00 00 |where... .� .....|
0040 60 08 01 81 e8 0e 61 01-ff ff fe e6 02 0e ff ff |`...�.a. ����..��|
0050 00 00 00 01 fe 67 00 e2-68 00 00 0a fd 00 00 01 |....�g.� h...�...|
0060 00 00 00 00 00         -                        |.....|

token.c:422:looking for login token, got  e3(ENVCHANGE)
token.c:131:tds_process_default_tokens() marker is e3(ENVCHANGE)
token.c:2305:server indicated charset change to "cp1252"
iconv.c:828:setting server single-byte charset to "CP1252"
token.c:422:looking for login token, got  e3(ENVCHANGE)
token.c:131:tds_process_default_tokens() marker is e3(ENVCHANGE)
token.c:2289:changing block size from 512 to 512
token.c:422:looking for login token, got  ad(LOGINACK)
token.c:348:server reports TDS version 5.0.0.0
token.c:350:Product name for 0x5000000 is unknown
token.c:384:Product version A000000
token.c:422:looking for login token, got  e2(CAPABILITY)
token.c:131:tds_process_default_tokens() marker is e2(CAPABILITY)
token.c:422:looking for login token, got  fd(DONE)
token.c:131:tds_process_default_tokens() marker is fd(DONE)
token.c:2100:tds_process_end: more_results = 0
        was_cancelled = 0
        error = 0
        done_count_valid = 0
token.c:2119:                rows_affected = 0
token.c:2122:tds_process_end() state set to TDS_IDLE
token.c:445:tds_process_login_tokens() returning TDS_SUCCESS
mem.c:665:tds_free_all_results()
util.c:179:Changed query state from IDLE to WRITING
packet.c:852:Sending packet
0000 0f 01 00 86 00 00 00 00-21 79 00 00 00 00 53 45 |........ !y....SE|
0010 54 20 54 45 58 54 53 49-5a 45 20 31 32 39 30 32 |T TEXTSI ZE 12902|
0020 34 20 53 45 4c 45 43 54-20 40 40 73 70 69 64 20 |4 SELECT  @@spid |
0030 41 53 20 73 70 69 64 20-20 53 45 4c 45 43 54 20 |AS spid   SELECT |
0040 43 41 53 54 28 27 61 62-63 27 20 41 53 20 4e 56 |CAST('ab c' AS NV|
0050 41 52 43 48 41 52 28 33-29 29 20 41 53 20 6e 76 |ARCHAR(3 )) AS nv|
0060 63 2c 20 43 41 53 54 28-27 78 79 7a 27 20 41 53 |c, CAST( 'xyz' AS|
0070 20 55 4e 49 56 41 52 43-48 41 52 28 33 29 29 20 | UNIVARC HAR(3)) |
0080 41 53 20 75 76 63      -                        |AS uvc|

util.c:179:Changed query state from WRITING to PENDING
token.c:555:tds_process_tokens(0x145f043d0, 0x16d94ebfc, 0x16d94ebf8, 0x500)
util.c:179:Changed query state from PENDING to READING
packet.c:410:Received packet
0000 04 01 00 66 00 00 00 00-e5 52 00 66 00 00 00 00 |...f.... �R.f....|
0010 0f 05 34 32 57 30 34 00-01 00 3d 00 53 51 4c 20 |..42W04. ..=.SQL |
0020 41 6e 79 77 68 65 72 65-20 45 72 72 6f 72 20 2d |Anywhere  Error -|
0030 31 33 31 3a 20 53 79 6e-74 61 78 20 65 72 72 6f |131: Syn tax erro|
0040 72 20 6e 65 61 72 20 27-53 45 4c 45 43 54 27 20 |r near ' SELECT' |
0050 6f 6e 20 6c 69 6e 65 20-31 00 00 00 00 fd 02 00 |on line  1....�..|
0060 01 00 00 00 00 00      -                        |......|

token.c:570:processing result tokens.  marker is  e5(EED)
token.c:131:tds_process_default_tokens() marker is e5(EED)
mem.c:665:tds_free_all_results()
token.c:2402:tds_process_info() reading message 102 from server
token.c:2497:tds_process_info() calling client msg handler
token.c:2514:tds_process_info() returning TDS_SUCCESS
token.c:570:processing result tokens.  marker is  fd(DONE)
token.c:2100:tds_process_end: more_results = 0
        was_cancelled = 0
        error = 1
        done_count_valid = 0
token.c:2119:                rows_affected = 0
token.c:2122:tds_process_end() state set to TDS_IDLE
util.c:179:Changed query state from READING to IDLE
util.c:83:logic error: cannot change query state from IDLE to PENDING
util.c:179:Changed query state from IDLE to IDLE
mem.c:665:tds_free_all_results()
util.c:179:Changed query state from IDLE to WRITING
packet.c:852:Sending packet
0000 0f 01 00 70 00 00 00 00-21 63 00 00 00 00 53 45 |...p.... !c....SE|
0010 54 20 54 45 58 54 53 49-5a 45 20 31 32 39 30 32 |T TEXTSI ZE 12902|
0020 34 20 20 53 45 4c 45 43-54 20 43 41 53 54 28 27 |4  SELEC T CAST('|
0030 61 62 63 27 20 41 53 20-4e 56 41 52 43 48 41 52 |abc' AS  NVARCHAR|
0040 28 33 29 29 20 41 53 20-6e 76 63 2c 20 43 41 53 |(3)) AS  nvc, CAS|
0050 54 28 27 78 79 7a 27 20-41 53 20 55 4e 49 56 41 |T('xyz'  AS UNIVA|
0060 52 43 48 41 52 28 33 29-29 20 41 53 20 75 76 63 |RCHAR(3) ) AS uvc|

util.c:179:Changed query state from WRITING to PENDING
token.c:555:tds_process_tokens(0x145f043d0, 0x16d94ebfc, 0x16d94ebf8, 0x500)
util.c:179:Changed query state from PENDING to READING
packet.c:410:Received packet
0000 04 01 00 66 00 00 00 00-e5 52 00 66 00 00 00 00 |...f.... �R.f....|
0010 0f 05 34 32 57 30 34 00-01 00 3d 00 53 51 4c 20 |..42W04. ..=.SQL |
0020 41 6e 79 77 68 65 72 65-20 45 72 72 6f 72 20 2d |Anywhere  Error -|
0030 31 33 31 3a 20 53 79 6e-74 61 78 20 65 72 72 6f |131: Syn tax erro|
0040 72 20 6e 65 61 72 20 27-53 45 4c 45 43 54 27 20 |r near ' SELECT' |
0050 6f 6e 20 6c 69 6e 65 20-31 00 00 00 00 fd 02 00 |on line  1....�..|
0060 01 00 00 00 00 00      -                        |......|

token.c:570:processing result tokens.  marker is  e5(EED)
token.c:131:tds_process_default_tokens() marker is e5(EED)
mem.c:665:tds_free_all_results()
token.c:2402:tds_process_info() reading message 102 from server
token.c:2497:tds_process_info() calling client msg handler
token.c:2514:tds_process_info() returning TDS_SUCCESS
token.c:570:processing result tokens.  marker is  fd(DONE)
token.c:2100:tds_process_end: more_results = 0
        was_cancelled = 0
        error = 1
        done_count_valid = 0
token.c:2119:                rows_affected = 0
token.c:2122:tds_process_end() state set to TDS_IDLE
util.c:179:Changed query state from READING to IDLE
util.c:83:logic error: cannot change query state from IDLE to PENDING
util.c:179:Changed query state from IDLE to IDLE
mem.c:665:tds_free_all_results()

Version 1.2.3


22:21:51.049532 490 (log.c:168):Starting log file for FreeTDS 1.2.3
        on 2024-05-01 22:21:51 with debug flags 0xffff.
22:21:51.049646 490 (iconv.c:365):tds_iconv_open(0x558a79f16990, UTF-8)
22:21:51.049797 490 (iconv.c:196):local name for ISO-8859-1 is ISO-8859-1
22:21:51.049815 490 (iconv.c:196):local name for UTF-8 is UTF-8
22:21:51.049829 490 (iconv.c:196):local name for UCS-2LE is UCS-2LE
22:21:51.049843 490 (iconv.c:196):local name for UCS-2BE is UCS-2BE
22:21:51.049893 490 (iconv.c:387):setting up conversions for client charset "UTF-8"
22:21:51.049910 490 (iconv.c:389):preparing iconv for "UTF-8" <-> "UCS-2LE" conversion
22:21:51.050043 490 (iconv.c:428):tds_iconv_open: done
22:21:51.050061 490 (net.c:391):Connecting with protocol version 7.4
22:21:51.050116 490 (net.c:318):Connecting to 192.168.1.11 port 2638
22:21:51.050403 490 (net.c:340):tds_setup_socket: connect(2) returned "Operation now in progress"
22:21:51.050875 490 (net.c:528):tds_open_socket() succeeded
22:21:51.050919 490 (packet.c:852):Sending packet
0000 12 01 00 3a 00 00 00 00-00 00 1a 00 06 01 00 20 |...:.... ....... |
0010 00 01 02 00 21 00 0c 03-00 2d 00 04 04 00 31 00 |....!... .-....1.|
0020 01 ff 09 00 00 00 00 00-00 4d 53 53 51 4c 53 65 |........ .MSSQLSe|
0030 72 76 65 72 00 ea 01 00-00 00                   |rver.... ..|

22:21:51.051482 490 (util.c:179):Changed query state from IDLE to DEAD
22:21:51.051500 490 (util.c:333):tdserror(0x7fff6bd13e20, 0x558a79f16d60, 20017, 0)
22:21:51.051517 490 (util.c:363):tdserror: client library returned TDS_INT_CANCEL(2)
22:21:51.051531 490 (util.c:386):tdserror: returning TDS_INT_CANCEL(2)
22:21:51.051546 490 (packet.c:539):Read attempt when state is TDS_DEAD
22:21:51.051560 490 (login.c:571):login packet rejected
22:21:51.051574 490 (util.c:333):tdserror(0x7fff6bd13e20, 0x558a79f16d60, 20002, 0)
22:21:51.051589 490 (util.c:363):tdserror: client library returned TDS_INT_CANCEL(2)
22:21:51.051603 490 (util.c:386):tdserror: returning TDS_INT_CANCEL(2)
22:21:51.051618 490 (net.c:391):Connecting with protocol version 5.0
22:21:51.051652 490 (net.c:318):Connecting to 192.168.1.11 port 2638
22:21:51.051717 490 (net.c:340):tds_setup_socket: connect(2) returned "Operation now in progress"
22:21:51.051923 490 (net.c:528):tds_open_socket() succeeded
22:21:51.051954 490 (packet.c:852):Sending packet
0000 02 00 02 00 00 00 00 00-65 37 37 36 31 38 34 38 |........ e7761848|
0010 30 62 39 64 00 00 00 00-00 00 00 00 00 00 00 00 |0b9d.... ........|
0020 00 00 00 00 00 00 0c 64-62 61 00 00 00 00 00 00 |.......d ba......|
0030 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0040 00 00 00 00 00 03 73 71-6c 00 00 00 00 00 00 00 |......sq l.......|
0050 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0060 00 00 00 00 03 34 39 30-00 00 00 00 00 00 00 00 |.....490 ........|
0070 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0080 00 00 00 03 03 01 06 0a-09 01 00 00 00 00 00 00 |........ ........|
0090 00 00 00 00 54 53 51 4c-00 00 00 00 00 00 00 00 |....TSQL ........|
00a0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
00b0 00 00 04 31 39 32 2e 31-36 38 2e 31 2e 31 31 00 |...192.1 68.1.11.|
00c0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
00d0 00 0c 00 03 73 71 6c 00-00 00 00 00 00 00 00 00 |....sql. ........|
00e0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
00f0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0100 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0110 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0120 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0130 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0140 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0150 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0160 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0170 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0180 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0190 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
01a0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
01b0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
01c0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
01d0 00 05 05 00 00 00 54 44-53 2d 4c 69 62 72 61 72 |......TD S-Librar|
01e0 0a 05 00 00 00 00 0d 11-75 73 5f 65 6e 67 6c 69 |........ us_engli|
01f0 73 68 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |sh...... ........|

22:21:51.052174 490 (packet.c:852):Sending packet
0000 02 01 00 6b 00 00 00 00-00 00 00 00 00 00 0a 00 |...k.... ........|
0010 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0020 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0030 00 00 00 00 00 00 00 00-00 00 00 00 01 35 31 32 |........ .....512|
0040 00 00 00 03 00 00 00 00-e2 20 00 01 0e 00 00 60 |........ . .....`|
0050 08 81 81 e8 0f 6d 7f ff-ff ff fe 02 0e 00 00 00 |.....m.. ........|
0060 00 00 00 00 00 00 02 68-00 00 00                |.......h ...|

22:21:51.052256 490 (token.c:417):tds_process_login_tokens()
22:21:51.053358 490 (packet.c:410):Received packet
0000 04 01 00 65 00 00 00 00-e3 09 00 03 06 63 70 31 |...e.... .....cp1|
0010 32 35 32 00 e3 09 00 04-03 35 31 32 03 35 31 32 |252..... .512.512|
0020 ad 16 00 05 05 00 00 00-0c 53 51 4c 20 41 6e 79 |........ .SQL Any|
0030 77 68 65 72 65 0a 00 00-00 e2 20 00 01 0e 00 00 |where... .. .....|
0040 60 08 01 81 e8 0e 61 01-ff ff fe e6 02 0e ff ff |`.....a. ........|
0050 00 00 00 01 fe 67 00 e2-68 00 00 0a fd 00 00 01 |.....g.. h.......|
0060 00 00 00 00 00         -                        |.....|

22:21:51.053408 490 (token.c:421):looking for login token, got  e3(ENVCHANGE)
22:21:51.053424 490 (token.c:130):tds_process_default_tokens() marker is e3(ENVCHANGE)
22:21:51.053444 490 (token.c:2301):server indicated charset change to "cp1252"
22:21:51.053460 490 (iconv.c:825):setting server single-byte charset to "CP1252"
22:21:51.053600 490 (token.c:421):looking for login token, got  e3(ENVCHANGE)
22:21:51.053618 490 (token.c:130):tds_process_default_tokens() marker is e3(ENVCHANGE)
22:21:51.053637 490 (token.c:2285):changing block size from 512 to 512
22:21:51.053653 490 (token.c:421):looking for login token, got  ad(LOGINACK)
22:21:51.053668 490 (token.c:347):server reports TDS version 5.0.0.0
22:21:51.053682 490 (token.c:349):Product name for 0x5000000 is unknown
22:21:51.053698 490 (token.c:383):Product version A000000
22:21:51.053712 490 (token.c:421):looking for login token, got  e2(CAPABILITY)
22:21:51.053726 490 (token.c:130):tds_process_default_tokens() marker is e2(CAPABILITY)
22:21:51.053741 490 (token.c:421):looking for login token, got  fd(DONE)
22:21:51.053755 490 (token.c:130):tds_process_default_tokens() marker is fd(DONE)
22:21:51.053769 490 (token.c:2096):tds_process_end: more_results = 0
                was_cancelled = 0
                error = 0
                done_count_valid = 0
22:21:51.053784 490 (token.c:2115):                rows_affected = 0
22:21:51.053798 490 (token.c:2118):tds_process_end() state set to TDS_IDLE
22:21:51.053812 490 (token.c:444):tds_process_login_tokens() returning TDS_SUCCESS
22:21:51.053836 490 (mem.c:656):tds_free_all_results()
22:21:51.053851 490 (util.c:179):Changed query state from IDLE to WRITING
22:21:51.053879 490 (packet.c:852):Sending packet
0000 0f 01 00 1c 00 00 00 00-21 0f 00 00 00 00 73 65 |........ !.....se|
0010 6c 65 63 74 20 40 40 73-70 69 64 20             |lect @@s pid |

22:21:51.053939 490 (util.c:179):Changed query state from WRITING to PENDING
22:21:51.053956 490 (token.c:554):tds_process_tokens(0x558a79f16d60, 0x7fff6bd13960, 0x7fff6bd13964, 0x500)
22:21:51.053971 490 (util.c:179):Changed query state from PENDING to READING
22:21:51.054365 490 (packet.c:410):Received packet
0000 04 01 00 38 00 00 00 00-61 1d 00 00 00 01 00 06 |...8.... a.......|
0010 40 40 73 70 69 64 00 00-00 06 40 40 73 70 69 64 |@@spid.. ..@@spid|
0020 00 00 00 00 07 00 00 00-38 00 d1 42 91 03 00 fd |........ 8..B....|
0030 10 00 01 00 01 00 00 00-                        |........|

22:21:51.054415 490 (token.c:569):processing result tokens.  marker is  61(ROWFMT2)
22:21:51.054431 490 (token.c:1786):tds5_process_result2
22:21:51.054445 490 (mem.c:656):tds_free_all_results()
22:21:51.054460 490 (token.c:1810):num_cols=1
22:21:51.054480 490 (token.c:1876):col 0:
22:21:51.054494 490 (token.c:1877):     column_name=[@@spid]
22:21:51.054507 490 (token.c:1883):     flags=0 utype=7 type=56 server type 56 varint=0
22:21:51.054521 490 (token.c:1887):     colsize=4 prec=0 scale=0
22:21:51.054536 490 (token.c:569):processing result tokens.  marker is  d1(ROW)
22:21:51.054551 490 (token.c:1951):tds_process_row(): reading column 0 
22:21:51.054565 490 (data.c:719):tds_get_data: type 56, varint size 0
22:21:51.054580 490 (data.c:763):tds_get_data(): wire column size is 4 
22:21:51.054594 490 (util.c:179):Changed query state from READING to PENDING
22:21:51.054608 490 (token.c:554):tds_process_tokens(0x558a79f16d60, 0x7fff6bd13960, 0x7fff6bd13964, 0x500)
22:21:51.054623 490 (util.c:179):Changed query state from PENDING to READING
22:21:51.054636 490 (token.c:569):processing result tokens.  marker is  fd(DONE)
22:21:51.054650 490 (token.c:2096):tds_process_end: more_results = 0
                was_cancelled = 0
                error = 0
                done_count_valid = 1
22:21:51.054664 490 (token.c:2115):                rows_affected = 1
22:21:51.054678 490 (token.c:2118):tds_process_end() state set to TDS_IDLE
22:21:51.054691 490 (util.c:179):Changed query state from READING to IDLE
22:21:51.054705 490 (util.c:83):logic error: cannot change query state from IDLE to PENDING
22:21:51.054718 490 (util.c:179):Changed query state from IDLE to IDLE
22:21:51.054732 490 (token.c:554):tds_process_tokens(0x558a79f16d60, 0x7fff6bd13960, 0x7fff6bd13964, 0x500)
22:21:51.054745 490 (token.c:557):tds_process_tokens() state is COMPLETED
22:21:53.472963 490 (query.c:3757):tds_disconnect() 
22:21:53.473056 490 (mem.c:656):tds_free_all_results()
22:21:53.473076 490 (util.c:179):Changed query state from IDLE to WRITING
22:21:53.473106 490 (packet.c:852):Sending packet
0000 0f 01 00 0a 00 00 00 00-71 00                   |........ q.|

22:21:53.473238 490 (util.c:179):Changed query state from WRITING to PENDING
22:21:53.473255 490 (token.c:554):tds_process_tokens(0x558a79f16d60, 0x7fff6bd141c0, 0x7fff6bd141c4, 0x100)
22:21:53.473270 490 (util.c:179):Changed query state from PENDING to READING
22:21:53.473903 490 (packet.c:410):Received packet
0000 04 01 00 11 00 00 00 00-fd 00 00 00 00 00 00 00 |........ ........|
0010 00                     -                        |.|

22:21:53.473928 490 (token.c:569):processing result tokens.  marker is  fd(DONE)
22:21:53.473944 490 (token.c:2096):tds_process_end: more_results = 0
                was_cancelled = 0
                error = 0
                done_count_valid = 0
22:21:53.473959 490 (token.c:2115):                rows_affected = 0
22:21:53.473973 490 (token.c:2118):tds_process_end() state set to TDS_IDLE
22:21:53.473987 490 (util.c:179):Changed query state from READING to IDLE
22:21:53.474001 490 (util.c:83):logic error: cannot change query state from IDLE to PENDING
22:21:53.474015 490 (util.c:179):Changed query state from IDLE to IDLE
22:21:53.474028 490 (token.c:554):tds_process_tokens(0x558a79f16d60, 0x7fff6bd141c0, 0x7fff6bd141c4, 0x100)
22:21:53.474042 490 (token.c:557):tds_process_tokens() state is COMPLETED
22:21:53.474117 490 (util.c:179):Changed query state from IDLE to DEAD
22:21:53.474134 490 (mem.c:656):tds_free_all_results()
freddy77 commented 5 months ago

I think I'll move to convert syntax, split the query in 2 selects and handle errors from UNIVARCHAR.

s7clarke10 commented 5 months ago

That sounds like a great plan. Thank you.On 4 May 2024, at 7:35 PM, freddy77 @.***> wrote: I think I'll move to convert syntax, split the query in 2 selects and handle errors from UNIVARCHAR.

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you authored the thread.Message ID: @.***>

freddy77 commented 5 months ago

Can you try with https://www.freetds.org/files/freetds-1.5.dev.20240428.tar.bz2 ?

s7clarke10 commented 5 months ago

Thank you. It does appear with the particular Sybase Version - Adaptive Server Enterprise/12.5.0.3 . That the query can work successful and gracefully handles there error as per this log. It will then progress with the particular query against the server.

mem.c:665:tds_free_all_results()
util.c:179:Changed query state from IDLE to WRITING
packet.c:852:Sending packet
0000 0f 01 00 88 00 00 00 00-21 7b 00 00 00 00 53 45 |........ !{....SE|
0010 4c 45 43 54 20 40 40 73-70 69 64 20 73 70 69 64 |LECT @@s pid spid|
0020 0a 55 53 45 20 6e 6d 64-73 0a 53 45 4c 45 43 54 |.USE nmd s.SELECT|
0030 20 43 4f 4e 56 45 52 54-28 4e 56 41 52 43 48 41 | CONVERT (NVARCHA|
0040 52 28 33 29 2c 20 27 61-62 63 27 29 20 6e 76 63 |R(3), 'a bc') nvc|
0050 0a 45 58 45 43 55 54 45-20 28 27 53 45 4c 45 43 |.EXECUTE  ('SELEC|
0060 54 20 43 4f 4e 56 45 52-54 28 55 4e 49 56 41 52 |T CONVER T(UNIVAR|
0070 43 48 41 52 28 33 29 2c-20 27 27 78 79 7a 27 27 |CHAR(3),  ''xyz''|
0080 29 20 75 76 63 27 29 0a-                        |) uvc').|

util.c:179:Changed query state from WRITING to PENDING
token.c:555:tds_process_tokens(0x55690c5be090, 0x7ffdf09b378c, 0x7ffdf09b3788, 0x500)
util.c:179:Changed query state from PENDING to READING
packet.c:410:Received packet
0000 04 01 01 4b 00 00 00 00-61 1a 00 00 00 01 00 04 |...K.... a.......|
0010 73 70 69 64 00 00 00 04-73 70 69 64 30 00 00 00 |spid.... spid0...|
0020 06 00 00 00 26 02 00 ae-01 00 00 d1 02 a5 00 fd |....&... ........|
0030 11 00 02 00 01 00 00 00-e3 0b 00 01 04 XX XX XX |........ .....XXX|
0040 73 04 XX XX XX XX e5 40-00 45 16 00 00 01 0a 05 |s.xxxx.@ .E......|
0050 5a 5a 5a 5a 5a 00 01 00-24 00 43 68 61 6e 67 65 |ZZZZZ... $.Change|
0060 64 20 64 61 74 61 62 61-73 65 20 63 6f 6e 74 65 |d databa se conte|
0070 78 74 20 74 6f 20 27 XX-XX XX XX 27 2e 0a 07 XX |xt to 'x xxx'...X|
0080 XX XX XX XX XX XX 00 02-00 fd 01 00 02 00 00 00 |XXXXXX.. ........|
0090 00 00 61 18 00 00 00 01-00 03 6e 76 63 00 00 00 |..a..... ..nvc...|
00a0 03 6e 76 63 10 00 00 00-19 00 00 00 27 03 00 ae |.nvc.... ....'...|
00b0 01 00 00 d1 03 61 62 63-fd 11 00 02 00 01 00 00 |.....abc ........|
00c0 00 e5 7e 00 dc 2d 00 00-01 0f 05 5a 5a 5a 5a 5a |..~..-.. ...ZZZZZ|
00d0 00 01 00 62 00 55 4e 49-43 48 41 52 20 61 6e 64 |...b.UNI CHAR and|
00e0 20 55 4e 49 56 41 52 43-48 41 52 20 64 61 74 61 | UNIVARC HAR data|
00f0 74 79 70 65 73 20 61 72-65 20 6f 6e 6c 79 20 73 |types ar e only s|
0100 75 70 70 6f 72 74 65 64-20 77 68 65 6e 20 73 65 |upported  when se|
0110 72 76 65 72 27 73 20 64-65 66 61 75 6c 74 20 63 |rver's d efault c|
0120 68 61 72 61 63 74 65 72-20 73 65 74 20 69 73 20 |haracter  set is |
0130 55 54 46 2d 38 2e 0a 07-XX XX XX XX XX XX XX 00 |UTF-8... XXXXXXX.|
0140 01 00 fd 02 00 02 00 00-00 00 00                |........ ...|

token.c:570:processing result tokens.  marker is  61(ROWFMT2)
token.c:1790:tds5_process_result2
mem.c:665:tds_free_all_results()
token.c:1814:num_cols=1
token.c:1880:col 0:
token.c:1881:   column_name=[spid]
token.c:1887:   flags=30 utype=6 type=38 server type 38 varint=1
token.c:1891:   colsize=2 prec=0 scale=0
token.c:570:processing result tokens.  marker is  ae(CONTROL/FEATUREEXTACK)
token.c:131:tds_process_default_tokens() marker is ae(CONTROL/FEATUREEXTACK)
token.c:250:Eating CONTROL/FEATUREEXTACK token
token.c:570:processing result tokens.  marker is  d1(ROW)
token.c:1955:tds_process_row(): reading column 0 
data.c:723:tds_get_data: type 38, varint size 1
data.c:767:tds_get_data(): wire column size is 2 
util.c:179:Changed query state from READING to PENDING
token.c:555:tds_process_tokens(0x55690c5be090, 0x7ffdf09b378c, 0x7ffdf09b3788, 0x500)
util.c:179:Changed query state from PENDING to READING
token.c:570:processing result tokens.  marker is  fd(DONE)
token.c:2100:tds_process_end: more_results = 1
                was_cancelled = 0
                error = 0
                done_count_valid = 1
token.c:2119:                rows_affected = 1
util.c:179:Changed query state from READING to PENDING
token.c:555:tds_process_tokens(0x55690c5be090, 0x7ffdf09b378c, 0x7ffdf09b3788, 0x500)
util.c:179:Changed query state from PENDING to READING
token.c:570:processing result tokens.  marker is  e3(ENVCHANGE)
token.c:131:tds_process_default_tokens() marker is e3(ENVCHANGE)
token.c:570:processing result tokens.  marker is  e5(EED)
token.c:131:tds_process_default_tokens() marker is e5(EED)
mem.c:665:tds_free_all_results()
token.c:2402:tds_process_info() reading message 5701 from server
token.c:2497:tds_process_info() calling client msg handler
token.c:2514:tds_process_info() returning TDS_SUCCESS
token.c:570:processing result tokens.  marker is  fd(DONE)
token.c:2100:tds_process_end: more_results = 1
                was_cancelled = 0
                error = 0
                done_count_valid = 0
token.c:2119:                rows_affected = 0
util.c:179:Changed query state from READING to PENDING
token.c:555:tds_process_tokens(0x55690c5be090, 0x7ffdf09b378c, 0x7ffdf09b3788, 0x500)
util.c:179:Changed query state from PENDING to READING
token.c:570:processing result tokens.  marker is  61(ROWFMT2)
token.c:1790:tds5_process_result2
mem.c:665:tds_free_all_results()
token.c:1814:num_cols=1
token.c:3249:adjust_character_column_size:
        Server charset: UTF-8
        Server column_size: 3
        Client charset: UTF-8
        Client column_size: 3
token.c:1880:col 0:
token.c:1881:   column_name=[nvc]
token.c:1887:   flags=10 utype=25 type=39 server type 39 varint=1
token.c:1891:   colsize=3 prec=0 scale=0
token.c:570:processing result tokens.  marker is  ae(CONTROL/FEATUREEXTACK)
token.c:131:tds_process_default_tokens() marker is ae(CONTROL/FEATUREEXTACK)
token.c:250:Eating CONTROL/FEATUREEXTACK token
token.c:570:processing result tokens.  marker is  d1(ROW)
token.c:1955:tds_process_row(): reading column 0 
data.c:723:tds_get_data: type 39, varint size 1
data.c:767:tds_get_data(): wire column size is 3 
util.c:179:Changed query state from READING to PENDING
token.c:555:tds_process_tokens(0x55690c5be090, 0x7ffdf09b378c, 0x7ffdf09b3788, 0x500)
util.c:179:Changed query state from PENDING to READING
token.c:570:processing result tokens.  marker is  fd(DONE)
token.c:2100:tds_process_end: more_results = 1
                was_cancelled = 0
                error = 0
                done_count_valid = 1
token.c:2119:                rows_affected = 1
util.c:179:Changed query state from READING to PENDING
token.c:555:tds_process_tokens(0x55690c5be090, 0x7ffdf09b378c, 0x7ffdf09b3788, 0x500)
util.c:179:Changed query state from PENDING to READING
token.c:570:processing result tokens.  marker is  e5(EED)
token.c:131:tds_process_default_tokens() marker is e5(EED)
mem.c:665:tds_free_all_results()
token.c:2402:tds_process_info() reading message 11740 from server
token.c:2497:tds_process_info() calling client msg handler
token.c:2514:tds_process_info() returning TDS_SUCCESS
token.c:570:processing result tokens.  marker is  fd(DONE)
token.c:2100:tds_process_end: more_results = 0
                was_cancelled = 0
                error = 1
                done_count_valid = 0
token.c:2119:                rows_affected = 0
token.c:2122:tds_process_end() state set to TDS_IDLE
util.c:179:Changed query state from READING to IDLE
util.c:83:logic error: cannot change query state from IDLE to PENDING
util.c:179:Changed query state from IDLE to IDLE
token.c:555:tds_process_tokens(0x55690c5be090, 0x7ffdf09b378c, 0x7ffdf09b3788, 0x500)
token.c:558:tds_process_tokens() state is COMPLETED
dbutil.c:75:_dblib_handle_info_message(0x55690c4ff0d0, 0x55690c5be090, 0x7ffdf09b3c48)
dbutil.c:76:msgno 5701: "Changed database context to 'xxxx'.
"
dbutil.c:75:_dblib_handle_info_message(0x55690c4ff0d0, 0x55690c5be090, 0x7ffdf09b3c80)
dbutil.c:76:msgno 5701: "Changed database context to 'xxxx'.
"
dbutil.c:75:_dblib_handle_info_message(0x55690c4ff0d0, 0x55690c5be090, 0x7ffdf09b3cb8)
dbutil.c:76:msgno 11740: "UNICHAR and UNIVARCHAR datatypes are only supported when server's default character set is UTF-8.
s7clarke10 commented 5 months ago

When I try this command against another test Sybase Anywhere Server 16.0.0.2043. I get an error message .

SELECT CONVERT(UNIVARCHAR(3), 'xyz') AS uvc
SQL Error [102] [42W04]: SQL Anywhere Error -131: Syntax error near '3' on line 1

I believe I receive this error message because UNIVARCHAR is an unknown datatype on this flavour of Sybase so it gives a different error message.

I didn't get this type of error message before. I used this server as a small test server using the docker image https://hub.docker.com/r/jaschweder/sybase/.

So the new code fixes the issue for the server I really care about - but I note that the UNIVARCHAR datatype maybe problematic on different types of Sybase Servers. I maybe wrong here, but is this related to this old issue - https://github.com/FreeTDS/freetds/issues/342 .

Note:

The following SQL Statement works just fine.

SELECT CONVERT(NVARCHAR(3), 'abc') AS nvc
freddy77 commented 5 months ago

Version 1.4.13 from website should work as expected! https://www.freetds.org/

s7clarke10 commented 5 months ago

Hi @freddy77 ,

This change looks good and is working now for my Sybase databases. Thank you very much for resolving this issue.

I really appreciated your support resolving this. 👍

I am happy for you to push this into a new Release when you are ready to do that - thanks again.

s7clarke10 commented 5 months ago

Testing the new stable version of freetds with pymssql. https://github.com/pymssql/pymssql/issues/895

freddy77 commented 4 months ago

I suppose I can close this issue