Re: [Squirrel-sql-develop] Another question about 1.2 beta
A Java SQL client for any JDBC compliant database
Brought to you by:
colbell,
gerdwagner
Menu
▾
▴
Re: [Squirrel-sql-develop] Another question about 1.2 beta
|
From: Colin b. <col...@bi...> - 2003-07-19 00:47:43
|
I'll check at work on Monday. Glenn wrote: > 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 >> >> > > > > > ------------------------------------------------------- > 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 > -- Colin Bell http://squirrel-sql.sf.net |