FreeTDS：如何设置运行存储过程的参数的字符集 [英] FreeTDS: How to set charset of parameters running stored procedure

问题描述

我有一个C程序通过

这是我的freetds.conf：

 ＃$ Id：freetds.conf，v 1.12 2007/12/25 06:02:36 jklowden Exp $ 
＃
＃如果在安装目录中找不到相同的
＃名称的文件，则此文件由FreeTDS安装。 
＃
＃有关此文件及其设置的布局的信息，
＃请参阅freetds.conf手册页man freetds.conf。 
 
＃数据库中的全局设置被覆盖
＃服务器特定部分
 [global] 
＃TDS协议版本
 tds版本= 8.0 
 
＃是否为了诊断目的编写TDSDUMP文件
＃（将此设置为/ tmp在多用户系统上不安全）
 dump file = /tmp/freetds.log 
; debug flags = 0xffff 
 
＃命令和连接超时
; timeout = 10 
; connect timeout = 10 
 
＃如果出现内存不足错误，这可能意味着您的客户端
＃正在尝试为TEXT字段分配一个巨大的缓冲区。 
＃尝试将文本大小设置为更合理的限制
文本大小= 64512 
 
客户端字符集= UTF-8 
  

这里是tds转储文件的一部分：

  log.c：196：FreeTDS的启动日志文件0.91 
在2016-05-17 19:19:41与调试标志0x4fff。 
 iconv.c：330：tds_iconv_open（0xaed19130，ISO-8859-1）
 iconv.c：353：使用trivial iconv 
 iconv.c：187：ISO-8859- 1是ISO-8859-1 
 iconv.c：187：UTF-8的本地名称是UTF-8 
 iconv.c：187：UCS-2LE的本地名称是UCS-2LE 
 iconv.c：187：UCS-2BE的本地名是UCS-2BE 
 iconv.c：349：为客户端字符集ISO-8859-1设置转换
 iconv.c：351：为ISO-8859-1准备iconv。 UCS-2LE转换
 iconv.c：391：为ISO-8859-1准备iconv<  - > ISO-8859-1转换
 iconv.c：394：tds_iconv_open：done 
 
 ... 
 
 net.c：741：发送数据包
 0000 01 01 00 84 00 00 00 00-20 45 58 45 43 20 55 50 | ........ EXEC UP | 
 0010 5f 49 4e 53 45 52 54 4d-4f 56 49 4d 45 4e 54 4f | _INSERTM OVIMENTO | 
 0020 20 27 32 30 31 36 2d 30-35 2d 31 37 20 31 39 3a | 2016-05-17 19： 
 0030 31 39 3a 33 31 2e 30 30-30 27 2c 27 30 30 30 30 | 19：31.00 0'，'0000 | 
 0040 30 30 30 30 30 30 36 34-38 33 30 35 37 30 33 30 | 00000064 83057031 | 
 0050 27 2c 27 53 27 2c 30 2c-32 2c 27 50 43 56 43 30 |'，'S'，0，2，'PCVC0 | 
 0060 34 20 3d 20 53 61 c3 ad-64 61 20 4c 69 62 65 72 | 4 = Sa .. da Liber | 
 0070 61 64 61 27 2c 34 2c 27-50 43 56 43 4d 41 4e 41 | ada'，4，'PCVCMANA | 
 0080 47 45 52 27  -  | GER'| 
  

根据freetds日志，它发送字符'í'这意味着它发送它作为UTF-8（拉丁小写字母I与ACUTE）。

如何设置客户端或服务器，所以字符串可以正确存储？

更新1：

  ＃tsql -C 
编译时设置（使用configure脚本建立）
版本：freetds v0.91 
 freetds.conf目录：/ etc 
 MS db-lib源兼容性：no 
 Sybase二进制兼容性：是
线程安全：是
 iconv库：no 
 TDS版本：4.2 
 iODBC：no 
 unixodbc： yes 
 SSPItrustedlogins：no 
 Kerberos：no 
＃
  

更新2

要连接的代码：

 code> ret = SQLDriverConnect（db_msserver_dbc_handle，
 NULL，Driver = {FreeTDS}; Server = FooBar; Port = 1433; Database = Foo; UID = sa; PWD = pwd @ 123; APP = XPTO; TDS_Version = 8.0;，
 SQL_NTS，outstr，sizeof（outstr），& outstrlen，
 SQL_DRIVER_COMPLETE）; 
  

解决方案

经过很多尝试， out为什么freetds.conf设置（客户端字符集和tds版本）不受尊重。至少，当我将 TDS_Version = 8.0; ClientCharset = UTF-8 附加到

 Driver = {FreeTDS}; Server =％s; Port =％s; Database =％s; UID =％s; PWD =％s; APP =％s; TDS_Version = 8.0; ClientCharset = UTF-8
  

日志文件更改，提到UTF-8转换：

  log.c：196：FreeTDS的启动日志文件0.91 
在2016-05-18 15:58:49与调试标志0x4fff。 
 iconv.c：330：tds_iconv_open（0xaeb19118，UTF-8）
 iconv.c：353：使用trivial iconv 
 iconv.c：187：ISO-8859-1的本地名称ISO-8859-1 
 iconv.c：187：UTF-8的本地名称是UTF-8 
 iconv.c：187：UCS-2LE的本地名称是UCS-2LE 
 iconv .c：187：UCS-2BE的本地名是UCS-2BE 
 iconv.c：349：为客户端字符集UTF-8设置转换
 iconv.c：351：为 UTF-8。 UCS-2LE转换
 iconv.c：391：为ISO-8859-1准备iconv<  - > UCS-2LE转换
 iconv.c：394：tds_iconv_open：done 
  

