Answered dataset save rowid as part of the temp-table

progdev1

Member
Hi All,
I am new to datasets and I am trying to copy an entire database table to a dataset temp-tables along with 1 extra field which is a holds the database table record rowid .

I am trying to do this in three stages
1. All the data is copied to the corresponding temp-table fields using a fill.
2. I then reuse the data source to copy the database table rowid to the temp-table field RecordRowid
3. I then do a second fill to try and copy the updated data to the temp table.

In the code below 1 works fine but 2. and 3 don't work. I have spent ages trying to figure this out but have failed to get this to work. I am probably going to have to use a for each here to populate the RecordRowid manually.

Before I do I was just wondering if it is possible to do this and I'm just doing something very simple wrong.

Code:
/* dsOrderTT.i*/
define temp table ttOrders Like sports2000.order
field RecordRowid as rowid. 
....

/* fillDSOrder.p -- Test procedure for an Order Dataset */
{dsOrderTT.i}
DEFINE DATASET dsOrder FOR ttOrder, ttOline, ttItem
    DATA-RELATION OrderLine FOR ttOrder, ttOline
    RELATION-FIELDS (OrderNum, OrderNum)
    DATA-RELATION LineItem FOR ttOline, ttItem
    RELATION-FIELDS (ItemNum, ItemNum) REPOSITION.

DEFINE INPUT PARAMETER piOrderNum AS INTEGER NO-UNDO.
DEFINE OUTPUT PARAMETER DATASET FOR dsOrder.
DEFINE VARIABLE vchPairs AS CHARACTER NO-UNDO.

DEFINE QUERY qOrder FOR Order, Customer, SalesRep.
DEFINE QUERY qItem FOR Item.
DEFINE DATA-SOURCE srcOrder FOR QUERY qOrder Order KEYS (OrderNum),Customer KEYS (CustNum), SalesRep KEYS (SalesRep).
DEFINE DATA-SOURCE srcOline FOR OrderLine KEYS (OrderNum).
DEFINE DATA-SOURCE srcItem FOR Item KEYS (ItemNum).

QUERY qOrder:QUERY-PREPARE("FOR EACH Order WHERE Order.OrderNum < 10 " +
                           ", FIRST Customer OF Order, FIRST SalesRep OF Order").
BUFFER ttOrder:ATTACH-DATA-SOURCE(DATA-SOURCE srcOrder:HANDLE,"Customer.Name,CustName").
BUFFER ttOline:ATTACH-DATA-SOURCE(DATA-SOURCE srcOline:HANDLE).
BUFFER ttItem:ATTACH-DATA-SOURCE(DATA-SOURCE srcItem:HANDLE).


DATASET dsOrder:FILL().
DATASET dsOrder:ACCEPT-CHANGES ().

MESSAGE  "Someone else changed it." DATA-SOURCE srcOrder:DATA-SOURCE-ROWID VIEW-AS alert-box.

BUFFER ttOrder:ATTACH-DATA-SOURCE(DATA-SOURCE srcOrder:HANDLE,"DATA-SOURCE srcOrder:DATA-SOURCE-ROWID,ttOrder.RecordRowident)").
BUFFER ttOLine:ATTACH-DATA-SOURCE(DATA-SOURCE srcOline:HANDLE,"DATA-SOURCE srcOline:DATA-SOURCE-ROWID,ttOLine.RecordRowident").
BUFFER ttItem:ATTACH-DATA-SOURCE(DATA-SOURCE srcItem:HANDLE,"DATA-SOURCE srcItem:DATA-SOURCE-ROWID,ttItem.RecordRowident").

DATASET dsOrder:GET-BUFFER-HANDLE("ttOrder"):FILL-MODE = "MERGE".
DATASET dsOrder:GET-BUFFER-HANDLE("ttOline"):FILL-MODE = "MERGE".
DATASET dsOrder:GET-BUFFER-HANDLE("ttItem"):FILL-MODE  = "MERGE".

DATASET dsOrder:FILL().
DATASET dsOrder:ACCEPT-CHANGES ().
FIND FIRST ttOline NO-ERROR. 
IF NOT BUFFER ttOLine:ROW-STATE = ROW-MODIFIED  THEN
    MESSAGE  "Someone else changed it." string(ttOLine.RecordRowident) VIEW-AS alert-box.

BUFFER ttOrder:DETACH-DATA-SOURCE().
BUFFER ttOline:DETACH-DATA-SOURCE().
BUFFER ttItem:DETACH-DATA-SOURCE().
 

TomBascom

Curmudgeon
So far as I know there is no way to do this without using a FOR EACH (or something similar) and populating the extra field individually.

Now that we have that out of the way... why?

What use are you going to make of this ROWID?

The chances are very, very good that whatever it is you are trying to do either 1) will not actually work the way that you think it will or 2) can be done in a better way.

You're also using FIRST and OF in your queries. Which makes a couple more big fat red flags. You could toss in RELEASE for a trifecta ;)
 

progdev1

Member
Now that we have that out of the way... why?

What use are you going to make of this ROWID?

The chances are very, very good that whatever it is you are trying to do either 1) will not actually work the way that you think it will or 2) can be done in a better way.

You're also using FIRST and OF in your queries. Which makes a couple more big fat red flags. You could toss in RELEASE for a trifecta ;)
The place I work in uses app server. We load data at the front end using a temp table, the user makes a change and we pass the data back to the appserver where we use the rowid to get the database record and write the results to the database . Fastest way to get a record for when writing updates to the database. But now that you asked I'm thinking about you have a point. Mabye should I be using the temp table passed back from the client, reading into a dataset and updating the database that way. Or something like that that is possibly a simpler solution

The code is actually from the progress book on training with datasets, I'm just using it as a guide to learn datasets so I can apply it to the project I work on where I work. I would never use OF or FIRST in such a situation. It doesn't help that the code is a bit messed up as well, but that is mainly coz I have changed it so many times trying to get it to work.
 

Cringer

ProgressTalk.com Moderator
Staff member
The main problem with ROWID is that as soon as the DBA does any maintenance on the storage area for that table there is no guarantee that the ROWID will remain the same. So the issue comes when you start using ROWID in a storage situation.
Using it as a means to find a row on the AppServer isn't necessarily too bad, but in my opinion all tables should have a primary key that is a unique way of finding a record, be that an integer key or a GUID or some such method. That way you don't need to worry about ROWIDs. Yes it may be slightly slower to find the record, but I doubt it would be much slower.
 

TomBascom

Curmudgeon
The described architecture will work fine in development. It might even make a good demo.

It will be an unmitigated disaster in a large scale production environment.

ROWIDs (or RECIDs) are not trustworthy unless you have a lock on the record.

Another user, in another session could delete the record. That or user (or a third user) could then insert a new record that reuses that ROWID. If there is more than one table in the storage area that the data came from the reused ROWID might not even contain data from the same table as the old one.

In a situation involving app servers passing datasets to and from clients you are NOT well advised to lock the data while the client processes it. That will lead to significant scalability problems. It will also be a usability nightmare.

The speed advantage of using a ROWID to update data is overwhelmed, by multiple orders of magnitude, by the fact that this data had to be passed between the app server and client. Saving a few microseconds on an operation that takes tenths of second is a pointless optimization and, in this case, one which will introduce obscure bugs that will drive you, and your users, insane. And then require a top to bottom rewrite to resolve.

As Cringer says -- all tables should have a unique key (it does not need to be the "primary" key). Use that. Do not use ROWIDs.
 
Top