001: package net.sourceforge.squirrel_sql.fw.sql;
002:
003: /*
004: * Copyright (C) 2001-2004 Colin Bell
005: * colbell@users.sourceforge.net
006: * Copyright (C) 2001-2004 Johan Compagner
007: * jcompagner@j-com.nl
008: *
009: * This library is free software; you can redistribute it and/or
010: * modify it under the terms of the GNU Lesser General Public
011: * License as published by the Free Software Foundation; either
012: * version 2.1 of the License, or (at your option) any later version.
013: *
014: * This library is distributed in the hope that it will be useful,
015: * but WITHOUT ANY WARRANTY; without even the implied warranty of
016: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
017: * Lesser General Public License for more details.
018: *
019: * You should have received a copy of the GNU Lesser General Public
020: * License along with this library; if not, write to the Free Software
021: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
022: */
023: import java.math.BigDecimal;
024: import java.sql.ResultSet;
025: import java.sql.ResultSetMetaData;
026: import java.sql.SQLException;
027: import java.sql.Types;
028:
029: import net.sourceforge.squirrel_sql.fw.datasetviewer.ColumnDisplayDefinition;
030: import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.CellComponentFactory;
031: import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeBlob;
032: import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeClob;
033: import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeDate;
034: import net.sourceforge.squirrel_sql.fw.util.StringManager;
035: import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory;
036: import net.sourceforge.squirrel_sql.fw.util.log.ILogger;
037: import net.sourceforge.squirrel_sql.fw.util.log.LoggerController;
038:
039: public class ResultSetReader {
040: /** Logger for this class. */
041: private final static ILogger s_log = LoggerController
042: .createLogger(ResultSetReader.class);
043:
044: /** Internationalized strings for this class. */
045: private static final StringManager s_stringMgr = StringManagerFactory
046: .getStringManager(ResultSetReader.class);
047:
048: /** The <TT>ResultSet</TT> being read. */
049: private final ResultSet _rs;
050:
051: /**
052: * The indices into the <TT>ResultSet that we want to read, starting from
053: * 1 (not 0). If this contains {1, 5, 6} then only columns 1, 5, and 6 will
054: * be read. If <TT>null or empty then all columns are read.
055: */
056: private final int[] _columnIndices;
057:
058: /**
059: * The number of columns to read. This may or may not be the same as the
060: * number of columns in the <TT>ResultSet</TT>. @see _columnIndices.
061: */
062: private int _columnCount;
063:
064: /** <TT>true</TT> if an error occured reading a column in th previous row. */
065: private boolean _errorOccured = false;
066:
067: /** Metadata for the <TT>ResultSet</TT>. */
068: private ResultSetMetaData _rsmd;
069:
070: /** whether or not the user requested to cancel the query */
071: private volatile boolean _stopExecution = false;
072:
073: public ResultSetReader(ResultSet rs) throws SQLException {
074: this (rs, null);
075: }
076:
077: public ResultSetReader(ResultSet rs, int[] columnIndices)
078: throws SQLException {
079: super ();
080: if (rs == null) {
081: throw new IllegalArgumentException("ResultSet == null");
082: }
083:
084: _rs = rs;
085:
086: if (columnIndices != null && columnIndices.length == 0) {
087: columnIndices = null;
088: }
089: _columnIndices = columnIndices;
090:
091: _rsmd = rs.getMetaData();
092:
093: _columnCount = columnIndices != null ? columnIndices.length
094: : _rsmd.getColumnCount();
095: }
096:
097: /**
098: * Read the next row from the <TT>ResultSet</TT>. If no more rows then
099: * <TT>null</TT> will be returned, otherwise an <TT>Object[]</TT> will be
100: * returned where each element of the array is an object representing
101: * the contents of the column. These objects could be of type <TT>String</TT>,
102: * <TT>BigDecimal</TT> etc.
103: *
104: * <P>If an error occurs calling <TT>next()</TT> on the <TT>ResultSet</TT>
105: * then an <TT>SQLException will be thrown, however if an error occurs
106: * retrieving the data for a column an error msg will be placed in that
107: * element of the array, but no exception will be thrown. To see if an
108: * error occured retrieving column data you can call
109: * <TT>getColumnErrorInPreviousRow</TT> after the call to <TT>readRow()</TT>.
110: *
111: * @throws SQLException Error occured on <TT>ResultSet.next()</TT>.
112: */
113: public Object[] readRow() throws SQLException {
114: _errorOccured = false;
115: if (_rs.next()) {
116: return doRead();
117: }
118: return null;
119: }
120:
121: /**
122: * Read the next row from the <TT>ResultSet</TT> for use in the ContentTab.
123: * This is different from readRow() in that data is put into the Object array
124: * in a form controlled by the DataType objects, and may be used for editing
125: * the data and updating the DB. If no more rows then
126: * <TT>null</TT> will be returned, otherwise an <TT>Object[]</TT> will be
127: * returned where each element of the array is an object representing
128: * the contents of the column. These objects could be of type <TT>String</TT>,
129: * <TT>BigDecimal</TT> etc.
130: *
131: * <P>If an error occurs calling <TT>next()</TT> on the <TT>ResultSet</TT>
132: * then an <TT>SQLException will be thrown, however if an error occurs
133: * retrieving the data for a column an error msg will be placed in that
134: * element of the array, but no exception will be thrown. To see if an
135: * error occured retrieving column data you can call
136: * <TT>getColumnErrorInPreviousRow</TT> after the call to <TT>readRow()</TT>.
137: *
138: * @throws SQLException Error occured on <TT>ResultSet.next()</TT>.
139: */
140: public Object[] readRow(ColumnDisplayDefinition colDefs[])
141: throws SQLException {
142: _errorOccured = false;
143: if (_rs.next()) {
144: return doContentTabRead(colDefs);
145: }
146: return null;
147: }
148:
149: /**
150: * Retrieve whether an error occured reading a column in the previous row.
151: *
152: * @return <TT>true</TT> if error occured.
153: */
154: public boolean getColumnErrorInPreviousRow() {
155: return _errorOccured;
156: }
157:
158: /**
159: * Attempt to get the column type name - for some drivers this results in an
160: * SQLException. Log an INFO message if this is unavailable.
161: *
162: * @param idx
163: * the column index to get the type name for.
164: *
165: * @return the type name of the column, or the Java class, or "Unavailable"
166: * if neither are available.
167: */
168: private String safelyGetColumnTypeName(int idx) {
169: String columnTypeName = null;
170: try {
171: /*
172: * Fails on DB2 8.1 for Linux
173: * However, Windows 8.1 fixpak 14 driver (2.10.52) works without
174: * exception
175: * Also, Linux 9.0.1 server with 3.1.57 driver works fine as well
176: */
177: columnTypeName = _rsmd.getColumnTypeName(idx);
178: } catch (SQLException e) {
179: if (s_log.isInfoEnabled()) {
180: s_log
181: .info("doRead: ResultSetMetaData.getColumnTypeName("
182: + idx
183: + ") threw an unexpected exception - "
184: + e.getMessage());
185: s_log
186: .info("Unable to determine column type name so "
187: + "any custom types provided by plugins will be "
188: + "unavailable. This is a driver bug.");
189: }
190: }
191: if (columnTypeName == null) {
192: try {
193: columnTypeName = _rsmd.getColumnClassName(idx);
194: } catch (SQLException e) {
195: if (s_log.isInfoEnabled()) {
196: s_log
197: .info("doRead: ResultSetMetaData.getColumnClassName("
198: + idx
199: + ") threw an unexpected exception - "
200: + e.getMessage());
201:
202: }
203: }
204: }
205: if (columnTypeName == null) {
206: columnTypeName = "Unavailable";
207: }
208: return columnTypeName;
209: }
210:
211: /**
212: * Method used to read data for all Tabs except the ContentsTab, where
213: * the data is used only for reading.
214: * The only data read in the non-ContentsTab tabs is Meta-data about the DB,
215: * which means that there should be no BLOBs, CLOBs, or unknown fields.
216: */
217: private Object[] doRead() {
218: Object[] row = new Object[_columnCount];
219: for (int i = 0; i < _columnCount && !_stopExecution; ++i) {
220: int idx = _columnIndices != null ? _columnIndices[i]
221: : i + 1;
222: try {
223: int columnType = _rsmd.getColumnType(idx);
224: String columnTypeName = safelyGetColumnTypeName(idx);
225:
226: switch (columnType) {
227: case Types.NULL:
228: row[i] = null;
229: break;
230:
231: case Types.BIT:
232: case Types.BOOLEAN:
233: row[i] = _rs.getObject(idx);
234: if (row[i] != null && !(row[i] instanceof Boolean)) {
235: if (row[i] instanceof Number) {
236: if (((Number) row[i]).intValue() == 0) {
237: row[i] = Boolean.FALSE;
238: } else {
239: row[i] = Boolean.TRUE;
240: }
241: } else {
242: row[i] = Boolean.valueOf(row[i].toString());
243: }
244: }
245: break;
246:
247: case Types.TIME:
248: row[i] = _rs.getTime(idx);
249: break;
250:
251: case Types.DATE:
252: if (DataTypeDate.getReadDateAsTimestamp()) {
253: row[i] = _rs.getTimestamp(idx);
254: } else {
255: row[i] = DataTypeDate.staticReadResultSet(_rs,
256: idx, false);
257: }
258: break;
259:
260: case Types.TIMESTAMP:
261: case -101: // Oracle's 'TIMESTAMP WITH TIME ZONE' == -101
262: case -102: // Oracle's 'TIMESTAMP WITH LOCAL TIME ZONE' == -102
263: row[i] = _rs.getTimestamp(idx);
264: break;
265:
266: case Types.BIGINT:
267: row[i] = _rs.getObject(idx);
268: if (row[i] != null && !(row[i] instanceof Long)) {
269: if (row[i] instanceof Number) {
270: row[i] = Long.valueOf(((Number) row[i])
271: .longValue());
272: } else {
273: row[i] = Long.valueOf(row[i].toString());
274: }
275: }
276: break;
277:
278: case Types.DOUBLE:
279: case Types.FLOAT:
280: case Types.REAL:
281: row[i] = _rs.getObject(idx);
282: if (row[i] != null && !(row[i] instanceof Double)) {
283: if (row[i] instanceof Number) {
284: Number nbr = (Number) row[i];
285: row[i] = new Double(nbr.doubleValue());
286: } else {
287: row[i] = new Double(row[i].toString());
288: }
289: }
290: break;
291:
292: case Types.DECIMAL:
293: case Types.NUMERIC:
294: row[i] = _rs.getObject(idx);
295: if (row[i] != null
296: && !(row[i] instanceof BigDecimal)) {
297: if (row[i] instanceof Number) {
298: Number nbr = (Number) row[i];
299: row[i] = new BigDecimal(nbr.doubleValue());
300: } else {
301: row[i] = new BigDecimal(row[i].toString());
302: }
303: }
304: break;
305:
306: case Types.INTEGER:
307: case Types.SMALLINT:
308: case Types.TINYINT:
309: row[i] = _rs.getObject(idx);
310: if (_rs.wasNull()) {
311: row[i] = null;
312: }
313: if (row[i] != null && !(row[i] instanceof Integer)) {
314: if (row[i] instanceof Number) {
315: row[i] = Integer.valueOf(((Number) row[i])
316: .intValue());
317: } else {
318: row[i] = new Integer(row[i].toString());
319: }
320: }
321: break;
322:
323: // TODO: Hard coded -. JDBC/ODBC bridge JDK1.4
324: // brings back -9 for nvarchar columns in
325: // MS SQL Server tables.
326: // -8 is ROWID in Oracle.
327: case Types.CHAR:
328: case Types.VARCHAR:
329: case Types.LONGVARCHAR:
330: case -9:
331: case -8:
332: row[i] = _rs.getString(idx);
333: if (_rs.wasNull()) {
334: row[i] = null;
335: }
336: break;
337:
338: case Types.BINARY:
339: case Types.VARBINARY:
340: case Types.LONGVARBINARY:
341: row[i] = _rs.getString(idx);
342: break;
343:
344: case Types.BLOB:
345: // Since we are reading Meta-data about the DB, we should
346: // never see a BLOB. If we do, the contents are not interpretable
347: // by Squirrel, so just tell the user that it is a BLOB and that it
348: // has data.
349:
350: row[i] = DataTypeBlob.staticReadResultSet(_rs, idx);
351:
352: break;
353:
354: case Types.CLOB:
355: // Since we are reading Meta-data about the DB, we should
356: // never see a CLOB. However, if we do we assume that
357: // it is printable text and that the user wants to see it, so
358: // read in the entire thing.
359: row[i] = DataTypeClob.staticReadResultSet(_rs, idx);
360:
361: break;
362:
363: //Add begin
364: case Types.JAVA_OBJECT:
365: row[i] = _rs.getObject(idx);
366: if (_rs.wasNull()) {
367: row[i] = null;
368: }
369: break;
370: //Add end
371:
372: case Types.OTHER:
373: // Since we are reading Meta-data, there really should never be
374: // a field with SQL type Other (1111).
375: // If there is, we REALLY do not know how to handle it,
376: // so do not attempt to read.
377: // ?? if (_largeObjInfo.getReadSQLOther())
378: // ?? {
379: // ?? // Running getObject on a java class attempts
380: // ?? // to load the class in memory which we don't want.
381: // ?? // getString() just gets the value without loading
382: // ?? // the class (at least under PostgreSQL).
383: // ?? //row[i] = _rs.getObject(idx);
384: // ?? row[i] = _rs.getString(idx);
385: // ?? }
386: // ?? else
387: // ?? {
388: row[i] = s_stringMgr
389: .getString("ResultSetReader.other");
390: // ?? }
391: break;
392:
393: default:
394: /*
395: * See if there is a plugin-registered DataTypeComponent
396: * that can handle this column.
397: */
398: row[i] = CellComponentFactory
399: .readResultWithPluginRegisteredDataType(
400: _rs, columnType, columnTypeName,
401: idx);
402: if (row[i] == null) {
403: Integer colTypeInteger = Integer
404: .valueOf(columnType);
405: row[i] = s_stringMgr.getString(
406: "ResultSetReader.unknown",
407: colTypeInteger);
408: }
409: }
410: } catch (Throwable th) {
411: // Don't bother the user with details about where the result fetch
412: // failed if they cancelled the query.
413: if (!_stopExecution) {
414: _errorOccured = true;
415: row[i] = s_stringMgr
416: .getString("ResultSetReader.error");
417: StringBuffer msg = new StringBuffer(
418: "Error reading column data");
419: msg.append(", column index = ").append(idx);
420: s_log.error(msg.toString(), th);
421: }
422: }
423: }
424:
425: return row;
426: }
427:
428: /**
429: * Method used to read data for the ContentsTab, where
430: * the data is used for both reading and editing.
431: */
432: private Object[] doContentTabRead(ColumnDisplayDefinition colDefs[]) {
433: Object[] row = new Object[_columnCount];
434: for (int i = 0; i < _columnCount && !_stopExecution; ++i) {
435: int idx = _columnIndices != null ? _columnIndices[i]
436: : i + 1;
437: try {
438: final int columnType = _rsmd.getColumnType(idx);
439: //final String columnClassName = _rsmd.getColumnClassName(idx);
440: switch (columnType) {
441: case Types.NULL:
442: row[i] = null;
443: break;
444:
445: // all of the following have been converted to use the DataType objects
446: // So, why not just have case Types.NULL and default??? (this seems pointless)
447: // RMM 20070726
448: case Types.BIT:
449: case Types.BOOLEAN:
450:
451: case Types.DECIMAL:
452: case Types.NUMERIC:
453:
454: case Types.INTEGER:
455: case Types.SMALLINT:
456: case Types.TINYINT:
457: case Types.BIGINT:
458:
459: case Types.DOUBLE:
460: case Types.FLOAT:
461: case Types.REAL:
462:
463: case Types.DATE:
464: case Types.TIME:
465: case Types.TIMESTAMP:
466:
467: // TODO: Hard coded -. JDBC/ODBC bridge JDK1.4
468: // brings back -9 for nvarchar columns in
469: // MS SQL Server tables.
470: // -8 is ROWID in Oracle.
471: case Types.CHAR:
472: case Types.VARCHAR:
473: case Types.LONGVARCHAR:
474: case -9:
475: case -8:
476:
477: // binary types
478: case Types.BINARY:
479: case Types.VARBINARY:
480: case Types.LONGVARBINARY:
481:
482: case Types.CLOB:
483: case Types.BLOB:
484:
485: case Types.OTHER:
486:
487: default:
488: row[i] = CellComponentFactory.readResultSet(
489: colDefs[i], _rs, idx, true);
490:
491: break;
492:
493: }
494: } catch (Throwable th) {
495: _errorOccured = true;
496: row[i] = s_stringMgr.getString("ResultSetReader.error");
497: if (!_stopExecution) {
498: StringBuffer msg = new StringBuffer(
499: "Error reading column data");
500: msg.append(", column index = ").append(idx);
501: s_log.error(msg.toString(), th);
502: }
503: }
504: }
505:
506: return row;
507: }
508:
509: /**
510: * @param _stopExecution The _stopExecution to set.
511: */
512: public void setStopExecution(boolean _stopExecution) {
513: this ._stopExecution = _stopExecution;
514: }
515:
516: /**
517: * @return Returns the _stopExecution.
518: */
519: public boolean isStopExecution() {
520: return _stopExecution;
521: }
522: }
|