Uncaught PDOException when using åäö

Question

I use to use sqlsrv_connect but changed it to PDO.
Now i got this syntax error when using åäö.

When i used sqlsrv_connect i could do this:

SELECT Order, [Benämning], [Vår ref] FROM table

and it worked.

Now i'm trying to figure out how to do it with PDO.
So i tried:

SELECT Order, [Benämning], Antal FROM table

And got this error:

Operand type clash: text is incompatible with float

And i tried:

SELECT Order, Benämning, Antal FROM table

And i got error:

Incorrect syntax near '�'.

In the connection i added utf8:

$sql = new PDO("odbc:Driver=$driver;server=$serverName,$port;Database=$database;ConnectionPooling=0", $uid, $pwd,
        array(
            PDO::ATTR_PERSISTENT => true,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"
        )
    );

Now, when pasting this i can se: PDO::MYSQL_ATTR_I... I'm connecting to SQL.. not MYSQL. Can this be the problem?

If i remove the "Benämning" column and just select columns without åäö or space the select works just fine.

UPDATE

I got åäö to work with sqlsrv instead of odbc.

Answer 1

You simply need to use the same driver (PHP Driver for SQL Server) and the same connection options when you create the PDO instance.

<?php

...

try
   $sql = new PDO("sqlsrv:Driver=$driver;server=$server,$port;Database=$database", $uid, $pwd);
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   ... 
} catch( PDOException $e ) {
    ...
}

try {
    $sql = "SELECT [Vår ref], ... FROM ...";
    $stmt = $conn->prepare($sql);
    $stmt->execute();
} catch( PDOException $e ) {
    ...
}

?>

Some additional notes:

• You need to quote the column name ([Vår ref]) to make the string literal a valid SQL Server identifier.
• Use driver specific PDO connection attributes (PDO::SQLSRV_xxx) to add driver specific features. The PDO::MYSQL_ATTR_INIT_COMMAND option is useful if you connect to MySQL instance.