Annotation of FM2SQL/DBBean.java, revision 1.31
1.11 rogo 1: /*
2: * DBBean.java -- Class that ecapsulates all database actions
3: * Filemake to SQL Converter
4: * Copyright (C) 2004 Robert Gordesch (rogo@mpiwg-berlin.mpg.de)
5: * This program is free software; you can redistribute it and/or modify it
6: * under the terms of the GNU General Public License as published by the Free
7: * Software Foundation; either version 2 of the License, or (at your option)
8: * any later version. Please read license.txt for the full details. A copy of
9: * the GPL may be found at http://www.gnu.org/copyleft/lgpl.html You should
10: * have received a copy of the GNU General Public License along with this
11: * program; if not, write to the Free Software Foundation, Inc., 59 Temple
12: * Place, Suite 330, Boston, MA 02111-1307 USA Created on 15.09.2003 by
13: * rogo
14: */
1.3 rogo 15:
1.1 rogo 16: import java.sql.*;
1.15 rogo 17: import java.text.DateFormat;
1.20 rogo 18: import java.text.ParseException;
1.1 rogo 19: import java.util.*;
1.15 rogo 20:
1.1 rogo 21: import com.fmi.jdbc.*;
22:
23: /**
24: *
25: *
26: * DBBean - Database bean
27: *
28: *<p> a Javabean to perform queries on a JDBC Database,
29: * or excute any other SQL statement
30: * </p>
31: * <p>
32: * Usage:
33: * <pre>
34: * DBBean bean = new DBBean();
35: * // setting user and passwd
36: * bean.setUserAndPasswd("bla","bla");
37: * try
38: * {
39: * bean.setConnection("jdbc:fmpro:http://localhost");
40: * Vector names=bean.getTableNames();
41: * Vector[] result=bean.getQueryData(names.get(0).toString());
42: * // print results to screen
43: * for(int i=0;i<result[1].size();++i)
44: * {
45: * //print Header
46: * System.out.print(" "+result[1].get(i));
47: * }
48: * System.out.println();
49: * for(int j=0;j<result[0].size();++j)
50: * {
51: * Vector row=(Vector)result[0].get(j);
52: * //print rows
53: * for(int k=0;k<row.size();++k)
54: * System.out.print(" "+row.get(k));
55: * System.out.println();
56: * }
57: * } catch(Exception e)
58: * {
59: * System.out.println("Error while connecting to database"+ e);
60: * }
61: * </pre>
62: *
63: * </p>
64: * @author rogo
65: */
66: public class DBBean
67: {
1.25 rogo 68: private boolean useNormanToUnicodeMapper = false;
1.1 rogo 69: Connection connection;
70: String url = "";
71: DatabaseMetaData dbMetaData;
72: Vector columnNames;
1.4 rogo 73: Vector ids = new Vector();
1.1 rogo 74: String user = (System.getProperty("user.name") == null) ? "" : System.getProperty("user.name"); //"postgres";
75: String passwd = ""; //"3333";//"rogo";
76: public int maxHits = 10;
77: ResultSet result;
78: String quoteChar = "";
79: Hashtable connectionPool = new Hashtable();
80: public ResultSetMetaData metaData;
81: // register DataBase Drivers
82: static {
83: try
84: {
85: DriverManager.registerDriver(new com.fmi.jdbc.JdbcDriver());
86: DriverManager.registerDriver((Driver) Class.forName("org.postgresql.Driver").newInstance());
1.9 rogo 87: DriverManager.registerDriver((Driver) Class.forName("com.mysql.jdbc.Driver").newInstance());
1.1 rogo 88: DriverManager.registerDriver((Driver) Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance());
1.19 rogo 89: DriverManager.registerDriver((Driver) Class.forName("acs.jdbc.Driver").newInstance());
1.25 rogo 90:
1.1 rogo 91: // wait a maximum of 10 seconds when attempting to establish a connection
92: DriverManager.setLoginTimeout(10);
93: } catch (Exception e)
94: {
95: System.out.println(e);
96: }
97: }
98: /**
99: * Constructs a database bean
100: */
101: public DBBean()
102: {
103:
104: }
105: /**
106: *Constructs a database bean
107: * and tries to connect to database
108: *specified in the jdbcConnectionURL
109: * @param jdbcConnectionURL url to connect to database
110: */
111:
112: public DBBean(String jdbcConnectionURL) throws Exception
113: {
114:
115: this.url = jdbcConnectionURL;
116:
117: connection = getConnection();
118: if (connection == null)
119: return;
120: // get the meta data for the current connection
121: DatabaseMetaData dbMetaData = (DatabaseMetaData) connection.getMetaData();
122: quoteChar = dbMetaData.getIdentifierQuoteString();
123: if (quoteChar == null)
124: quoteChar = "\""; // needed for postgres
125:
126: // create the root node of the tree
127: // get the open tables from the current connection; the FileMaker Pro
128: // JDBC driver ignores all of the parameters to this method
129:
130: // iterate over the table names and add them to the root node of the tree
131:
132: }
133: /**
134: * sets the maximum number of hits
135: */
136: public void setMaxHits(int maxHits)
137: {
138: this.maxHits = maxHits;
139: }
140: /**
141: * gets the maximum number of hits
142: */
143: public int getMaxHits()
144: {
145: return maxHits;
146: }
147:
148: /**
149: * returns the Connection if null creates a new one from the url property.
150: *
151: */
152: public Connection getConnection() throws Exception
153: {
154: ConnectionPool conPool = (ConnectionPool) connectionPool.get(url);
155: if (conPool == null)
156: {
1.25 rogo 157: createConnection();
158:
1.1 rogo 159: } else
160: {
161: if (!conPool.user.equals(user) || !conPool.passwd.equals(passwd))
162: {
163: conPool.con.close();
164: conPool.user = user;
165: conPool.passwd = passwd;
166:
167: }
168: connection = conPool.con;
169: if (connection.isClosed())
170: {
171: System.out.println("Made new connection!!!");
1.25 rogo 172: createConnection();
173:
174: // connection = DriverManager.getConnection(conPool.url, conPool.user, conPool.passwd);
1.1 rogo 175: conPool.con = connection;
176: }
177: }
178: if (url != "" && connection == null)
1.18 rogo 179: createConnection();
1.25 rogo 180: //connection = DriverManager.getConnection(url, user, passwd);
1.1 rogo 181: dbMetaData = connection.getMetaData();
182: quoteChar = dbMetaData.getIdentifierQuoteString();
183: if (quoteChar == null)
184: quoteChar = "\""; // needed for postgres
185:
186: return connection;
187: }
1.18 rogo 188: private void createConnection() throws SQLException
189: {
1.25 rogo 190: // setup the properties
191: java.util.Properties prop = new java.util.Properties();
192: // prop.put("charSet", "MacCentralEurope");
193: prop.put("user", user);
194: prop.put("password", passwd);
195: System.out.println("url " + url);
196: if (url.indexOf("fmpro") >= 0)
1.18 rogo 197: {
1.25 rogo 198: // Connect to the database
1.18 rogo 199: connection = DriverManager.getConnection(url, prop);
200: System.out.println("odbc with properties inited");
1.25 rogo 201: } else
202: connection = DriverManager.getConnection(url, user, passwd);
1.18 rogo 203: connectionPool.put(url, new ConnectionPool(url, user, passwd, connection));
1.25 rogo 204:
1.18 rogo 205: }
1.1 rogo 206: /**
207: * sets the connection of this DBBean to the database specified in the url
208: * property
209: */
210: public void setConnection(String url) throws Exception
211: {
212: this.url = url;
213: if (url != "")
1.18 rogo 214: //connection = DriverManager.getConnection(url, user, passwd);
1.25 rogo 215: createConnection();
1.1 rogo 216: dbMetaData = connection.getMetaData();
217: quoteChar = dbMetaData.getIdentifierQuoteString();
218: if (quoteChar == null)
219: quoteChar = "\""; // needed for postgres
220: }
221: /**
222: * sets the connection of this DBBean to the database specified in the url
223: * and the url,user and passwd property of this DBBean instance
224: */
225: public void setConnection(String url, String user, String passwd) throws Exception
226: {
227: this.user = user;
228: this.passwd = passwd;
229: this.url = url;
230: if (url != "")
1.25 rogo 231: createConnection();
232: // connection = DriverManager.getConnection(url, user, passwd);
1.1 rogo 233: dbMetaData = connection.getMetaData();
234: quoteChar = dbMetaData.getIdentifierQuoteString();
235: if (quoteChar == null)
236: quoteChar = "\""; // needed for postgres
237: }
238:
1.4 rogo 239: public void setIDVector(Vector ids)
240: {
241: this.ids = ids;
242: }
243:
244: /**
245: * returns a Vector containing the ID Row Name
246: **/
247: public Vector getIDVector()
248: {
249: return ids;
250: }
1.1 rogo 251: /**
252: * returns a Vector containing the Tablenames or an error message in the Vector
253: */
254: public Vector getTableNames()
255: {
256: Vector tableNameVec = new Vector();
257: try
258: {
259: if (connection == null)
260: {
261: Vector vec = new Vector();
262: vec.add("no database connection");
263: return vec;
264: }
265: if (dbMetaData == null)
266: dbMetaData = connection.getMetaData();
267: ResultSet tableNames = dbMetaData.getTables(null, null, null, null);
268: // System.out.println(dbMetaData.supportsAlterTableWithAddColumn());
269: // iterate over the table names and add them to the root node of the tree
270:
271: while (tableNames.next())
272: {
273: String tableName = tableNames.getString("TABLE_NAME");
274: tableNameVec.add(tableName);
275:
276: }
277: } catch (Exception e)
278: {
279: e.printStackTrace();
280: }
281: return tableNameVec;
282: }
283: /**
284: * returns a Vector containing the Tablenames or an error message in the Vector
285: */
286: public Vector getTableNames(String catalog)
287: {
288: Vector tableNameVec = new Vector();
289: try
290: {
291: if (connection == null)
292: {
293: Vector vec = new Vector();
294: vec.add("no database connection");
295: return vec;
296: }
297: setConnection(url.substring(0, url.lastIndexOf("/") + 1) + catalog);
298: if (dbMetaData == null)
299: dbMetaData = connection.getMetaData();
300: System.out.println("catalog " + catalog + " " + dbMetaData.getCatalogSeparator() + " " + url.substring(0, url.lastIndexOf("/") + 1));
301: ResultSet tableNames = dbMetaData.getTables(null, null, null, null);
302: // System.out.println(dbMetaData.supportsAlterTableWithAddColumn());
303: // iterate over the table names and add them to the root node of the tree
304:
305: while (tableNames.next())
306: {
307: String tableName = tableNames.getString("TABLE_NAME");
308: tableNameVec.add(tableName);
309:
310: }
311: } catch (Exception e)
312: {
313: e.printStackTrace();
314: }
315: return tableNameVec;
316: }
317:
318: /**
319: * returns a Vector containing the Catalog or an error message in the Vector
320: */
321: public Vector getCatalogs()
322: {
323: Vector tableNameVec = new Vector();
324: try
325: {
326: if (connection == null)
327: {
328: Vector vec = new Vector();
329: vec.add("no database connection");
330: return vec;
331: }
332: if (dbMetaData == null)
333: dbMetaData = connection.getMetaData();
334: ResultSet tableNames = dbMetaData.getCatalogs();
335: // System.out.println(dbMetaData.supportsAlterTableWithAddColumn());
336: // iterate over the table names and add them to the root node of the tree
337: System.out.println(tableNames.getMetaData().getColumnName(1));
338: while (tableNames.next())
339: {
340: String tableName = tableNames.getString(1);
341: tableNameVec.add(tableName);
342: //tableName = tableNames.getString(1);
343: //tableNameVec.add(tableName);
344:
345: }
346: } catch (Exception e)
347: {
348: e.printStackTrace();
349: }
350: return tableNameVec;
351: }
352:
353: /**
354: * returns a Vector containing the layoutNames for the specified Table
355: * if the database supports this otherwise Vector containing an empty String
356: */
357:
358: public Vector getLayoutNames(String tableName) throws SQLException
359: {
360: Vector layouts = new Vector();
361: if (dbMetaData instanceof DatabaseMetaDataExt)
362: layouts.add("");
363: if (dbMetaData == null)
364: dbMetaData = connection.getMetaData();
365:
366: if (dbMetaData instanceof DatabaseMetaDataExt)
367: {
368: ResultSet layoutNames = ((DatabaseMetaDataExt) dbMetaData).getLayouts(null, null, tableName, null);
369:
370: // iterate over the layout names and add them to the "Layouts" node
371: while (layoutNames.next())
372: layouts.add(layoutNames.getString("LAYOUT_NAME"));
373: }
374: return layouts;
375: }
376: /**
377: * Returns the result for select * from table
378: * with maxHits = 500 default value
379: */
1.25 rogo 380: public Vector[] getQueryData(String table) throws SQLException, ParseException
1.1 rogo 381: {
382:
383: return getQueryData("SELECT * from " + quoteChar + table + quoteChar, maxHits);
384:
385: }
386:
387: /**
388: * Returns the result of the query
389: * or an Vector array of Vectors containing error messages
390: */
391: public Vector[] getQueryData(String query, FM2SQL.ProgressDialog dialog, int maxHits) throws SQLException
392: {
393: long timeStart = System.currentTimeMillis();
394: ResultSet resultSet = null;
395: if (connection == null)
396: {
397: Vector[] noData = new Vector[2];
398: //System.out.println("Exception occured");
399: noData[1] = new Vector();
400: Vector vec2 = new Vector();
401: noData[0] = new Vector();
402: vec2.add("no Connection available");
403: noData[0].add(vec2);
404: noData[1].add("Exception occured! No results available");
405: //noData[1].add("no Results were produced");
406:
407: return noData;
408: }
409: if (dialog != null)
410: dialog.progress.setValue(0);
411:
412: resultSet = makeQuery(query, maxHits);
413:
414: metaData = resultSet.getMetaData();
415: int columnCount = metaData.getColumnCount();
416: int rowCount = 0;
417: if (maxHits == 0)
418: rowCount = (metaData instanceof ResultSetMetaDataExt) ? 1000 : getRowCount(query);
419: else
420: rowCount = maxHits;
421: int counter = 0;
422: Vector tableData = new Vector();
423: Vector tableRow = new Vector();
424: // System.out.println("rowCount "+rowCount+" "+maxHits);
425: try
426: {
427: while ((tableRow = getNextRow()) != null)
428: {
429: counter++;
430: if (dialog != null)
431: dialog.progress.setValue((int) ((double) counter / (double) rowCount * 100.0));
432:
433: tableData.add(tableRow);
434:
435: }
436: } catch (Exception e)
437: {
438: // TODO Auto-generated catch block
439: e.printStackTrace();
440: }
441:
442: // retrieve the column names from the result set; the column names
443: // are used for the table header
444: columnNames = new Vector();
445:
446: for (int i = 1; i <= columnCount; i++)
447: columnNames.addElement(metaData.getColumnName(i));
448: Vector data[] = new Vector[2];
449: data[0] = tableData;
450: data[1] = columnNames;
451: System.out.println("Rows " + tableData.size() + " " + ((Vector) tableData.get(0)).size());
452: long timeEnd = System.currentTimeMillis();
453: System.out.println("Time needed for query and data retrieval " + (timeEnd - timeStart) + " ms");
454: return data;
455: }
456: /**
457: * Returns the result of the query
458: * or an Vector array of Vectors containing error messages
459: */
1.20 rogo 460: public Vector[] getQueryData(String query, int maxHits) throws SQLException, ParseException
1.1 rogo 461: {
462: long timeStart = System.currentTimeMillis();
463: ResultSet resultSet = null;
464: if (connection == null)
465: {
466: Vector[] noData = new Vector[2];
467: //System.out.println("Exception occured");
468: noData[1] = new Vector();
469: Vector vec2 = new Vector();
470: noData[0] = new Vector();
471: vec2.add("no Connection available");
472: noData[0].add(vec2);
473: noData[1].add("Exception occured! No results available");
474: //noData[1].add("no Results were produced");
475:
476: return noData;
477: }
478: resultSet = makeQuery(query, maxHits);
479: metaData = resultSet.getMetaData();
480: int columnCount = metaData.getColumnCount();
481:
482: Vector tableData = new Vector();
483: while (resultSet.next())
484: {
485: //System.out.println("datatype "+(Types.LONGVARCHAR ==metaData.getColumnType(3)));
486: Vector tableRow = new Vector(), m_columnClasses = new Vector();
487: for (int i = 1; i <= columnCount; i++)
488: {
489: // repeating fields and fields from related databases may contain
490: // multliple data values; the data values are stored using
491: // a Vector which is then added to the tableRow
492: // if (metaData instanceof ResultSetMetaDataExt)
493: if ((metaData instanceof ResultSetMetaDataExt) && (((ResultSetMetaDataExt) metaData).isRelated(i) || ((ResultSetMetaDataExt) metaData).isRepeating(i)))
494: {
495: //System.out.println("Related fields");
496: // retrieve the repeating or related field contents as a
497: // com.fmi.jdbc.Array via the ResultSet.getObject method
498: com.fmi.jdbc.Array array = (com.fmi.jdbc.Array) resultSet.getObject(i);
499: // create a Vector for storing all of the data values
500: ArrayList columnData = new ArrayList();
501:
502: try
503: {
504:
505: // call the Array.getStringArray method since the data will
506: // only be displayed
507: Object[] fieldData = (Object[]) array.getArray();
508:
509: if (fieldData != null)
510: {
511: // add each value to the Vector
512: for (int j = 0; j < fieldData.length; j++)
513: {
514: if (fieldData[j] != null)
515: columnData.add(fieldData[j]);
516: }
517:
518: }
519: } catch (Exception e)
520: {
521: //System.out.println(e);
522: }
523: if (columnData.isEmpty())
524: tableRow.add(null);
525: else
526: tableRow.addElement(columnData);
527: //System.out.println(columnData);
528: //System.out.println("Related fields"+columnData.size()+" "+tableRow.size());
529:
530: m_columnClasses.addElement(java.util.Vector.class);
531: } else if (metaData.getColumnType(i) == Types.LONGVARBINARY)
532: {
533: // use the ResultSet.getObject method for retrieving images
534: // from FileMaker Pro container fields; the ResultSet.getObject
535: // method returns a java.awt.Image object for FileMaker Pro
536: // container fields
537: try
538: {
539:
540: tableRow.addElement(resultSet.getObject(i));
541: } catch (Exception e)
542: {
543: // TODO Auto-generated catch block
544: //e.printStackTrace();
545: tableRow.addElement(null);
546: }
547: //tableRow.addElement("Picture ignored");
548: m_columnClasses.addElement(java.awt.Image.class);
549: } else if (metaData.getColumnType(i) == Types.TIME)
550: {
551: // use the ResultSet.getObject method for retieving images
552: // from FileMaker Pro container fields; the ResultSet.getObject
553: // method returns a java.awt.Image object for FileMaker Pro
554: // container fields
555: try
556: {
557: tableRow.addElement(resultSet.getTime(i).toString());
558: m_columnClasses.addElement(java.sql.Time.class);
559: } catch (Exception e)
560: {
561:
562: String value = resultSet.getString(i);
563: if (value != null)
564: {
565: //System.out.println("SQLTime new "+Time.valueOf("17:00:00").toString());
566: int index = 0;
567: for (int j = 0; j < value.length(); ++j)
568: {
569: if (!Character.isLetter(value.charAt(j)))
570: index = j + 1;
571: else
572: break;
573: }
574:
575: tableRow.addElement(value.substring(0, index));
576: //m_columnClasses.addElement(java.sql.Time.class);
577: } else
578: tableRow.add(null);
579: m_columnClasses.addElement(String.class);
580: } // to catch
581:
582: } else if (metaData.getColumnType(i) == Types.DATE)
583: {
584: // use the ResultSet.getObject method for retieving images
585: // from FileMaker Pro container fields; the ResultSet.getObject
586: // method returns a java.awt.Image object for FileMaker Pro
587: // container fields
588:
1.25 rogo 589: try
590: {
591: tableRow.addElement(resultSet.getDate(i));
592:
593: } catch (Exception e)
594: {
595: // work around for parse bug in FM JDBC Driver
596: // for dates of format dd-mm-yyyy
597: String date = resultSet.getString(i);
598: date = date.replace('-', '.');
599: java.text.DateFormat dateFormat = DateFormat.getDateInstance(DateFormat.SHORT, Locale.GERMAN);
600: java.util.Date d = dateFormat.parse(date);
601: // Calendar cal=Calendar.getInstance(Locale.GERMAN);
602: // cal.setTime(d);
603: // date=(cal.get(Calendar.YEAR))+"-"+(cal.get(Calendar.MONTH)+1)+"-"+cal.get(Calendar.DATE);
604: tableRow.addElement(new java.sql.Date(d.getTime()));
605: System.out.println("Date " + date);
606: }
607: } else if (metaData.getColumnTypeName(i) == "NUMBER")
1.1 rogo 608: {
609: // use the ResultSet.getObject method for retieving images
610: // from FileMaker Pro container fields; the ResultSet.getObject
611: // method returns a java.awt.Image object for FileMaker Pro
612: // container fields
613: try
614: {
1.29 rogo 615: Double value =new Double(resultSet.getDouble(i));
616:
617:
618: // tableRow.addElement(new Double(resultSet.getDouble(i)));
619: String tVal =value.toString();
620: tVal = tVal.substring(tVal.indexOf('.')+1);
621: boolean checkMe = tVal.length()==1&&tVal.equals("0");
622: // System.out.println("check was"+checkMe+" "+tVal);
623: if(checkMe)
624: tableRow.addElement(new Integer(value.intValue()));
625: else
626: tableRow.addElement(value);
1.1 rogo 627:
628: } catch (Exception e)
629: {
630:
631: StringBuffer number = new StringBuffer();
632: String value = resultSet.getString(i);
633: System.out.println(value);
634: for (int c = 0; c < value.length(); ++c)
635: {
636: if (Character.isDigit(value.charAt(c)))
637: {
638: number.append(value.charAt(c));
639: }
640: }
641: if (number.length() > 0)
642: {
643: tableRow.addElement(null);
1.13 rogo 644: m_columnClasses.addElement(Double.class);
1.1 rogo 645: } else
646: tableRow.addElement(null);
647: }
648: } else
649: {
650: // all other field values are retrieved as strings and
651: // added to the tableRow Vector
652: // if(resultSet.getObject(i)!=null) System.out.println(resultSet.getObject(i));
653: try
654: {
655: byte[] b = resultSet.getBytes(i);
656: String utf8 = null;
657: if (metaData instanceof ResultSetMetaDataExt)
658: utf8 = (b == null) ? null : new String(b);
659: else
660: utf8 = (b == null) ? null : new String(b, "UTF-8");
661: utf8 = (utf8 == null) ? null : new String(utf8.getBytes("UTF-8"), "UTF-8");
662: tableRow.addElement(utf8);
663: } catch (Exception e)
664: {
665: System.out.println("Hey I Got an error" + e);
666: }
667: m_columnClasses.addElement(java.lang.String.class);
668: }
669: }
670:
671: // add the tableRow Vector to the tableData Vector
672: tableData.addElement(tableRow);
673: }
674:
675: // retrieve the column names from the result set; the column names
676: // are used for the table header
677: columnNames = new Vector();
678:
679: for (int i = 1; i <= columnCount; i++)
680: columnNames.addElement(metaData.getColumnName(i));
681: Vector data[] = new Vector[2];
682: data[0] = tableData;
683: data[1] = columnNames;
684: System.out.println("Rows " + tableData.size() + " " + ((Vector) tableData.get(0)).size());
685: long timeEnd = System.currentTimeMillis();
686: System.out.println("Time needed for query and data retrieval " + (timeEnd - timeStart) + " ms");
687: return data;
688: }
689:
690: public Vector getColumnNames()
691: {
692: if (result == null)
693: return null;
694: try
695: {
696: ResultSetMetaData metaData = result.getMetaData();
697: int columnCount = metaData.getColumnCount();
698: columnNames = new Vector();
699:
700: for (int i = 1; i <= columnCount; i++)
701: columnNames.addElement(metaData.getColumnName(i));
702: } catch (Exception e)
703: {
704: }
705: return columnNames;
706: }
707: /**
708: * makes the database Query
709: * with the numberOfHits as maximum
710: * @return the result as an ResultSet object
711: */
712: public ResultSet makeQuery(String query, int numberOfHits) throws SQLException
713: {
714: result = null;
715: Statement stm = null;
1.25 rogo 716:
1.1 rogo 717: // System.out.println("Query " + query);
718:
719: if (!connection.isClosed())
720: stm = connection.createStatement();
1.25 rogo 721: else
722: {
723:
1.12 rogo 724: try
725: {
726: connection = getConnection();
1.25 rogo 727: stm = connection.createStatement();
1.12 rogo 728: } catch (Exception e)
729: {
730: // TODO Auto-generated catch block
731: e.printStackTrace();
732: }
733: }
1.1 rogo 734: stm.setMaxRows(numberOfHits);
1.2 rogo 735: long time = System.currentTimeMillis();
1.25 rogo 736: try
737: {
738: stm.execute(query);
739: long time2 = System.currentTimeMillis();
740:
741: System.out.println("time to execute " + (time2 - time));
742: // stm.setMaxRows(numberOfHits);
743:
744: result = stm.getResultSet();
745: // System.out.println(result+" "+stm.getUpdateCount());
746: metaData = result.getMetaData();
747: } catch (Exception e)
748: {
1.19 rogo 749: // TODO remove
1.25 rogo 750: if (FM2SQL.fmInstance != null)
751: FM2SQL.showErrorDialog("Error caught!! \n Query was " + query + " \n", "Debug Info");
752: }
753:
1.1 rogo 754: return result;
755: }
756: /**
757: * sets the database user
758: */
759: public void setUser(String user)
760: {
761: this.user = user;
762: }
763: /**
764: * sets the database passwd
765: */
766: public void setPasswd(String passwd)
767: {
768: this.passwd = passwd;
769: }
770:
771: /**
772: * sets the database user and passwd
773: */
774: public void setUserAndPasswd(String user, String passwd)
775: {
776: this.user = user;
777: this.passwd = passwd;
778:
779: }
780: /**
781: * just sets the connection URL
782: */
783: public void setURL(String url)
784: {
785: this.url = url;
786: }
787:
788: /**
789: * Test the database drivers features given by the DatabaseMetaData object
790: */
791: public Vector[] TestDB(DatabaseMetaData d) throws SQLException
792: {
793:
794: Vector data[] = new Vector[2];
795: Vector[] rows = new Vector[120];
796: for (int i = 0; i < rows.length; ++i)
797: rows[i] = new Vector();
798: Vector columnNames = new Vector();
799: columnNames.add("Feature");
800: columnNames.add("Supported");
801:
802: Vector cols = new Vector();
803: rows[0].add("allProceduresAreCallable");
804: rows[0].add(new Boolean(d.allProceduresAreCallable()));
805: //boolean allProceduresAreCallable() throws SQLException;
806: rows[1].add("allTablesAreSelectable");
807: rows[1].add(new Boolean(d.allTablesAreSelectable()));
808: // boolean allTablesAreSelectable() throws SQLException;
809: rows[2].add("isReadOnly");
810: rows[2].add(new Boolean(d.isReadOnly()));
811: // boolean isReadOnly() throws SQLException;
812: rows[3].add("nullsAreSortedHigh");
813: rows[3].add(new Boolean(d.nullsAreSortedHigh()));
814: // boolean nullsAreSortedHigh() throws SQLException;
815: rows[4].add("nullsAreSortedLow");
816: rows[4].add(new Boolean(d.nullsAreSortedLow()));
817: // boolean nullsAreSortedLow() throws SQLException;
818: rows[5].add("nullsAreSortedAtStart");
819: rows[5].add(new Boolean(d.nullsAreSortedAtStart()));
820: // boolean nullsAreSortedAtStart() throws SQLException;
821: rows[6].add("nullsAreSortedAtEnd");
822: rows[6].add(new Boolean(d.nullsAreSortedAtEnd()));
823: // boolean nullsAreSortedAtEnd() throws SQLException;
824: rows[7].add("usesLocalFiles");
825: rows[7].add(new Boolean(d.usesLocalFiles()));
826: // boolean usesLocalFiles() throws SQLException;
827: rows[8].add("usesLocalFilePerTable");
828: rows[8].add(new Boolean(d.usesLocalFilePerTable()));
829: // boolean usesLocalFilePerTable() throws SQLException;
830: rows[9].add("supportsMixedCaseIdentifiers");
831: rows[9].add(new Boolean(d.supportsMixedCaseIdentifiers()));
832: //boolean supportsMixedCaseIdentifiers() throws SQLException;
833: rows[10].add("storesUpperCaseIdentifiers");
834: rows[10].add(new Boolean(d.storesUpperCaseIdentifiers()));
835: // boolean storesUpperCaseIdentifiers() throws SQLException;
836: rows[11].add("storesLowerCaseIdentifiers");
837: rows[11].add(new Boolean(d.storesLowerCaseIdentifiers()));
838: // boolean storesLowerCaseIdentifiers() throws SQLException;
839: rows[12].add("storesMixedCaseIdentifiers");
840: rows[12].add(new Boolean(d.storesMixedCaseIdentifiers()));
841: // boolean storesMixedCaseIdentifiers() throws SQLException;
842: rows[13].add("supportsMixedCaseQuotedIdentifiers");
843: rows[13].add(new Boolean(d.supportsMixedCaseQuotedIdentifiers()));
844: // boolean supportsMixedCaseQuotedIdentifiers() throws SQLException;
845: rows[14].add("storesUpperCaseQuotedIdentifiers");
846: rows[14].add(new Boolean(d.storesUpperCaseQuotedIdentifiers()));
847: // boolean storesUpperCaseQuotedIdentifiers() throws SQLException;
848: rows[15].add("storesLowerCaseQuotedIdentifiers");
849: rows[15].add(new Boolean(d.storesLowerCaseQuotedIdentifiers()));
850: //boolean storesLowerCaseQuotedIdentifiers() throws SQLException;
851: rows[16].add("storesMixedCaseQuotedIdentifiers");
852: rows[16].add(new Boolean(d.storesMixedCaseQuotedIdentifiers()));
853: // boolean storesMixedCaseQuotedIdentifiers() throws SQLException;
854: rows[17].add("supportsAlterTableWithAddColumn");
855: rows[17].add(new Boolean(d.supportsAlterTableWithAddColumn()));
856: // boolean supportsAlterTableWithAddColumn() throws SQLException;
857: rows[18].add("supportsAlterTableWithDropColumn");
858: rows[18].add(new Boolean(d.supportsAlterTableWithDropColumn()));
859: // boolean supportsAlterTableWithDropColumn() throws SQLException;
860: rows[19].add("nullPlusNonNullIsNull");
861: rows[19].add(new Boolean(d.nullPlusNonNullIsNull()));
862: // boolean nullPlusNonNullIsNull() throws SQLException;
863: rows[20].add("supportsConvert");
864: rows[20].add(new Boolean(d.supportsConvert()));
865: // boolean supportsConvert() throws SQLException;
866:
867: // boolean supportsConvert(int fromType, int toType) throws SQLException;
868: rows[21].add("supportsTableCorrelationNames");
869: rows[21].add(new Boolean(d.supportsTableCorrelationNames()));
870: // boolean supportsTableCorrelationNames() throws SQLException;
871: rows[22].add("supportsDifferentTableCorrelationNames");
872: rows[22].add(new Boolean(d.supportsDifferentTableCorrelationNames()));
873: // boolean supportsDifferentTableCorrelationNames() throws SQLException;
874: rows[23].add("supportsExpressionsInOrderBy");
875: rows[23].add(new Boolean(d.supportsExpressionsInOrderBy()));
876: // boolean supportsExpressionsInOrderBy() throws SQLException;
877: rows[24].add("supportsOrderByUnrelated");
878: rows[24].add(new Boolean(d.supportsOrderByUnrelated()));
879: // boolean supportsOrderByUnrelated() throws SQLException;
880: rows[25].add("supportsGroupBy");
881: rows[25].add(new Boolean(d.supportsGroupBy()));
882: // boolean supportsGroupBy() throws SQLException;
883: rows[26].add("supportsGroupByUnrelated");
884: rows[26].add(new Boolean(d.supportsGroupByUnrelated()));
885: // boolean supportsGroupByUnrelated() throws SQLException;
886: rows[27].add("supportsGroupByBeyondSelect");
887: rows[27].add(new Boolean(d.supportsGroupByBeyondSelect()));
888: // boolean supportsGroupByBeyondSelect() throws SQLException;
889: rows[28].add("supportsLikeEscapeClause");
890: rows[28].add(new Boolean(d.supportsLikeEscapeClause()));
891: // boolean supportsLikeEscapeClause() throws SQLException;
892: rows[29].add("supportsMultipleResultSets");
893: rows[29].add(new Boolean(d.supportsMultipleResultSets()));
894: // boolean supportsMultipleResultSets() throws SQLException;
895: rows[30].add("supportsMultipleTransactions");
896: rows[30].add(new Boolean(d.supportsMultipleTransactions()));
897: // boolean supportsMultipleTransactions() throws SQLException;
898: rows[31].add("supportsNonNullableColumns");
899: rows[31].add(new Boolean(d.supportsNonNullableColumns()));
900: // boolean supportsNonNullableColumns() throws SQLException;
901: rows[32].add("supportsMinimumSQLGrammar");
902: rows[32].add(new Boolean(d.supportsMinimumSQLGrammar()));
903: // boolean supportsMinimumSQLGrammar() throws SQLException;
904: rows[33].add("supportsCoreSQLGrammar");
905: rows[33].add(new Boolean(d.supportsCoreSQLGrammar()));
906: // boolean supportsCoreSQLGrammar() throws SQLException;
907: rows[34].add("supportsExtendedSQLGrammar");
908: rows[34].add(new Boolean(d.supportsExtendedSQLGrammar()));
909: // boolean supportsExtendedSQLGrammar() throws SQLException;
910: rows[35].add("supportsANSI92EntryLevelSQL");
911: rows[35].add(new Boolean(d.supportsANSI92EntryLevelSQL()));
912: // boolean supportsANSI92EntryLevelSQL() throws SQLException;
913: rows[36].add("supportsANSI92IntermediateSQL");
914: rows[36].add(new Boolean(d.supportsANSI92IntermediateSQL()));
915: //boolean supportsANSI92IntermediateSQL() throws SQLException;
916: rows[37].add("supportsANSI92FullSQL");
917: rows[37].add(new Boolean(d.supportsANSI92FullSQL()));
918: //boolean supportsANSI92FullSQL() throws SQLException;
919: rows[38].add("supportsIntegrityEnhancementFacility");
920: rows[38].add(new Boolean(d.supportsIntegrityEnhancementFacility()));
921: //boolean supportsIntegrityEnhancementFacility() throws SQLException;
922: rows[39].add("supportsOuterJoins");
923: rows[39].add(new Boolean(d.supportsOuterJoins()));
924: //boolean supportsOuterJoins() throws SQLException;
925: rows[40].add("supportsFullOuterJoins");
926: rows[40].add(new Boolean(d.supportsFullOuterJoins()));
927: //boolean supportsFullOuterJoins() throws SQLException;
928: rows[41].add("supportsLimitedOuterJoins");
929: rows[41].add(new Boolean(d.supportsLimitedOuterJoins()));
930: //boolean supportsLimitedOuterJoins() throws SQLException;
931: rows[42].add("isCatalogAtStart");
932: rows[42].add(new Boolean(d.isCatalogAtStart()));
933: //boolean isCatalogAtStart() throws SQLException;
934: rows[43].add("supportsSchemasInDataManipulation");
935: rows[43].add(new Boolean(d.supportsSchemasInDataManipulation()));
936: //boolean supportsSchemasInDataManipulation() throws SQLException;
937: rows[44].add("supportsSchemasInProcedureCalls");
938: rows[44].add(new Boolean(d.supportsSchemasInProcedureCalls()));
939: //boolean supportsSchemasInProcedureCalls() throws SQLException;
940: rows[45].add("supportsSchemasInTableDefinitions");
941: rows[45].add(new Boolean(d.supportsSchemasInTableDefinitions()));
942: //boolean supportsSchemasInTableDefinitions() throws SQLException;
943: rows[46].add("supportsSchemasInIndexDefinitions");
944: rows[46].add(new Boolean(d.supportsSchemasInIndexDefinitions()));
945: //boolean supportsSchemasInIndexDefinitions() throws SQLException;
946: rows[47].add("supportsSchemasInPrivilegeDefinitions");
947: rows[47].add(new Boolean(d.supportsSchemasInPrivilegeDefinitions()));
948: //boolean supportsSchemasInPrivilegeDefinitions() throws SQLException;
949: rows[48].add("supportsCatalogsInDataManipulation");
950: rows[48].add(new Boolean(d.supportsCatalogsInDataManipulation()));
951: //boolean supportsCatalogsInDataManipulation() throws SQLException;
952: rows[49].add("supportsCatalogsInProcedureCalls");
953: rows[49].add(new Boolean(d.supportsCatalogsInProcedureCalls()));
954: //boolean supportsCatalogsInProcedureCalls() throws SQLException;
955: rows[50].add("supportsCatalogsInTableDefinitions");
956: rows[50].add(new Boolean(d.supportsCatalogsInTableDefinitions()));
957: //boolean supportsCatalogsInTableDefinitions() throws SQLException;
958: rows[51].add("supportsCatalogsInIndexDefinitions");
959: rows[51].add(new Boolean(d.supportsCatalogsInIndexDefinitions()));
960: //boolean supportsCatalogsInIndexDefinitions() throws SQLException;
961: rows[52].add("supportsCatalogsInPrivilegeDefinitions");
962: rows[52].add(new Boolean(d.supportsCatalogsInPrivilegeDefinitions()));
963: //boolean supportsCatalogsInPrivilegeDefinitions() throws SQLException;
964: rows[53].add("supportsPositionedDelete");
965: rows[53].add(new Boolean(d.supportsPositionedDelete()));
966: //boolean supportsPositionedDelete() throws SQLException;
967: rows[54].add("supportsPositionedUpdate");
968: rows[54].add(new Boolean(d.supportsPositionedUpdate()));
969: //boolean supportsPositionedUpdate() throws SQLException;
970: rows[55].add("supportsSelectForUpdate");
971: rows[55].add(new Boolean(d.supportsSelectForUpdate()));
972: //boolean supportsSelectForUpdate() throws SQLException;
973: rows[56].add("supportsStoredProcedures");
974: rows[56].add(new Boolean(d.supportsStoredProcedures()));
975: //boolean supportsStoredProcedures() throws SQLException;
976: rows[57].add("supportsSubqueriesInComparisons");
977: rows[57].add(new Boolean(d.supportsSubqueriesInComparisons()));
978: //boolean supportsSubqueriesInComparisons() throws SQLException;
979: rows[58].add("supportsSubqueriesInExists");
980: rows[58].add(new Boolean(d.supportsSubqueriesInExists()));
981: //boolean supportsSubqueriesInExists() throws SQLException;
982: rows[59].add("supportsSubqueriesInIns");
983: rows[59].add(new Boolean(d.supportsSubqueriesInIns()));
984: //boolean supportsSubqueriesInIns() throws SQLException;
985: rows[60].add("supportsSubqueriesInQuantifieds");
986: rows[60].add(new Boolean(d.supportsSubqueriesInQuantifieds()));
987: //boolean supportsSubqueriesInQuantifieds() throws SQLException;
988: rows[61].add("supportsCorrelatedSubqueries");
989: rows[61].add(new Boolean(d.supportsCorrelatedSubqueries()));
990: //boolean supportsCorrelatedSubqueries() throws SQLException;
991: rows[62].add("supportsUnion");
992: rows[62].add(new Boolean(d.supportsUnion()));
993: //boolean supportsUnion() throws SQLException;
994: rows[63].add("supportsUnionAll");
995: rows[63].add(new Boolean(d.supportsUnionAll()));
996: //boolean supportsUnionAll() throws SQLException;
997: rows[64].add("supportsOpenCursorsAcrossCommit");
998: rows[64].add(new Boolean(d.supportsOpenCursorsAcrossCommit()));
999: //boolean supportsOpenCursorsAcrossCommit() throws SQLException;
1000: rows[65].add("supportsOpenCursorsAcrossRollback");
1001: rows[65].add(new Boolean(d.supportsOpenCursorsAcrossRollback()));
1002: //boolean supportsOpenCursorsAcrossRollback() throws SQLException;
1003: rows[66].add("supportsOpenStatementsAcrossCommit");
1004: rows[66].add(new Boolean(d.supportsOpenStatementsAcrossCommit()));
1005: //boolean supportsOpenStatementsAcrossCommit() throws SQLException;
1006: rows[67].add("supportsOpenStatementsAcrossRollback");
1007: rows[67].add(new Boolean(d.supportsOpenStatementsAcrossRollback()));
1008: //boolean supportsOpenStatementsAcrossRollback() throws SQLException;
1009: rows[68].add("doesMaxRowSizeIncludeBlobs");
1010: rows[68].add(new Boolean(d.doesMaxRowSizeIncludeBlobs()));
1011: //boolean doesMaxRowSizeIncludeBlobs() throws SQLException;
1012: rows[69].add("supportsTransactions");
1013: rows[69].add(new Boolean(d.supportsTransactions()));
1014: //boolean supportsTransactions() throws SQLException;
1015: rows[70].add("supportsTransactionIsolationLevel");
1016: rows[70].add(new Boolean(d.supportsTransactionIsolationLevel(1)));
1017: //boolean supportsTransactionIsolationLevel(int level) throws SQLException;
1018: rows[71].add("supportsDataDefinitionAndDataManipulationTransactions");
1019: rows[71].add(new Boolean(d.supportsDataDefinitionAndDataManipulationTransactions()));
1020: //boolean supportsDataDefinitionAndDataManipulationTransactions() throws SQLException;
1021: rows[72].add("supportsDataManipulationTransactionsOnly");
1022: rows[72].add(new Boolean(d.supportsDataManipulationTransactionsOnly()));
1023: //boolean supportsDataManipulationTransactionsOnly() throws SQLException;
1024: rows[73].add("dataDefinitionCausesTransactionCommit");
1025: rows[73].add(new Boolean(d.dataDefinitionCausesTransactionCommit()));
1026: //boolean dataDefinitionCausesTransactionCommit() throws SQLException;
1027: rows[74].add("dataDefinitionIgnoredInTransactions");
1028: rows[74].add(new Boolean(d.dataDefinitionIgnoredInTransactions()));
1029: //boolean dataDefinitionIgnoredInTransactions() throws SQLException;
1030: rows[75].add("getMaxBinaryLiteralLength");
1031: rows[75].add(new Integer(d.getMaxBinaryLiteralLength()));
1032: // int getMaxBinaryLiteralLength() throws SQLException;
1033: rows[76].add("getMaxCharLiteralLength");
1034: rows[76].add(new Integer(d.getMaxCharLiteralLength()));
1035: //int getMaxCharLiteralLength() throws SQLException;
1036: rows[77].add("getMaxColumnNameLength");
1037: rows[77].add(new Integer(d.getMaxColumnNameLength()));
1038: // int getMaxColumnNameLength() throws SQLException;
1039: rows[78].add("getMaxColumnsInGroupBy");
1040: rows[78].add(new Integer(d.getMaxColumnsInGroupBy()));
1041: //int getMaxColumnsInGroupBy() throws SQLException;
1042: rows[79].add("getMaxColumnsInIndex");
1043: rows[79].add(new Integer(d.getMaxColumnsInIndex()));
1044: //int getMaxColumnsInIndex() throws SQLException;
1045: rows[80].add("getMaxColumnsInOrderBy");
1046: rows[80].add(new Integer(d.getMaxColumnsInOrderBy()));
1047: //int getMaxColumnsInOrderBy() throws SQLException;
1048: rows[81].add("getMaxColumnsInSelect");
1049: rows[81].add(new Integer(d.getMaxColumnsInSelect()));
1050: //int getMaxColumnsInSelect() throws SQLException;
1051: rows[82].add("getMaxColumnsInTable");
1052: rows[82].add(new Integer(d.getMaxColumnsInTable()));
1053: //int getMaxColumnsInTable() throws SQLException;
1054: rows[83].add("getMaxConnections");
1055: rows[83].add(new Integer(d.getMaxConnections()));
1056: //int getMaxConnections() throws SQLException;
1057: rows[84].add("getMaxCursorNameLength");
1058: rows[84].add(new Integer(d.getMaxCursorNameLength()));
1059: // int getMaxCursorNameLength() throws SQLException;
1060: rows[85].add("getMaxIndexLength");
1061: rows[85].add(new Integer(d.getMaxIndexLength()));
1062: //int getMaxIndexLength() throws SQLException;
1063: rows[86].add("getMaxSchemaNameLength");
1064: rows[86].add(new Integer(d.getMaxSchemaNameLength()));
1065: //int getMaxSchemaNameLength() throws SQLException;
1066: rows[87].add("getMaxProcedureNameLength");
1067: rows[87].add(new Integer(d.getMaxProcedureNameLength()));
1068: //int getMaxProcedureNameLength() throws SQLException;
1069: rows[88].add("getMaxCatalogNameLength");
1070: rows[88].add(new Integer(d.getMaxCatalogNameLength()));
1071: //int getMaxCatalogNameLength() throws SQLException;
1072: rows[89].add("getMaxRowSize");
1073: rows[89].add(new Integer(d.getMaxRowSize()));
1074: //int getMaxRowSize() throws SQLException;
1075: rows[90].add("getMaxStatementLength");
1076: rows[90].add(new Integer(d.getMaxStatementLength()));
1077: //int getMaxStatementLength() throws SQLException;
1078: rows[91].add("getMaxStatements");
1079: rows[91].add(new Integer(d.getMaxStatements()));
1080: //int getMaxStatements() throws SQLException;
1081: rows[92].add("getMaxTableNameLength");
1082: rows[92].add(new Integer(d.getMaxTableNameLength()));
1083: //int getMaxTableNameLength() throws SQLException;
1084: rows[93].add("getMaxTablesInSelect");
1085: rows[93].add(new Integer(d.getMaxTablesInSelect()));
1086: //int getMaxTablesInSelect() throws SQLException;
1087: rows[94].add("getMaxUserNameLength");
1088: rows[94].add(new Integer(d.getMaxUserNameLength()));
1089: // int getMaxUserNameLength() throws SQLException;
1090: rows[95].add("getDefaultTransactionIsolation");
1091: rows[95].add(new Integer(d.getDefaultTransactionIsolation()));
1092: //int getDefaultTransactionIsolation() throws SQLException;
1093:
1094: rows[96].add("getDatabaseProductName");
1095: rows[96].add(d.getDatabaseProductName());
1096: // String getDatabaseProductName() throws SQLException;
1097: rows[97].add("getDatabaseProductVersion");
1098: rows[97].add(d.getDatabaseProductVersion());
1099: //String getDatabaseProductVersion() throws SQLException;
1100:
1101: rows[98].add("getURL");
1102: rows[98].add(d.getURL());
1103: //String getURL() throws SQLException;
1104: rows[99].add("getUserName");
1105: rows[99].add(d.getUserName());
1106: //String getUserName() throws SQLException;
1107: rows[100].add("getDriverName");
1108: rows[100].add(d.getDriverName());
1109: // String getDriverName() throws SQLException;
1110: rows[101].add("getIdentifierQuoteString");
1111: rows[101].add(d.getIdentifierQuoteString());
1112: //String getIdentifierQuoteString() throws SQLException;
1113:
1114: rows[102].add("getDriverVersion");
1115: rows[102].add(d.getDriverVersion());
1116: //String getDriverVersion() throws SQLException;
1117: rows[103].add("getDriverMajorVersion");
1118: rows[103].add(new Integer(d.getDriverMajorVersion()));
1119: //int getDriverMajorVersion();
1120: rows[104].add("getDriverMinorVersion");
1121: rows[104].add(new Integer(d.getDriverMinorVersion()));
1122: //int getDriverMinorVersion();
1123: rows[105].add("getSQLKeywords");
1124: rows[105].add(d.getSQLKeywords());
1125: //String getSQLKeywords() throws SQLException;
1126: rows[106].add("getNumericFunctions");
1127: rows[106].add(d.getNumericFunctions());
1128: //String getNumericFunctions() throws SQLException;
1129: rows[107].add("getStringFunctions");
1130: rows[107].add(d.getStringFunctions());
1131: // String getStringFunctions() throws SQLException;
1132: rows[108].add("getSystemFunctions");
1133: rows[108].add(d.getSystemFunctions());
1134: //String getSystemFunctions() throws SQLException;
1135: rows[109].add("getTimeDateFunctions");
1136: rows[109].add(d.getTimeDateFunctions());
1137: //String getTimeDateFunctions() throws SQLException;
1138: rows[110].add("getSearchStringEscape");
1139: rows[110].add(d.getSearchStringEscape());
1140: //String getSearchStringEscape() throws SQLException;
1141: rows[111].add("getExtraNameCharacters");
1142: rows[111].add(d.getExtraNameCharacters());
1143: //String getExtraNameCharacters() throws SQLException;
1144: rows[112].add("getSchemaTerm");
1145: rows[112].add(d.getSchemaTerm());
1146: //String getSchemaTerm() throws SQLException;
1147: rows[113].add("getProcedureTerm");
1148: rows[113].add(d.getProcedureTerm());
1149: //String getProcedureTerm() throws SQLException;
1150: rows[114].add("getCatalogTerm");
1151: rows[114].add(d.getCatalogTerm());
1152: // String getCatalogTerm() throws SQLException;
1153: rows[115].add("getCatalogSeparator");
1154: rows[115].add(d.getCatalogSeparator());
1155: //String getCatalogSeparator() throws SQLException;
1156:
1157: /*
1158: boolean supportsResultSetType(int type) throws SQLException;
1159:
1160: boolean supportsResultSetConcurrency(int type, int concurrency) throws SQLException;
1161:
1162: boolean ownUpdatesAreVisible(int type) throws SQLException;
1163:
1164: boolean ownDeletesAreVisible(int type) throws SQLException;
1165:
1166: boolean ownInsertsAreVisible(int type) throws SQLException;
1167:
1168: boolean othersUpdatesAreVisible(int type) throws SQLException;
1169:
1170: boolean othersDeletesAreVisible(int type) throws SQLException;
1171:
1172: boolean othersInsertsAreVisible(int type) throws SQLException;
1173: boolean updatesAreDetected(int type) throws SQLException;
1174: boolean deletesAreDetected(int type) throws SQLException;
1175:
1176: boolean insertsAreDetected(int type) throws SQLException;
1177: */
1178: // not in filemaker
1179: // rows[96].add("supportsBatchUpdates");
1180: // rows[96].add(new Boolean(d.supportsBatchUpdates()));
1181: //boolean supportsBatchUpdates() throws SQLException;
1182:
1183: /*
1184: ResultSet getProcedures(String catalog, String schemaPattern, String procedureNamePattern) throws SQLException;
1185:
1186: ResultSet getProcedureColumns(String catalog, String schemaPattern, String procedureNamePattern, String columnNamePattern) throws SQLException;
1187: ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String types[]) throws SQLException;
1188: ResultSet getSchemas() throws SQLException;
1189: ResultSet getCatalogs() throws SQLException;
1190: ResultSet getTableTypes() throws SQLException;
1191: ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) throws SQLException;
1192: ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException;
1193: ResultSet getImportedKeys(String catalog, String schema, String table) throws SQLException;
1194: ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException;
1195: ResultSet getCrossReference(String primaryCatalog, String primarySchema, String primaryTable, String foreignCatalog, String foreignSchema, String foreignTable) throws SQLException;
1196: ResultSet getTypeInfo() throws SQLException;
1197: ResultSet getIndexInfo(String catalog, String schema, String table, boolean unique, boolean approximate) throws SQLException;
1198:
1199: ResultSet getUDTs(String catalog, String schemaPattern, String typeNamePattern, int[] types) throws SQLException;
1200:
1201: Connection getConnection() throws SQLException;
1202:
1203: */
1204: for (int i = 0; i < rows.length; ++i)
1205: cols.add(rows[i]);
1206: data[0] = cols;
1207: data[1] = columnNames;
1208: return data;
1209: }
1210: public Vector getNextRow() throws Exception
1211: {
1212:
1213: if (result == null)
1214: return null;
1215: boolean check = false;
1216: ResultSet resultSet = result;
1217: ResultSetMetaData metaData = resultSet.getMetaData();
1218: int columnCount = metaData.getColumnCount();
1219: Vector tableData = new Vector();
1220: check = resultSet.next();
1221: // System.out.println("hallo check "+check);
1222: if (!check)
1223: return null;
1224: Vector tableRow = new Vector(), m_columnClasses = new Vector();
1225: for (int i = 1; i <= columnCount; i++)
1226: {
1227: // repeating fields and fields from related databases may contain
1228: // multliple data values; the data values are stored using
1229: // a Vector which is then added to the tableRow
1230: // if (metaData instanceof ResultSetMetaDataExt)
1231: if ((metaData instanceof ResultSetMetaDataExt) && (((ResultSetMetaDataExt) metaData).isRelated(i) || ((ResultSetMetaDataExt) metaData).isRepeating(i)))
1232: {
1233: //System.out.println("Related fields");
1234: // retrieve the repeating or related field contents as a
1235: // com.fmi.jdbc.Array via the ResultSet.getObject method
1236: com.fmi.jdbc.Array array = (com.fmi.jdbc.Array) resultSet.getObject(i);
1237: // create a Vector for storing all of the data values
1238: ArrayList columnData = new ArrayList();
1239: try
1240: {
1241:
1242: // call the Array.getStringArray method since the data will
1243: // only be displayed
1244: Object[] fieldData = (Object[]) array.getArray();
1245:
1246: if (fieldData != null)
1247: {
1248: // add each value to the Vector
1249: for (int j = 0; j < fieldData.length; j++)
1250: {
1251: if (fieldData[j] != null)
1252: columnData.add(fieldData[j]);
1253: }
1254: }
1255: } catch (Exception e)
1256: {
1257: //System.out.println(e);
1258: }
1259:
1260: if (columnData.isEmpty())
1261: tableRow.add(null);
1262: else
1263: tableRow.addElement(columnData);
1264: //System.out.println(columnData);
1265: //System.out.println("Related fields"+columnData.size()+" "+tableRow.size());
1266:
1267: // m_columnClasses.addElement(java.util.Vector.class);
1268: } else if (metaData.getColumnType(i) == Types.LONGVARBINARY)
1269: {
1270: // use the ResultSet.getObject method for retrieving images
1271: // from FileMaker Pro container fields; the ResultSet.getObject
1272: // method returns a java.awt.Image object for FileMaker Pro
1273: // container fields
1274:
1275: try
1276: {
1277: tableRow.addElement(resultSet.getObject(i));
1278: } catch (Exception e)
1279: {
1280: // TODO Auto-generated catch block
1281: // e.printStackTrace();
1282: tableRow.addElement(null);
1283: }
1284: // m_columnClasses.addElement(java.awt.Image.class);
1285: } else if (metaData.getColumnType(i) == Types.TIME)
1286: {
1287: // use the ResultSet.getObject method for retieving images
1288: // from FileMaker Pro container fields; the ResultSet.getObject
1289: // method returns a java.awt.Image object for FileMaker Pro
1290: // container fields
1291: try
1292: {
1293: tableRow.addElement(resultSet.getTime(i).toString());
1294: // m_columnClasses.addElement(java.sql.Time.class);
1295: } catch (Exception e)
1296: {
1297:
1298: String value = resultSet.getString(i);
1299: if (value != null)
1300: {
1301: //System.out.println("SQLTime new "+Time.valueOf("17:00:00").toString());
1302: int index = 0;
1303: for (int j = 0; j < value.length(); ++j)
1304: {
1305: if (!Character.isLetter(value.charAt(j)))
1306: index = j + 1;
1307: else
1308: break;
1309: }
1310:
1311: tableRow.addElement(value.substring(0, index));
1312: //m_columnClasses.addElement(java.sql.Time.class);
1313: } else
1314: tableRow.add(null);
1315: // m_columnClasses.addElement(String.class);
1316: } // to catch
1317:
1.10 rogo 1318: } else if (metaData.getColumnType(i) == Types.INTEGER)
1.25 rogo 1319: {
1320: // use the ResultSet.getObject method for retieving images
1321: // from FileMaker Pro container fields; the ResultSet.getObject
1322: // method returns a java.awt.Image object for FileMaker Pro
1323: // container fields
1.10 rogo 1324:
1.25 rogo 1325: tableRow.addElement(new Integer(resultSet.getInt(i)));
1326: // m_columnClasses.addElement(java.sql.Date.class);
1327: } else if (metaData.getColumnType(i) == Types.DATE)
1328: {
1.1 rogo 1329: // use the ResultSet.getObject method for retieving images
1330: // from FileMaker Pro container fields; the ResultSet.getObject
1331: // method returns a java.awt.Image object for FileMaker Pro
1332: // container fields
1.25 rogo 1333: try
1334: {
1335: tableRow.addElement(resultSet.getDate(i));
1.1 rogo 1336:
1.25 rogo 1337: } catch (Exception e)
1338: {
1339: // work around for parse bug in FM JDBC Driver
1340: // for dates of format dd-mm-yyyy
1341: String date = resultSet.getString(i);
1342: date = date.replace('-', '.');
1343: java.text.DateFormat dateFormat = DateFormat.getDateInstance(DateFormat.SHORT, Locale.GERMAN);
1344: java.util.Date d = dateFormat.parse(date);
1345: // Calendar cal=Calendar.getInstance(Locale.GERMAN);
1346: // cal.setTime(d);
1347: // date=(cal.get(Calendar.YEAR))+"-"+(cal.get(Calendar.MONTH)+1)+"-"+cal.get(Calendar.DATE);
1348: tableRow.addElement(new java.sql.Date(d.getTime()));
1349: System.out.println("Date " + date);
1350: }
1.1 rogo 1351: // m_columnClasses.addElement(java.sql.Date.class);
1352: } else if (metaData.getColumnTypeName(i) == "NUMBER")
1353: {
1354: // use the ResultSet.getObject method for retieving images
1355: // from FileMaker Pro container fields; the ResultSet.getObject
1356: // method returns a java.awt.Image object for FileMaker Pro
1357: // container fields
1358: try
1359: {
1.26 rogo 1360: Double value =new Double(resultSet.getDouble(i));
1361:
1362:
1363: // tableRow.addElement(new Double(resultSet.getDouble(i)));
1364: String tVal =value.toString();
1365: tVal = tVal.substring(tVal.indexOf('.')+1);
1366: boolean checkMe = tVal.length()==1&&tVal.equals("0");
1.27 rogo 1367: // System.out.println("check was"+checkMe+" "+tVal);
1.26 rogo 1368: if(checkMe)
1369: tableRow.addElement(new Integer(value.intValue()));
1370: else
1371: tableRow.addElement(value);
1372:
1.1 rogo 1373: // m_columnClasses.addElement(Integer.class);
1374:
1375: } catch (Exception e)
1376: {
1377:
1378: StringBuffer number = new StringBuffer();
1379: String value = resultSet.getString(i);
1380: System.out.println(value);
1381: for (int c = 0; c < value.length(); ++c)
1382: {
1383: if (Character.isDigit(value.charAt(c)))
1384: {
1385: number.append(value.charAt(c));
1386: }
1387: }
1388: if (number.length() > 0)
1389: {
1390: tableRow.addElement(null);
1391: // m_columnClasses.addElement(Integer.class);
1392: } else
1393: tableRow.addElement(null);
1394: }
1395: } else
1396: {
1397: // all other field values are retrieved as strings and
1398: // added to the tableRow Vector
1399: // System.out.println("row "+resultSet.getString(i));
1400: try
1401: {
1402: byte[] b = null;
1403: if (metaData instanceof ResultSetMetaDataExt)
1404: b = resultSet.getBytes(i);
1.25 rogo 1405: /* if (b != null)
1406: {
1407: java.io.ByteArrayInputStream stream = (java.io.ByteArrayInputStream) resultSet.getBinaryStream(i);
1408: // System.out.println(" stream "+resultSet.getBinaryStream(i));
1409: byte[] c = new byte[stream.available()];
1410: int length = stream.read(c, 0, c.length);
1411: int count = 0;
1412: b = new byte[c.length];
1413: for (int n = 0; n < length; ++n)
1414: {
1415:
1416: if (c[n] != 0)
1417: {
1418: // System.out.println(c[n]+" "+(int)'?'+" "+(char)c[n]+" "+count+" "+b.length);
1419: b[count++] = c[n];
1420: }
1421: }
1422: byte[] bCopy = new byte[count];
1423: System.arraycopy(b, 0, bCopy, 0, count);
1424: b = bCopy;
1425: }*/
1.1 rogo 1426: String utf8 = null;
1.24 rogo 1427: utf8 = (b == null) ? null : new String(b);
1.1 rogo 1428: if (metaData instanceof ResultSetMetaDataExt)
1.24 rogo 1429: {
1430: String rowElement = "";
1431: if (b != null)
1432: {
1433: rowElement = resultSet.getString(i);
1.25 rogo 1434: if (useNormanToUnicodeMapper)
1435: rowElement = Convert.normanToUnicode(rowElement);
1.24 rogo 1436: tableRow.addElement(rowElement);
1437:
1438: } else
1439: tableRow.addElement(null);
1.25 rogo 1440: } else
1.1 rogo 1441: {
1.25 rogo 1442: if (url.toLowerCase().indexOf("odbc") >= 0)
1443: {
1444: byte[] val = resultSet.getBytes(i);
1445: for (int j = 0; j < val.length; ++j)
1446: System.out.println(Integer.toHexString(val[j]));
1447: tableRow.addElement((val == null) ? null : new String(val));
1448:
1449: } else
1450: // byte[] val = resultSet.getBytes(i);
1451: tableRow.add(resultSet.getString(i));
1.1 rogo 1452: //tableRow.addElement((val==null) ? null:new String(val,"UTF-8"));
1453: }
1454: } catch (Exception e)
1455: {
1.24 rogo 1456: System.out.println("Hey I got an error" + e);
1.1 rogo 1457: e.printStackTrace();
1458: }
1459: // m_columnClasses.addElement(java.lang.String.class);
1460: }
1461: }
1462: // tableData.addElement(tableRow);
1463: if (check)
1464: return tableRow;
1465: else
1466: return null;
1467: }
1468: class ConnectionPool
1469: {
1470: String user = "", passwd = "", url = "";
1471: Connection con;
1472: public ConnectionPool(String url, String user, String passwd, Connection con)
1473: {
1474: this.con = con;
1475: this.user = user;
1476: this.passwd = passwd;
1477: this.url = url;
1478: }
1479:
1480: }
1481: public String getQC()
1482: {
1483: // if (connection == null)
1484: // return "";
1485:
1486: // check if connection null if null try to get one
1487: if (connection == null)
1488: try
1489: {
1490: getConnection();
1491: } catch (Exception e)
1492: {
1493: if (FM2SQL.debug)
1494: System.out.println("cannot get a connection");
1495: }
1496: if (connection == null)
1497: {
1498: if (url.toLowerCase().indexOf("fmpro") >= 0 || url.toLowerCase().indexOf("postgres") >= 0)
1499: quoteChar = "\"";
1500: else if (url.toLowerCase().indexOf("mysql") >= 0)
1501: quoteChar = "`";
1502: }
1503: if (quoteChar == null)
1504: quoteChar = "\""; // needed for postgres
1505: return quoteChar;
1506: }
1507: public int getRowCount(String query) throws SQLException
1508: {
1509: String table = query.substring(query.indexOf("from") + 4).trim();
1510: int index = table.indexOf(" ");
1511: table = table.substring(0, (index >= 0) ? index : table.length());
1512: System.out.println(table);
1513: Statement stm = null;
1514:
1515: if (metaData instanceof ResultSetMetaDataExt)
1516: return 1000;
1517: if (!connection.isClosed())
1518: stm = connection.createStatement();
1519: stm.setMaxRows(1);
1520: ResultSet resultSet = stm.executeQuery("select count(*) from " + table);
1521: resultSet.next();
1522: return resultSet.getInt(1);
1523: }
1.25 rogo 1524: public TreeSet getIDVector(String id, String table, String query, int numHits) throws Exception
1525: {
1526: TreeSet t = new TreeSet();
1527: getConnection();
1528: ResultSet result = this.result;
1529: String subQuery = query.substring(query.lastIndexOf(table) + table.length() + 1);
1530: System.out.println("subQuery " + subQuery);
1.30 rogo 1531: makeQuery("select " + id + " from " + getQC() + table + getQC() + subQuery+"order by "+id, numHits);
1.25 rogo 1532: while (true)
1533: {
1.6 rogo 1534: Vector vec = getNextRow();
1535: if (vec == null)
1536: break;
1537: t.add(vec.get(0));
1.25 rogo 1538: }
1539: this.result = result;
1540: metaData = (this.result == null) ? null : this.result.getMetaData();
1.6 rogo 1541: return t;
1.25 rogo 1542: }
1.24 rogo 1543: /**
1544: * @return
1545: */
1546: public boolean isUseNormanToUnicodeMapper()
1547: {
1548: return useNormanToUnicodeMapper;
1549: }
1550:
1551: /**
1552: * @param b
1553: */
1554: public void setUseNormanToUnicodeMapper(boolean b)
1555: {
1556: useNormanToUnicodeMapper = b;
1557: }
1.28 rogo 1558: /**
1559: *
1560: */
1561: protected void closeAllConnections()
1562: {
1563: Enumeration enumeration = connectionPool.elements();
1564: while(enumeration.hasMoreElements())
1565: {
1566: ConnectionPool conPol =(ConnectionPool)enumeration.nextElement();
1567: try
1568: {
1569: System.out.println(conPol);
1570: conPol.con.close();
1571: } catch (SQLException e)
1572: {
1573: // TODO Auto-generated catch block
1574: e.printStackTrace();
1575: }
1576: }
1577:
1578: }
1.31 ! rogo 1579: /**
! 1580: * @param indexField
! 1581: */
! 1582: public String getColumnType(String indexField) throws SQLException
! 1583: {
! 1584: Vector names=getColumnNames();
! 1585: for (int i = 0; i < names.size(); i++)
! 1586: {
! 1587: if(names.get(i).toString().equals(indexField))
! 1588: {
! 1589: System .out.println("found field "+names.get(i)+" "+metaData.getColumnTypeName(i+1));
! 1590: return metaData.getColumnTypeName(i+1);
! 1591:
! 1592: }
! 1593: }
! 1594: return "";
! 1595: }
1.24 rogo 1596:
1.5 rogo 1597: }
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>