FreeTDS Development Group

[Hegedűs Gábor <ghegedus0 AT freemail.hu>]

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@hegedus:~$ export TDSDUMP=/tmp/freetds.log 
hegedus@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@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