bindValue problem with scandinavian characters using Sqlsrv 2.0 with PDO

miércoles, 15 de septiembre de 2010 13:20

I've made simple php-form to get data by city name...

<?php
$city = isset($_REQUEST["city"]) ? $_REQUEST["city"] : "";
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="fi" lang="fi">
  <head>
	<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
    <title>PDO Tester</title>
  </head>
  <body>
    <form action="" method="post">
      <label for="city">City</label>
      <input type="text" name="city" id="city" value="<?php echo htmlentities($city) ?>" />
      <input type="submit" value="Get" />
<?php
try 
{
  $server  = "xxx.xxx.xxx.xxx";
  $database = "mydb";
  $username = "myuid";
  $password = "mypwd";
  //$options = array( "CharacterSet" => "UTF-8");
  $connectionString = "sqlsrv:server=$server;Database=$database";
  $dbh = new PDO($connectionString, $username, $password);
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   
  try
  {
    $sql = "SELECT X, Y FROM MYTABLE WHERE CITY = :city";
    $stmt = $dbh->prepare($sql);
    $stmt->bindValue(":city", $city, PDO::PARAM_STR);

    if($stmt->execute())
    {
      $table = $stmt->fetchAll(PDO::FETCH_ASSOC);

      foreach($table as $row)
      {
        foreach($row as $fieldName => $fieldValue)
        {
          echo "<p>" . $fieldName . " = " . $fieldValue . "</p>";
        }
      }
    }
    else
    {
      print_r($stmt->errorInfo());
    }

    $stmt = null;
  }
  catch (PDOException $ex)
  {
    echo "Database::DataTable: " . $ex->getCode() . " - " . $ex->getMessage();
  }
}
catch ( PDOException $e ) 
{
  echo "An error occurred when creating database connection! <br>";
  die ( $e->getMessage() );
}
?>
    </form>
  </body>
</html>

It's working fine when city to search is for example 'Helsinki', but when city to search is for example 'Jyväskylä' or any city with scandinavian letters - it gives an error:

"Database::DataTable: IMSSP - SQLSTATE[IMSSP]: An error occurred translating string for input param 1 to UCS-2: No mapping for the Unicode character exists in the target multi-byte code page."

Database is SQL Server 2005

When I change (ie. "hard code")...

$stmt->bindValue(":city", "Jyväskylä", PDO::PARAM_STR);

...then it's working fine.

What wrong with my parameter binding with city names containin scandinavian letters?

Todas las respuestas

miércoles, 15 de septiembre de 2010 18:56

0

Thanks for reporting this. I am investigating this right now. I will let you know what I find.
- Responder
- Citar
miércoles, 15 de septiembre de 2010 21:06

0
The default encoding is UTF-8. Can you try setting the encoding on the statement to be "SQLSRV_ENCODING_SYSTEM" just before you call bindvalue? Something like this:

$stmt->setAttribute(PDO::SQLSRV_ATTR_ENCODING, PDO::SQLSRV_ENCODING_SYSTEM);

$stmt->bindValue(":city", $city, PDO::PARAM_STR);
- Marcado como respuesta Mikanen jueves, 16 de septiembre de 2010 6:04
- Responder
- Citar
jueves, 16 de septiembre de 2010 6:03

0

It works! Thank You very much! You saved my day.
- Responder
- Citar
jueves, 15 de marzo de 2012 16:50

0

This is not working for me.
If I call setAttribue(), the error is not triggered, but no data is read from database.

Any other idea?
- Responder
- Citar
lunes, 15 de octubre de 2012 14:27

0
hi, i have same or similar problem.

I have a test server windows 2008 r2 64bits, i install all necesary to run php and connecting with sql server 2008 r2 express. All is running perfect, but when try to set the same config with the production server having a 32bits windows server 2008 r2. Simple i CANT connect to sqlserver. Even cant connect with a clean database (NO data). Allways the browser showme this error:

Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -47
[code] => -47 [2] => An error occurred translating the connection string
to UTF-16: No mapping for the Unicode character exists in the target multi-byte
code page. [message] => An error occurred translating the connection string
to UTF-16: No mapping for the Unicode character exists in the target multi-byte
code page. ) )

cesardgo
- Propuesto como respuesta cesardgo lunes, 15 de octubre de 2012 17:12
- Votado como útil Jonathan GuerinMicrosoft Employee miércoles, 17 de octubre de 2012 23:47
- Responder
- Citar
lunes, 15 de octubre de 2012 17:12

0

Oh my god ... i found the problem, cant believeit. I was using a sql server user password with this characters @?¿ ... when change the user with a simple password .. wow ... php connects with sql server.

So if any one has this error, try changing your sql server credentials.
cesardgo
- Responder
- Citar