|
From: Glenn <gw...@co...> - 2003-07-18 14:36:23
|
The Firebird/Interbase documents say their BLOB has 3 sub-types, and depending on the sub-type code it gets stored as a BLOB, LONGVARBINARY, or LOGVARCHAR. (Just happened to read this 5 minutes ago. Good coincidence.) Maybe that's what's happening in Mimer? BTW - I haven't yet seen how to specify the sub-type :-) Colin - One of the folks in this discussion said that Oracle does have a "real" BLOB type. Did you check to see if there was a type with code 2004 but under a different name? Glenn Colin Bell wrote: > Bloody Oracle. > > I've just done some checking with some other DBMs's > > DB2 > > TYPE_NAME value DATA_TYPE value java.sql.Types.? > --------------- --------------- ------------------------ > TIME 92 java.sql.Types.TIME > DATE 91 java.sql.Types.DATE > TIMESTAMP 93 java.sql.Types.TIMESTAMP > BLOB 2004 java.sql.Types.BLOB > CLOB 2005 java.sql.Types.CLOB > > > Microsoft SQL Server > > TYPE_NAME value DATA_TYPE value java.sql.Types.? > --------------- --------------- ------------------------ > DATETIME 93 java.sql.Types.TIMESTAMP > IMAGE -4 java.sql.Types.LONGVARBINARY > TEXT -1 java.sql.Types.LONGVARCHAR > MONEY 3 java.sql.Types.DECIMAL > BINARY -2 java.sql.Types.BINARY > > > Mimer > > TYPE_NAME value DATA_TYPE value java.sql.Types.? > --------------- --------------- ------------------------ > TIMESTAMP 93 java.sql.Types.TIMESTAMP > DATE 91 java.sql.Types.DATE > BINARY LARGE OBJECT -4 java.sql.Types.LONGVARBINARY > BINARY LARGE OBJECT 2004 java.sql.Types.BLOB > > > The binary large object is interesting. The column definitions for the > table reported the same column name twice, once as -4 and once as 2004. > When I tried to display the table in SQSC I got a java.sql.SQLException: > Numeric value out of range > > The problem mightn't be too bad. Oracles problematical and obviously we > need to check a few more databases but I would hope that most DBMS's get > this information correct. > > -- > Colin Bell > http://squirrel-sql.sf.net > > ----- Original Message ----- > From: gwg <gw...@co...> > Date: Friday, July 18, 2003 9:15 am > Subject: Re: [Squirrel-sql-develop] Another question about 1.2 beta > > >>Maury, >>Thanks for looking them up... >> >>Maury Hammel wrote: >> >> >>>Glenn: >>> >>> >>>>Well, that depends.... >>>>The data type in the DBMS gets mapped to a SQL type code >>>> >>somewhere >> >>>>(either in the DBMS or in the Java driver - I don't know). >>>> >>There is a >> >>>>list of standard "known" codes in java.sql.Types.java, but >>>> >>every DBMS >> >>>>I've seen so far implements lots of other data types that do >>>> >>not map >> >>>>to codes in that file. The only codes that we can reliably >>>> >>build >> >>>>editors for are those in the Types.java file (since different >>>> >>DBMSs >> >>>>might use the same non-standard code for different purposes). >>>>Therefore, to know if we will be able to create an editor for >>>> >>the >> >>>>NUMBER type, we will need to know the SQL code used by Oracle >>>> >>for that >> >>>>type. This can be found in the COLUMNS tab field named >>>> >>"DATA_TYPE". >> >>>>If you let me know what that number is, I can tell you if it is >>>>possible to make an editor for it, but we still won't know >>>> >>whether >> >>>>we'll get around to it or not. >>>> >>> >>>Ah, okay. I did a quick check of some databases and got the >>> >>types that >> >>>don't appear to be editable: >>> >>>TYPE_NAME value DATA_TYPE value java.sql.Types.? >>>--------------- --------------- ------------------------ >>> >>- >> >>>NUMBER 3 java.sql.Types.DECIMAL >>>DATE 93 java.sql.Types.TIMESTAMP >>>TIMESTAMP 1111 java.sql.Types.OTHER >>>BLOB 1111 java.sql.Types.OTHER >>>CLOB 1111 java.sql.Types.OTHER >>> >>>Ugh, I don't like the look of the TIMESTAMP, BLOB and CLOB >>> >>values. Can >> >>>you not base the decision about editing on the "TYPE_NAME" >>> >>column? Or >> >>>will there be the API to allow a database-specific plug-in to >>> >>handle the >> >>>'OTHER's? >>> >> >>I don't like their looks either :-). >> >>The problem is how to handle things that do not have a "standard" >>type >>associated with them. What java object are they transformed into >>internally? What rules do we allow/disallow on editing of the >>string >>representation of that type? >> >>The 3 OTHERs listed here are prime examples of the problems. Each >>DBMS >>seems to have its own representation of TIMESTAMP, and there is no >>guarantee that they would look or work the same from one DBMS to >>another. Does your DMBS include milliseconds? Yes? Well too bad >>- we >>couldn't include those because someone else's DBMS does not allow >>them. >> Your DBMS wants TIMESTAMPs as "hh:mm:ss"? Well your neighbor's >>stores >>them in "seconds since beginning of the epoch". You get the idea. >> >>BLOBs and CLOBs are even worse. You may have noticed in >>java.sql.Types >>that BLOB and CLOB have special codes of their own (2004 & 2005 >>respectivly). These have special meaning since they are handled >>by Blob >>and Clob objects within Java. When you get the data from the >>resultset, >>you read it into a Blob/Clob object which does _not_ include the >>actual >>data. That column data is read from the DB by making a call on >>the >>Blob/Clob object. Since Oracle maps type-name "BLOB" to >>java.sql.Type.OTHER, it would not use that 2-stage mechanism, and >>thus >>would not operate as a "BLOB" should, even though it has that >>name. >>(Actually, this surprises the heck out of me since I had the >>impression >>from some info from Colin that Oracle had real BLOB/CLOB types, >>not >>something fudged. By the way, every DBMS I've been able to get >>running >>on my machine uses some funky non-standard BLOB/CLOB mechanism >>while >>calling the types "BLOB" and "CLOB". Postgess actually stores >>them as >>LONGVARBINARY objects!) >> >>So you see my problem is how to handle things consistantly from >>DBMS to >>DBMS when the DMBS manufacturers haven't agreed on how to handle >>things >>consistantly. I really think that relying on the name would get >>us in >>trouble (witness the "BLOB/CLOB" case). I haven't thought about >>the >>DBMS-specific plug-in approach, but that might work. Any other >>good ideas? >> >>Glenn >> >> >> >>> >>>Maury... >>> >> >> >> >>------------------------------------------------------- >>This SF.net email is sponsored by: VM Ware >>With VMware you can run multiple operating systems on a single >>machine.WITHOUT REBOOTING! Mix Linux / Windows / Novell virtual >>machines at the >>same time. Free trial click here: http://www.vmware.com/wl/offer/345/0 >>_______________________________________________ >>Squirrel-sql-develop mailing list >>Squ...@li... >>https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop >> >> > > > > > ------------------------------------------------------- > This SF.net email is sponsored by: VM Ware > With VMware you can run multiple operating systems on a single machine. > WITHOUT REBOOTING! Mix Linux / Windows / Novell virtual machines at the > same time. Free trial click here: http://www.vmware.com/wl/offer/345/0 > _______________________________________________ > Squirrel-sql-develop mailing list > Squ...@li... > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop > > |
