Getting the string format of ROWID via SQL92

There is a KB on this subject, it may help you:

https://knowledgebase.progress.com/articles/Article/21616

@rob I had found that KB which is what prompted my confusion.  It seems to giving incorrect information - just like the documentation for TO-ROWID().

The method "IntToHex()" in the sample code in part b doesn't work.  I believe it has to do with the character case.  I'm using OE 11.7.4 on windows.

Above you see that the hex value that is generated.  It is capitalized and is totally consistent with the documentation about TO-ROWID.  

But the record cannot be found, after converting that back to a ROWID.

rRowId = TO-ROWID(cRowId).
FIND rci_cust WHERE ROWID(rci_cust) = rRowId.

However if I use TO-ROWID(LC(cRowId)) then it seems to start working again, probably because of the LC() function.

I tested on HP-UX as well and it behaves the same as windows.  I suspect that both the docs and the KB article are mistaken about the special string representation for ROWID.  Does that seem possible?

I'm not as bothered by the fact that I have to use the LC() function as I am by the fact that the formal information from Progress is wrong.  Also, I noticed that the STRING(ROWID(rci_cust)) returns a much longer hex string than the one generated by "IntToHex"  and I'm wondering if this should be a concern.  (eg. the STRING(ROWID(rci_cust)) generates the long string "0x0000000000a002e1")

Hi David,

It looks like this IntToHex function is from the days of 32-bit rowids (pre-10.1B).  If you change the line in IntToHex to "j = 16 - LENGTH(c)." then you'll get the 64-bit value "0x0000000000A002E1" when your recid is 10486497.

As to why you need to add the LC function, to get a find by rowid to work, I'd call that a bug in TO-ROWID.  Hexadecimal digits don't have a case, so TO-ROWID should give the same output whether the input string is "0x0000000000a002e1" or "0x0000000000A002E1".

In current versions, the rowid is a 64 bit integer, which in hex is 0x + 16 hexadecimal digits (total length 18).

Since sql92 does not have a hex function and I have no idea how to create functions or loops in sql92, I proudly present my highly optimized 'to_rowid' select ;-):

select 
	name, 
	'0x'
	+ substring( '0123456789abcdef', 1 + mod( cast( rowid as bigint ), power( 16, 16 ) ) / power( 16, 15 ), 1 ) 
	+ substring( '0123456789abcdef', 1 + mod( cast( rowid as bigint ), power( 16, 15 ) ) / power( 16, 14 ), 1 ) 
	+ substring( '0123456789abcdef', 1 + mod( cast( rowid as bigint ), power( 16, 14 ) ) / power( 16, 13 ), 1 ) 
	+ substring( '0123456789abcdef', 1 + mod( cast( rowid as bigint ), power( 16, 13 ) ) / power( 16, 12 ), 1 ) 
	+ substring( '0123456789abcdef', 1 + mod( cast( rowid as bigint ), power( 16, 12 ) ) / power( 16, 11 ), 1 ) 
	+ substring( '0123456789abcdef', 1 + mod( cast( rowid as bigint ), power( 16, 11 ) ) / power( 16, 10 ), 1 ) 
	+ substring( '0123456789abcdef', 1 + mod( cast( rowid as bigint ), power( 16, 10 ) ) / power( 16, 9 ), 1 ) 
	+ substring( '0123456789abcdef', 1 + mod( cast( rowid as bigint ), power( 16, 9 ) ) / power( 16, 8 ), 1 ) 
	+ substring( '0123456789abcdef', 1 + mod( cast( rowid as bigint ), power( 16, 8 ) ) / power( 16, 7 ), 1 ) 
	+ substring( '0123456789abcdef', 1 + mod( cast( rowid as bigint ), power( 16, 7 ) ) / power( 16, 6 ), 1 ) 
	+ substring( '0123456789abcdef', 1 + mod( cast( rowid as bigint ), power( 16, 6 ) ) / power( 16, 5 ), 1 ) 
	+ substring( '0123456789abcdef', 1 + mod( cast( rowid as bigint ), power( 16, 5 ) ) / power( 16, 4 ), 1 ) 
	+ substring( '0123456789abcdef', 1 + mod( cast( rowid as bigint ), power( 16, 4 ) ) / power( 16, 3 ), 1 ) 
	+ substring( '0123456789abcdef', 1 + mod( cast( rowid as bigint ), power( 16, 3 ) ) / power( 16, 2 ), 1 ) 
	+ substring( '0123456789abcdef', 1 + mod( cast( rowid as bigint ), power( 16, 2 ) ) / power( 16, 1 ), 1 ) 
	+ substring( '0123456789abcdef', 1 + mod( cast( rowid as bigint ), power( 16, 1 ) ) / power( 16, 0 ), 1 ) as 'to_rowid'

from pub.customers

RECID and ROWID are not the same thing and were never intnded to be so.

