Problems with encoding (PHP+ODBC+MSSQL Server 7.0)

[ruhestorer]

Hi,

I have problems with getting data out of the MS SQL Server 7.0 database in right encoding using odbc connection:

        $conn = odbc_connect("Driver={SQL Server};Server=$server;Database=$database;  CharacterSet => UTF-8", $user, $password);

I had the same problem when I used MS SQL Server 2005 and sqlsrv connection, but I found the solution. 'CharacterSet' => 'UTF-8' did the work and everything was fine in above connection it changes nothing:

        $connectionInfo = array("Database" => ini_get("odbc.default_db"), "UID" => ini_get("odbc.default_user"), "PWD" => ini_get("odbc.default_pw"), 'CharacterSet' => 'UTF-8');
        $conn = sqlsrv_connect($serverName, $connectionInfo);

I am stuck for 2 days now and desperate, i have looked all over google, php and ms forums and tried lot of solutions, none of them work.
I can't change anything in database as it's client db. Right now I just whant to get data out of one column (type= text), in this text column client stores html documents in windows-1257 charset (Latvian language), and when I select data from db all latvian characters (ēūīāšķļžčņ) are displayed as (euia??????).
I have tried to change parameters:

• ini_set('default_charset', 'utf-8');
• ini_set('mssql.charset', 'utf-8');
• iconv_set_encoding("input_encoding", 'utf-8');
• iconv_set_encoding("internal_encoding", 'utf-8');
• iconv_set_encoding("output_encoding", 'utf-8');
• ini_set("mbstring.internal_encoding", 'utf-8');
• ini_set("mbstring.script_encoding", 'utf-8');
• ini_set("mbstring.language",'Latvian');

Also tried to change the encoding of the string i get from db using comands:

• iconv
• mb_convert_encoding
• utf8_encode

The statement

 $sql = "SELECT TOP 10 'ēūīāšķļņžč' t, TEKSTS FROM VESTURE WHERE TEKSTS IS NOT NULL";
        echo $sql . '</br>';
        $stmt = odbc_exec($conn, $sql);
        if ($stmt === false) {
            die(print_r(sqlsrv_errors(), true));
        }

        while ($row = odbc_fetch_row($stmt)) {


         echo odbc_result($stmt, "t")."</br>".odbc_result($stmt, "TEKSTS") . "</br>";
        }

Returns

ēūīā�?ķļņž�?
...
3. Katra civiltiesiska attieciba apsprie�ama pec likumiem, kas biju�i speka tad, kad �i attieciba radusies, pargrozijusies vai izbeigusies. Neskartas paliek jau iegutas tiesibas.
...

Insted of

ēūīāšķļņžč
....
3. Katra civiltiesiskā attiecība apspriežama pēc likumiem, kas bijuši spēka tad, kad šī attiecība radusies, pārgrozījusies vai izbeigusies. Neskartas paliek jau iegūtas tiesības.
...

When i put N front of 'ēūīāšķļņžč' it returns value as it have to:

SELECT TOP 10 N'ēūīāšķļņžč' t, TEKSTS FROM VESTURE WHERE TEKSTS IS NOT NULL

Returns

ēūīāšķļņžč
...
3. Katra civiltiesiska attieciba apsprie�ama pec likumiem, kas biju�i speka tad, kad �i attieciba radusies, pargrozijusies vai izbeigusies. Neskartas paliek jau iegutas tiesibas.
...

[jkon]

Save all you php in UTF-8 (Eclipse , Netbeans or other editors will help you with that) . Connect to your db through PDO , PHP has ONLY (and ONLY) that great tool (could be greater but that is what we have) all others are just waist of time and quick and dirty solutions. When you connect set names to UTF-8 and respectively all the field or tables that has UTF-8 should declared as such or if you want to search without capitalization limitations UTF-8-general-CI . There are more to it … but that’s the basic basics, There are more basics in programming with PHP that few take care …

[ruhestorer]

I have tried PDO, but it wont return any results:

        <?php
        try {
            $hostname = "*";
            $dbname = "*";
            $username = ini_get("odbc.default_user");
            $pw = "*";
            $dbh = new PDO("dblib:host=$hostname;dbname=$dbname", "$username", "$pw");
        } catch (PDOException $e) {
            echo "Failed to get DB handle: " . $e->getMessage() . "\n";
            exit;
        }
        $stmt = $dbh->prepare("SELECT TOP 10 TEKSTS FROM VESTURE WHERE TEKSTS IS NOT NULL");
        $stmt->execute();
        while ($row = $stmt->fetch()) {
            print_r($row);
        }
        unset($dbh);
        unset($stmt);
        ?>

Result:

Failed to get DB handle: could not find driver

Tried to replace dblib with mssql and sqlsrv. mssql returns same error as dblib, but sqlsrv returns:

Failed to get DB handle: SQLSTATE[IMSSP]: An invalid keyword 'host' was specified in the DSN string.

[ruhestorer]

Changed connection to

$dbh = new PDO("sqlsrv:server=$hostname;Database=$dbname", "$username", "$pw");

Got error again:

Failed to get DB handle: SQLSTATE[08001]: [Microsoft][SQL Server Native Client 10.0]Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server.

Looks like PDO doesn't support MS SQL SERVER 7.0. At least ODBC returned data in wrong encoding. Maybe someone has some idea how to make ODBC return data in right encoding!?

[ruhestorer]

I found solution, if anyone got the same problem and finds this post, here is what worked for me:

• Check if Control Panel->Region and Language->Administrative->Current language for non-Unicode programs is set to language you need (in my case Latvian)
• When setting up ODBC in System DSN make sure 'Perform translation for character data' is NOT checked
• In php.ini file make sure that all charsets is blank (ex., mssql.charset =)

• If youre php is inside html make sure that there is no charset defined in meta tag:

  <html>
      <head>
          <meta http-equiv="Content-Type" content="text/html"/>
          <title></title>
      </head>
      <body>
          <?php
          ?>
      </body>
  </html>
  

• And make sure there is no CharacterSet in connection:

$conn = odbc_connect("Driver={SQL Server};Server=$server;Database=$database", $user, $password);