how to run mysql command in VFP

[crisabella777]

truncate table tbl;
alter table tbl auto_increment = 1;

thanks

 

[Mike Lewis]

Are you asking how to run those particular commands against VFP data?

If so, there is no truncate command in VFP. The nearest equivalent is to delete the old records and then pack the table.

ALTER TABLE exists in VFP, but if you want to reset an auto-increment field, the syntax is different:

ALTER TABLE TableName1 ADD | ALTER [COLUMN] FieldName1 
   [AUTOINC [NEXTVALUE NextValue [STEP StepValue]]]

Or are you asking how to run commands in general against a MySQL remote back end? If so, then there are various options. Read the Help file topics on: remote views; SQL pass-through; and cursor adapters. And come back when you have a more specific question.

UPDATE: If you want to run TRUNCATE and ALTER TABLE against a remote MySQL back end, then you will almost certainly do it via SQL pass-through. In that case, you should read the help topic on SQLEXEC() as a starting point.

Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[crisabella777]

thanks Sir for the quick reply
I am using MySql as backend.
here is my code.
text to lcSql noshow
truncate table tbl
alter table tbl auto_increment = 1
endtext
But I've encountered an error (MySql Error).

 

[Mike Lewis]

The code you have posted does nothing to update the database. All that you are doing is storing the commands in a VFP variable, named lcSQL. That's a good first step. Now you need to actually send those commands to MySQL. That's where SQLEXEC() comes in.

So, if you follow my previous suggestion and look up SQLEXEC() in the VFP Help, you will see how to do it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[crisabella777]

Thank you Sir