|
From: Colin B. <col...@te...> - 2003-07-18 05:30:25
|
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 > |
