MS SQL Encoding - "An error occurred translating string for input param to UCS-2"

svenos

Hi all, Hi Allan,

my PHP datatables code has to work with both MySQL and MS SQL since I work in different environments from time to time.
Usually this works pretty well. Currently there is one exception.

I have this code for uploading files directly into a table:
https://datatables.net/forums/discussion/47623/storing-and-retrieving-documents-directly-from-a-database-via-datatables-editor#latest
(code in my last comment)

Now, when I try to upload a new file on MS SQL I always get this message:
"SQLSTATE[IMSSP]: An error occurred translating string for input param 4 to UCS-2: No mapping for the Unicode character exists in the target multi-byte code page."

When using MySQL, my configuration is set to UTF8. When using MS SQL, I was not able to configure it.
- How can I do it in MS SQL?
- Where do I define thins like "PDO::SQLSRV_ENCODING_SYSTEM"?
I tried it in the pdoAttr part in the Editor PHP config file but with error messages.

Thank you!

svenos

Just fyi. My config.php is currently looking like this:

if($dbConfig['main']['type'] == "Mysql") {
    $sql_details = array(
        "type" => $dbConfig['main']['type'],   // Database type: "Mysql", "Postgres", "Sqlserver", "Sqlite" or "Oracle"
        "user" => $dbConfig['main']['user'],        // Database user name
        "pass" => $dbConfig['main']['password'],        // Database password
        "host" => $dbConfig['main']['host'],        // Database host
        "port" => "",        // Database connection port (can be left empty for default)
        "db"   => $dbConfig['main']['name'],        // Database name
        "dsn"  => "charset=utf8",        // PHP DSN extra information. Set as `charset=utf8` if you are using MySQL
        "pdoAttr" => array() // PHP PDO attributes array. See the PHP documentation for all options
    );
} elseif($dbConfig['main']['type'] == "Sqlserver") {
    $sql_details = array(
        "type" => $dbConfig['main']['type'],   // Database type: "Mysql", "Postgres", "Sqlserver", "Sqlite" or "Oracle"
        "user" => $dbConfig['main']['user'],        // Database user name
        "pass" => $dbConfig['main']['password'],        // Database password
        "host" => $dbConfig['main']['host'],        // Database host
        "port" => "",        // Database connection port (can be left empty for default)
        "db"   => $dbConfig['main']['name'],        // Database name
        "dsn"  => "",        // PHP DSN extra information. Set as `charset=utf8` if you are using MySQL
        "pdoAttr" => array() // PHP PDO attributes array. See the PHP documentation for all options
    );
}

You may notice the empty pdoAttr when using Sqlserver. I'm not able to use it e.g. for "PDO::SQLSRV_ENCODING_SYSTEM" without producing error messages. Is it the right place?

allan

I tried it in the pdoAttr part in the Editor PHP config file but with error messages.

That's the right place - you should be able to use:

"pdoAttr" => array(
  PDO::SQLSRV_ENCODING_SYSTEM
)

This is where it is used. What error(s) are you getting when you enable that?

Thanks,
Allan

svenos

Thank you for your reply and the reference to the code!
Now I know that it should be the right way to use it.

However, when I use it like this I get the following error message in the browser:

"DataTables warning: (...) An error occurred while connecting to the database 'abc'. The error reported by the server was: The auto-commit mode cannot be changed for this driver"

There is no PHP error in the logs.

I get some matches when googling this "auto-commit mode" error but I have not yet figured out how to solve it. Still investigating...

svenos

And again an addition:
My other code (non datatables) is running on the same database with this code without problems:

$connection->setAttribute(PDO::SQLSRV_ATTR_ENCODING, PDO::SQLSRV_ENCODING_UTF8);

This is why I'm a bit confused.

allan

Let's try this:

$db
  ->resource()
  ->setAttribute(PDO::SQLSRV_ATTR_ENCODING, PDO::SQLSRV_ENCODING_UTF8);

The $db->resource() method will give you the PDO instance, so you can run commands against it directly.

I tried reading the PHP documentation for the new PDO() instance and passing attributes in, but frankly, I didn't get it! The above should do the business though.

Allan

svenos

Again, thank you for your answer! Last question, I promise!

Your code works but unfortunately it does not solve the problem.

It still says that the uploaded file contains invalid characters. MS SQL only allows UCS-2 (or now UTF16) and I hoped that the mentioned options will force PDO to translate it properly.

Well, it does not. It looks like I have to encode everything on my own. I know how to do it when downloading things. But how do I do it when uploading stuff with DataTables Editor?

This is my original code:

Editor::inst( $db, $GLOBALS['schema'].'.File', 'File_ID' )
    ->fields(
        Field::inst($GLOBALS['schema'].'.File.File_ID' ),
        Field::inst($GLOBALS['schema'].'.File.Contract_ID' ),
        Field::inst($GLOBALS['schema'].'.File.File_Name' ),
        Field::inst($GLOBALS['schema'].'.File.File_Desc' ),
        Field::inst($GLOBALS['schema'].'.File.FileContent_ID' )
            ->setFormatter( Format::ifEmpty( null ) )
        ->upload( 
            Upload::inst( '/tmp/__ID__.__EXTN__' )
            ->db( $GLOBALS['schema'].'.FileContent', 'FileContent_ID', array(
                    'FileContent_FileName'    => Upload::DB_FILE_NAME,
                    'FileContent_FileSize'    => Upload::DB_FILE_SIZE,
                    'FileContent_FileType'    => Upload::DB_MIME_TYPE,
                    'FileContent_Content' => Upload::DB_CONTENT
                ) )
                ->validator( Validate::fileSize( 100000000, 'Files must be smaller than 100MB' ) )
                ->validator( Validate::fileExtensions( array( 'png', 'jpg', 'jpeg', 'gif', 'zip', 'rar', 'doc', 'docx', 'docm', 'docb', 'ppt', 'pptx', 'pptm', 'xls', 'xlsx', 'xlsm', 'xlsb', 'odt', 'ott', 'oth', 'odm', 'ods', 'ots', 'odp', 'odg', 'otp', 'pdf', 'fdf', 'xfdf' ), "Please upload a document, presentation, spreadsheet, image or zip file" ) )
            )
    )
    ->process( $_POST )
    ->json();   

I want to add a function like php's bin2hex or some self written stuff.
I'm not very good in php and tried several things that did NOT work:

Try 1: setFormatter

Instead of
->setFormatter( Format::ifEmpty( null ) )
I tried
->setFormatter( function ( $val ) { if($val == "") { return null; } else { return bin2hex($val); } )

But it looks like it is doing nothing. I have tried to use the setFormatter elsewhere too because for me it looks like that this function is only for the ID and not for the content but it gave me some errors.

Try 2: Upload::DB_CONTENT

Instead of
'FileContent_Content' => Upload::DB_CONTENT
I tried
'FileContent_Content' => bin2hex(Upload::DB_CONTENT)

But it looks like he already tries to convert something on loading (the already contained files).

Where is the proper place to use such a function for the upload content?

svenos

Just purchased some support credits and will contact you directly. Thanks!

allan

Hi,

Thanks for picking up the quick support option! I'll reply to your e-mail as soon as I've written this, but I've got most of the information here, and a thought on how to address the issue, so I'll do so - its specifically the the upload'ed file that is the issue?

What you can do is give the properties to write to the database as functions that will be evaluated - so:

'FileContent_Content' => function ($db, $file) {
  $fileContents = file_get_contents($file['tmp_name']);

  return bin2hex($fileContents);
}

It "feels" wrong though. There must be a way to store binary data in SQL Server. How is the field defined in the database?

Thanks,
Allan