Batch Inserts And Prepared Query Error

Question

Ok, so I need to populate a MS Access database table with results from a MySQL query. That's not hard at all. I've got the program written to where it copies a template .mdb file to a temp name and opens it via odbc. No problem so far.

I've noticed that Access does not support batch inserting (VALUES (foo, bar), (second, query), (third query)). So that means I need to execute one query per row (there are potentially hundreds of thousands of rows). Initial performance tests show a rate of around 900 inserts/sec into Access. With our largest data sets, that could mean execution times of minutes (Which isn't the end of the world, but obviously the faster the better).

So, I tried testing a prepared statement. But I keep getting an error (Warning: odbc_execute() [function.odbc-execute]: SQL error: [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect , SQL state 07001 in SQLExecute in D:\....php on line 30).

Here's the code I'm using (Line 30 is odbc_execute):

$sql = 'INSERT INTO table 
    ([field0], [field1], [field2], [field3], [field4], [field5]) 
    VALUES (?, ?, ?, ?, ?, ?)';
$stmt = odbc_prepare($conn, $sql);
for ($i = 200001; $i < 300001; $i++) {
    $a = array($i, "Field1 $", "Field2 $i", "Field3 $i", "Field4 $i", $i);
    odbc_execute($stmt, $a);
}

So my question is two fold. First, is there any idea on why I'm getting that error (I've checked, and the number in the array matches the field list which matches the number of parameter ? markers)? And second, should I even bother with this or just use the straight INSERT statements? Like I said, time isn't critical, but if it's possible, I'd like to get that time as low as possible (Then again, I may be limited by disk throughput, since 900 operations/sec is high already)...

Thanks

Answer 1

Does PHP give you a way to view the INSERT statement you're executing after parameter replacement? I think you may not be getting quotes around text values in your VALUES list. Without quotes, the Jet database engine will interpret 'Field1 200001' as two values rather than one.

Also, I don't know PHP, but should the second member of your array be "Field1 $i" instead of "Field1 $"?

Can you execute this statement from PHP? And does it work?

INSERT INTO table 
    ([field0], [field1], [field2], [field3], [field4], [field5]) 
VALUES (
    200001,
    'Field1 200001',
    'Field2 200001',
    'Field3 200001',
    'Field4 200001',
    200001);

How about this?

$sql = 'INSERT INTO table 
    ([field0], [field1], [field2], [field3], [field4], [field5]) 
    VALUES (?, "?", "?", "?", "?", ?)';

Answer 2

Do you need to do this row by row? Why not insert all the data at once?

http://stackoverflow.com/questions/442915/what-is-the-best-way-to-synchronize-data-between-ms-access-and-mysql/443003#443003