MSSQL Blob -> MySQL

[Jamie_Bohr]

I am trying to migrate data from a MSSQL DB to a MySQL DB for reporting purposes. I will have to do this every day. I have a PHP script that connects to the MSSQL DB to extract the data but I am having problems with a BLOB field. This field is used for tracking an indivigual computer system, like and index field; let us call this field AgentID.

Can some one point me in the right direction on how to convert this field to something that is ledgable? I have SybSQL configured to access one of the six MSSQL I need to extract data from, it converts it to a HEX field or at least that is what it looks likes it does.

I have searched google and have not come up with anything useful, this is probably due to my lack of understanding on how to deal with blob fields.

Thank you,
Jamie Bohr

[Roger_Ramjet]

Blob fields are binary fields and you can use the mysql UNHEX string function to convert it back to your base character set.

eg UNHEX(column) will return the original character string.

[Jamie_Bohr]

Thank you for the responce. I think there maybe something else wrong because I get NULL from the return. I suspect I am not getting the blob out of the MSSQL DB correctly.

Here is how I am trying to migrate the data.

$ms_connection = @ mssql_connect($db_server, $db_user, $db_passwd)
$my_connection = @ mysql_pconnect($my_server, $my_user, $my_passwd)))

$result=mssql_query("SELECT blob_column FROM some_table");

while($row = mssql_fetch_row($result))
{
$sql = "INSERT INTO mysql_test (blob_column) VALUES ('$row[0]');";

mysql_query ($sql, $my_connection)
}

Pretty sure the above is the WRONG way to do it, please point me in a direction that help me migrate the data correctly.

Thanks