#33 Provide RowSet implementation

[Danny Yates]

Hi Alin,

Is there any chance that you could add a RowSet
implementation any time soon?

In one thread on the user forum, you mentioned that you
hadn't done this because Sun provide a robust
implementation. Unfortunately, I would suggest that
Sun's implementation is anything other than robust.

For starters, Sun are doing something completely weird
with properties bundles, so that if you are in any locale
other than US (e.g. GB), then the RI fails. (The
workaround is to extract the US bundle, rename it as
GB and make it available to your app - but why?!)
Another issue is the one people keep mentioning with
doing toString() on a CLOB field.

These are just 2 of the issues facing me!

So, please, please, please, can I ask for a RowSet
implementation?

Thanks,

Dan.

[Alin Sinpalean]

Logged In: YES
user_id=564978

Dan,

I'm sorry to hear about your problems with the Sun RowSet
implementation. You can, of course, ask for a RowSet
implementation but I don't know who will find the time to do
it and most importantly when. As you can see there are a
number of other RFEs, as well as a number of patches and
improvements that need to be done for more common functionality.

The topic will remain open in the meantime; you can also try
to provide even a simple implementation at first; it will be
good as a base to start from and will probably encourage
anyone with even a remote interest to add functionality to it.

Alin.

[Mike Hutchinson]

Logged In: YES
user_id=641437

Dan,

It is possible to get the Sun RowSet code to work but it
does require a few changes to jTDS to work around bugs in
the implementation. The RowSet in java 1.5 seems to have
fixed the properties problem but there remain a number of
others.

Using the following code as example illustrates some of
these other problems:

public void test() throws Exception {
    Statement stmt = con.createStatement();
    stmt.execute("CREATE TABLE #TEST (id int primary

key, txt text)");
stmt.executeUpdate("INSERT INTO #TEST VALUES(1,
'THIS IS A TEST')");
stmt.executeUpdate("INSERT INTO #TEST VALUES(2, 'AS
IS THIS')");
stmt.executeUpdate("INSERT INTO #TEST VALUES(3, 'AND
THIS')");
JdbcRowSet jrs = new JdbcRowSetImpl(con);
jrs.setType(ResultSet.TYPE_SCROLL_SENSITIVE); //
Ignored!
jrs.setCommand("SELECT * FROM #TEST");
jrs.execute();
assertTrue(jrs.absolute(2)); // Prove scrollable
assertEquals("AS IS THIS", jrs.getString(2));
jrs.updateString(2, "Hello from JDBCRowSet");
jrs.updateRow(); // Prove updateable
jrs.close();
}

Firstly the RowSet code outputs a message to stderr
complaining that jTDS setTypeMap() method returned a not
implemented SQLException. Why SUNs code calls this method
with a null type map is beyond me. The quickest fix here is
to alter jTDS to ignore the setTypeMap.

Secondly the result set is not in fact updateable. This is
because RowSet tries to create a SCROLL_INSENSTIVE /
UPDATEABLE cursor. This option is not supported by SQL
Server and is downgraded to a read only one instead. The SUN
implementation ignores my attempt to override this in the
example above. The only option for this problem is to amend
jTDS Connection.prepareStatement to change insensitive to
sensitive if the caller is also requesting an updateable
cursor.

You will note that I am using text columns that will be
returned as CLOB objects but I am then using getString to
access them and this works. If you use getObject in this
circumstance you will return a CLOB and not a String, which
is intentional. One option if this is unacceptable is to
change jTSD to add a ClobImpl.toString method that returns
the string value of the CLOB.

A final irritation is that calling RowSet.close not only
closes the internal result set and statement but also the
connection object that I passed to the RowSet constructor.
Only answer here is not to call close in which case the
internal statement is never explicitly closed which is bad
practice, alternatively use the setDataSource option and get
the RowSet to obtain its own connection from a pool.

I appreciate that hacking jTDS is not what you had in mind
but as Alin pointed out building a full RowSet
implementation from scratch is not a quick task

Mike.

[Andy Santosa]

Logged In: YES
user_id=773250

I agree with Dan, I also feel that I need a good RowSet
implementation, currently, I am using Sun RowSet
Implementation but still in earlyAccess, I have to
reverse-engineer the classes because there are some bugs
(not just the Clob thing) but bugs in some functions, so I
hope Alin can provide us with the RowSet implementation in
the jTDS

Thanks and Regards
Andy Santosa

[Alin Sinpalean]

Logged In: YES
user_id=564978

Ok, then. If anyone is willing to provide some basic
implementation, something to start from, I will try to
maintain and improve it. Otherwise you might have to wait
too long; remember, I also have a job.

Alin.

[Mike Hutchinson]

jTDS RowSet implementation

[Mike Hutchinson]

Logged In: YES
user_id=641437

Alin,

Despite my comments below from a few months ago, I have also
come to the conclusion that the RI is badly flawed. A major
problem is that the design of
The RI makes it difficult to extend and customise which was
my original idea and apparently Suns intention as well. In
the end I just implemented my own versions partly because I
wanted a working CachedRowSet and WebRowSet and partly for
interest.

I have tidied up this code and added some documentation and
tried to make it integrate properly with the existing jTDS
source tree. One issue is that, although I started off
developing these classes with the jTDS driver in mind, I
have actually been using them with PostgreSQL more. They
will also work with the MS driver. As a result I have
removed any direct dependencies on the jTDS driver itself
and modified the build script to create a standalone rowset
jar. I think this also helps to keep the actual driver
compatible with java 1.3 but you may wish to re-integrate
the code with jTDS rather than end up supporting two projects!

If anyone wants to try this code out, download the latest
CVS code for jTDS (or use the source distribution of 1.1)
and expand the zip file (attached to this RFE) over the
existing jTDS folder. If you are running under java 1.4 you
will also need the Sun RI to be installed in the lib folder.
NB. There isnt a problem with the localisation bug in this
application, as the jTDS implementation does not use any of
the com.sun.rowset classes.

The code works for me and I have included a couple of test
cases but I am sure there are many bugs to find. There is
also major scope for optimisation especially in the
RowSetWriter class. Unfortunately I lack the time and
enthusiasm do much more in this area at present but I hope
it will be a useful starting point.

Mike.