Since time immemorial, RECID's were stored as 32-bit integers and you could freely convert between the two. When 64-bit integer daattype was introduced to the 4GL, this conversion ability was retained.

ROWID's are NOT integers. They are variabkle kenght byte strings. When they were added to the 4GL, they could (and still can) have values much larger than 32-bit integers. Although some ROWID values can be converted to integers, many cannot (and vice versa). This is the reason that there is no easy function to convert ROWID';s to RECID's. This was a deliberate decision, not an accident.

Furthermore, there is no need at all for such conversion. Just use ROWID's and dont try to convert them to anything else.

[quote user="gus bjorklund"]Furthermore, there is no need at all for such conversion. Just use ROWID's and dont try to convert them to anything else.[/quote]

If I am reading OPs question correctly, he is trying to extract data via the SQL92 to make use of the sql engines query optimizer capabilities and then import this into ABL where the rest of the rows may be needed.

Since the SQL92 engine is exposing rowid as a 19 character leading zero bigint in base 10, it needs to be converted to be usable in ABL, you cannot just use it.

@Stefan Yes exactly.  The SQL92 engine can optimize & execute my dynamic queries on multiple joined tables via the CLR bridge.  The results can be returned with multiple ROWID's in the columns (representing any interesting FK relationships).  Then you just EXPORT/IMPORT the results back into my ABL session (the topic of a different thread).  This technique will gather lots of raw data in a small fraction of the time that "client/server" ABL would do it (primarily because "client/server" performance is super-chatty and the performance of many FOR-EACH "queries" will degrade in proportion to the number of records that are being retrieved)

The only thing left to do is to fetch any remaining/discretionary column data which isn't very SQL92-friendly (or isn't EXPORT-IMPORT-friendly, see PS***).  That is where I need the "special" ROWID format.  The SQL92 engine doesn't provide the "special" format for ROWID but I could use your query (or do a little bit of query-post-processing within the CLR bridge assembly)

Once I have a distinct list of ROWID's, then "client/server" ABL can once again play a role in the data-gathering operation.  It can retrieve a reasonable list of records based on their ROWID's.  This is fairly quick so long as it is happening on a single table at a time, and can be done in batches.  The technique here is to use a series of dynamic queries with batches of up to 100 predicates in the form ("OR ROWID(customer) = TO-ROWID(" + Ch-RowId + ")").  Notice that these dynamic queries require the "special" string representation of ROWID.  Each query will be handled as a single round-trip to the server, but it will fetch large amounts of data and isn't super chatty.

Yes, it is not a very simple process, but the performance improvement can be compelling for large queries.  Also, much of this can be done with generic, re-usable code that runs behind an abstracted interface (ie OO classes that hides the CLR bridge interop).  The end result is that all of our local, client session TT's can be filled with OpenEdge data much quicker than before.

Beyond the performance improvement, there are additional benefits related to the ability to run dynamically-generated SQL queries that have customized predicates.

Thanks, David

*** PS.  Examples are the "extent" columns and other columns like CLOBs, BLOBs, multi-line CHARACTER, etc.

apologies.

i did not understand that ABL and SQL were being used together on the same data.

the SQL syntax needs some improvement to better support rowid and also to include all the same inbuilt functions that are in the 4GL.

you might want to make a stored procedure to do the to/from hex representation for SQL.

note that rowid to integer conversion might not work right for rows in partitioned tables. it (integer conversion) wont work for 4GL that uses the oracle dataserver either as oracle rowids are longer than 8 bytes.

I opened a tech support case so that Progress is at least aware of the case-sensitivity that is enforced by the TO-ROWID function.  This is very unexpected behavior and is not logical for anyone that has worked with hex numbers in the past.  

Most people would assume that, if case sensitivity was "a thing", then it would be the uppercase variety that is the "right" version of a hex.  However where ABL ROWID's are concerned, Progress seems to have determined that lowercase is better, for whatever reason.  This makes things very confusing when copying the "=DEC2HEX(A1)" output from Excel into ABL.

> This makes things very confusing when copying the "=DEC2HEX(A1)" output from Excel into ABL.

David, I'm not sure from context if this is something you do regularly or if it was just an isolated test.  Just want you to be aware that Excel doesn't have the precision needed to deal with 64-bit values, and that there is a 40-bit limit on the dec2hex and hex2dec functions.

Thanks Rob.  No I don't do it regularly.  There are certain error messages that the system generates to point you to a record based on its rowid.  For example, if you are querying SQL and it encounters a field that has surpassed the so-called "SQL WIDTH" then it will identify that record based on its rowid in DECIMAL format.

An update from Progress tech support.  They accepted this as a defect:

"Defect OCTA-18273 has been created and attached to this case. I have also created a KB article, 000100179 for this issue which will get updated as the status of the defect changes."

Here is the KB article that points out the fact that "TO-ROWID" is overly sensitive to upper/lower character case:
https://knowledgebase.progress.com/articles/Article/unexpected-and-illogical-case-sensitivity-in-to-rowid