0

I have two MYSQL tables in a Delphi program.

1) A table "IPs" with fields ID (Auto-increment), IP and Servername.

2) A table "Traffic" with fields ID(Auto-inc), Date, Status and FK_ID (to link to table "IPs".

Working / adding records with these two tables in record mode using Append,.., Post is quite slow, so I wish to do SQL inserts to speed up things.

Data comes from a stringgrid with columns IP, Domain, Date, Status which I can read line by line in my program. I then make a mySQL statement:

Q1.SQL.Add ('Insert Ignore into IPs (ip, Server) values (' + QuotedStr(IP) + ',' + QuotedStr(ServerName) + ')');
Q1.ExecSQL;

This works fine so far.

In the same database, in the Traffic table, I also need to insert The date (DateTime) and Status, all from the same row in my stringgrid. In addition need to update the FK_ID with the ID from "IPs". The stringgrid is read in a loop, line by line, to post it into the two tables.

Should I use the same Query Q1 or use another query Q2? And how to read the ID from the master table and put it into FK_ID in the detail table?

Variable names from the StringGrid are IP, Servername, Date and Status.

Tore
  • 11
  • 2
  • Using SQL parameters is the first step to improve performance here. Your data do not come from string grid, you are storing them there but in practice it's a tree structure. Store it like that on client side and storing to the server side will get far much easier then (even presenting can be more user friendly then). – Victoria Jun 10 '17 at 11:19
  • See this question https://stackoverflow.com/questions/3837990/last-insert-id-mysql You could also use a stored procedure with the last id as an out parameter for IP table.. google has plenty of examples of this. – John Easley Jun 10 '17 at 18:01
  • Thanks a lot! Victoria pointed in the right direction. I studied how to use paramteres, and changed my program accordingly. It works like a charm now. – Tore Jun 11 '17 at 12:01

0 Answers0