how to use rowid to update records in oracle database

[sameer kumar]

hello,

i am fetching rowid along with other columns fron an oracle table based on some criteria. these records are populated onto a form which is editable by a user. After modification , user submits the form. Now i have to update the corresponding record based on rowid that was fetched for that record, but i am not able to do so.

i am using prepared statement and the framework is struts.
the queries are:

select ROWID,GENERALSUBTYPE As SUBTYPE,TO_CHAR(ISSUEOPENDATE,'MM/DD/YYYY')As ISSUEDATE, from kbtable where =?

Now i store this rowid and after modification update the record as:

update kbtable set generalsubtype=?,issueopendate=to_date(?,'MM/DD/YYYY'),where rowid=?

but this is not working and "0" records are being updated.

Also a point to note is that i am fetching ROWID as String from the resultset and i am getting a value something like:41414147474541414441414141457A414142

but in oracle the rowid is shown as: AAAGGEAADAAAAEzAAB

how to get around this?

thanks
sameer

[stu derby]

Speaking as an Oracle database administrator... DON'T DO THIS

The only time any application should be using Oracle ROWIDs is when performance is so important that you'd cut off your hand and sell you grandmother into slavery to get a 5% performance improvement. Nothing, absolutely nothing, about a web-based application can ever justify using ROWID in this manner.

Instead, create a proper primary key column and use that.

Oracle has lots of features that enable easy maintenance on the database, even while other applications are using it; many of these features absolutely depend on being able to change a ROWID during a table reorganization. If you code your program in this manner using ROWID, then your program will need to be stopped whenever some of these maintenance operations are required.

[Reid M. Pinchback]

Also, you might want to consider doing yourself a favour and let JDBC deal with handling date conversion issues for you, and just pull the column from the result set as the appropriate java.sql.Date/Time/Timestamp type you need. You can always use a date formatter to do whatever you want within Java anwyays, and the code will be more portable across databases. I wouldn't even be surprised if it was a smidgeon faster (it should take fewer bits to transmit a date over the wire in its native format than to transmit the stringified version).

[sameer kumar]

hello,

thanks for that enlightening info. now i have dropped the idea of using ROWID. but i dont have any primary key in the table. so i decided to use IDENTITY columns instead. i used the following query in oracle 9i:

create table kbtable (RELATEDTO VARCHAR2(100) NOT NULL,
GENERALSUBTYPE VARCHAR2(200) NOT NULL,
ISSUEOPENDATE DATE NOT NULL,
ISSUESTATUS VARCHAR2(7) NOT NULL,
ISSUE VARCHAR2(1000) NOT NULL,
UPDATEGIVENBYVARCHAR2(100),
ADDEDBY VARCHAR2(2) NOT NULL,
ADDEDONDATEDATE NOT NULL,
LASTMODIFIEDDATE ,
LASTMODIFIEDBYVARCHAR2(2),
ISSUE_IDINT IDENTITY PRIMARY KEY )

but i am getting an error:

missing or invalid option

Please help

regards
sameer