[freetds] Character conversion , command order

Hegedűs Gábor ghegedus0 at freemail.hu
Wed Mar 17 11:13:11 EDT 2010


Hello Colleagues,

I have a MSSQL 2000 database and a system written in WinPROLOG (LPA;
unicode character coding) using it via ODBC. They are running with no
problem in Win XP (on common or different hardware). Now I try to
migrate the last one (the DB remains in WinXP) into the next
environment: Ubuntu 2.6.31-20 (English), Wine1.2 (1.1.40-0ubuntu1; XP),
unixodbc (2.2.11-16ubuntu1), freetds-0.82 (freetds-stable 2009.12.11).
The main settings:

/etc/odbcinst.ini:

[ODBC]
Trace		= yes
Tracefile	= /tmp/odbc.log
ForceTrace	= Yes
[mssql_tds]
Description	= MSSQL driver
Driver		= /usr/local/lib/libtdsodbc.so
Driver64		= 
Setup		= /usr/lib/odbc/libtdsS.so
Setup64		= 
UsageCount	= 2 
CPTimeout	= 
CPReuse		= 

/etc/odbc.ini:

[MSSQL]
Description	= mssql_tds
Driver		= mssql_tds
Server		= 192.168.1.65
Port		= 1433
Trace		= Yes
TraceFile	= /tmp/odbc.log

/usr/local/etc/freetds.conf (active lines)

[global]
	tds version = 8.0
	client charset = UTF-8
	dump file = /tmp/freetds.log
	dump file append = yes
	debug flags = 0xffff
	debug level = 10
	text size = 64512
[mssql_tds]
	host = 192.168.1.65
	port = 1433
	tds version = 8.0
	client charset = UTF-8

1) A slight problem: There is no freetds.log unless export
TDSDUMP=/tmp/freetds.log (start of Wine is complicated this way).

What to do to activate settings of freetds.conf?

2) The character conversion problem. On terminal I see:

hegedus at hegedus:~$ export TDSDUMP=/tmp/freetds.log 
hegedus at hegedus:~$ isql -v MSSQL user pw 
 <Connected! etc>
SQL> sp_databases 
 <list of databases including model>
SQLRowCount returns -1 
6 rows fetched 
SQL> use model 
[01000][unixODBC][FreeTDS][SQL Server]Changed database context to
'model'. 
[ISQL]INFO: SQLExecute returned SQL_SUCCESS_WITH_INFO 
SQLRowCount returns -1 
SQL> select * from jog 
+------------+---------------------------------+ 
| j_azon     | j_nev                           | 
+------------+---------------------------------+ 
| 1          | | 
| 2          | ellen?r                         | 
| 4          | | 
| 3          | | 
+------------+---------------------------------+ 
SQLRowCount returns 4 
4 rows fetched 
SQL> quit 
hegedus at hegedus:~$ 

In the freetdslog starts with:

iconv.c:197:names for ISO-8859-1: ISO-8859-1
iconv.c:197:names for UTF-8: UTF-8
iconv.c:197:names for UCS-2LE: UCS-2LE
iconv.c:197:names for UCS-2BE: UCS-2BE
iconv.c:363:iconv to convert client-side data to the "ISO-8859-1"
character set
iconv.c:516:tds_iconv_info_init: converting "ISO-8859-1"->"UCS-2LE"
iconv.c:516:tds_iconv_info_init: converting "ISO-8859-1"->"UCS-2LE"

The result of the select in the log is:

net.c:671:Received packet
0000 81 02 00 00 00 08 00 38-06 6a 00 5f 00 61 00 7a |.......8 .j._.a.z|
0010 00 6f 00 6e 00 00 00 08-00 a7 20 00 0e 04 d0 00 |.o.n.... .. .....|
0020 00 05 6a 00 5f 00 6e 00-65 00 76 00 d1 01 00 00 |..j._.n. e.v.....|
0030 00 0e 00 61 64 6d 69 6e-69 73 7a 74 72 e1 74 6f |...admin isztr.to|
0040 72 d1 02 00 00 00 07 00-65 6c 6c 65 6e f5 72 d1 |r....... ellen.r.|
0050 04 00 00 00 06 00 6f 6c-76 61 73 f3 d1 03 00 00 |......ol vas.....|
0060 00 07 00 72 f6 67 7a ed-74 f5 ff 11 00 c1 00 04 |...r.gz. t.......|
0070 00 00 00 79 00 00 00 00-fe 00 00 e0 00 04 00 00 |...y.... ........|
0080 00                     -                        |.|

