File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes how to use rowid to update records in oracle database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to use rowid to update records in oracle database" Watch "how to use rowid to update records in oracle database" New topic
Author

how to use rowid to update records in oracle database

sameer kumar
Greenhorn

Joined: Jan 06, 2006
Posts: 17
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
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
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
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
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).


Reid - SCJP2 (April 2002)
sameer kumar
Greenhorn

Joined: Jan 06, 2006
Posts: 17
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how to use rowid to update records in oracle database