RE: [DBMS] MS SQL Server
Max and others, this was a useful thread for me, and i hope others will find it useful also :-> I think you are right in your search for database independence, but as someone mentioned, CLOBs and BLOBs probably should not be autoconverted by OJB (as they can become very large, and then probably are best suited for stream i/o ?) It does not strike me as the most efficient interface if they are transparantely read and written for every retrieve and store of the object. On the other hand, keeping them as stream-interface would not work well in EJB environments because I do not know of a good way to serialise such a stream and its associated database connection. In our EJB webapplication by the way we have our documents stored in files on a network file system, referenced as UNC filesname strings in OJB, because we could not really think of a good solution to manage large text and binary files. Not ideal from a transctional point of view, but quite useful in other ways. Thanks for the information, Theo > -Oorspronkelijk bericht- > Van: Geigl Maximilian, R235 [mailto:[EMAIL PROTECTED] > Verzonden: vrijdag 28 maart 2003 13:33 > Aan: OJB Users List > Onderwerp: AW: [DBMS] MS SQL Server > > > Hello Theo, > > I try to explain the reason, why (I thought) we need CLOB: > a) we need to support different DBMS (at least oracle, ms sql server, db2) so we > have to find java-side datatypes that > have similar domains in the various dbms' > b) we have a protocol object which must be able to store long character values > > our aproach was to use String in java, habe LONGVARCHAR in the ojb repository and > use the dbms datatypes acording to the > jdbc documentation of the respective driver, i.e. > DBMS type domain > -- > MSS TEXT 2 GB > ORA LONG 2 GB > DB2 LONG VARCHAR 32 KB > > The problem is the small domain of LONG VARCHAR. > > So the idea was to swutch to CLOB, but apparently I have no jdbc driver for MSS > which can process CLOB fields. > That was the background of my question. > > Acording to Mario Toffias reply I tried the following successfully: > as ojb (or some instance between the dbms and our java application) coverts (oracle) > CLOB field into String fields, I > left LONGVARCHAR in the repository, use String in the application and use CLOB in > the DB2 database. This would work for us. > > Ok, I think this is undocumented (if not unintended) behviour - and in this respect > I agree with Mario - as acording to > the docs the (java) CLOBs should map to (jdbc) CLOBs in the reposotory and to CLOBs > in DB2. > > Well, that's how I understand it and I can live with it for now. > > I want to thank everyone for contributing and helping - I think this is really a > great and interesting/useful mailing list. > > Theo, you find some thoughts in the text of your mail below. > > Regards > Max > > > > > -----Ursprüngliche Nachricht- > > Von: Theo Niemeijer [mailto:[EMAIL PROTECTED] > > Gesendet: Donnerstag, 27. März 2003 15:17 > > An: OJB Users List > > Betreff: RE: [DBMS] MS SQL Server > > > > > > > > Max, > > > > Have not tried it, sorry. My problem with CLOB fields (and > > BLOB fields) is that they are not really "Object Oriented" in > > JDBC: AFAIK > > you would have to use _streams_ for input and output wouldn't you ? > > maybe this is, why the mapping between String and Clob works > > > (I have used BLOB's in Oracle in a previous Bea Weblogic > > based JSP/servlet/JDBC website project, for storing uploaded > > images and > > retrieving them lateron to write them to the webserver > > filesystem. I remember I had to open the uploaded file as a > > stream, give this > > stream to JDBC, and on retrieval I got a stream handle back). > > > > I would have no idea how to use this all the way through OJB, > > so please enlighten me on this: > > We use these "TEXT" fields in the database,and these are > > mapped to JDBC type "LONGVARCHAR", to > > get the same effect as a CLOB (Character Large OBject isn't > > it?) > > right > > > , namely to store arbitary long strings in the database. > > with MSS, ORA (and MySQL I think) this works fine, my problem is actually the > limited range of the DB2 type LONG VARCHAR > > > (By the > > way, we use OJB 0.9.5 with JBoss in production, and are > > moving to OJB 1.0rc? when we feel confident that nothing got > > broken in our > > application. So maybe support for CLOB's is different from > > 0.9.5, and I do not really have initimat
RE: [DBMS] MS SQL Server
Hi,
I'm using a Oracle 9i and both BLOB and CLOB fields. OBJ automatically convert those
into byte[] respectvely String fields. My concern is that it really does so since it
would be much nicer if it maps them to Blob and Clob instances and does not read from
the stream before actually accessing such.
Regards,
Mario
-Original Message-
From: Theo Niemeijer [mailto:[EMAIL PROTECTED]
Sent: Thu 3/27/2003 3:17 PM
To: OJB Users List
Cc:
Subject: RE: [DBMS] MS SQL Server
Max,
Have not tried it, sorry. My problem with CLOB fields (and BLOB fields) is
that they are not really "Object Oriented" in JDBC: AFAIK
you would have to use _streams_ for input and output wouldn't you ?
(I have used BLOB's in Oracle in a previous Bea Weblogic based
JSP/servlet/JDBC website project, for storing uploaded images and
retrieving them lateron to write them to the webserver filesystem. I remember
I had to open the uploaded file as a stream, give this
stream to JDBC, and on retrieval I got a stream handle back).
I would have no idea how to use this all the way through OJB, so please
enlighten me on this:
We use these "TEXT" fields in the database,and these are mapped to JDBC type
"LONGVARCHAR", to
get the same effect as a CLOB (Character Large OBject isn't it?), namely to
store arbitary long strings in the database. (By the
way, we use OJB 0.9.5 with JBoss in production, and are moving to OJB 1.0rc?
when we feel confident that nothing got broken in our
application. So maybe support for CLOB's is different from 0.9.5, and I do not
really have initimate knowledge of all the low-level
parts of OJB anyway. Would OJB automatically convert a string datatype to a
CLOB stream or something ?)
Why would you want to use CLOB's ?
By the way: This is the class descriptor for the TextBlock object:
> -Oorspronkelijk bericht-
> Van: Geigl Maximilian, R235 [mailto:[EMAIL PROTECTED]
> Verzonden: donderdag 27 maart 2003 14:51
> Aan: OJB Users List
> Onderwerp: AW: [DBMS] MS SQL Server
>
>
> Hello Theo,
>
> thanks a lot for you exhaustive explanations. Just one question left (just
to be sure): I cannot handle
> java.sql.Types.CLOB fields with the driver, I'm I right?
>
> Max
>
> > -Ursprüngliche Nachricht-
> > Von: Theo Niemeijer [mailto:[EMAIL PROTECTED]
> > Gesendet: Donnerstag, 27. März 2003 14:29
> > An: OJB Users List
> > Betreff: RE: [DBMS] MS SQL Server
> >
> >
> >
> > 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 converte
RE: [DBMS] MS SQL Server
Max, Have not tried it, sorry. My problem with CLOB fields (and BLOB fields) is that they are not really "Object Oriented" in JDBC: AFAIK you would have to use _streams_ for input and output wouldn't you ? (I have used BLOB's in Oracle in a previous Bea Weblogic based JSP/servlet/JDBC website project, for storing uploaded images and retrieving them lateron to write them to the webserver filesystem. I remember I had to open the uploaded file as a stream, give this stream to JDBC, and on retrieval I got a stream handle back). I would have no idea how to use this all the way through OJB, so please enlighten me on this: We use these "TEXT" fields in the database,and these are mapped to JDBC type "LONGVARCHAR", to get the same effect as a CLOB (Character Large OBject isn't it?), namely to store arbitary long strings in the database. (By the way, we use OJB 0.9.5 with JBoss in production, and are moving to OJB 1.0rc? when we feel confident that nothing got broken in our application. So maybe support for CLOB's is different from 0.9.5, and I do not really have initimate knowledge of all the low-level parts of OJB anyway. Would OJB automatically convert a string datatype to a CLOB stream or something ?) Why would you want to use CLOB's ? By the way: This is the class descriptor for the TextBlock object: > -Oorspronkelijk bericht- > Van: Geigl Maximilian, R235 [mailto:[EMAIL PROTECTED] > Verzonden: donderdag 27 maart 2003 14:51 > Aan: OJB Users List > Onderwerp: AW: [DBMS] MS SQL Server > > > Hello Theo, > > thanks a lot for you exhaustive explanations. Just one question left (just to be > sure): I cannot handle > java.sql.Types.CLOB fields with the driver, I'm I right? > > Max > > > -Ursprüngliche Nachricht- > > Von: Theo Niemeijer [mailto:[EMAIL PROTECTED] > > Gesendet: Donnerstag, 27. März 2003 14:29 > > An: OJB Users List > > Betreff: RE: [DBMS] MS SQL Server > > > > > > > > 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 Ma
RE: [DBMS] MS SQL Server
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:[EMAIL PROTECTED] > 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 > > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [DBMS] MS SQL Server
Don't know about CLOB. Maybe ask the developer or a mailing list. Sylvain -Message d'origine- De: Geigl Maximilian, R235 [mailto:[EMAIL PROTECTED] Date: mercredi, 26. mars 2003 12:55 À: OJB Users List Objet: AW: [DBMS] MS SQL Server Hi, the MS driver does not support CLOB fields, do you know if jdts does? (or should i look in the sources) Max > -Ursprüngliche Nachricht- > Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Gesendet: Mittwoch, 26. März 2003 12:53 > An: [EMAIL PROTECTED] > Betreff: RE: [DBMS] MS SQL Server > > > Hi again, > > I don't have it. > I'm using the same table as the Microsoft driver. It works > fine for me. > > Regards > Sylvain > > -Message d'origine- > De: Geigl Maximilian, R235 [mailto:[EMAIL PROTECTED] > Date: mercredi, 26. mars 2003 10:49 > À: OJB Users List > Objet: AW: [DBMS] MS SQL Server > > > Hi Sylvain, > > thanks fpr the info, doesn't look bad and i'll give it a try. > Can you point me to a table with the mapping between jdbc and > dbms data types, please. I couldn't find one. > > Regards > Max > > > -Ursprüngliche Nachricht----- > > Von: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > > Gesendet: Mittwoch, 26. März 2003 10:35 > > An: [EMAIL PROTECTED] > > Betreff: RE: [DBMS] MS SQL Server > > > > > > Hi, > > > > I'm using jtds (http://jtds.sourceforge.net/). > > > > It's totally open source and it works well although there is > > no release (the last version is 0.5.1 I think). > > > > Regards > > Sylvain > > > > -Message d'origine- > > De: Geigl Maximilian, R235 [mailto:[EMAIL PROTECTED] > > Date: mercredi, 26. mars 2003 10:23 > > À: OJB Users List > > Objet: [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 > > > > > > > - > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [DBMS] MS SQL Server
Hi again, I don't have it. I'm using the same table as the Microsoft driver. It works fine for me. Regards Sylvain -Message d'origine- De: Geigl Maximilian, R235 [mailto:[EMAIL PROTECTED] Date: mercredi, 26. mars 2003 10:49 À: OJB Users List Objet: AW: [DBMS] MS SQL Server Hi Sylvain, thanks fpr the info, doesn't look bad and i'll give it a try. Can you point me to a table with the mapping between jdbc and dbms data types, please. I couldn't find one. Regards Max > -Ursprüngliche Nachricht- > Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Gesendet: Mittwoch, 26. März 2003 10:35 > An: [EMAIL PROTECTED] > Betreff: RE: [DBMS] MS SQL Server > > > Hi, > > I'm using jtds (http://jtds.sourceforge.net/). > > It's totally open source and it works well although there is > no release (the last version is 0.5.1 I think). > > Regards > Sylvain > > -Message d'origine- > De: Geigl Maximilian, R235 [mailto:[EMAIL PROTECTED] > Date: mercredi, 26. mars 2003 10:23 > À: OJB Users List > Objet: [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 > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [DBMS] MS SQL Server
Hi, I'm using jtds (http://jtds.sourceforge.net/). It's totally open source and it works well although there is no release (the last version is 0.5.1 I think). Regards Sylvain -Message d'origine- De: Geigl Maximilian, R235 [mailto:[EMAIL PROTECTED] Date: mercredi, 26. mars 2003 10:23 À: OJB Users List Objet: [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 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
