Still can't update result sets using UpdateRow

[Peter]

I still find that when I try to update result sets using UpdateRow,
nothing happens. I've now switched to using a JDBC driver called
JSQLConnect by JNetDirect and it works perfectly. I very much want to
go back to using the Microsoft driver if I can before the evaluation
licence I have for this other driver runs out, but this limitation is
preventing me from doing so.

If anybody from MS has any idea what the problem is, please let me
know asap

Regards,
Peter

[Peter]

Ok - a bit more information here - UpdateRow does actually work, it
just takes over 20 minutes to update the first row in the result set.
After that, it seemed to be a lot faster for the remaining rows. But
what the hell is the driver doing for 20 minutes at the start?!

Some sort of explaination/reassurance that this will be fixed would be
nice :)

Thanks again
Peter

[Sridhar Paladugu[CompCon tech]]

peter,

I tried to reproduce the issue but i have no problem in updating a
resultset with 300 rows and 5 columns. Tthe code just worked fine.

If you can set up profile for the SQL Trace on the SQLServer we can find
more information about this issue.

If want information on how to set up profile for user query please reply
me.This helps us to find what happening on the server.

I appriciate if you can send me more information regarding the database
config and the JDBC code.

Thanks

Sridhar Paladugu
Microsoft Developer Support
JDBC Webdata

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>.

[Zoran Majcenic]

I get this:
java.sql.SQLException: [Microsoft][SQLServer JDBC Driver]Row update failed.

at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)

at
com.microsoft.jdbc.base.BaseImplUpdatableResultSet.executeStatement(Unknown
Source)

at com.microsoft.jdbc.base.BaseImplUpdatableResultSet.updateRow(Unknown
Source)

at com.microsoft.jdbc.base.BaseResultSet.updateRow(Unknown Source)

at com.envox.db.Row.UpdateDB(Row.java:111)

at dbtest.Main2.main(Main2.java:96)

This situation occurs when I try to update through a ResultSet that contins
no uniquely identifiable data:

First I create a table:
CREATE TABLE table2 (id INTEGER NOT NULL PRIMARY KEY, ime VARCHAR(30),
prezime VARCHAR(50), datum_rodjenja DATETIME, komentar TEXT, slika IMAGE)

than fill it with initial data through a PreparedStatement
INSERT INTO table2 VALUES (?,?,?,?,NULL,NULL)

now I try to update last two columns through a RecordSet using:
"SELECT komentar,slika FROM table2 WHERE id=" + i // i is the row being
inserted

after this I update columns with RecordSet.updateCharacterStream and
RecordSet.updateBinaryStream

calling ResultSet.updateRow() cause an Exception

This code works if I modify SELECT statement to contain more data: i.e.
"SELECT id,komentar,slika FROM table2 WHERE id=" + i, or
"SELECT ime,slika FROM table2 WHERE id=" + i // where ime differs in each
row

[Peter]

Hi Sridhar

Ok, well, I'm actually using a table that has 82 columns and 22000
rows of data, so maybe that's why it is ok with your test? If you let
me know how I can set up the profiler or if you need any other
information, just let me know. I've set the result set up using
CONCUR_UPDATABLE and SCROLL_INSENSITIVE. For most of the rows, nothing
needs to be updated, and reading the rows from the table is fine. I
only have a problem when I want to update it - at best it takes 20-30
minutes to update a row, at worst it doesn't update it at all, but no
exceptions ever get thrown.

Thanks again
Peter

[Sridhar Paladugu[CompCon tech]]

Peter,

You can start the profiler by following the steps.

1.In th start menu, go to SQLServer menu, select profiler.
2.In the SQL Profiler select File-> new-> Trace.
3. select server and set login params. ->Next
4. go thru all the tabs on the trace Properties screen.

so when you run any query it will be shown in the profiler. It will gives
an idea of current running threads on SQL Server.

I am trying to reproduce the issue with a large data. I would appriciate if
you can send the code and table description.

Thank you.

[Peter]

Hi Sridhar

One other thing I forgot to mention, I actually have 2 connections
with a resultset open on each when I try to do the update. Both are
the same size (82 columns, 22000 rows), and both are scroll
insensitive and updateable. I'm basically comparing the values between
the 2 resultsets and altering a status 'flag' column on one resultset
if the row differs from the other resultset (hope that made sense!).

I've got to go now, but will try to send you some code and an export
tomorrow

Regards,
Peter

[Brian Kresge]

Here's my 2¢ on this,
I had the same problem updating ResultSet's. . . Did you try adding
the "SelectMethod=cursor" property to you connection URL? According
to ppl @ Microsoft, this problem went away in Beta 2. I have not been
able to utilize updateable ResultSet's w/o this property. Maybe it's
a config issue, but it solved it 4 me. BTW: It looks like this method
has its drawbacks in terms of resource usage on the database side, but
it appears to solve (err: *hack*) around the problem...

