FreeTDS Development Group

[Henry Lafleur <HLafleur AT phoenixforge.com>]

I misread Jon's question. I was thinking about using cursors in a result set
and not in a prepared statement, and I was thinking about updateObject and
not setObject, so sorry about that misnake. Using cursors for prepared
statements would be pretty stupid.

But, since I'm on this tangent (and I already wrote a response), I was
talking about using SQL cursors for the implementation detail in the driver
for having updateable result sets. I just read that Sun is working on JDBC
3.0, so this seems like a moving target, but I digress.

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.

With a cursor (something like this):

DECLARE CURSOR csr AS SELECT * FROM X -- send on connection
FETCH csr -- send on connection
FETCH csr -- send on connection
FETCH csr -- send on connection
UPDATE X SET B=6 WHERE CURRENT OF csr -- will only change record 3

(The update shouldn't go until the user calls the rs.updateRow() method, of
course.)

Of course, I am no expert with the TDS protocol and there may be a way to
write to records without using Transact SQL, but I don't remember seeing
that. If you're limited to transact SQL statements, then how else would you
update records from the driver while handling any table structure?

Again, sorry about the confused answer to Jon's problem. I do think that
stored procedures and generated code would be good alternatives if the
prepared statement won't work.

Thanks,

Henry


> -----Original Message-----
> From: Bob Kline [mailto:bkline AT rksystems.com]
> Sent: Tuesday, September 12, 2000 3:35 PM
> To: TDS Development Group
> Subject: [freetds] Re: [JDBC] setObject() - not implemented
> 
> 
> Henry:
> 
> Thanks for your reply.  However, I'm a bit confused.  First, I wasn't
> the one posting the original problem, so I assume you just 
> got me mixed
> up with Jon, who was.
> 
> Also, I may be exposing some gross misunderstanding of a basic JDBC
> concept, but I'm not sure what cursors have to do with the setObject()
> method.  I had thought that setObject() was simply a thin layer
> providing a simple convenience of mapping arbitrary objects to JDBC
> types.  Help me understand how this fits in with cursor programming.
> 
> Cheers,
> Bob
> 
> On Tue, 12 Sep 2000, Henry Lafleur wrote:
> 
> > The way that freetds_jdbc is designed, it seems that it JDBC 1.0 was
> > in mind. The process seems to be:
> > 
> > 1) Take the user's (the user being a Java program) query 
> and send it to the
> > SQL server.
> > 2) Take the response from the SQL server and read it as the 
> user goes
> > through the result set using next(), if there is a result 
> set. Let the user
> > go to other result sets, if any.
> > 
> > (My description is a little oversimplified here.)
> > 
> > Given this, how would the setObject be implemented in 
> freetds_jdbc? It seems
> > that the only way to do this would be for freetds_jdbc to 
> work differently.
> > One way would be to open a cursor and keep the connection 
> open as the user
> > moves around the result set with next(), prev(), first(), last(),
> > movetoinsertrow(), etc. (I didn't verify the method names 
> here) and to
> > compute update statements using the "where current of <cursor>" SQL
> > condition. The insert statement is straightforward: use 
> Insert <table name>
> > ... In other words, implement JDBC 2.0.
> > 
> > I know that the 1.0 implementation is the current goal, but 
> has anyone
> > discussed this?
> > 
> > For your problem Bob, I use an abstraction layer on top of 
> JDBC to handle
> > table navigation and saving data back to the database. My 
> abstraction layer
> > requires that tables have keys. Without keys how do you 
> know which record in
> > the table to change? (a semi-rhetorical question) Since you 
> can't use
> > cursors, there is no way to know that the record that you 
> are looking at is
> > the only record that will be changed in an update statement 
> unless you have
> > a table-wide unique identifier. 
> > 
> > An MS-SQL timestamp field will also work as a key. This also has the
> > advantage that if the record has changed since you read it, 
> your update will
> > not change it again since timestamps change on update. You 
> would need to
> > check to see if no records were affected by the update and 
> tell the user
> > that someone else changed the record while you were looking at it.
> > 
> > Stored procedures are another solution, which brings us to 
> server-side
> > programming. They are also much faster that other solutions.
> > 
> > Henry
> > 
> > 
> > > -----Original Message-----
> > > From: Strande, Jon [mailto:JohnS AT nwpasta.com]
> > > Sent: Tuesday, September 12, 2000 12:53 PM
> > > To: TDS Development Group
> > > Subject: [freetds] Re: [JDBC] setObject() - not implemented
> > > 
> > > 
> > > Bob,
> > > 
> > > Sorry about that, I meant to write that it 
> > > does not say that it does not work. Quite a
> > > difference... My Readme says: "The prepared
> > > statement class does not have all the data 
> > > types implemented yet". Again, my apologies.
> > > 
> > > So, I am out of luck huh? 
> > > 
> > > Thanks Bob,
> > > 
> > > Jon
> > > 
> > > -----Original Message-----
> > > From: Bob Kline [mailto:bkline AT rksystems.com]
> > > Sent: Tuesday, September 12, 2000 1:58 PM
> > > To: TDS Development Group
> > > Subject: [freetds] Re: [JDBC] setObject() - not implemented
> > > 
> > > 
> > > On Tue, 12 Sep 2000, Strande, Jon wrote:
> > > 
> > > > I have searched for answer for this, so I apologize if this 
> > > is a repeated
> > > > question. 
> > > > 
> > > > NT 4.0 Sp 5
> > > > IIS 4
> > > > Tomcat 
> > > > JDK 1.3
> > > > freetds - JDBC V 1.3 1999/03/09 17:15:49
> > > > 
> > > > In the readme file it says that setObject works
> > > > yet I am getting an error "not implemented" when
> > > > I call:
> > > > java.lang.Integer myInteger = new... etc.
> > > > myPreparedStatement.setObject(i, myInteger);
> > > > 
> > > 
> > > The README file that I have doesn't say what yours does.  The 
> > > one I have
> > > says that getObject() is implemented for all SQLServer 
> datatypes, but
> > > says nothing whatsoever about setObject() (which is indeed not
> > > implemented).  Where did you get your copy of the README file?
> > > 
> > > -- 
> > > Bob Kline
> > > mailto:bkline AT rksystems.com
> > > http://www.rksystems.com
> > > 
> > > 
> > > ---
> > > You are currently subscribed to freetds as: JohnS AT nwpasta.com
> > > To unsubscribe, forward this message to
> > > $subst('Email.Unsub')
> > > 
> > > ---
> > > You are currently subscribed to freetds as: 
> > > HLafleur AT phoenixforge.com
> > > To unsubscribe, forward this message to 
> > > $subst('Email.Unsub')
> > > 
> > 
> > ---
> > You are currently subscribed to freetds as: bkline AT rksystems.com
> > To unsubscribe, forward this message to 
> $subst('Email.Unsub')
> > 
> 
> -- 
> Bob Kline
> mailto:bkline AT rksystems.com
> http://www.rksystems.com
> 
> 
> ---
> You are currently subscribed to freetds as: 
> HLafleur AT phoenixforge.com
> To unsubscribe, forward this message to 
> $subst('Email.Unsub')
>