PDO + MsSQL + freetds = INSERT查询中的错误字符

问题描述：

I've noticed a problem during SELECT queries in my internal project, regarding utf8 characters (šđčćž). After I'd fixed problem regarding freetds definition for charset and version in freetds.conf, I've started to receive right characters when I run SELECT queries.

But! Now I'm experiencing problem regarding INSERT/UPDATE queries and with SELECT query when I try to search fields with some of those characters (šđčćž), and that's only with PDO bindValue method.

For example, this is my code:

try {
$pdo = new \PDO(
      "dblib:host=$host:$port;dbname=$database",
      "$username",
      "$password"
   );
   $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
   echo "There was a problem connecting. " . $e->getMessage();
}

When I try insert values with following code, inserted values in database are right:

$query = "INSERT INTO dbo.TABLE (ID, NAME) VALUES (2, 'Beriša');";
$statement = $pdo->prepare($query);
$statement->execute();

//Result:
//2 Beriša

But, after using bindValue method (which I must use, as I work with Symfony2), problem happens:

$query = "INSERT INTO dbo.TABLE (ID, NAME) VALUES (?, ?);";
$statement = $pdo->prepare($query);
$statement->bindValue(1, 2, \PDO::PARAM_INT);
$statement->bindValue(2, 'Beriša', \PDO::PARAM_STR);
$statement->execute();

//Result:
//2 BeriĹˇa

Bellow, you can find my current freetds configuration:

[global]
    tds version = 8.0
    text size = 20971520
    client charset = UTF-8

Database charset coalition is Croatian_CI_AS. Unfortunately, I can't change that, as it's old database, designed to work with old application, and for that application I'm working on web presentation.

我在内部项目的SELECT查询中发现了一个关于utf8字符（šđčćž）的问题。在修复了freetds.conf中有关charset和版本的freetds定义的问题后，我在运行SELECT查询时开始收到正确的字符。 p>

但是！现在我遇到有关INSERT / UPDATE查询和SELECT查询的问题，当我尝试搜索包含其中一些字符的字段时（šđčćž），这只是使用PDO bindValue方法。 p>

For 例如，这是我的代码： p>

  try {
 $ pdo = new \ PDO（
“dblib：host = $ host：$ port; dbname = $ database”  ，
“$ username”，
“$ password”
）; 
 $ pdo-＆gt; setAttribute（PDO :: ATTR_ERRMODE，PDO :: ERRMODE_EXCEPTION）; 
} catch（PDOException $ e）{
 回声“连接时出现问题。”  $ e-＆gt; getMessage（）; 
} 
  code>  pre> 
 
 当我尝试使用以下代码插入值时，数据库中插入的值是正确的： p> \  n 
 
  $ query =“INSERT INTO dbo.TABLE（ID，NAME）VALUES（2，'Beriša'）;”; 
 $ statement = $ pdo-＆gt; prepare（$ query）;  
 $ statement-＆gt; execute（）; 
 
 // //结果：
 //2Beriša
  code>  pre> 
 
 但是，使用bindValue方法后（其中） 我必须使用，当我使用Symfony2时），问题发生： p> 
 
 
  $ query =“INSERT INTO dbo.TABLE（ID，NAME）VALUES（？，？）;”  ; 
 $ statement = $ pdo-＆gt; prepare（$ query）; 
 $ statement-＆gt; bindValue（1,2，\ PDO :: PARAM_INT）; 
 $ statement-＆gt; bindValue（2，'Beriša  '，\ PDO :: PARAM_STR）; 
 $ statement-＆gt; execute（）; 
 
 // //结果：
 //2BeriĹa
  code>  pre> 
 
  Bellow，你可以找到我当前的freetds配置： p> 
 
 
  [global] 
 tds version = 8.0 
 text size = 20971520 
 client charset = UTF-8 
   code>  pre> 
 
 数据库字符集联盟是Croatian_CI_AS。 不幸的是，我无法改变它，因为它是旧数据库，旨在与旧应用程序一起使用，而对于该应用程序，我正在进行Web演示。 p> 
  div>

答

I have found a solution after some time. It's not a perfect one, as I need to process each value (it's small fix for code, but still not clean enough). If anyone has another idea to make this one better, post it, pls.

$statement->bindValue(2, iconv('utf-8', 'Windows-1252', 'Beriša'), \PDO::PARAM_STR);

PDO + MsSQL + freetds = INSERT查询中的错误字符

相关推荐