ADOTable OR ADOQuery? 2

[AP81]

I am written an application which performs a mass insert of data into a mySQL database. The way I am inserting the data is by using an ADOTable and update the database line by line, e.g.

AddInvoicesToDB(Invoice.CustomerCode, Invoice.TemplateNo, Invoice.ToBePrinted, Invoice.InvMemo, strSubTotal, Invoice.GST, Invoice.CustMsg, Invoice.Date);


procedure TfrmMain.AddInvoicesToDB(CustCode, TempNo, ToBePrinted, Memo, Amount,GST,CustMsg, GenDate : String);
begin
ADOTableInsertInvoices.Open; //open table
ADOTableInsertInvoices.Edit; //set to edit mode for insertion of data
//begin insert
ADOTableInsertInvoices.Insert;
//set values
ADOTableInsertInvoices.FieldByName('cust_code').Value := CustCode;
ADOTableInsertInvoices.FieldByName('temp_no').Value := StrToInt(TempNo);
ADOTableInsertInvoices.FieldByName('to_be_printed').Value := ToBePrinted;
ADOTableInsertInvoices.FieldByName('inv_memo').Value := Memo;
ADOTableInsertInvoices.FieldByName('amount').Value := strToFloat(Amount);
ADOTableInsertInvoices.FieldByName('gst').Value := strToFloat(GST);
ADOTableInsertInvoices.FieldByName('cust_msg').Value := CustMsg;
ADOTableInsertInvoices.FieldByName('gen_date').Value := GenDate;
//end insert
ADOTableInsertInvoices.Post;
end;



Is it more appropriate to use an INSERT Query rather than an ADOTable? The database will eventutally be holding 5000+ lines, so would an ADOTable be less efficient than an SQL Insert Query? Is there a better way of performing this operation?






------------------------------------
There's no place like 127.0.0.1
------------------------------------

 

[Griffyn]

If you use a TADOQuery, you can do something like this: (This looks messy because of all your fields)

ADOQuery.SQL.Text := 'INSERT INTO MyTable (CUST_CODE, TEMP_NO, TO_BE_PRINTED, INV_MEMO, AMOUNT, GST, CUST_MSG, GEN_DATE) VALUES ("' + CustCode + '", ' + TempNo + ', "' + ToBePrinted + '", "' + Memo + '", ' + Amount + ', ' + GST + ', "' + CustMsg + '", "' + GenDate + '"';

ADOQuery.ExecSQL;

You can be even more efficient by using a TADOCommand component instead - these don't return a cursor by default.

ADOCommand.CommandType := cmdText;
ADOCommand.CommandText := 'INSERT INTO MyTable (CUST_CODE, TEMP_NO, TO_BE_PRINTED, INV_MEMO, AMOUNT, GST, CUST_MSG, GEN_DATE) VALUES ("' + CustCode + '", ' + TempNo + ', "' + ToBePrinted + '", "' + Memo + '", ' + Amount + ', ' + GST + ', "' + CustMsg + '", "' + GenDate + '"';

ADOCommend.Execute;

 

[Griffyn]

Actually, to aid readibility, I should take my own advice and make use of the Format function.

TADOCommand.CommandText := Format('"%s",%s,"%s","%s","%s",%s,%s,"%s","%s"', [CustCode, TempNo, ToBePrinted, Memo, Amount, GST, CustMsg, GenDate]);

 

[Griffyn]

Gah, where's an Edit feature when you need one

That previous post should have been

TADOCommand.CommandText := Format('INSERT INTO MyTable (CUST_CODE, TEMP_NO, TO_BE_PRINTED, INV_MEMO, AMOUNT, GST, CUST_MSG, GEN_DATE) VALUES ("%s",%s,"%s","%s","%s",%s,%s,"%s","%s")', [CustCode, TempNo, ToBePrinted, Memo, Amount, GST, CustMsg, GenDate]);

 

[AP81]

OK. Thanks heaps. Should I also use an ADOCommand when deleting?

The reason why I don't want to use an ADO table is because an ADOTable loads the whole table right? Thus meaning it will create a lot of overhead.

I never thought of using an ADOCommand, so I will give it a try.

One more thing... Is there any way to perform the insert in one go, rather than line by line?




------------------------------------
There's no place like 127.0.0.1
------------------------------------

 

[LucieLastic]

hi

Yep, use the ADOCommand for all statements which don't return a result set (including Deletes).

lou

 

[AP81]

Thanks all for the valuable info.




------------------------------------
There's no place like 127.0.0.1
------------------------------------