It's not a secret anymore!*
The moose likes JDBC and the fly likes updatable recordsets in SYBASE Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "updatable recordsets in SYBASE" Watch "updatable recordsets in SYBASE" New topic
Author

updatable recordsets in SYBASE

Aleksey Matiychenko
Ranch Hand

Joined: Apr 03, 2001
Posts: 178
Does anyone know of a JDBC driver that provides functionality for updatable AND scrollable recordsets for Sybase. Niether jconnect to Merant drivers do.
John Bateman
Ranch Hand

Joined: Mar 09, 2000
Posts: 320
Funny, but I am pretty sure that I have used jconnect 5.2 to do scrollable_results with the SybResultSet interface and even with the java.sql.ResultSet interface. I just setup the 'rs' with the CONCUR_UPDATABLE and TYPE_SCROLL_SENSISTIVE/INSENSTIVIE
Maybe my memory is rusty and I am mistaken but I beleive it can be done. I'll do a test and check it out, I just can't look try it until I get home this weekend.
Hope that's okay.

SOURCE CODE should be SURROUNDED by "code" tags.
Aleksey Matiychenko
Ranch Hand

Joined: Apr 03, 2001
Posts: 178
Thanks for reply.
I've done that. The problem is that you can only scroll forward once. What I need is the recordset where you can move both back and force and still update the resultset.
Let me know what version of jconnect you used.
Aleksey Matiychenko
Ranch Hand

Joined: Apr 03, 2001
Posts: 178
My fault.
I did not read your answer carefully. I have not tried SybResultset. I will now.
Thanks again
John Bateman
Ranch Hand

Joined: Mar 09, 2000
Posts: 320
Hi
I used version jConnect 5.2 (I notice out timestamps are pretty close here.. it's like JavaRanch Chat)..
I just found this on JDBC Tutorial on Sun (advanced section)

This code is similar to what you have used earlier, except that it adds two arguments to the method createStatement. The first new argument must be one of the three constants added to the ResultSet interface to indicate the type of a ResultSet object: TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, or TYPE_SCROLL_SENSITIVE. The second new argument must be one of the two ResultSet constants for specifying whether a result set is read-only or updatable: CONCUR_READ_ONLY or CONCUR_UPDATABLE. The point to remember is that if you specify a result set type, you must also specify whether the result set is read-only or updatable. Also, the order is important. You must specify the type first, and because both parameters are of type int, the compiler will not complain if you switch the order.
Specifying the constant TYPE_FORWARD_ONLY creates a nonscrollable result set, that is, one in which the cursor moves only forward. If you do not specify any constants for the type and updatability, you will automatically get the default, which is a ResultSet object that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY (as has always been the case).
[This message has been edited by John Bateman (edited April 04, 2001).]
Aleksey Matiychenko
Ranch Hand

Joined: Apr 03, 2001
Posts: 178
The problem that I have is that every time I use TYPE_SCROLL_INSESITIVE it converts my resultset to be CONCUR_READ_ONLY as well.
Does anyone know how to avoid that?
John Bateman
Ranch Hand

Joined: Mar 09, 2000
Posts: 320
Hmm.. totally mystifying.. talked to a colleague and he uses Sybase jConnect 5.2 and does not have this problem.
Can you post some code here for us to look at?
Thanks.
Aleksey Matiychenko
Ranch Hand

Joined: Apr 03, 2001
Posts: 178
Here is the sample
public static void main(java.lang.String[] args) {
String host = "cartman1.ny.jpmorgan.com";
String uid = "pldb_dbo";
String pwd = "pldb_dbo";
String DRIVER_NAME = "com.sybase.jdbc2.jdbc.SybDriver";
int port = 1025;
Connection conn = null;
try {
SybDriver Driver = (SybDriver) Class.forName(DRIVER_NAME).newInstance();
DriverManager.registerDriver(Driver);
Properties props = new Properties();
props.put("user", uid);
props.put("password", pwd);
String connection_string = "jdbc:sybase:Tds:cartman1:1025";
conn = (Connection) DriverManager.getConnection(connection_string, props);
Statement st =
(Statement) conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
String sql = "select * from cfg_pkt ";
st.executeQuery(sql);
ResultSet rs = st.executeQuery(sql);
int vieworder = 0;
while (rs.next()) {
vieworder = rs.getInt(4);
if (vieworder == 1000) {
rs.updateInt(4, 1001);
rs.updateRow();
}
System.out.println(rs.getInt(4));
}
rs.first();
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}

This throws an exception that says:
java.sql.SQLException: JZ0BT: The updateInt(int, int) method is not supported for ResultSets of type CONCUR_READ_ONLY.
java.lang.Throwable(java.lang.String)
java.lang.Exception(java.lang.String)
java.sql.SQLException(java.lang.String, java.lang.String)
void com.sybase.jdbc2.jdbc.ErrorMessage.raiseError(java.lang.String, java.lang.String, java.lang.String)
void com.sybase.jdbc2.jdbc.SybResultSet.updateInt(int, int)
void alex.db.DBTest.main(java.lang.String [])
I am using jconnect5.2
Aleksey Matiychenko
Ranch Hand

Joined: Apr 03, 2001
Posts: 178
Sybase Jconnect manual actually sais that they do not support scrollable updatable recordsets.
SO does anyone know about another driver
John Bateman
Ranch Hand

Joined: Mar 09, 2000
Posts: 320
Hi
Sorry , I guess I was more focused on getting you a SCROLLABLE resultSet that I didn't realise it was defaulting to CONCUR_READ_ONLY while doing that.
I'll look around for a driver for you.
 
Consider Paul's rocket mass heater.
 
subject: updatable recordsets in SYBASE
 
Similar Threads
Updatable AND Scrollable Recordsets in Sybase
Typical extension to storedProcedures and packages
select all the colums although some empty/null
How to determine Primary Key of New Record
Examples of heavy GUI apps