[JDBC] setObject() - not implemented -- I need to read questions...

Lowden, James K LowdenJK at bernstein.com
Fri Sep 15 17:14:36 EDT 2000


Since your question regards SQL rather than TDS, I can answer it.  

There isn't any standard way AFAIK to do what you want (and I'm not sure how
standard cursors are, either).  On both Sybase and Microsoft servers,

set rowcount 1
update X set B=6 WHERE A=1 AND B=2 AND C=3
set rowcount 0

will do the trick as long as no triggers are involved.  

Another way, a lot more data schlepping, and a little more standard (?), is
to differentiate the rows somehow, as in:

create table #X ( id int IDENTITY, A int, B int, C int )
insert #X (A, B, C) select * from X WHERE A=1 AND B=2 AND C=3
delete X WHERE A=1 AND B=2 AND C=3
update #X set B=6 WHERE id = (select min(id) from #X)
insert X select A, B, C from #X
drop table #X

You don't have to use identities, of course; any unique value (such as
timestamp) will do the job.


-----Original Message-----
Here is an example: 

In SQL Server, lets say we have a table, X:

_____ _____ _______
1     2     3
4     5     6
1     2     3
9     9     9

Here we have two rows where (A, B, C) = (1, 2, 3). If I'm reading through
the table and get to the third record and want to do rs.setObject(6) on B,
then how will I identify that I want only the third record changed and not
the first one from the standpoint of the freetds driver? The only way I can
see to do this is by using a cursor for navigation. Without a cursor, there
is no way to differentiate between the first and third row.

If I want to update without a cursor:

UPDATE X set B=6 WHERE A=1 AND B=2 AND C=3 --will change records 1 and 3.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.ibiblio.org/pipermail/freetds/attachments/20000915/6c7da96b/attachment.html 

More information about the FreeTDS mailing list