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 initimate knowledge of all > the low-level > parts of OJB anyway. Would OJB automatically convert a string > datatype to a CLOB stream or something ?) seems so, but acording to the type mapping table in the docs I don't think it is really intended (?) > Why would you want to use CLOB's ? > > > By the way: This is the class descriptor for the TextBlock object: > > <class-descriptor > class="nl.casthere.uib.ojb.Textblock" > table="@[EMAIL PROTECTED]" > > > <field-descriptor id="1" > name="id" > column="id" > jdbc-type="INTEGER" > primarykey="true" > autoincrement="true" > /> > > <field-descriptor id="2" > name="ojbConcreteClass" > column="ojb_concrete_class" > jdbc-type="VARCHAR" > /> > > <field-descriptor id="3" > name="text" > column="text" > jdbc-type="LONGVARCHAR" > /> > > </class-descriptor> > > > > > > > -----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 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] > > > > > > > > > > > --------------------------------------------------------------------- > > 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]
