how to delete all records from MySQL server using ODBC

[gjbroek]

Hi,

What is the easiest and fastest way to delete all records from a table in a MySQL table using an ODBC view.

Using doesn't work for many reasons (sometimes "connection busy", sometimes "update conflict"):

select table_view
requery()
delete all
= tableupdate(1,.T.)

Deleting record by record after a requery is stable, but way too slow. Any suggestions?

Regards, Gerrit

 

[craigber]

Can you use SQL Passthrough and issue DELETE FROM <table>?

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports&quot;

 

[Mike Lewis]

Gerrit,

Something like this:

lnConn = SQLCONNECT("MyDSN", "MyLogin", "MyPassword")
SQLEXEC(lnConn, "DELETE FROM MyTable")
SQLDISCONNECT(loConn)

In SQL Server, you can make this much faster by switching off logging while the delete is in progress. I don't know if MySQL lets you do that as well -- might be worth checking.

Also, check to see if MySQL supprts TRUNCATE TABLE. If so, that would be even faster.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[gjbroek]

Thanks!

This is just what I need.
Speed realy makes the difference...

Regards, Gerrit