Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 6 of 6 total
Thread Autoinc Field in a Transaction
Wed, May 29 2013 10:43 PMPermanent Link

Glenn Mc

Library Concepts

I have Transaction that SAVES a unique Row to three different DataSets. The first DataSet uses a DBISAM "autoinc" field to generate 'ItemNum' which is duplicated as the key field in the other two DataSets.

If I use Database.Load on each of the complete DataSets, the Transaction works perfectly. However, to avoid the lengthy Load time for all three large DataSets, I have used three small, query-based (QB) DataSets, one for each full DataSet.

Is there a way to structure a Transaction so that the autoinc 'ItemNum' is available for the second and third QB DataSets before the Commit? It appears that the QB DataSet1.Save must Commit before the autoinc information is available. Should this be broken into two Transactions?

Any help will be appreciated. Thank You.

- Glenn
Wed, May 29 2013 11:05 PMPermanent Link

Raul

Team Elevate Team Elevate

Glenn,

The commit is when the dataset communicates with the back-end web
service and the actual changes are sent and stored in the database.
Hence there is no way to know what the autoinc value is until after commit.

Using 2 transaction should work though have not tried it myself.

Raul


On 5/29/2013 10:43 PM, Glenn Mc wrote:
> Is there a way to structure a Transaction so that the autoinc 'ItemNum' is available for the second and third QB DataSets before the Commit? It appears that the QB DataSet1.Save must Commit before the autoinc information is available. Should this be broken into two Transactions?
Thu, May 30 2013 1:33 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Glenn,

<< Is there a way to structure a Transaction so that the autoinc 'ItemNum'
is available for the second and third QB DataSets before the Commit? It
appears that the QB DataSet1.Save must Commit before the autoinc information
is available. Should this be broken into two Transactions?  >>

The short answer is "yes".

The biggest issue with autoinc fields is that the transactions don't
currently return any rows *after* the commit occurs, so any generated
fields/columns won't be picked up without reloading the relevant datasets,
which can affect the row positioning, etc.

This issue, along with paged window loads of rows, are the two remaining big
issues that need to be addressed with the dataset handling.

Tim Young
Elevate Software
www.elevatesoft.com
Thu, May 30 2013 5:57 PMPermanent Link

Glenn Mc

Library Concepts

Raul, Tim:

Thank you both. I was hoping I was on the right track.

The two-transaction process has its own challenges as you noted, Tim. I had created another query-based DataSet to recall the most recent Save (and its autoinc 'ItemNum') after the first Commit:
       SELECT ItemNum FROM Items WHERE ItemNum = LASTAUTOINC(Items)

This approach, however, is time-sensitive. I couldn't Load that DataSet too quickly or I would get the previous autoinc 'ItemNum'.

I think there may be a better way by assigning a random, but known value for a field and repeating the Load until the field with that match is available. Haven't tried it yet. We'll see what happens.

-- Glenn
Fri, May 31 2013 5:00 AMPermanent Link

Matthew Jones

> assigning a random, but known value for a field

A GUID is of course a natural option. Does EWB have a way to allocate a GUID easily?
If not, it should.

/Matthew Jones/
Fri, May 31 2013 3:49 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Matthew,

<< A GUID is of course a natural option. Does EWB have a way to allocate a
GUID easily? If not, it should. >>

EWB can "simulate" GUIDs:

http://stackoverflow.com/questions/105034/how-to-create-a-guid-uuid-in-javascript

but it's going to be subject to the vagaries of the Math.random()
implementation.  A more solid solution would be to code a custom module on
the back-end (or add to an existing custom module) that returns an actual,
honest-to-goodness, GUID from the host system.  You could even have the
front-end EWB application grab them in bunches, if you'd like.

Tim Young
Elevate Software
www.elevatesoft.com
Image