Move a MSSQL blob field to a MySQL blob field

I have an existing MSSQL blob field that I would like to "export" to a MySQL database.  I have a script that will display the existing blob field, but I am guessing that I need to "save" the image and then "import" them into the MySQL database.

I tried to use the display script to just insert, but I get a "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2".
If I remove the blob fields from the insert(`STI_Image`&`STI_Large_Image`), it inserts the rest of the information into the table.

Any help would be appreciated.
InsertBlob.php
DisplayImages.php
James Stonewarehouse managerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tomas Helgi JohannssonCommented:
Hi!

In SQLServer Management Studio you could choose to export the data to file either as a CSV file or with INSERT statements and easily convert that insert statement to valid MySQL statements that you import with

mysql -u username -p userpass  the_database  <  the_insertscript.sql

Open in new window

or if you choose CSV format
then in the mysql console
LOAD DATA INFILE 'c:/tmp/myexport.csv' 
INTO TABLE mydatabase.mytable 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Open in new window


Regards,
     Tomas Helgi
0
James Stonewarehouse managerAuthor Commented:
Sorry I guess I worded what I am looking for wrong -- I am trying to do this with PHP.  I have an android device that has php/MySql (runs as localhost)  that I use as a warehouse scanner.  I am wanting to upload the MSSQL database info (filtered to specific items from a php form) into the android device.  The android device can hook to the internet to download, but then the device goes to the warehouse with no outside connection.  If I can get the MS info into a sql file using php, I could use php to read that file.
0
Tomas Helgi JohannssonCommented:
Hi!

You could do this by having the an export.php script with code like this where the connection is to the MS SQLServer
....
// output headers so that the file is downloaded rather than displayed
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=data.csv');

// create a file pointer connected to the output stream
$output = fopen('php://output.csv', 'w');

// output the column headings
fputcsv($output, array('Column 1', 'Column 2', 'Column 3'));

// fetch the data
mysql_connect('localhost', 'username', 'password');
mysql_select_db('database');
$rows = mysql_query('SELECT field1,field2,field3 FROM table');

// loop over the rows, outputting them
while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row);
....

Open in new window


And import.php would be something like this with connection to MySQL

... 
$filename=$_FILES["file"]["csv_name"];		
 
 if($_FILES["file"]["size"] > 0)
 {
  	$file = fopen($filename, "r");
 while (($getData = fgetcsv($file, 10000, ",")) !== FALSE)
 {
     $sql = "INSERT into mytable(field1,field2,field3)
         values ('".$getData[0]."','".$getData[1]."','".$getData[2]."','".$getData[3]."','".$getData[4]."')";
     $result = mysqli_query($con, $sql);
     if(!isset($result))
     {
       //error in insert
     }
     else {
	//successfull insert  
     }
   }
   fclose($file);	
 }
...

Open in new window


or you also could do the import by simply issue a load statement like this

...
$filename=$_FILES["file"]["csv_name"];		
$myloadquery = "LOAD DATA INFILE '$filename' 
INTO TABLE mydatabase.mytable 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(field1,field2,field3)
IGNORE 1 ROWS";

$res = mysqli_query($conn,$myloadquery);

Open in new window


Regards,
     Tomas Helgi
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Tomas Helgi JohannssonCommented:
Hi!

Did you manage to solve this ?

Regards,
    Tomas Helgi
0
James Stonewarehouse managerAuthor Commented:
Sorry I ended up just exporting the files to a folder and saving the link in the database.  Thanks for your help.
0
Tomas Helgi JohannssonCommented:
Hi!

Please close this question as you see appropriate.

Regards,
     Tomas Helgi
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.