Transferring MSSQL blob from One Database to Another

[Michael D. Hudson]

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 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

[WebSavvy]

Do you have CPanel or PhpMyAdmin on the server where the db is?

If you have PhpMyAdmin, select your db from the drop select menu. Click the link to the table you want to work with.

When the table loads in the right window pane, you will see a tabbed menu selection located at the top. From this menu select the tab marked export.

From there this will bring you to a new window with a form to download your db to your hard drive.

Tick the box marked :: Use hexadecimal for binary
Also, make sure the box marked :: Extended inserts is ticked as well.

The hexdecimal checkbox will export your blob field as binary Vs ACSII while all other normal fields containing text will be exported as ASCII fields.

Extended inserts is the best choice as it gets the data line by line Vs one BIG line.

When getting the data as one big line it sometimes can be hard to insert it into another database (causing timeout issues).

Depending on how much data you're dealing with, you may need to upload it to the new db using SSH/Telnet.

It's very simple to do using SSH/Telnet.

Simply upload the file to your server via FTP.
Then use PuTTy to connect to the server to begin the SSH db import.

Use the following commands to insert the data from your uploaded file into your db:

mysql -udb_user -pdb_pass db_name < name_of_your_file.sql

Replace db_user
with your db username

Replace db_pass
with your db password

Replace db_name
with your db name

Replace name_of_your_file.sql
with the name of your actual file (can be .sql or .txt)

the beginning -u prior to the db_user line tells the server it's your username

the beginning -p prior to the db_pass line tells the server it's your password

[Michael D. Hudson]

Thanks Savvy1 for the reply!

For the source database, I only have access to login via code (e.g. create a database connection). For the local database, I have Plesk access to ASP.NET Enterprise Manager (online only limited version). Both of these databases are Microsoft Sql Server 2005 which is why I've been using mssql and not mysql.

BTW, I have not been using a

Am I missing something with your reference to the mysql connection?

Again thanks for the response!sql file for this. All of the code has been written and processed within php.

[WebSavvy]

hmmm ... well, I've not ever used mssql or a windows server. I use mysql & unix.

Looking at your $sql statement, I have to ask about this:

$sql = 'SELECT TOP 100 id, medium_photo FROM ' . $table;


Are ::
TOP
100

field names?

If so, therein lies your problem. You'd need to separate the field names using a comma.

example:

$sql = 'SELECT TOP, 100, id, medium_photo FROM ' . $table;

A normal table connection and while loop structure would be;

db user/pass
connect or die
select fields from table
while loop
close connection

[Michael D. Hudson]

Top 100 returns the first 100 records. Field names are: id, medium_photo.

A normal table connection and while loop structure would be;

db user/pass
connect or die
select fields from table
while loop
close connection


Mine works as:
1- Call function to perform data connection including to the table.
2- While Loop *** this is the exact code I need!!!***
3- Close data connection (I already have this working fine)

[WebSavvy]

Michael, I really don't know anything about mssql just mysql. I also do not have any experience with windows servers, only unix.

We have a Moderator here named chrishirst, and he's pretty experienced with mssql and windows servers. Maybe send him a PM?

I know he's quite busy right now, so he might not have seen this thread.

HTH

[chrishirst]

Why not simply fire up SQL Enterprise Manager and export the databases between servers?

[Michael D. Hudson]

It's not available...

BTW, I understand if the tools are available that it would be easier. What I don't understand is whether or not anyone has been able to perform this task without the additional tools (e.g. just staying with PHP, MSSQL, etc.).

[Michael D. Hudson]

Originally posted by savvy1
Michael, I really don't know anything about mssql just mysql. I also do not have any experience with windows servers, only unix.

We have a Moderator here named chrishirst, and he's pretty experienced with mssql and windows servers. Maybe send him a PM?

I know he's quite busy right now, so he might not have seen this thread.

HTH

Thanks for the head's up!

[chrishirst]

That'll be me then :D


Originally posted by Michael D. Hudson
It's not available... Fair do's :D

Originally posted by Michael D. Hudson
BTW, I understand if the tools are available that it would be easier. What I don't understand is whether or not anyone has been able to perform this task without the additional tools (e.g. just staying with PHP, MSSQL, etc.). [/B]

It should be possible, never used MSSQL with PHP myself, but what you will need to do is to read the BLOB out of the database as a binary stream (BLOBs usually cannot be held in a variable) and then stream it back to the new DB table.

This is one good reason why you should never store images in a database, it's real resource hog trying to get them out again.

[Michael D. Hudson]

Chris, I completely concur. Any ideas where I might find example code for this? I've had absolutely no luck at all with my personal searches.

[chrishirst]

just a thought,

Have you tried

$rows = mssql_fetch_array(mssql_query($sql));
$imgdata = $rows['columnname'];


Then run an INSERT/UPDATE query to the new DB

[Michael D. Hudson]

First choice once I felt there was no way other than code direct.

Thanks again!

[Michael D. Hudson]

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) {