Import Microsoft Access Database in Mysql database

Question

I have a particular situation where my client require to import (periodically) an ms-access database into his mysql website database (so it's a remote database).

Because the hosting plan is a shared hosting (not a vps), the only way to do it is through PHP through an SQL query, because I don't have ODBC support on hosting.

My current idea is this one (obviusly the client has a MS-Windows O.S.):

Create a small C# application that convert MS-Access database into a big SQL query written on a file
The application will then use FTP info to send the file into a specified directory on the website
A PHP script will then run periodically (like every 30 minutes) and check if file exists, eventually importing it into the database

I know it's not the best approach so I'm proposing a question to create a different workaround for this problem. The client already said that he wants keep using his ms-access database.

The biggest problem I have is that scripts can last only 30 seconds, which is obviusly a problem to import data.

Similar process I've used before. Used a script to export Access database to a valid MySQL import SQL file and upload to an FTP site where another script on the remote server ran every night to see if the file existed. If so, it would import the SQL file (after some security checks) — Aaron W.
– Aaron W., Commented Mar 14, 2012 at 18:39
How you handle the 30seconds script execution limit? I don't know how much will the database be big — Francesco Belladonna
– Francesco Belladonna, Commented Mar 14, 2012 at 18:46
Php is running in safe mode, I already know that function :( — Francesco Belladonna
– Francesco Belladonna, Commented Mar 14, 2012 at 19:15

Martin · Accepted Answer · 2021-03-02 23:10:02Z

2

To work around the 30-second limit, call your script repeatedly, and keep track of your progress. Here's one rough idea:

if(!file_exists('upload.sql')) exit();

$max = 2000; // the maximum number you want to execute.

if(file_exists('progress.txt')) {
    $progress = file_get_contents('progress.txt');
} else {
    $progress = 0;
}

// load the file into an array, expecting one query per line
$file = file('upload.sql');

foreach($file as $current => $query) {
    if($current < $progress) continue; // skip the ones we've done
    if($current - $progress >= $max) break; // stop before we hit the max
    mysql_query($query);
}

// did we finish the file?
if($current == count($file) - 1) {
    unlink('progress.txt');
    unlink('upload.sql');
} else {
    file_put_contents('progress.txt', $current);
}

edited Mar 2, 2021 at 23:10

answered Mar 14, 2012 at 20:24

Martin

6,0257 gold badges52 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Francesco Belladonna Over a year ago

Yeah that's the same approach I thought about, however I like the way you handle it in code (expecially the $current < $progress thing), I'll tell you the result in a few days. Thanks a lot

Collectives™ on Stack Overflow

Import Microsoft Access Database in Mysql database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related