I have a C program connecting to a MSSQL database through FreeTDS.

I'm trying to execute a stored procedure with a varchar parameter set to something like "Saída Liberada", but it's being stored with strange characters in MS SQL, as the screenshot above:

Here is my freetds.conf:

#   $Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".

# Global settings are overridden by those in a database
# server specific section
[global]
        # TDS protocol version
        tds version = 8.0

        # Whether to write a TDSDUMP file for diagnostic purposes
        # (setting this to /tmp is insecure on a multi-user system)
        dump file = /tmp/freetds.log
;       debug flags = 0xffff

        # Command and connection timeouts
;       timeout = 10
;       connect timeout = 10

        # If you get out-of-memory errors, it may mean that your client
        # is trying to allocate a huge buffer for a TEXT field.
        # Try setting 'text size' to a more reasonable limit
        text size = 64512

        client charset = UTF-8

Here is parts of the tds dump file:

log.c:196:Starting log file for FreeTDS 0.91
        on 2016-05-17 19:19:41 with debug flags 0x4fff.
iconv.c:330:tds_iconv_open(0xaed19130, ISO-8859-1)
iconv.c:353:Using trivial iconv
iconv.c:187:local name for ISO-8859-1 is ISO-8859-1
iconv.c:187:local name for UTF-8 is UTF-8
iconv.c:187:local name for UCS-2LE is UCS-2LE
iconv.c:187:local name for UCS-2BE is UCS-2BE
iconv.c:349:setting up conversions for client charset "ISO-8859-1"
iconv.c:351:preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion
iconv.c:391:preparing iconv for "ISO-8859-1" <-> "ISO-8859-1" conversion
iconv.c:394:tds_iconv_open: done

...

net.c:741:Sending packet
0000 01 01 00 84 00 00 00 00-20 45 58 45 43 20 55 50 |........  EXEC UP|
0010 5f 49 4e 53 45 52 54 4d-4f 56 49 4d 45 4e 54 4f |_INSERTM OVIMENTO|
0020 20 27 32 30 31 36 2d 30-35 2d 31 37 20 31 39 3a | '2016-0 5-17 19:|
0030 31 39 3a 33 31 2e 30 30-30 27 2c 27 30 30 30 30 |19:31.00 0','0000|
0040 30 30 30 30 30 30 36 34-38 33 30 35 37 30 33 30 |00000064 83057030|
0050 27 2c 27 53 27 2c 30 2c-32 2c 27 50 43 56 43 30 |','S',0, 2,'PCVC0|
0060 34 20 3d 20 53 61 c3 ad-64 61 20 4c 69 62 65 72 |4 = Sa.. da Liber|
0070 61 64 61 27 2c 34 2c 27-50 43 56 43 4d 41 4e 41 |ada',4,' PCVCMANA|
0080 47 45 52 27            -                        |GER'|

According to the freetds log, it's sending the character 'í' (i acute) as c3 ad, which means it's sending it as UTF-8 (LATIN SMALL LETTER I WITH ACUTE).

How can I setup the client or the server, so the string can be properly stored?

Update 1:

# tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v0.91
             freetds.conf directory: /etc
     MS db-lib source compatibility: no
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: no
                        TDS version: 4.2
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: no
#

Update 2

Code to connect:

ret = SQLDriverConnect(db_msserver_dbc_handle,
    NULL,  "Driver={FreeTDS};Server=FooBar;Port=1433;Database=Foo;UID=sa;PWD=pwd@123;APP=XPTO;TDS_Version=8.0;",
    SQL_NTS, outstr, sizeof(outstr), &outstrlen,
    SQL_DRIVER_COMPLETE);

解决方案

After a lot of attempts, I couldn't figure out why freetds.conf settings (client charset and tds version) are not being respected. At least, when I append TDS_Version=8.0;ClientCharset=UTF-8 into the connection string, it works!

"Driver={FreeTDS};Server=%s;Port=%s;Database=%s;UID=%s;PWD=%s;APP=%s;TDS_Version=8.0;ClientCharset=UTF-8"

Also, the header of freetds log file is changed, mentioning UTF-8 conversion:

log.c:196:Starting log file for FreeTDS 0.91
        on 2016-05-18 15:58:49 with debug flags 0x4fff.
iconv.c:330:tds_iconv_open(0xaeb19118, UTF-8)
iconv.c:353:Using trivial iconv
iconv.c:187:local name for ISO-8859-1 is ISO-8859-1
iconv.c:187:local name for UTF-8 is UTF-8
iconv.c:187:local name for UCS-2LE is UCS-2LE
iconv.c:187:local name for UCS-2BE is UCS-2BE
iconv.c:349:setting up conversions for client charset "UTF-8"
iconv.c:351:preparing iconv for "UTF-8" <-> "UCS-2LE" conversion
iconv.c:391:preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion
iconv.c:394:tds_iconv_open: done

这篇关于FreeTDS：如何设置运行存储过程的参数的字符集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！