Progress 9.1E ODBC questions

Posted by kefannon on 04-Jun-2008 09:42


Progress 9.1E, OE 10.1A,B,C - Same questions:

1. When using SQL clients (ODBC into Progress), how does one extract the transaction ID for a particular SQL transaction? I know when using a 4GL/ABL client, I can simply get it from DBTASKID. However, I'm relatively new to SQLing into Progress.

2. It appears that SELECT ROWID from pub.table returns the RECID for that particular record (at least for 9.1E, haven't checked 10 yet). Is there anyway to extract the rowid without using a hex converter to convert recid over?



All Replies

Posted by Admin on 04-Jun-2008 20:38

I have not come across the sql92 equiv of DBTASKID, however you could try PUB."_Connect"."_Connect-transId". (see DGR.PDF 'Database Admin Guide & Reference' for vst table descriptions, and KCentre article P10977 for info on sql92 access to vst entries (make sure your connected user has permissions granted).

SQL92 returns the p4gl equiv of RECID when you request ROWID - and dont think there is a true rowid function. The two values are NOT the same, you cant convert one into the other. The only explanation for the ROWID values is to allow for meta schema joins. eg _File to _Field via recid, fow which you use the ROWID function within sql92, clear as mud.

Will be interested to hear how you finally work things through.

- Colin

Posted by kefannon on 12-Jun-2008 07:58

With respect the the RECID/ROWID not being the same or interchangeable, this is true to an extent. The reality is, at least for Progress 9 and below where ROWID and RECID are both implemented, that ROWID is simply the RECID stored in hexadecimal format. Java does have a function that will convert an integer to a hex string.

Now, placing that into a TO-ROWID function will net absolutely nothing. The string must be completed. I'm sure we all know that a ROWID in Progress v9 can be displayed as a 10 character string. I'm also sure we all know that the entire 10 character string must be present in the TO-ROWID function to correctly find the particular record. Java can handle this as well, by a simple for loop followed by a final java insert statement.

An example:

61 (Hex) is equal to 97 (Base 10). TO-ROWID("61") will not pull anything. TO-ROWID("0x00000061") will grab the record with RECID 97. So to convert "61" into "0x00000061," it is a simple matter of the following:

for (int i = <>.toString().length(); i




This will give you full ROWID from the converted hexadecimal.

I don't know how this will work in OE10, as ROWID can go up to 64bit, but the maximum integer value for RECID is still 2^31-1

This thread is closed