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

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


Henry, 

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,
though:

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.

--jkl


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

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

A	B	C
_____ _____ _______
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