MS SQL Dataserver - VALIDATE statements / why can't loca

Posted by guymills on 10-May-2016 09:49

Hi,

We've been testing our OpenEdge code with the MS SQL Dataserver, with mixed results.  We've got an OpenEdge product that has been developed over that past 25 years, and has several hundred thousand lines of code.

Processes that depend on reads, seem OK.

However, for updates we're hitting the non-committed database issue, as described in the dataserver documentation by:

DEFINE BUFFER xcust FOR cust.

CREATE cust. cust-num = 111.

FIND xcust WHERE xcust.cust-num = 111.

DISPLAY xcust.

In our case it's seems mainly due to further procedures being run which FIND a newly created record, before that new record's scope has ended.  In the subsequent procedures, the record doesn't appear to exist - hence errors.

The fix is to insert a VALIDATE statement after the CREATE and ASSIGN - which forces a DB write and ensures the subsequent FIND has something to work with.

If we decide to go down the dataserver route, we're considering analysing our code (e.g. using proparse), and auto-inserting VALIDATE statements where we have CREATEs followed by RUN statements within the record scope - if the RUN statements reference the newly created buffer.

If anyone has any experience of doing such a thing, any pointers would be appreciated.  NB. If this is too tricky, we might try the sledgehammer approach of doing VALIDATE NO-ERROR after every DB assign statement!

But the other question is this, could Progress make things easier for us?

I would have thought it would be (relatively) easy for a FIND to check both the DB and local buffers for the table before deciding that the record isn't there.  So in the case above, the

FIND xcust WHERE xcust.cust-num = 111.

would fail on the DB, but would be found in a local buffer.  Then the buffer pointed to xcust would just be the same as the one pointed to by cust.  Both buffers would reference the same record - but isn't that what happens anyway for the "standard" ABL behaviour?

BTW I think FOR EACHes would be too difficult for this approach to be done, but generally the pattern is:

CREATE <table>.

ASSIGN <fields>.

RUN proc.p(INPUT <record>.<pk>).

/* proc.p */

DEFINE INPUT PARAMETER IcPK AS CHARACTER NO-UNDO.

FIND <table> WHERE <table>.<pk> = IcPK NO-LOCK.

So I don't think there is a strong need to support this "help" for FOR EACHes anyway - and we'd be happy to live with this limitation, and a flag (startup parameter) which would confirm we're working in such a way.

NB. We also use RECID/ROWIDs to a certain extent, and I understand that currently these too would fail.   However, we've also ensured that our MS-SQL database has the RECID columns added.  So I would have thought (again) that Progress could populate the RECID/ROWID prior to the DB update, and then just assign that value on DB update (e.g. using the user number of the session to prevent 2 users assigning the same ROWID concurrently).

I assume if it was as simple as I've described, then it would have been done already - so I'd be happy for someone to explain why my thinking is wrong.

And like I say, any pointers as to how best to deal with the VALIDATE statements would also be much appreciated.

Many thanks for reading,

Guy :-)

All Replies

This thread is closed