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