| ROWID differences [message #401625] |
Tue, 05 May 2009 18:54  |
wmbutler
Messages: 3
Registered: May 2009
Location: Nashville, TN
|
Junior Member |
|
|
All,
I have a few queries where I like to use the oracle unique identifier, rowid, for identifying a particular record. In the past, I used coldfusion with a native oracle driver to perform the query. It would display the rowid as a long text string.
select rowid from customer where customer_id = 1;
ROWID
------------------
AAADinAAEAAAAYGAAA
In the past, I could then perform a select on that rowid to pull up the associated data.
Well, I migrated the oracle database to XE from 8.1.7 and also switched to railo (a coldfusion open source version). I also ended up using the oracle java thin client instead of the native oracle driver (which I suspect is the culprit).
Now, when I perform the query from within railo (via the OJDBC driver), the returned value of rowid is:
select rowid from customer where customer_id = 1;
oracle.sql.ROWID@31bcc8 instead of AAADinAAEAAAAYGAAA
If I use sqlplus from the unix prompt, I still get the nice familiar rowid from before: AAADinAAEAAAAYGAAA
My question is this: Is there a way to get my old familiar rowid representation. I'm happy to either convert the oracle.sql.ROWID@31bcc8 representation or figure out a different keyword to use in my query.
|
|
|
|
|
|
| Re: ROWID differences [message #401628 is a reply to message #401626] |
Tue, 05 May 2009 19:31  |
wmbutler
Messages: 3
Registered: May 2009
Location: Nashville, TN
|
Junior Member |
|
|
Yes, the part to the right of the '@' is unique, but when I try to reference via a select statement either "oracle.sql.ROWID@127d15e" or just "127d15e", I can't get it to locate a row.
I sort of doubt it's railo causing the problem, but rather the jdbc thin client. When I search for "oracle.sql.ROWID" in google, I get all sorts of interesting references for how this 'value' can be manipulated in java but nobody really talks about how to get the original rowid.
If nobody here knows the answer, I'll try to install the oracle native driver on Linux and reference it instead, but I'd rather not go that route if I can possibly avoid it.
|
|
|
|
|
|
| Re: ROWID differences [message #401658 is a reply to message #401628] |
Wed, 06 May 2009 00:12 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| wmbutler wrote on Wed, 06 May 2009 02:31 | Yes, the part to the right of the '@' is unique, but when I try to reference via a select statement either "oracle.sql.ROWID@127d15e" or just "127d15e", I can't get it to locate a row.
|
I don't know railo, but in java this is the way an object is denoted. I think railo, or whatever thing is displaying this, has a rowid datatype that has no toString() which would display it contents neatly
|
|
|
|
| Re: ROWID differences [message #401686 is a reply to message #401658] |
Wed, 06 May 2009 03:07 |
bonker
Messages: 402
Registered: July 2005
|
Senior Member |
|
|
Like Frank even I have no knowlede on Railo. Can you check if you convert rowid to char in Railo to see if it then displays the rowid as varchar field?
select rowidtochar(rowid) customer where customer_id = 1
|
|
|
|
| Re: ROWID differences [message #401693 is a reply to message #401686] |
Wed, 06 May 2009 03:24 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Taking a step back, I think the problem lies in this quote:| Quote: | I have a few queries where I like to use the oracle unique identifier, rowid, for identifying a particular record.
|
WHY?
You've got a unique id on your table - customer_id
Just use that?
Why double the amount of SQL that you perform?
|
|
|
|
|
|
| Re: ROWID differences [message #401816 is a reply to message #401693] |
Wed, 06 May 2009 09:02 |
wmbutler
Messages: 3
Registered: May 2009
Location: Nashville, TN
|
Junior Member |
|
|
| JRowbottom wrote on Wed, 06 May 2009 03:24 | Taking a step back, I think the problem lies in this quote:| Quote: | I have a few queries where I like to use the oracle unique identifier, rowid, for identifying a particular record.
|
WHY?
You've got a unique id on your table - customer_id
Just use that?
Why double the amount of SQL that you perform?
|
Because I don't have a unique id on the table for the specific row. Thought I'd spare you guys the long winded explanation, but if you want it, I'll surely give it to you. Suffice it to say that customer_id is not a unique key, as a matter of fact, customer_id is not even the real world column name. I was just using it as an example to explain the problem.
Thanks to others for your suggestions. I actually took the time to switch to the native oracle driver (oci) today. Railo still grabs the rowid value as the oracle.sql.rowid@98448, so I think I need to contact the railo developers at this point.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
