[DBMS] MS SQL Server

["Geigl Maximilian, R235" <Ge...@akdb.de>]

Hello,

I need to connect to MS SQL Server 2000 and think I must get along with the MS JDBC driver, as it is free for our customers and doesn't need extra licensing of fees.
Does anybody have experiences with Microsoft SQL Server 2000 Driver for JDBC, Service Pack 1 (is it the latest)?
As far as I understand, it cannot handle CLOB fields, it this right?
Does anybody know of an alternative jdbc driver for sql server, which is free of charge and/or redistributable?

Thanks
Max

RE: [DBMS] MS SQL Server

[Theo Niemeijer <th...@juras.org>]

We are using the Microsoft JDBC driver from the MS website.

We do not have problems with it, and use it in production together with OJB for a medium-size website application with content
management services. Using it with success since september 2002.

There are lots of (expensive) commercial JDBC drivers for MS SQL Server 2000, but I really do not know what the difference would be.
Maybe performance or scalability, but we have not experienced problems in those area's.

There is an issue related to OJB:
We make use of TEXT fields to circumvent the small 8000 bytes maximum row size for some columns (with some string fields the
possible total size could not be guaranteed to be that under 8000 bytes) and found that we needed to create separate reference
objects for those. So we have TextBlock objects. This is because MSSQL server can not (will not) do sorting or distinct on TEXT
fields. Because OJB will always use all possible fields of a mapped object extent these TEXT fields gave problems, so we mapped them
to seperate objects and use auto-retrieve, auto-update etc. on them so the object behaves just like it would be if the TEXT fields
were part of the table.

There is another issue, but that has more to do with JDBC standards and the way they can use Unicode. We found that Unicode in Java
strings will be converted by the JDBC driver to 8-bits ANSI codes (Windows CP1252 I think) unless you would use special JDBC
functions. Or something like that, it was not completely clear to me what happened. So the "eur" sign (20AC or something in Unicode)
would correctly be stored, and lots of Western Language special characters etc. (as long as they are in Microsofts CP1252 codepage,
but not Chinese or other Eastern Language characters.  We use VARCHAR, not NVARCHAR, because AFAIK thet would limit the total row
width to an even more crippling 4000 characters (the 8000 bytes database page size). (So if you have 8 NVARCHAR fields they can all
be 500 chars, or one can be 3200 and the others 100 chars :-(. Difficult to explain to your customers complaining about the database
errors they got.)

So all in all we have no problems with the MS SQL JDBC driver, and would be interested to hear what others found about it.

Regards,
	Theo Niemeijer

> -----Oorspronkelijk bericht-----
> Van: Geigl Maximilian, R235 [mailto:Geigl.Maximilian@akdb.de]
> Verzonden: woensdag 26 maart 2003 10:23
> Aan: OJB Users List
> Onderwerp: [DBMS] MS SQL Server
>
>
> Hello,
>
> I need to connect to MS SQL Server 2000 and think I must get along with the MS JDBC driver, as it is free for our
> customers and doesn't need extra licensing of fees.
> Does anybody have experiences with Microsoft SQL Server 2000 Driver for JDBC, Service Pack 1 (is it the latest)?
> As far as I understand, it cannot handle CLOB fields, it this right?
> Does anybody know of an alternative jdbc driver for sql server, which is free of charge and/or redistributable?
>
> Thanks
> Max
>
>