blocks|key|219325|text|MSSQL和UTF-8在...有时候。我不得不手动转换它。问题:+MSSQL实际上并不知道和支持UTF-8。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|219326|将数据库值转换为UTF-8：|219327|mb_detect_encoding($value,+mb_detect_order(),+true)+===+'UTF-8'+?+$value+:+mb_convert_encoding($value,+'UTF-8');|code-block|syntax|javascript|219328|从UTF-8转换为数据库值：|219329|mb_convert_encoding($value,+'UCS-2LE',+mb_detect_encoding($value,+mb_detect_order(),+true));|219330|幸运的是，我使用的是Doctrine，所以我所拥有的就是创建一个自定义的StringType实现。|219331|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

MSSQL and UTF-8 are quite a pain in the ... sometimes. I had to convert it manually.
The problem: MSSQL doesn't actually know and support UTF-8.

Convert from database value to UTF-8:

<pre><code>mb_detect_encoding($value, mb_detect_order(), true) === 'UTF-8' ? $value : mb_convert_encoding($value, 'UTF-8');
</code></pre>

Converting from UTF-8 to database value:

<pre><code>mb_convert_encoding($value, 'UCS-2LE', mb_detect_encoding($value, mb_detect_order(), true));
</code></pre>

Fortunately I was using Doctrine so all I had was to create a custom StringType implementation.

blocks|key|219387|text|我也有类似的问题，我尝试了所有我能在网上找到的设置--都是徒劳的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|219388|在我的例子中，问题出在FreeTDS本身的配置上。在Linux上，该文件为/etc/freetds/freetds.conf|219389|我不得不将版本改为7.0+(也许其他数字也可以。我刚试过7.0)|219390|[global]
++++#+TDS+protocol+version
++++tds+version+=+7.0|code-block|syntax|javascript|219391|在此之后，驱动程序似乎接受字符集的更改，如。|219392|ini_set('mssql.charset',+'UTF-8');|219393|顺便说一句:更改立即生效，之后不需要重新启动任何内容|219394|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|U|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|V|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|X|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

I had a similar problem and tried all settings i could find on the web - in vain.

In my case the problem was the configuration of FreeTDS itself. On Linux the file is /etc/freetds/freetds.conf

I had to change the Version to 7.0 (maybe other numbers work, too. i just tried 7.0)

<pre><code>[global]
 # TDS protocol version
 tds version = 7.0
</code></pre>

After this, the driver seemed to accept changes of the charset like. 

<pre><code>ini_set('mssql.charset', 'UTF-8');
</code></pre>

Btw: the change immediately is in effect, no need to restart anything afterwards

blocks|key|219446|text|您还可以通过在连接到数据库之前在$connectionInfo中添加CharacterSet+UTF-8来解决此问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|219447|$serverName+=+"MyServer";
$connectionInfo+=+array(+"Database"=>"AdventureWorks",+"CharacterSet"+=>+"UTF-8");
$conn+=+sqlsrv_connect(+$serverName,+$connectionInfo);|code-block|syntax|javascript|219448|运行良好，不需要额外的编码。|219449|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You can also solve this issue by adding CharacterSet UTF-8 in the $connectionInfo before connecting to the DB.

<pre><code>$serverName = "MyServer";
$connectionInfo = array( "Database"=&gt;"AdventureWorks", "CharacterSet" =&gt; "UTF-8");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
</code></pre>

Worked fine NO additional encoding needed.

blocks|key|219473|text|似乎需要7.0或更高版本。iconv()似乎也工作得很好，但很繁琐。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|219474|$query+=+$this->db->query($sql);
$result+=+$query->fetchAll(PDO::FETCH_OBJ);
foreach+($result+as+$row)+{
++++foreach+(get_object_vars($row)+as+$key+=>+$value)+{
++++$row->$key+=+(mb_detect_encoding($value,+mb_detect_order(),+true)+===+'UTF-8')+
++++++++++++?+$value+:+iconv('iso-8859-1',+'utf-8',+$value);
++++}
++++$results[]+=+$row;
}|code-block|syntax|javascript|219475|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

It seem version 7.0 or great is required. iconv() also seems to work well, but is tedious.

<pre><code>$query = $this-&gt;db-&gt;query($sql);
$result = $query-&gt;fetchAll(PDO::FETCH_OBJ);
foreach ($result as $row) {
 foreach (get_object_vars($row) as $key =&gt; $value) {
 $row-&gt;$key = (mb_detect_encoding($value, mb_detect_order(), true) === 'UTF-8') 
 ? $value : iconv('iso-8859-1', 'utf-8', $value);
 }
 $results[] = $row;
}
</code></pre>

blocks|key|219515|text|如果您使用freeTDS，则应在/etc/freetds/freetds.conf上更改以下行|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|219516|[global]
#+TDS+protocol+version
tds+version+=+4.2|code-block|syntax|javascript|219517|要这样做：|219518|[global]
#+TDS+protocol+version
tds+version+=+8.0
;tds+version+=+4.2|219519|最后添加下面这一行：|219520|#+set+charset
client+charset+=+UTF-8|219521|**客户端字符集在全局范围内|219522|在查询中，您应该使用N个字符。如下所示：|219523|$query+=+"INSERT+INTO+dbo.SMSOutbox+(StationID,+Dest,+Text)+VALUES+";
+++$query+.=+'(';
+++$query+.=+"'"+.+$this->stationId+.+"',+";
+++$query+.=+"'"+.+$this->destination+.+"',+";
+++$query+.=+"N'"+.+$this->text+.+"'";
+++$query+.=+')';|219524|entityMap^0|G|P|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|13|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|14|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|15|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|16|8|@]|D|@]|E|$]]|$1|Q|3|R|5|H|7|17|8|@]|D|@]|E|$I|J]]|$1|S|3|T|5|6|7|18|8|@]|D|@]|E|$]]|$1|U|3|V|5|6|7|19|8|@]|D|@]|E|$]]|$1|W|3|X|5|H|7|1A|8|@]|D|@]|E|$I|J]]|$1|Y|3|-4|5|6|7|1B|8|@]|D|@]|E|$]]]|Z|$]]

