I have two tables contactactivity and contactactivitynotes, a one to one relationship based upon activityid.  The contactactivity primary key is activityID, it's a GUID with a New Record, Detault Value = CURRENT_GUID defined.  So, it appears, and works in my test,  that when I insert a record, the activityID GUID gets created for me.   The primary key for contactactivitynotes is also activityid, but does not have the details defined.  Here is the SQL I am trying.  

Insert into ContactActivity
(ContactID,ActivityType, ActivityDescription)
Values ('{8F7D0394-97C4-4ED4-A3CF-56E6BA0C56A6}',9, 'DOUBLE DOUBLE');
Insert into ContactActivityNotes
(ActivityID, Notes)
Values (cast(ident_current('ContactActivity') as CHAR(38)),'xxxx');


I am trying to insert contactactivity and contactactivitynote within the same transactions.  I have tried using the ident_current for the insert on the contactactivitynotes but it inserts a record with a blank GUID.  I am assuming this is happening because ident_current is for auto increment and won't work with how my table that is defined CURRENT_GUID?  

I have also tried using CURRENT_GUID in place of IDENT_CURRENT within the same transactions but it changes the GUID  on the second insert.  

Is there a way to do this insert within the same transaction or what would be the best way to update this table combo.  

What I am looking for is how to get the GUID of the record that I just inserted so I can do another insert with the same GUID.  

The only way that I can think of doing this is to run the first insert.  Then do a select and find the GUID key based upon the data in the columns that I inserted,  Then using that GUID key create an insert and do the second insert.  I can envision a  problem with getting the wrong key with the scenario.  It might be unlikely but it is possible.   

If anybody has a graceful way around this I would sure like to hear it.  

On 3/7/2014 7:16 PM, Owen wrote:
> What I am looking for is how to get the GUID of the record that I just inserted so I can do another insert with the same GUID.
> The only way that I can think of doing this is to run the first insert.  Then do a select and find the GUID key based upon the data in the columns that I inserted,  Then using that GUID key create an insert and do the second insert.  I can envision a  problem with getting the wrong key with the scenario.  It might be unlikely but it is possible.
> If anybody has a graceful way around this I would sure like to hear it.
>
Don't really have a solution for the GUID but DBISAM SQL does have the
LASTAUTOINC function. You could add an autoinc column (in addition to
guid) but only use it to ensure you have the correct row. Still use guid
as you normally would.

If you do the sql inserts inside a transaction you'll be guaranteed it's
the right row you're querying back.

Raul

Raul wrote:

On 3/7/2014 7:16 PM, Owen wrote:
> What I am looking for is how to get the GUID of the record that I just inserted so I can do another insert with the same GUID.
> The only way that I can think of doing this is to run the first insert.  Then do a select and find the GUID key based upon the data in the columns that I inserted,  Then using that GUID key create an insert and do the second insert.  I can envision a  problem with getting the wrong key with the scenario.  It might be unlikely but it is possible.
> If anybody has a graceful way around this I would sure like to hear it.
>
Don't really have a solution for the GUID but DBISAM SQL does have the
LASTAUTOINC function. You could add an autoinc column (in addition to
guid) but only use it to ensure you have the correct row. Still use guid
as you normally would.

If you do the sql inserts inside a transaction you'll be guaranteed it's
the right row you're querying back.

Raul


Thanks Raul, that is a good idea that I did not think of.  I have been looking though some of the other posts with similar problems and came across two other solutions.  These generate a GUID prior to any of the inserts.  One is to generate a key within the program using the CREATEGUID function as follows:

_CID: string;
_guid: TGUID;
begin
if OpenPictureDialog1.Execute then begin
FileName := LowerCase(OpenPictureDialog1.FileName);
if not InLinePics.Locate('_Name', FileName, []) then begin
 PicStream := TMemoryStream.Create;
 PicStream.LoadFromFile(FileName);
 CreateGUID(_guid);
 _CID := GUIDtoString(_guid);


I don't know how this GUID interacts with DBISAM, it looks to me like it creates a 32 byte string with some dashes and some {}.  Whereas DBISAM GUID is 38.  

Other options is to use a control table for the generation:  "It involves going to a specified record in a control table, putting that  table in Edit mode, then getting the new key  and cancelling the editing. Meanwhile, any other access from another user of the network to do the same will have to wait for the edit lock to be removed."

This table might involve the autoinc column as well for easy retrieval of the GUID with use of the lastautoinc function or the IDENT_CURRENT.

Not sure which solutions is better, although I would lean toward the GUID generation rather than incorporating a new autoinc column in my table.  I did notice that I have other tables down the road with this re-write that I am going to have similar issues, so a standard GUID generator would be reusable.  

On 3/11/2014 2:20 PM, Owen wrote:
> _CID: string;
> _guid: TGUID;
> begin
>    CreateGUID(_guid);
>    _CID := GUIDtoString(_guid);
>
>
> I don't know how this GUID interacts with DBISAM, it looks to me like it creates a 32 byte string with some dashes and some {}.  Whereas DBISAM GUID is 38.
>

That's exactly what DBISAM does internally.

If you can pre-generate the GUID then yes that would be cleanest solution.

Raul