Insert blob (from mysql) data in varbinary (SQL Server) field with Zend_Db

Question

I have a MySQL table that needs to be synchronized with a SQL Server table. So the data from MySQL moves to SQL Server. It is done by simple SELECT * and INSERT INTO queries. However, I ran into problems with migrating some BLOB data to a varbinary field.

So I have this code:

$db_mysql = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'      =>  '127.0.0.1',
    'username'  =>  '<user>',
    'password'  =>  '<password>',
    'dbname'    =>  '<db>',
    'charset'   =>  'utf8'
));
$db_mssql = new Zend_Db_Adapter_Pdo_Mssql(array(
    'pdoType'   =>  'sqlsrv',
    'host'      =>  '<host>',
    'username'  =>  '<user>',
    'password'  =>  '<password>',
    'dbname'    =>  '<db>'
));

$rows = $db_mysql->fetchAll("SELECT Id, Picture FROM Rosters");
foreach ($rows as $row) {
    $update_sql = "UPDATE Rosters SET Picture = :picture WHERE Id = :id";

    $stmt = new Zend_Db_Statement_Pdo($db_mssql, $update_sql);
    $stmt->bindValue(":picture", bin2hex($row['Picture']), Zend_Db::PARAM_LOB);
    $stmt->bindValue(":id", $row['Id'], Zend_Db::PARAM_INT);

    try {
        $stmt->execute();
    } catch (Exception $e) {
        echo $e->getMessage();
        var_dump($e);
        die();
    }
}

This gives me the incredible not-so-useful error message: PDOException: SQLSTATE[HY000]: General error: 257 General SQL Server error: Check messages from the SQL Server [257] (severity 16) [(null)]

The MySQL Blob field is defined as BLOB with the attribute BINARY, the SQL Server field is defined as (varbinary(max), null). I think it's just a simple mistake, but I can't figure it out. Can anyone help me out with this?

I found it out on my own, but I have to wait 7 hours before I can reply to it. I hope I still remember having this question open after 7 hours.. — henk
– henk, Commented Mar 1, 2012 at 13:32

henk · Accepted Answer · 2012-12-10 09:47:11Z

Ok. I found it out on my own, this is the working code:

$db_mysql = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'      =>  '127.0.0.1',
    'username'  =>  '<user>',
    'password'  =>  '<pass>',
    'dbname'    =>  '<db>',
    'charset'   =>  'utf8'
));
$db_mssql = new Zend_Db_Adapter_Pdo_Mssql(array(
    'pdoType'   =>  'odbc',
    'host'      =>  '<host>',
    'username'  =>  '<user>',
    'password'  =>  '<pwd>',
    'dbname'    =>  'Roosters'
));
    $rows = $db_mysql->fetchAll("SELECT Id, Picture FROM Rosters");
    foreach ($rows as $row) {
        $update_sql = "UPDATE Rosters SET Picture = CONVERT(varbinary(max), :picture, 2) WHERE Id = :id";
        $stmt = new Zend_Db_Statement_Pdo($db_mssql, $update_sql);
        $stmt->bindValue(":picture", bin2hex($row['Picture']));
        $stmt->bindValue(":id", $row['Id'], Zend_Db::PARAM_INT);

    try {
        $stmt->execute();
    } catch (Exception $e) {
        echo $e->getMessage();
        var_dump($e);
        die();
    }
}
?>

Collectives™ on Stack Overflow

Insert blob (from mysql) data in varbinary (SQL Server) field with Zend_Db

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related