Hi,

I have an application which contains approximately 30 tables.

The first table contains the primary records for the database. The others either link to the first table on it's primary key, or link to one of the other tables linking on it. This can go down 4 'layers'.

I wish to be able to clone a primary record, along with all it's child records (and their children, etc).

In the past I have been able to do this relatively easily because the database contained GUID fields for it's primary keys. The method I used was:

1) Copy the primary record to a temporary location (normally the memory).

2) Copy the child tables records (1:many) and their children to the same temporary location.

3) Perform an UpdateSQL command that will update the primary keys for each table with a new GUID field along with the child linking field.

4) Perform "Insert Into" SQL requests back into the main database from the temporary (memory) database.

The problem I face this time is that the database doesn't use GUID fields, and instead uses Autoinc fields (which are assigned on the after post event). I was hoping to avoid having to do the insert via TDBISAMTable's if possible but not sure if it can be done.

I was just wondering if anyone may have any suggestions or recommendations as to some techniques I could use with Autoinc values instead?

Cheers

Adam

Adam


You don't have to use tables but I don't think you can do it in one clean sql statement.

This statement "uses Autoinc fields (which are assigned on the after post event)" baffles me - are you saying that your program generates the autoinc IE its just an integer or is it a "proper" autoinc.

Roy Lambert

Hi Roy,

Sorry for baffling you.

What I've noticed with DBISAM is that the Autoinc fields aren't calculated (DBISAM's own Autoinc fields) - until the record is posted.

ie: If I open up DBSYS and open up a table with an Autoinc field, and insert a record - the autoinc field remains blank until after I post the record. This is what I mean by "Assigned on the after post event".

With my original way of cloning (where GUID's were used) - I didn't have to wait until posting a record to know what the key field ID's were going to be - as I could assign them at any time - they would be unique.

With Autoinc's - I can see I have to change my approach, but I'm not sure how I go about finding out the autoinc value that's generated by DBISAM when doing an INSERT INTO SQL statement, or what the best approach to this is - especially when there are multiple tables and multiple records per tables in some instances.

Cheers

Adam.

Adam

>Sorry for baffling you.

sallright - its easy to accomplish

>What I've noticed with DBISAM is that the Autoinc fields aren't calculated (DBISAM's own Autoinc fields) - until the record is posted.

>ie: If I open up DBSYS and open up a table with an Autoinc field, and insert a record - the autoinc field remains blank until after I post the record. This is what I mean by "Assigned on the after post event".

OK I understand now

>With my original way of cloning (where GUID's were used) - I didn't have to wait until posting a record to know what the key field ID's were going to be - as I could assign them at any time - they would be unique.
>
>With Autoinc's - I can see I have to change my approach, but I'm not sure how I go about finding out the autoinc value that's generated by DBISAM when doing an INSERT INTO SQL statement, or what the best approach to this is - especially when there are multiple tables and multiple records per tables in some instances.

I only have an old version of DBISAM so things may have changed.

There's an SQL command - LASTAUTOINC. In my pdf version of the 4.25 manual the example is

START TRANSACTION;
INSERT INTO customer (company) VALUES ('Test');
INSERT INTO orders (custno,empno) VALUES (LASTAUTOINC('customer'),100);
INSERT INTO orders (custno,empno) VALUES (LASTAUTOINC('customer'),200);
COMMIT FLUSH;

so you should be able to get away with a script as long as you're careful about the order in which you do things

Roy

Thanks Roy,

I'll check this out - this may give me just what I need. I missed it whilst looking for an answer. Cheers!