Here's a discussion on Google Groups I found on this topic:
http://groups.google.com/groups?hl=en&threadm=uOAxrx5nBHA.2196%40tkmsftngp07&rnum=1&prev=/groups%3Fhl%3Den%26selm%3DuOAxrx5nBHA.2196%2540tkmsftngp07

Microsoft ppl, any ideas on a better workaround???

HTH,

Brian Kresge
SW Engineer
http://www.netsetgo.com

pete...@hotmail.com (Peter) wrote in message news:<3ffb51d.02021...@posting.google.com>...

[Brian Kresge]

Peter-
I did not see the *real* problem you were having; as I glossed over
one of your discussion threads... I have not used the
"SelectMethod=cursor" work-around to solve the problem you are
describing (in a large ResultSet). Just thought I would clarify.

Brian Kresge
SW Engineer
http://www.netsetgo.com

bkr...@rochester.rr.com (Brian Kresge) wrote in message news:<303b1cdb.02021...@posting.google.com>...

[Sridhar Paladugu[CompCon tech]]

Hi all,

I have tested all the issues and i have some inputs in using the updatable
resultset.

Firstly Update works (or doesn't work as described below) in both cursor
and direct methods. I.e. cursor mode has no effect on the situation.

The update fails when a column has some kind of constraint on it. This is a
bug in the current release.

Another issue is with Text and Image data types of SQLServer.

If you are updating a Text data with
Resultset.updateAsciiStream(.........),
i advise you to use
Resultset.updateCharacterStream(.................)
Because the driver is sending NULL data to the database if you use
updateAsciiStream(........).

Also in the select statement of the Resultset if you select only the Text
data and tries to update that column with updateCharacterStream(.......) it
fails. So if you select one more column with that column it works fine.

the driver works fine with set/updateBinaryStream(.................)
methods. But it also has the same issue as with Text data when sigle column
selected.

We filed these issues as bugs. I appriciate your feedback regarding this
issue.

Thank you all,

[Peter]

Hi Sridhar

Sorry I haven't sent you any code yet - I hope to be able to either
today or tomorrow. I'm still not sure if the problems you've found
cover my issue or not? I'm using ResultSet.UpdateString, followed by
ResultSet.UpdateRow(). It doesn't 'fail' as such, just hangs and
doesn't return an exception or anything. I don't have any constraints
on the column either. I'll try to recreate the problem with a more
simple program and a table with 'non confidential' data in it, but
this might take a while and I'm pretty busy :)

[Sridhar Paladugu[CompCon tech]]

Hi Peter,
Ok. Let me know once are able to do that. I would be more than happy to
help you.

[Peter]

Hi Sridhar

I've managed to reproduce the problem and tried to email you a zip
file with some code and an exported table, but it got bounced back
when I sent it to 'sr...@online.microsoft.com'

Is this the right email address or should I try a different one?

Regards
Peter

[Sridhar Paladugu[CompCon tech]]

Peter,

You can send the code to "a-sr...@microsoft.com."

Thanks

[Sridhar Paladugu[CompCon tech]]

Peter,

I tried to reproduce the error with your sample code and sample data.
But i had no problem in running that code. It took me 30mns on my machine
to finish the program. I observed that this code only have single resultset
doing updatin every row in the table. If you send me the code which is
doing multiple resultsets updates i will test it again.
Also is the data format same as original table? I mean column types.
I will try to reproduce the error if you can send me the code which is much
nearer to original code.

Thank you,

[Peter]

Hi Sridhar

This is wierd - when I try running the example code, it just sits
there at the first UpdateRow() call and does nothing - regardless of
whether I have 2 connections or just one. The table I sent you is
exactly the same as what I'm using normally - I just altered some of
the data so it was ok to send to you.

The only thing I can think is different is that the ID column of the
table is a primary key - this probably wasn't reflected in the export
I sent you.

This sounds like it could be the way my database is setup perhaps? Is
there any other data I can give you to help track down the problem?

Thanks again
Peter

[Michael Dupuis]

This thread hasn't been touched since the end of February, but I'd
thought I'd add some additional info on problems I've also been having
updating a result set.

I am trying to modify a result set that was created as updatable and
scroll insensitive. I attempt to update it in a similar manner to the
directions given in the Sun JDBC docs:

rs.absolute(5); // moves the cursor to the fifth row of rs
rs.updateString("NAME", "AINSWORTH"); // updates the
// NAME column of row 5 to be AINSWORTH
rs.updateRow(); // updates the row in the data source

Now, for me, the update actually happens, but I cannot see the change
in the current result set, I can only see it if I fetch it again. This
is regardless of whether or not I commit (committing makes no
difference to the open result set. Now, if I update the current result
set, I should be able to see the changes without fetching it again.

I also found another problem that should be verified. I had some test
data in a table, about 10 rows with the exact same values in every
column, and if I update a row value as described above, the update
effects EVERY row, instead of just the current row.