I have the current need to move MS SQL Server 2005 data from an external server to a local host (e.g. shared) windows server. The environment is PHP and I do have the connections created for both external and internal databases.

The question is how to transfer the blob to the local MS Sql Server database from the external MS Sql Server database. I am already able to process all the other fields within the database except the image (e.g. blob) field.

The Ole header data is within the file. Anyone know how to do this?


Example code selecting the data below:

// Specify the login info here. (later move to external file)
// Select from database first
$msserver = '999.99.999.999';
$usrname = 'myusername';
$passwrd = 'mypassword';
$database = 'mydatabase';
$table = 'mytable';
// Build SQL statement here that has the blob field (medium_photo)
// medium_photo is a jpg file 320 x 208
$sql = 'SELECT TOP 100 id, medium_photo FROM ' . $table;

// Open the MS Sql Server Connection
$result = opensqldata($msserver,$usrname,$passwrd,$database, $sql);

// $result declared global within opensqldata function
$nrows = mssql_num_rows($result);


function opensqldata($msserver,$usrname,$passwrd,$database, $sql) {

global $msserver, $usrname, $passwrd, $database, $sql, $result, $dbcon;
// Open the MS Sql Server Connection
$dbcon = mssql_connect($msserver,$usrname,$passwrd);
if ($dbcon) {
echo "<font color='blue'>Connection to $msserver was successful.</font><hr>";
} else {
echo "<font color='red'>Error: ".mssql_get_last_message()."</font><hr>";
} // End if ($dbcon)

// Specify database here
mssql_select_db($database);

// Build SQL statement here
// $sql = 'SELECT TOP 10 * FROM ' . $sel_table;
$result = mssql_query($sql);
return $result;

} // End function open-sql-data

__________________
~ Mike
Antiques Online News::Seafood Recipes

Alas, thanks to others on the web forums I have the answer! Converting the data connection to Adodb and converting with the following function, I have been able to sql insert the data from the external MS Sql Server to the local MS Sql Server. Thanks to all who assisted in any manner.

function storeimage($fldimage) {
// Handling IMAGE - BLOB fields
$imgdata = unpack("H*hex", $fldimage);
$imgdataresult = "0x".$imgdata['hex'];

return $imgdataresult;

} // END function storeimage($fldimage) {

__________________
~ Mike
Antiques Online News::Seafood Recipes

Glad you sorted it out and thanks for taking the time to post a solution. Maybe one day someone will do a search and come across it.

Cheers,
Paz.

__________________
10.3 million entries for Hotels in Turkey but I'm still chipping away.