• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 100
  • Last Modified:

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
0
James Stone
Asked:
James Stone
  • 4
  • 2
1 Solution
 
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now