The ? in the only displayed name is instead of o-double-acute (not in
8859-1); the missing lines have characters existing in 8859-1 but not in
ASCI (the last one has 2 of them with o-double-acute). 

What to do to handle non-ASCI characters?

3) The mentioned programme uses procedure of MSSQL sp_databases, but –
as I see in the odbc.log – with different to isql set of ODBC commands.

The corresponding part of the odbc.log of isql is:

[ODBC][5053][SQLPrepare.c][189]
		Entry:            
			Statement = 0x80da110            
			SQL = [sp_databases][length = 12 (SQL_NTS)]
[ODBC][5053][SQLPrepare.c][364]
		Exit:[SQL_SUCCESS]
[ODBC][5053][SQLExecute.c][183]
		Entry:            
			Statement = 0x80da110
[ODBC][5053][SQLExecute.c][344]
		Exit:[SQL_SUCCESS]
[ODBC][5053][SQLNumResultCols.c][149]
		Entry:            
			Statement = 0x80da110            
			Column Count = 0xbfaaf44e
[ODBC][5053][SQLNumResultCols.c][234]
		Exit:[SQL_SUCCESS]                
			Count = 0xbfaaf44e -> 3
[ODBC][5053][SQLNumResultCols.c][149]
		Entry:            
			Statement = 0x80da110            
			Column Count = 0xbfaaf18c
[ODBC][5053][SQLNumResultCols.c][234]
		Exit:[SQL_SUCCESS]                
			Count = 0xbfaaf18c -> 3
[ODBC][5053][SQLColAttribute.c][277]
		Entry:            
			Statement = 0x80da110            
			Column Number = 1            
			Field Identifier = SQL_DESC_DISPLAY_SIZE            
			Character Attr = (nil)            
			Buffer Length = 0            
			String Length = (nil)            
			Numeric Attribute = 0xbfaaefe8
[ODBC][5053][SQLColAttribute.c][648]
		Exit:[SQL_SUCCESS]
[ODBC][5053][SQLColAttribute.c][277]
		Entry:            
			Statement = 0x80da110            
			Column Number = 1            
			Field Identifier = SQL_DESC_LABEL            
			Character Attr = 0xbfaaefef            
			Buffer Length = 301            
			String Length = (nil)            
			Numeric Attribute = (nil)
[ODBC][5053][SQLColAttribute.c][648]
		Exit:[SQL_SUCCESS]

That of Prolog programme is:

[ODBC][4992][SQLExecDirectW.c][135]
		Entry:            
			Statement = 0x7e03cc70            
			SQL = [sp_databases][length = 12 (SQL_NTS)]
[ODBC][4992][SQLExecDirectW.c][393]
		Exit:[SQL_SUCCESS]
[ODBC][4992][SQLNumResultCols.c][149]
		Entry:            
			Statement = 0x7e03cc70            
			Column Count = 0x10107776
[ODBC][4992][SQLNumResultCols.c][234]
		Exit:[SQL_SUCCESS]                
			Count = 0x10107776 -> 3
[ODBC][4992][SQLDescribeColW.c][155]
		Entry:            
			Statement = 0x7e03cc70            
			Column Number = 1            
			Column Name = 0x10147fc0            
			Buffer Length = 120            
			Name Length = 0x10107720            
			Data Type = 0x10107b98            
			Column Size = 0x1000730c            
			Decimal Digits = 0x10147fb0            
			Nullable = 0x10007310
[ODBC][4992][SQLDescribeColW.c][398]
		Exit:[SQL_SUCCESS]                
			Column Name = [D]                
			Data Type = 0x10107b98 -> 12                
			Column Size = 0x1000730c -> 128                
			Decimal Digits = 0x10147fb0 -> 0                
			Nullable = 0x10007310 -> 1
[ODBC][4992][SQLBindCol.c][165]
		Entry:            
			Statement = 0x7e03cc70            
			Column Number = 1            
			Target Type = -8 SQL_WCHAR            
			Target Value = 0x10007720            
			Buffer Length = 168            
			StrLen Or Ind = 0x10107ba4
[ODBC][4992][SQLBindCol.c][251]
		Exit:[SQL_ERROR]
		DIAG [S1003] [FreeTDS][SQL Server]Program type out of range

What can I do for this range (accepted by Windows-MSSQL2000 pair)?

Thank you
Gábor





More information about the FreeTDS mailing list