ResultSet not updateable

[Brad Friedman]

If the answer to this problem is blatantly obvious and I'm being very naive
for asking, please inform me as such.

The following code throws an exception telling me that the ResultSet is not
updateable, even though I'm explicitly asking for it to return one that is
updateable. If I dump the ResultSet theory entirely and just execute a
dynamically created INSERT statement, it works fine. However, its a real
pain in the rear to keep doing that. Anyone know what is going on?

------------snip------------------
java.sql.Statement statement =
connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_
UPDATABLE);
ResultSet results = statement.executeQuery("Select * FROM
dbo.GuildWarsNewsDB");

results.moveToInsertRow();
results.updateString("headline",postBean.getHeadline());
---------------snip------------------

any insight would be appreciated,
-Brad

[Volker Berlin]

Hello,

If it a SQL Server exception and not a driver exception then look at:
http://www.inetsoftware.de/English/Produkte/JDBCTreiber/Faq.htm

chapter "java.sql.SQLException: [ServerHost]The cursor is READ ONLY."

Volker Berlin
i-net software
www.inetsoftware.de

Brad Friedman schrieb in Nachricht ...

[Kamil Sykora [MS]]

Hello Brad,

This is a bug in Beta 1. If you specify a user in the table name, the
result set is not updateable. We expect the fix for this bug in Beta 2.

Thanks,
Kamil Sykora
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
--------------------
| Reply-To: "Brad Friedman" <ko...@fullimmersionentertainment.com>
| From: "Brad Friedman" <ko...@fullimmersionentertainment.com>
| Subject: ResultSet not updateable
| Date: Thu, 17 Jan 2002 16:21:42 -0500

[Leonid Kunin]

Hello Kamil,
Has this been fixed in Beta 2?
I am using Windows 2000 (both client and server), SQL
Server 2000 (MSDE, and SQL Server 2000 Driver for JDBC
Beta 2 (downloaded from
http://www.microsoft.com/SQL/downloads/2000/jdbc.asp
page). User's guide on page 29 claims that "The SQL Server
driver supports scroll-insensitive result sets and
updatable result sets.", however looking up the warnings
after executing:
Connection conn = DriverManager.getConnection(
"jdbc:microsoft:sqlserver://localhost:1433;databasen
ame=testdb", "sa", "" );

Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE
);
Shows:
java.sql.SQLWarning: [Microsoft][SQLServer JDBC Driver]
Updateable concurrency not supported, downgraded to
readonly concurrency.
Since I am not sure I understood what you meant by "If you
specify a user in the table name" in your reply, could you
please post an example that would create an updatable
result set?
Thanks in advance,
Leonid Kunin

>.
>

[Leonid Kunin]

[Leonid Kunin]

Well,
I guess I found the problem - the result set is downgraded
to read-only if autocommit is set to OFF.
Is this by design?
All suggestions are appreciated,
Leonid
PS This morning I saw a posting by Steven Zhao on this
matter so at least I am not alone...
Leonid

>.
>

[Kamil Sykora [MS]]

Hello Leonid,

Yes, the bug has been fixed in Beta 2. The bug was that a SELECT from
user.table would result in a read-only result set, whereas SELECT from
table would be updatable. Note that in the first SELECT I'm specifying the
username.

Here's a short sample that should work for you:

Create Table SimpleTable
(id int CONSTRAINT pk_st PRIMARY KEY,
value varchar(20)
)
INSERT INTO SimpleTable VALUES (1, 'Test')

Connection connection =

DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databasename=Cu
stomer","user","password");

String sql = "Select id, value from dbo.SimpleTable";

Statement stmt = connection.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery(sql);
rs.next();
rs.updateString("value", "New Value");
rs.updateRow();

Thanks,
Kamil Sykora
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
--------------------

| From: "Leonid Kunin" <kun...@hotmail.com>
| Subject: RE: ResultSet not updateable
| Date: Tue, 5 Feb 2002 13:02:42 -0800

[Kamil Sykora [MS]]

Hi Leonid,

I have reproduced this behavior with the new information, thanks. More
specifically, the result set is read-only if you are using the direct
cursor method and autocommit off.
If you use the cursor method, you will get an updatable result set with
autocommit on or off.

To use cursor mode, use a connection string something like this:

DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databasename=Cu

stomer;SelectMethod=cursor","user","password");

Let me check whether this is by design or not.

Kamil Sykora
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
--------------------

| From: "Leonid Kunin" <kun...@hotmail.com>
| Subject: ResultSet not updateable
| Date: Wed, 6 Feb 2002 05:16:51 -0800