• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 774
  • Last Modified:

MS Access Blob to MYSQL

Six years ago I built an .asp based site where teachers could upload their lesson plans into an MS Access Database .  This was my first site where I utilized a database and while it was my best work at the time in hind-sight I would have done a lot of things differently.

 

Currently I am developing a .php based site where teachers can upload their lesson plans , but the files will be stored in a directory and the file path will be stored in a sql database that I administer with phpmyadmin 2.10.2.

 

The problem I am having is that I would like to take all of the files that are stored in the Access database as BLOBs on the first site and save them to a directory for my new site.  I think I have figured out how to move some of the database information; I can move author, lesson_name, keywords, file_path, etc, but I cannot figure out how to get all of the files stored as BLOBs out of Access and into a directory.  Currently the only way I have of doing this is going to my site an where all of the files are displayed and I can save them one by one and rename them, but there has to be a better way, an automated way to deal with over 1700 files.  I tried DB Blob Editor and MS Access Extract Images, but with no luck.

 

The actual database is too large to email so I have attached a copy of the database with only 10 records in it so that you can see the structure and layout. My server  blocks .mdb file attachments so you will have to rename the attached file with the .mdb extension.  The table is fairly intuitive, but&

 

File Name = The name of the file they uploaded

File Size = Numeric file size

File Data = The Long Binary Data, the actual file

Content Type = The type of file

Type of File = Either a lesson plan, test, quiz, etc&

Any help would be appreciated..

Thanks, Dan

0
dk4210
Asked:
dk4210
1 Solution
 
mankowitzCommented:
Probably the best way to do it is to

1. Make the access database a system DSN (http://www.devasp.com/samples/dsn_access.asp)
2. Connect to it by PHP (http://www.w3schools.com/php/php_db_odbc.asp)
3. have PHP save the files in systematic way.

<?php
$conn=odbc_connect('northwind','','');
if (!$conn)
  {exit("Connection Failed: " . $conn);}
$sql="SELECT * FROM table";
$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}
while (odbc_fetch_row($rs))
{
$handle = fopen(odbc_result($rs,"file name"), "w");
fwrite($handle, odbc_result($rs,"file data")) ;
fclose($handle);
}
odbc_close($conn);
?>
0
 
burchmarcelCommented:
I had a similar Problem reading Office Documents in LongBinary format. my Solution was a bit diffrent, and so probably interesting for others:

conn=odbc_connect($_SESSION['mgmtsys'],'','');
if (!$conn) exit("Connection Failed: " . $conn);

$query = "set textsize 2147483647";
$query = "SELECT * ";
$query .= "FROM tblVAttach ";
$query .= "WHERE VA_VZaehler=".$_GET['verbid']."; ";

$rs=odbc_exec($conn,$query);
if (!$rs) exit("Error in SQL");


odbc_binmode($rs,ODBC_BINMODE_RETURN);
odbc_longreadlen($rs,4096);
while (odbc_fetch_row($rs)) {
   $fileHandle = fopen("temp\\".odbc_result($rs,"VABezeichnung").odbc_result($rs,"VAExt"), "wb");
   while (($chunk=odbc_result($rs,'VAAttach'))!==false) {
        fwrite($fileHandle,$chunk);
   }
   fclose($fileHandle);
   //do something with the file
}
// change to ODBC_BINMODE_CONVERT for comparison
odbc_close($conn);

}
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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