If you use freeTDS, you should change below lines on <code>/etc/freetds/freetds.conf</code>:

<pre><code>[global]
# TDS protocol version
tds version = 4.2
</code></pre>

To this:

<pre><code>[global]
# TDS protocol version
tds version = 8.0
;tds version = 4.2
</code></pre>

and finally add this line:

<pre><code># set charset
client charset = UTF-8
</code></pre>

** clinet charset is in global [scope]

In your queries, you should use N character. like this:

<pre><code>$query = "INSERT INTO dbo.SMSOutbox (StationID, Dest, Text) VALUES ";
 $query .= '(';
 $query .= "'" . $this-&gt;stationId . "', ";
 $query .= "'" . $this-&gt;destination . "', ";
 $query .= "N'" . $this-&gt;text . "'";
 $query .= ')';
</code></pre>

blocks|key|437683|text|我遇到了这个问题，通过在连接到MSSQL+Server之前将下面这一行添加到我的php脚本中，解决了这个问题：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|437684|ini_set('mssql.charset',+'UTF-8');|code-block|syntax|javascript|437685|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I had this problem and it solved by adding this line to my php script before connecting to MSSQL Server:

<pre><code>ini_set('mssql.charset', 'UTF-8');
</code></pre>

blocks|key|437695|text|您应该根据您使用的SQL+server版本来更改您的TDS版本。有关详细信息，请查看安装指南。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|437696|http://www.freetds.org/userguide/choosingtdsprotocol.htm|offset|length|437697|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|1K|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@$D|O|E|P|1|Q]]|A|$]]|$1|F|3|-4|5|6|7|R|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|C]]]]

You should change your TDS version based on what version of SQL server your using.
Check out the installation guide for details.

<a href="http://www.freetds.org/userguide/choosingtdsprotocol.htm" rel="nofollow noreferrer">http://www.freetds.org/userguide/choosingtdsprotocol.htm</a>

blocks|key|219593|text|我使用的是与上面相同的windows+1250，所以：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|219594|$query+=+$this->db->query($sql);
$result+=+$query->fetchAll(PDO::FETCH_OBJ);
foreach+($result+as+$row)+{
++++foreach+(get_object_vars($row)+as+$key+=>+$value)+{
++++$row->$key+=+(mb_detect_encoding($value,+mb_detect_order(),+true)+===+'UTF-8')+
++++++++++++?+$value+:+iconv('windows-1250',+'utf-8',+$value);
++++}
++++$results[]+=+$row;
}|code-block|syntax|javascript|219595|然后它起作用了，但我使用波兰语字符|219596|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I used same as above but windows 1250, so:

<pre><code>$query = $this-&gt;db-&gt;query($sql);
$result = $query-&gt;fetchAll(PDO::FETCH_OBJ);
foreach ($result as $row) {
 foreach (get_object_vars($row) as $key =&gt; $value) {
 $row-&gt;$key = (mb_detect_encoding($value, mb_detect_order(), true) === 'UTF-8') 
 ? $value : iconv('windows-1250', 'utf-8', $value);
 }
 $results[] = $row;
}
</code></pre>

And then it worked, but I use polish characters

I can't seem to get data from MSSQL encoded as UTF-8 using FreeTDS extension.

Connecting:

<pre><code>ini_set('mssql.charset', 'UTF-8');
$this-&gt;_resource = mssql_connect($config['servername'], $config['username'], $config['password']);
</code></pre>

I have no ability to use any other extension.

I've tried creating ~/.freetds.conf

<pre><code>[global]
client charset = UTF-8
</code></pre>

I've tried passing parameters to php:

<pre><code>php -d mssql.charset="UTF-8" index.php
</code></pre>

Data is still not in UTF-8.

php -i

<pre><code>mssql

MSSQL Support =&gt; enabled
Active Persistent Links =&gt; 0
Active Links =&gt; 0
Library version =&gt; FreeTDS

Directive =&gt; Local Value =&gt; Master Value
mssql.allow_persistent =&gt; On =&gt; On
mssql.batchsize =&gt; 0 =&gt; 0
mssql.charset =&gt; no value =&gt; no value
mssql.compatability_mode =&gt; Off =&gt; Off
mssql.connect_timeout =&gt; 5 =&gt; 5
mssql.datetimeconvert =&gt; On =&gt; On
mssql.max_links =&gt; Unlimited =&gt; Unlimited
mssql.max_persistent =&gt; Unlimited =&gt; Unlimited
</code></pre>

Ideas?

Getting data with UTF-8 charset from MSSQL server using PHP FreeTDS extension

SQL Server

数据库

Linux

我似乎无法从使用FreeTDS扩展的UTF-8编码的MSSQL中获取数据。正在连接：ini_set('mssql.charset', 'UTF-8');$this->_resource = mssql_connect($config['servername'], $config['username'], $config...

问使用PHP FreeTDS扩展从MSSQL服务器获取使用UTF-8字符集的数据
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用PHP FreeTDS扩展从MSSQL服务器获取使用UTF-8字符集的数据EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用PHP FreeTDS扩展从MSSQL服务器获取使用UTF-8字符集的数据
EN