mySQL question

[Trevor DeVore]

On Dec 27, 2005, at 4:54 PM, Ton Kuypers wrote:
> I've seem to have a small problem using mySQL from within  
> Revolution...
>
> I'm trying to import a lot of data into mySQL tables.
>
> I'm reading an export file and build a query using a repeat loop,  
> which basically creates a command like:
> 	insert into Clients  
> (ID,Name,FirstName,LastName,Email,Country,EmailCC,Remarks) values  
> ('480','S-P Africa','Maamoun','Allam','','1','','');"
> ...
>
> This can result is a command that inserts more then 10.000 records.
>
> When I send each line separately, the records get inserted into the  
> database, but when I try to send them all together, it takes  
> forever...
>
> I'm using the revExecuteSQL command.
>
> Is there a better way to import a bunch of data into mySQL?
>
> When I copy the query and execute it from CocoaMySQL, it works fine.
>
> Anyone any suggestions?

I believe with MySQL and Revolution you need to execute each command  
by itself.  If you are looking for the fastest way to import data  
into mysql then I would use the mysql command line tool.  It will  
process a text file containing multiple insert statements very  
quickly.  You can read about that here:

<http://dev.mysql.com/doc/refman/5.0/en/mysql.html>

You would just put all of the insert calls into a text file and the  
command line tool will do all of the inserts for you.

If you want to do the import using Revolution then you will need to  
do something like this:

1) Split list of insert commands into each individual command
2) LOCK tables you are inserting into (faster).  You may also want to  
use AUTOCOMMIT=0 if you are using InnoDB tables in MySQL <http:// 
dev.mysql.com/doc/refman/5.0/en/lock-tables.html>.
3) Loop through all commands doing insert
4) UNLOCK tables

-- 
Trevor DeVore
Blue Mango Multimedia
trevor at mangomultimedia.com