Newbee INSERT Delphi 6

Newbee INSERT Delphi 6

Postby teunis » Wed 01 Feb 2006 14:34

Dear,
I am making a conversion program for the Advantage data format tables to MySQL
It works quite well but it is not elegant.
Can someone give a comment on the following code:
MyConnection and myQuery Q1 on a form
In a loop the program does:
1 Q1.Close
2 Q1.SQL.Clear
3 Q1.SQL.Add('INSERT INTO TableName VALUES ("1","2","3");');
4 Q1.Execute
It works fine
Why can't I repat line 3 in this program?
3 Q1.SQL.Add('INSERT INTO TableName VALUES ("1","2","3");');
3 Q1.SQL.Add('INSERT INTO TableName VALUES ("5","6","7")');
3 etc.
4 Q1.Execute
Any comments? Please, let me know
Bij the way is there a way to find out if a tableName already exists in the database?
I used Table1.TableName:= A_name; a_boolean:= Table1.Exists;
Thanks for your attention Teunis
teunis
 
Posts: 48
Joined: Wed 01 Feb 2006 14:15
Location: Curacao

Postby GEswin » Wed 01 Feb 2006 15:51

You should use a TMyScript to execute more than one sentence.
GEswin
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

Table Exists

Postby teunis » Thu 02 Feb 2006 17:57

The second question answered for the interested reader.
How to know if a table with a certain name exists.
Q1.SQL.Clear;
Q1.SQL.Add('SHOW TABLES FROM test LIKE "TableName" ');
Q1.Active;
IF Q1.Fields[0].asString = 'TableName' THEN do your thing....
Teunis
teunis
 
Posts: 48
Joined: Wed 01 Feb 2006 14:15
Location: Curacao

TMyScript OR TMyQuery

Postby teunis » Thu 02 Feb 2006 18:00

Is there a performance gain (better performance) If TMyScriipt is used
instead of TMyQuery in a loop???
Thanks for replying Teunis
teunis
 
Posts: 48
Joined: Wed 01 Feb 2006 14:15
Location: Curacao

Postby Ikar » Mon 06 Feb 2006 10:25

>Why can't I repat line 3 in this program?

If you need to insert huge number of records into the table, you should use MyLoader component. This component joins multiple INSERT statemens and posts them as one statement, therefore it is very quick. For more information read MyDAC Help.

>Bij the way is there a way to find out if a tableName already exists in the database?

Try using MyConnection.GetTableNames(..).
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56


Return to MySQL Data Access Components