do stuff via coding

[johanja]

Hi,

Using Delphi 2007 and myDAC 5.10.0.10.

I need to do some batch stuff using several tables on line.
Is there some example to find how to do this.

Need to go through some records from table A or query A. For each record in A I need to do some controls and as a result create new records in table B.
eg. get all online orders not yet processed, for each order create a new record (header information) and for all ordered products add records (detail information). All this in mysql...

Thanks,
Johanja

[Antaeus]

If you just need to fill two tables using data from the table A, you can use two TCRBatchMove components. You need to setup fields mapping correctly, and chose the appropriate value for Mode. If you need to perform some additional conversions/checking, you should implement such procedure manually.

[pimentel_flores]

Have you Tried using cursors?


I mean if you create a stored procedure and declare a cursor you can do this in the server side, Which will make your batch run faster,

review the Mysql Help or search for declaring cursors

Code: Select all

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN cur1;
  OPEN cur2;

  REPEAT
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF NOT done THEN
       IF b < c THEN
          INSERT INTO test.t3 VALUES (a,b);
       ELSE
          INSERT INTO test.t3 VALUES (a,c);
       END IF;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
  CLOSE cur2;
END

[johanja]

I think I need to clarify my needs.

I need this for an online ordering system.

I've got three tables to start from:

table1 is the orders table
table2 is the orderdetails table
table3 is the products table

I've got two tables I need to write the results to.
table4 processedorders table
table5 processedordersdetails table

I've created a txgrid with an overview of all records of the orders table. I select an order which is not flagged as processed and then click a button "process order"

action:
For this order create a new record in table4 and copy customer data and set some extra fields to a certain value, e.g. ordertype="online order"
We do this because we also want to add orders manually.

Also for each record for this order in table2, lookup product data in table3, because we need extra information for each article (recupel, auvibel, ... tax issues). For each product I create a new record in table5 with the products data and princing info.

It's rather complicated stuff so I can't do this with a stored procedure and all data is on a remote mysql server.

Hope this is a more detailed overview about what I want to create.

I'm a begiiner in delphi so...

Thanks
Johanja

[Antaeus]

The logic of your application should be developed by yourself, as there are many things to be considered. This question is out of MyDAC support scopes.
Feel free to contact us if you any MyDAC-related questions.