Annotation of FM2SQL/DBBean.java, revision 1.37
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.32 rogo 89: DriverManager.registerDriver((Driver) Class.forName("com.ddtek.jdbc.sequelink.SequeLinkDriver").newInstance());
1.19 rogo 90: DriverManager.registerDriver((Driver) Class.forName("acs.jdbc.Driver").newInstance());
1.32 rogo 91:
1.1 rogo 92: // wait a maximum of 10 seconds when attempting to establish a connection
93: DriverManager.setLoginTimeout(10);
94: } catch (Exception e)
95: {
96: System.out.println(e);
97: }
98: }
99: /**
100: * Constructs a database bean
101: */
102: public DBBean()
103: {
104:
105: }
106: /**
107: *Constructs a database bean
108: * and tries to connect to database
109: *specified in the jdbcConnectionURL
110: * @param jdbcConnectionURL url to connect to database
111: */
112:
113: public DBBean(String jdbcConnectionURL) throws Exception
114: {
115:
116: this.url = jdbcConnectionURL;
117:
118: connection = getConnection();
119: if (connection == null)
120: return;
121: // get the meta data for the current connection
122: DatabaseMetaData dbMetaData = (DatabaseMetaData) connection.getMetaData();
123: quoteChar = dbMetaData.getIdentifierQuoteString();
124: if (quoteChar == null)
125: quoteChar = "\""; // needed for postgres
126:
127: // create the root node of the tree
128: // get the open tables from the current connection; the FileMaker Pro
129: // JDBC driver ignores all of the parameters to this method
130:
131: // iterate over the table names and add them to the root node of the tree
132:
133: }
134: /**
135: * sets the maximum number of hits
136: */
137: public void setMaxHits(int maxHits)
138: {
139: this.maxHits = maxHits;
140: }
141: /**
142: * gets the maximum number of hits
143: */
144: public int getMaxHits()
145: {
146: return maxHits;
147: }
148:
149: /**
150: * returns the Connection if null creates a new one from the url property.
151: *
152: */
153: public Connection getConnection() throws Exception
154: {
155: ConnectionPool conPool = (ConnectionPool) connectionPool.get(url);
156: if (conPool == null)
157: {
1.25 rogo 158: createConnection();
159:
1.1 rogo 160: } else
161: {
162: if (!conPool.user.equals(user) || !conPool.passwd.equals(passwd))
163: {
164: conPool.con.close();
165: conPool.user = user;
166: conPool.passwd = passwd;
167:
168: }
169: connection = conPool.con;
170: if (connection.isClosed())
171: {
172: System.out.println("Made new connection!!!");
1.25 rogo 173: createConnection();
174:
175: // connection = DriverManager.getConnection(conPool.url, conPool.user, conPool.passwd);
1.1 rogo 176: conPool.con = connection;
177: }
178: }
179: if (url != "" && connection == null)
1.18 rogo 180: createConnection();
1.25 rogo 181: //connection = DriverManager.getConnection(url, user, passwd);
1.1 rogo 182: dbMetaData = connection.getMetaData();
183: quoteChar = dbMetaData.getIdentifierQuoteString();
184: if (quoteChar == null)
185: quoteChar = "\""; // needed for postgres
186:
187: return connection;
188: }
1.18 rogo 189: private void createConnection() throws SQLException
190: {
1.25 rogo 191: // setup the properties
192: java.util.Properties prop = new java.util.Properties();
193: // prop.put("charSet", "MacCentralEurope");
194: prop.put("user", user);
195: prop.put("password", passwd);
196: System.out.println("url " + url);
197: if (url.indexOf("fmpro") >= 0)
1.18 rogo 198: {
1.25 rogo 199: // Connect to the database
1.18 rogo 200: connection = DriverManager.getConnection(url, prop);
201: System.out.println("odbc with properties inited");
1.25 rogo 202: } else
203: connection = DriverManager.getConnection(url, user, passwd);
1.18 rogo 204: connectionPool.put(url, new ConnectionPool(url, user, passwd, connection));
1.25 rogo 205:
1.18 rogo 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);
1.25 rogo 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.25 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.37 ! rogo 381: public Vector[] getQueryData(String table) throws SQLException, ParseException,Exception
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: */
1.37 ! rogo 392: public Vector[] getQueryData(String query, FM2SQL.ProgressDialog dialog, int maxHits) throws SQLException,Exception
1.1 rogo 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.37 ! rogo 461: public Vector[] getQueryData(String query, int maxHits) throws SQLException, ParseException,Exception
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.25 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.29 rogo 616: Double value =new Double(resultSet.getDouble(i));
617:
618:
619: // tableRow.addElement(new Double(resultSet.getDouble(i)));
620: String tVal =value.toString();
621: tVal = tVal.substring(tVal.indexOf('.')+1);
622: boolean checkMe = tVal.length()==1&&tVal.equals("0");
623: // System.out.println("check was"+checkMe+" "+tVal);
624: if(checkMe)
625: tableRow.addElement(new Integer(value.intValue()));
626: else
627: tableRow.addElement(value);
1.1 rogo 628:
629: } catch (Exception e)
630: {
631:
632: StringBuffer number = new StringBuffer();
633: String value = resultSet.getString(i);
634: System.out.println(value);
635: for (int c = 0; c < value.length(); ++c)
636: {
637: if (Character.isDigit(value.charAt(c)))
638: {
639: number.append(value.charAt(c));
640: }
641: }
642: if (number.length() > 0)
643: {
644: tableRow.addElement(null);
1.13 rogo 645: m_columnClasses.addElement(Double.class);
1.1 rogo 646: } else
647: tableRow.addElement(null);
648: }
649: } else
650: {
651: // all other field values are retrieved as strings and
652: // added to the tableRow Vector
653: // if(resultSet.getObject(i)!=null) System.out.println(resultSet.getObject(i));
654: try
655: {
1.35 rogo 656: String utf8 = null;
657: if (metaData instanceof ResultSetMetaDataExt)
658: {
1.1 rogo 659: byte[] b = resultSet.getBytes(i);
1.35 rogo 660:
1.1 rogo 661: if (metaData instanceof ResultSetMetaDataExt)
662: utf8 = (b == null) ? null : new String(b);
663: else
664: utf8 = (b == null) ? null : new String(b, "UTF-8");
1.35 rogo 665: }
666: else
667: utf8=resultSet.getString(i);
668:
669: // utf8 = (utf8 == null) ? null : new String(utf8.getBytes("UTF-8"), "UTF-8");
670: tableRow.addElement(utf8);
671:
1.1 rogo 672: } catch (Exception e)
673: {
674: System.out.println("Hey I Got an error" + e);
1.35 rogo 675: e.printStackTrace();
1.1 rogo 676: }
677: m_columnClasses.addElement(java.lang.String.class);
678: }
679: }
680:
681: // add the tableRow Vector to the tableData Vector
682: tableData.addElement(tableRow);
683: }
684:
685: // retrieve the column names from the result set; the column names
686: // are used for the table header
687: columnNames = new Vector();
688:
689: for (int i = 1; i <= columnCount; i++)
690: columnNames.addElement(metaData.getColumnName(i));
691: Vector data[] = new Vector[2];
692: data[0] = tableData;
693: data[1] = columnNames;
694: System.out.println("Rows " + tableData.size() + " " + ((Vector) tableData.get(0)).size());
695: long timeEnd = System.currentTimeMillis();
696: System.out.println("Time needed for query and data retrieval " + (timeEnd - timeStart) + " ms");
697: return data;
698: }
699:
700: public Vector getColumnNames()
701: {
702: if (result == null)
703: return null;
704: try
705: {
706: ResultSetMetaData metaData = result.getMetaData();
707: int columnCount = metaData.getColumnCount();
708: columnNames = new Vector();
709:
710: for (int i = 1; i <= columnCount; i++)
711: columnNames.addElement(metaData.getColumnName(i));
712: } catch (Exception e)
713: {
714: }
715: return columnNames;
716: }
717: /**
718: * makes the database Query
719: * with the numberOfHits as maximum
720: * @return the result as an ResultSet object
721: */
1.37 ! rogo 722: public ResultSet makeQuery(String query, int numberOfHits) throws SQLException,Exception
1.1 rogo 723: {
724: result = null;
725: Statement stm = null;
1.25 rogo 726:
1.1 rogo 727: // System.out.println("Query " + query);
728:
729: if (!connection.isClosed())
730: stm = connection.createStatement();
1.25 rogo 731: else
732: {
733:
1.12 rogo 734: try
735: {
736: connection = getConnection();
1.25 rogo 737: stm = connection.createStatement();
1.12 rogo 738: } catch (Exception e)
739: {
740: // TODO Auto-generated catch block
741: e.printStackTrace();
742: }
743: }
1.1 rogo 744: stm.setMaxRows(numberOfHits);
1.2 rogo 745: long time = System.currentTimeMillis();
1.25 rogo 746: try
747: {
748: stm.execute(query);
749: long time2 = System.currentTimeMillis();
750:
1.34 rogo 751: System.out.println("time to execute " + (time2 - time));
1.25 rogo 752: // stm.setMaxRows(numberOfHits);
753:
754: result = stm.getResultSet();
755: // System.out.println(result+" "+stm.getUpdateCount());
756: metaData = result.getMetaData();
757: } catch (Exception e)
758: {
1.19 rogo 759: // TODO remove
1.37 ! rogo 760: if (Convert.isGUI&&Convert.debug)
1.25 rogo 761: FM2SQL.showErrorDialog("Error caught!! \n Query was " + query + " \n", "Debug Info");
1.33 rogo 762: e.printStackTrace();
1.37 ! rogo 763: throw e;
1.25 rogo 764: }
765:
1.1 rogo 766: return result;
767: }
768: /**
769: * sets the database user
770: */
771: public void setUser(String user)
772: {
773: this.user = user;
774: }
775: /**
776: * sets the database passwd
777: */
778: public void setPasswd(String passwd)
779: {
780: this.passwd = passwd;
781: }
782:
783: /**
784: * sets the database user and passwd
785: */
786: public void setUserAndPasswd(String user, String passwd)
787: {
788: this.user = user;
789: this.passwd = passwd;
790:
791: }
792: /**
793: * just sets the connection URL
794: */
795: public void setURL(String url)
796: {
797: this.url = url;
798: }
799:
800: /**
801: * Test the database drivers features given by the DatabaseMetaData object
802: */
803: public Vector[] TestDB(DatabaseMetaData d) throws SQLException
804: {
805:
806: Vector data[] = new Vector[2];
807: Vector[] rows = new Vector[120];
808: for (int i = 0; i < rows.length; ++i)
809: rows[i] = new Vector();
810: Vector columnNames = new Vector();
811: columnNames.add("Feature");
812: columnNames.add("Supported");
813:
814: Vector cols = new Vector();
815: rows[0].add("allProceduresAreCallable");
816: rows[0].add(new Boolean(d.allProceduresAreCallable()));
817: //boolean allProceduresAreCallable() throws SQLException;
818: rows[1].add("allTablesAreSelectable");
819: rows[1].add(new Boolean(d.allTablesAreSelectable()));
820: // boolean allTablesAreSelectable() throws SQLException;
821: rows[2].add("isReadOnly");
822: rows[2].add(new Boolean(d.isReadOnly()));
823: // boolean isReadOnly() throws SQLException;
824: rows[3].add("nullsAreSortedHigh");
825: rows[3].add(new Boolean(d.nullsAreSortedHigh()));
826: // boolean nullsAreSortedHigh() throws SQLException;
827: rows[4].add("nullsAreSortedLow");
828: rows[4].add(new Boolean(d.nullsAreSortedLow()));
829: // boolean nullsAreSortedLow() throws SQLException;
830: rows[5].add("nullsAreSortedAtStart");
831: rows[5].add(new Boolean(d.nullsAreSortedAtStart()));
832: // boolean nullsAreSortedAtStart() throws SQLException;
833: rows[6].add("nullsAreSortedAtEnd");
834: rows[6].add(new Boolean(d.nullsAreSortedAtEnd()));
835: // boolean nullsAreSortedAtEnd() throws SQLException;
836: rows[7].add("usesLocalFiles");
837: rows[7].add(new Boolean(d.usesLocalFiles()));
838: // boolean usesLocalFiles() throws SQLException;
839: rows[8].add("usesLocalFilePerTable");
840: rows[8].add(new Boolean(d.usesLocalFilePerTable()));
841: // boolean usesLocalFilePerTable() throws SQLException;
842: rows[9].add("supportsMixedCaseIdentifiers");
843: rows[9].add(new Boolean(d.supportsMixedCaseIdentifiers()));
844: //boolean supportsMixedCaseIdentifiers() throws SQLException;
845: rows[10].add("storesUpperCaseIdentifiers");
846: rows[10].add(new Boolean(d.storesUpperCaseIdentifiers()));
847: // boolean storesUpperCaseIdentifiers() throws SQLException;
848: rows[11].add("storesLowerCaseIdentifiers");
849: rows[11].add(new Boolean(d.storesLowerCaseIdentifiers()));
850: // boolean storesLowerCaseIdentifiers() throws SQLException;
851: rows[12].add("storesMixedCaseIdentifiers");
852: rows[12].add(new Boolean(d.storesMixedCaseIdentifiers()));
853: // boolean storesMixedCaseIdentifiers() throws SQLException;
854: rows[13].add("supportsMixedCaseQuotedIdentifiers");
855: rows[13].add(new Boolean(d.supportsMixedCaseQuotedIdentifiers()));
856: // boolean supportsMixedCaseQuotedIdentifiers() throws SQLException;
857: rows[14].add("storesUpperCaseQuotedIdentifiers");
858: rows[14].add(new Boolean(d.storesUpperCaseQuotedIdentifiers()));
859: // boolean storesUpperCaseQuotedIdentifiers() throws SQLException;
860: rows[15].add("storesLowerCaseQuotedIdentifiers");
861: rows[15].add(new Boolean(d.storesLowerCaseQuotedIdentifiers()));
862: //boolean storesLowerCaseQuotedIdentifiers() throws SQLException;
863: rows[16].add("storesMixedCaseQuotedIdentifiers");
864: rows[16].add(new Boolean(d.storesMixedCaseQuotedIdentifiers()));
865: // boolean storesMixedCaseQuotedIdentifiers() throws SQLException;
866: rows[17].add("supportsAlterTableWithAddColumn");
867: rows[17].add(new Boolean(d.supportsAlterTableWithAddColumn()));
868: // boolean supportsAlterTableWithAddColumn() throws SQLException;
869: rows[18].add("supportsAlterTableWithDropColumn");
870: rows[18].add(new Boolean(d.supportsAlterTableWithDropColumn()));
871: // boolean supportsAlterTableWithDropColumn() throws SQLException;
872: rows[19].add("nullPlusNonNullIsNull");
873: rows[19].add(new Boolean(d.nullPlusNonNullIsNull()));
874: // boolean nullPlusNonNullIsNull() throws SQLException;
875: rows[20].add("supportsConvert");
876: rows[20].add(new Boolean(d.supportsConvert()));
877: // boolean supportsConvert() throws SQLException;
878:
879: // boolean supportsConvert(int fromType, int toType) throws SQLException;
880: rows[21].add("supportsTableCorrelationNames");
881: rows[21].add(new Boolean(d.supportsTableCorrelationNames()));
882: // boolean supportsTableCorrelationNames() throws SQLException;
883: rows[22].add("supportsDifferentTableCorrelationNames");
884: rows[22].add(new Boolean(d.supportsDifferentTableCorrelationNames()));
885: // boolean supportsDifferentTableCorrelationNames() throws SQLException;
886: rows[23].add("supportsExpressionsInOrderBy");
887: rows[23].add(new Boolean(d.supportsExpressionsInOrderBy()));
888: // boolean supportsExpressionsInOrderBy() throws SQLException;
889: rows[24].add("supportsOrderByUnrelated");
890: rows[24].add(new Boolean(d.supportsOrderByUnrelated()));
891: // boolean supportsOrderByUnrelated() throws SQLException;
892: rows[25].add("supportsGroupBy");
893: rows[25].add(new Boolean(d.supportsGroupBy()));
894: // boolean supportsGroupBy() throws SQLException;
895: rows[26].add("supportsGroupByUnrelated");
896: rows[26].add(new Boolean(d.supportsGroupByUnrelated()));
897: // boolean supportsGroupByUnrelated() throws SQLException;
898: rows[27].add("supportsGroupByBeyondSelect");
899: rows[27].add(new Boolean(d.supportsGroupByBeyondSelect()));
900: // boolean supportsGroupByBeyondSelect() throws SQLException;
901: rows[28].add("supportsLikeEscapeClause");
902: rows[28].add(new Boolean(d.supportsLikeEscapeClause()));
903: // boolean supportsLikeEscapeClause() throws SQLException;
904: rows[29].add("supportsMultipleResultSets");
905: rows[29].add(new Boolean(d.supportsMultipleResultSets()));
906: // boolean supportsMultipleResultSets() throws SQLException;
907: rows[30].add("supportsMultipleTransactions");
908: rows[30].add(new Boolean(d.supportsMultipleTransactions()));
909: // boolean supportsMultipleTransactions() throws SQLException;
910: rows[31].add("supportsNonNullableColumns");
911: rows[31].add(new Boolean(d.supportsNonNullableColumns()));
912: // boolean supportsNonNullableColumns() throws SQLException;
913: rows[32].add("supportsMinimumSQLGrammar");
914: rows[32].add(new Boolean(d.supportsMinimumSQLGrammar()));
915: // boolean supportsMinimumSQLGrammar() throws SQLException;
916: rows[33].add("supportsCoreSQLGrammar");
917: rows[33].add(new Boolean(d.supportsCoreSQLGrammar()));
918: // boolean supportsCoreSQLGrammar() throws SQLException;
919: rows[34].add("supportsExtendedSQLGrammar");
920: rows[34].add(new Boolean(d.supportsExtendedSQLGrammar()));
921: // boolean supportsExtendedSQLGrammar() throws SQLException;
922: rows[35].add("supportsANSI92EntryLevelSQL");
923: rows[35].add(new Boolean(d.supportsANSI92EntryLevelSQL()));
924: // boolean supportsANSI92EntryLevelSQL() throws SQLException;
925: rows[36].add("supportsANSI92IntermediateSQL");
926: rows[36].add(new Boolean(d.supportsANSI92IntermediateSQL()));
927: //boolean supportsANSI92IntermediateSQL() throws SQLException;
928: rows[37].add("supportsANSI92FullSQL");
929: rows[37].add(new Boolean(d.supportsANSI92FullSQL()));
930: //boolean supportsANSI92FullSQL() throws SQLException;
931: rows[38].add("supportsIntegrityEnhancementFacility");
932: rows[38].add(new Boolean(d.supportsIntegrityEnhancementFacility()));
933: //boolean supportsIntegrityEnhancementFacility() throws SQLException;
934: rows[39].add("supportsOuterJoins");
935: rows[39].add(new Boolean(d.supportsOuterJoins()));
936: //boolean supportsOuterJoins() throws SQLException;
937: rows[40].add("supportsFullOuterJoins");
938: rows[40].add(new Boolean(d.supportsFullOuterJoins()));
939: //boolean supportsFullOuterJoins() throws SQLException;
940: rows[41].add("supportsLimitedOuterJoins");
941: rows[41].add(new Boolean(d.supportsLimitedOuterJoins()));
942: //boolean supportsLimitedOuterJoins() throws SQLException;
943: rows[42].add("isCatalogAtStart");
944: rows[42].add(new Boolean(d.isCatalogAtStart()));
945: //boolean isCatalogAtStart() throws SQLException;
946: rows[43].add("supportsSchemasInDataManipulation");
947: rows[43].add(new Boolean(d.supportsSchemasInDataManipulation()));
948: //boolean supportsSchemasInDataManipulation() throws SQLException;
949: rows[44].add("supportsSchemasInProcedureCalls");
950: rows[44].add(new Boolean(d.supportsSchemasInProcedureCalls()));
951: //boolean supportsSchemasInProcedureCalls() throws SQLException;
952: rows[45].add("supportsSchemasInTableDefinitions");
953: rows[45].add(new Boolean(d.supportsSchemasInTableDefinitions()));
954: //boolean supportsSchemasInTableDefinitions() throws SQLException;
955: rows[46].add("supportsSchemasInIndexDefinitions");
956: rows[46].add(new Boolean(d.supportsSchemasInIndexDefinitions()));
957: //boolean supportsSchemasInIndexDefinitions() throws SQLException;
958: rows[47].add("supportsSchemasInPrivilegeDefinitions");
959: rows[47].add(new Boolean(d.supportsSchemasInPrivilegeDefinitions()));
960: //boolean supportsSchemasInPrivilegeDefinitions() throws SQLException;
961: rows[48].add("supportsCatalogsInDataManipulation");
962: rows[48].add(new Boolean(d.supportsCatalogsInDataManipulation()));
963: //boolean supportsCatalogsInDataManipulation() throws SQLException;
964: rows[49].add("supportsCatalogsInProcedureCalls");
965: rows[49].add(new Boolean(d.supportsCatalogsInProcedureCalls()));
966: //boolean supportsCatalogsInProcedureCalls() throws SQLException;
967: rows[50].add("supportsCatalogsInTableDefinitions");
968: rows[50].add(new Boolean(d.supportsCatalogsInTableDefinitions()));
969: //boolean supportsCatalogsInTableDefinitions() throws SQLException;
970: rows[51].add("supportsCatalogsInIndexDefinitions");
971: rows[51].add(new Boolean(d.supportsCatalogsInIndexDefinitions()));
972: //boolean supportsCatalogsInIndexDefinitions() throws SQLException;
973: rows[52].add("supportsCatalogsInPrivilegeDefinitions");
974: rows[52].add(new Boolean(d.supportsCatalogsInPrivilegeDefinitions()));
975: //boolean supportsCatalogsInPrivilegeDefinitions() throws SQLException;
976: rows[53].add("supportsPositionedDelete");
977: rows[53].add(new Boolean(d.supportsPositionedDelete()));
978: //boolean supportsPositionedDelete() throws SQLException;
979: rows[54].add("supportsPositionedUpdate");
980: rows[54].add(new Boolean(d.supportsPositionedUpdate()));
981: //boolean supportsPositionedUpdate() throws SQLException;
982: rows[55].add("supportsSelectForUpdate");
983: rows[55].add(new Boolean(d.supportsSelectForUpdate()));
984: //boolean supportsSelectForUpdate() throws SQLException;
985: rows[56].add("supportsStoredProcedures");
986: rows[56].add(new Boolean(d.supportsStoredProcedures()));
987: //boolean supportsStoredProcedures() throws SQLException;
988: rows[57].add("supportsSubqueriesInComparisons");
989: rows[57].add(new Boolean(d.supportsSubqueriesInComparisons()));
990: //boolean supportsSubqueriesInComparisons() throws SQLException;
991: rows[58].add("supportsSubqueriesInExists");
992: rows[58].add(new Boolean(d.supportsSubqueriesInExists()));
993: //boolean supportsSubqueriesInExists() throws SQLException;
994: rows[59].add("supportsSubqueriesInIns");
995: rows[59].add(new Boolean(d.supportsSubqueriesInIns()));
996: //boolean supportsSubqueriesInIns() throws SQLException;
997: rows[60].add("supportsSubqueriesInQuantifieds");
998: rows[60].add(new Boolean(d.supportsSubqueriesInQuantifieds()));
999: //boolean supportsSubqueriesInQuantifieds() throws SQLException;
1000: rows[61].add("supportsCorrelatedSubqueries");
1001: rows[61].add(new Boolean(d.supportsCorrelatedSubqueries()));
1002: //boolean supportsCorrelatedSubqueries() throws SQLException;
1003: rows[62].add("supportsUnion");
1004: rows[62].add(new Boolean(d.supportsUnion()));
1005: //boolean supportsUnion() throws SQLException;
1006: rows[63].add("supportsUnionAll");
1007: rows[63].add(new Boolean(d.supportsUnionAll()));
1008: //boolean supportsUnionAll() throws SQLException;
1009: rows[64].add("supportsOpenCursorsAcrossCommit");
1010: rows[64].add(new Boolean(d.supportsOpenCursorsAcrossCommit()));
1011: //boolean supportsOpenCursorsAcrossCommit() throws SQLException;
1012: rows[65].add("supportsOpenCursorsAcrossRollback");
1013: rows[65].add(new Boolean(d.supportsOpenCursorsAcrossRollback()));
1014: //boolean supportsOpenCursorsAcrossRollback() throws SQLException;
1015: rows[66].add("supportsOpenStatementsAcrossCommit");
1016: rows[66].add(new Boolean(d.supportsOpenStatementsAcrossCommit()));
1017: //boolean supportsOpenStatementsAcrossCommit() throws SQLException;
1018: rows[67].add("supportsOpenStatementsAcrossRollback");
1019: rows[67].add(new Boolean(d.supportsOpenStatementsAcrossRollback()));
1020: //boolean supportsOpenStatementsAcrossRollback() throws SQLException;
1021: rows[68].add("doesMaxRowSizeIncludeBlobs");
1022: rows[68].add(new Boolean(d.doesMaxRowSizeIncludeBlobs()));
1023: //boolean doesMaxRowSizeIncludeBlobs() throws SQLException;
1024: rows[69].add("supportsTransactions");
1025: rows[69].add(new Boolean(d.supportsTransactions()));
1026: //boolean supportsTransactions() throws SQLException;
1027: rows[70].add("supportsTransactionIsolationLevel");
1028: rows[70].add(new Boolean(d.supportsTransactionIsolationLevel(1)));
1029: //boolean supportsTransactionIsolationLevel(int level) throws SQLException;
1030: rows[71].add("supportsDataDefinitionAndDataManipulationTransactions");
1031: rows[71].add(new Boolean(d.supportsDataDefinitionAndDataManipulationTransactions()));
1032: //boolean supportsDataDefinitionAndDataManipulationTransactions() throws SQLException;
1033: rows[72].add("supportsDataManipulationTransactionsOnly");
1034: rows[72].add(new Boolean(d.supportsDataManipulationTransactionsOnly()));
1035: //boolean supportsDataManipulationTransactionsOnly() throws SQLException;
1036: rows[73].add("dataDefinitionCausesTransactionCommit");
1037: rows[73].add(new Boolean(d.dataDefinitionCausesTransactionCommit()));
1038: //boolean dataDefinitionCausesTransactionCommit() throws SQLException;
1039: rows[74].add("dataDefinitionIgnoredInTransactions");
1040: rows[74].add(new Boolean(d.dataDefinitionIgnoredInTransactions()));
1041: //boolean dataDefinitionIgnoredInTransactions() throws SQLException;
1042: rows[75].add("getMaxBinaryLiteralLength");
1043: rows[75].add(new Integer(d.getMaxBinaryLiteralLength()));
1044: // int getMaxBinaryLiteralLength() throws SQLException;
1045: rows[76].add("getMaxCharLiteralLength");
1046: rows[76].add(new Integer(d.getMaxCharLiteralLength()));
1047: //int getMaxCharLiteralLength() throws SQLException;
1048: rows[77].add("getMaxColumnNameLength");
1049: rows[77].add(new Integer(d.getMaxColumnNameLength()));
1050: // int getMaxColumnNameLength() throws SQLException;
1051: rows[78].add("getMaxColumnsInGroupBy");
1052: rows[78].add(new Integer(d.getMaxColumnsInGroupBy()));
1053: //int getMaxColumnsInGroupBy() throws SQLException;
1054: rows[79].add("getMaxColumnsInIndex");
1055: rows[79].add(new Integer(d.getMaxColumnsInIndex()));
1056: //int getMaxColumnsInIndex() throws SQLException;
1057: rows[80].add("getMaxColumnsInOrderBy");
1058: rows[80].add(new Integer(d.getMaxColumnsInOrderBy()));
1059: //int getMaxColumnsInOrderBy() throws SQLException;
1060: rows[81].add("getMaxColumnsInSelect");
1061: rows[81].add(new Integer(d.getMaxColumnsInSelect()));
1062: //int getMaxColumnsInSelect() throws SQLException;
1063: rows[82].add("getMaxColumnsInTable");
1064: rows[82].add(new Integer(d.getMaxColumnsInTable()));
1065: //int getMaxColumnsInTable() throws SQLException;
1066: rows[83].add("getMaxConnections");
1067: rows[83].add(new Integer(d.getMaxConnections()));
1068: //int getMaxConnections() throws SQLException;
1069: rows[84].add("getMaxCursorNameLength");
1070: rows[84].add(new Integer(d.getMaxCursorNameLength()));
1071: // int getMaxCursorNameLength() throws SQLException;
1072: rows[85].add("getMaxIndexLength");
1073: rows[85].add(new Integer(d.getMaxIndexLength()));
1074: //int getMaxIndexLength() throws SQLException;
1075: rows[86].add("getMaxSchemaNameLength");
1076: rows[86].add(new Integer(d.getMaxSchemaNameLength()));
1077: //int getMaxSchemaNameLength() throws SQLException;
1078: rows[87].add("getMaxProcedureNameLength");
1079: rows[87].add(new Integer(d.getMaxProcedureNameLength()));
1080: //int getMaxProcedureNameLength() throws SQLException;
1081: rows[88].add("getMaxCatalogNameLength");
1082: rows[88].add(new Integer(d.getMaxCatalogNameLength()));
1083: //int getMaxCatalogNameLength() throws SQLException;
1084: rows[89].add("getMaxRowSize");
1085: rows[89].add(new Integer(d.getMaxRowSize()));
1086: //int getMaxRowSize() throws SQLException;
1087: rows[90].add("getMaxStatementLength");
1088: rows[90].add(new Integer(d.getMaxStatementLength()));
1089: //int getMaxStatementLength() throws SQLException;
1090: rows[91].add("getMaxStatements");
1091: rows[91].add(new Integer(d.getMaxStatements()));
1092: //int getMaxStatements() throws SQLException;
1093: rows[92].add("getMaxTableNameLength");
1094: rows[92].add(new Integer(d.getMaxTableNameLength()));
1095: //int getMaxTableNameLength() throws SQLException;
1096: rows[93].add("getMaxTablesInSelect");
1097: rows[93].add(new Integer(d.getMaxTablesInSelect()));
1098: //int getMaxTablesInSelect() throws SQLException;
1099: rows[94].add("getMaxUserNameLength");
1100: rows[94].add(new Integer(d.getMaxUserNameLength()));
1101: // int getMaxUserNameLength() throws SQLException;
1102: rows[95].add("getDefaultTransactionIsolation");
1103: rows[95].add(new Integer(d.getDefaultTransactionIsolation()));
1104: //int getDefaultTransactionIsolation() throws SQLException;
1105:
1106: rows[96].add("getDatabaseProductName");
1107: rows[96].add(d.getDatabaseProductName());
1108: // String getDatabaseProductName() throws SQLException;
1109: rows[97].add("getDatabaseProductVersion");
1110: rows[97].add(d.getDatabaseProductVersion());
1111: //String getDatabaseProductVersion() throws SQLException;
1112:
1113: rows[98].add("getURL");
1114: rows[98].add(d.getURL());
1115: //String getURL() throws SQLException;
1116: rows[99].add("getUserName");
1117: rows[99].add(d.getUserName());
1118: //String getUserName() throws SQLException;
1119: rows[100].add("getDriverName");
1120: rows[100].add(d.getDriverName());
1121: // String getDriverName() throws SQLException;
1122: rows[101].add("getIdentifierQuoteString");
1123: rows[101].add(d.getIdentifierQuoteString());
1124: //String getIdentifierQuoteString() throws SQLException;
1125:
1126: rows[102].add("getDriverVersion");
1127: rows[102].add(d.getDriverVersion());
1128: //String getDriverVersion() throws SQLException;
1129: rows[103].add("getDriverMajorVersion");
1130: rows[103].add(new Integer(d.getDriverMajorVersion()));
1131: //int getDriverMajorVersion();
1132: rows[104].add("getDriverMinorVersion");
1133: rows[104].add(new Integer(d.getDriverMinorVersion()));
1134: //int getDriverMinorVersion();
1135: rows[105].add("getSQLKeywords");
1136: rows[105].add(d.getSQLKeywords());
1137: //String getSQLKeywords() throws SQLException;
1138: rows[106].add("getNumericFunctions");
1139: rows[106].add(d.getNumericFunctions());
1140: //String getNumericFunctions() throws SQLException;
1141: rows[107].add("getStringFunctions");
1142: rows[107].add(d.getStringFunctions());
1143: // String getStringFunctions() throws SQLException;
1144: rows[108].add("getSystemFunctions");
1145: rows[108].add(d.getSystemFunctions());
1146: //String getSystemFunctions() throws SQLException;
1147: rows[109].add("getTimeDateFunctions");
1148: rows[109].add(d.getTimeDateFunctions());
1149: //String getTimeDateFunctions() throws SQLException;
1150: rows[110].add("getSearchStringEscape");
1151: rows[110].add(d.getSearchStringEscape());
1152: //String getSearchStringEscape() throws SQLException;
1153: rows[111].add("getExtraNameCharacters");
1154: rows[111].add(d.getExtraNameCharacters());
1155: //String getExtraNameCharacters() throws SQLException;
1156: rows[112].add("getSchemaTerm");
1157: rows[112].add(d.getSchemaTerm());
1158: //String getSchemaTerm() throws SQLException;
1159: rows[113].add("getProcedureTerm");
1160: rows[113].add(d.getProcedureTerm());
1161: //String getProcedureTerm() throws SQLException;
1162: rows[114].add("getCatalogTerm");
1163: rows[114].add(d.getCatalogTerm());
1164: // String getCatalogTerm() throws SQLException;
1165: rows[115].add("getCatalogSeparator");
1166: rows[115].add(d.getCatalogSeparator());
1167: //String getCatalogSeparator() throws SQLException;
1168:
1169: /*
1170: boolean supportsResultSetType(int type) throws SQLException;
1171:
1172: boolean supportsResultSetConcurrency(int type, int concurrency) throws SQLException;
1173:
1174: boolean ownUpdatesAreVisible(int type) throws SQLException;
1175:
1176: boolean ownDeletesAreVisible(int type) throws SQLException;
1177:
1178: boolean ownInsertsAreVisible(int type) throws SQLException;
1179:
1180: boolean othersUpdatesAreVisible(int type) throws SQLException;
1181:
1182: boolean othersDeletesAreVisible(int type) throws SQLException;
1183:
1184: boolean othersInsertsAreVisible(int type) throws SQLException;
1185: boolean updatesAreDetected(int type) throws SQLException;
1186: boolean deletesAreDetected(int type) throws SQLException;
1187:
1188: boolean insertsAreDetected(int type) throws SQLException;
1189: */
1190: // not in filemaker
1191: // rows[96].add("supportsBatchUpdates");
1192: // rows[96].add(new Boolean(d.supportsBatchUpdates()));
1193: //boolean supportsBatchUpdates() throws SQLException;
1194:
1195: /*
1196: ResultSet getProcedures(String catalog, String schemaPattern, String procedureNamePattern) throws SQLException;
1197:
1198: ResultSet getProcedureColumns(String catalog, String schemaPattern, String procedureNamePattern, String columnNamePattern) throws SQLException;
1199: ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String types[]) throws SQLException;
1200: ResultSet getSchemas() throws SQLException;
1201: ResultSet getCatalogs() throws SQLException;
1202: ResultSet getTableTypes() throws SQLException;
1203: ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) throws SQLException;
1204: ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException;
1205: ResultSet getImportedKeys(String catalog, String schema, String table) throws SQLException;
1206: ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException;
1207: ResultSet getCrossReference(String primaryCatalog, String primarySchema, String primaryTable, String foreignCatalog, String foreignSchema, String foreignTable) throws SQLException;
1208: ResultSet getTypeInfo() throws SQLException;
1209: ResultSet getIndexInfo(String catalog, String schema, String table, boolean unique, boolean approximate) throws SQLException;
1210:
1211: ResultSet getUDTs(String catalog, String schemaPattern, String typeNamePattern, int[] types) throws SQLException;
1212:
1213: Connection getConnection() throws SQLException;
1214:
1215: */
1216: for (int i = 0; i < rows.length; ++i)
1217: cols.add(rows[i]);
1218: data[0] = cols;
1219: data[1] = columnNames;
1220: return data;
1221: }
1222: public Vector getNextRow() throws Exception
1223: {
1224:
1225: if (result == null)
1226: return null;
1227: boolean check = false;
1228: ResultSet resultSet = result;
1229: ResultSetMetaData metaData = resultSet.getMetaData();
1230: int columnCount = metaData.getColumnCount();
1231: Vector tableData = new Vector();
1232: check = resultSet.next();
1233: // System.out.println("hallo check "+check);
1234: if (!check)
1235: return null;
1236: Vector tableRow = new Vector(), m_columnClasses = new Vector();
1237: for (int i = 1; i <= columnCount; i++)
1238: {
1239: // repeating fields and fields from related databases may contain
1240: // multliple data values; the data values are stored using
1241: // a Vector which is then added to the tableRow
1242: // if (metaData instanceof ResultSetMetaDataExt)
1243: if ((metaData instanceof ResultSetMetaDataExt) && (((ResultSetMetaDataExt) metaData).isRelated(i) || ((ResultSetMetaDataExt) metaData).isRepeating(i)))
1244: {
1245: //System.out.println("Related fields");
1246: // retrieve the repeating or related field contents as a
1247: // com.fmi.jdbc.Array via the ResultSet.getObject method
1248: com.fmi.jdbc.Array array = (com.fmi.jdbc.Array) resultSet.getObject(i);
1249: // create a Vector for storing all of the data values
1250: ArrayList columnData = new ArrayList();
1251: try
1252: {
1253:
1254: // call the Array.getStringArray method since the data will
1255: // only be displayed
1256: Object[] fieldData = (Object[]) array.getArray();
1257:
1258: if (fieldData != null)
1259: {
1260: // add each value to the Vector
1261: for (int j = 0; j < fieldData.length; j++)
1262: {
1263: if (fieldData[j] != null)
1264: columnData.add(fieldData[j]);
1265: }
1266: }
1267: } catch (Exception e)
1268: {
1269: //System.out.println(e);
1270: }
1271:
1272: if (columnData.isEmpty())
1273: tableRow.add(null);
1274: else
1275: tableRow.addElement(columnData);
1276: //System.out.println(columnData);
1277: //System.out.println("Related fields"+columnData.size()+" "+tableRow.size());
1278:
1279: // m_columnClasses.addElement(java.util.Vector.class);
1280: } else if (metaData.getColumnType(i) == Types.LONGVARBINARY)
1281: {
1282: // use the ResultSet.getObject method for retrieving images
1283: // from FileMaker Pro container fields; the ResultSet.getObject
1284: // method returns a java.awt.Image object for FileMaker Pro
1285: // container fields
1286:
1287: try
1288: {
1289: tableRow.addElement(resultSet.getObject(i));
1290: } catch (Exception e)
1291: {
1292: // TODO Auto-generated catch block
1293: // e.printStackTrace();
1294: tableRow.addElement(null);
1295: }
1296: // m_columnClasses.addElement(java.awt.Image.class);
1297: } else if (metaData.getColumnType(i) == Types.TIME)
1298: {
1299: // use the ResultSet.getObject method for retieving images
1300: // from FileMaker Pro container fields; the ResultSet.getObject
1301: // method returns a java.awt.Image object for FileMaker Pro
1302: // container fields
1303: try
1304: {
1305: tableRow.addElement(resultSet.getTime(i).toString());
1306: // m_columnClasses.addElement(java.sql.Time.class);
1307: } catch (Exception e)
1308: {
1309:
1310: String value = resultSet.getString(i);
1311: if (value != null)
1312: {
1313: //System.out.println("SQLTime new "+Time.valueOf("17:00:00").toString());
1314: int index = 0;
1315: for (int j = 0; j < value.length(); ++j)
1316: {
1317: if (!Character.isLetter(value.charAt(j)))
1318: index = j + 1;
1319: else
1320: break;
1321: }
1322:
1323: tableRow.addElement(value.substring(0, index));
1324: //m_columnClasses.addElement(java.sql.Time.class);
1325: } else
1326: tableRow.add(null);
1327: // m_columnClasses.addElement(String.class);
1328: } // to catch
1329:
1.10 rogo 1330: } else if (metaData.getColumnType(i) == Types.INTEGER)
1.25 rogo 1331: {
1332: // use the ResultSet.getObject method for retieving images
1333: // from FileMaker Pro container fields; the ResultSet.getObject
1334: // method returns a java.awt.Image object for FileMaker Pro
1335: // container fields
1.10 rogo 1336:
1.25 rogo 1337: tableRow.addElement(new Integer(resultSet.getInt(i)));
1338: // m_columnClasses.addElement(java.sql.Date.class);
1339: } else if (metaData.getColumnType(i) == Types.DATE)
1340: {
1.1 rogo 1341: // use the ResultSet.getObject method for retieving images
1342: // from FileMaker Pro container fields; the ResultSet.getObject
1343: // method returns a java.awt.Image object for FileMaker Pro
1344: // container fields
1.25 rogo 1345: try
1346: {
1347: tableRow.addElement(resultSet.getDate(i));
1.1 rogo 1348:
1.25 rogo 1349: } catch (Exception e)
1350: {
1351: // work around for parse bug in FM JDBC Driver
1352: // for dates of format dd-mm-yyyy
1353: String date = resultSet.getString(i);
1354: date = date.replace('-', '.');
1355: java.text.DateFormat dateFormat = DateFormat.getDateInstance(DateFormat.SHORT, Locale.GERMAN);
1356: java.util.Date d = dateFormat.parse(date);
1357: // Calendar cal=Calendar.getInstance(Locale.GERMAN);
1358: // cal.setTime(d);
1359: // date=(cal.get(Calendar.YEAR))+"-"+(cal.get(Calendar.MONTH)+1)+"-"+cal.get(Calendar.DATE);
1360: tableRow.addElement(new java.sql.Date(d.getTime()));
1361: System.out.println("Date " + date);
1362: }
1.1 rogo 1363: // m_columnClasses.addElement(java.sql.Date.class);
1364: } else if (metaData.getColumnTypeName(i) == "NUMBER")
1365: {
1366: // use the ResultSet.getObject method for retieving images
1367: // from FileMaker Pro container fields; the ResultSet.getObject
1368: // method returns a java.awt.Image object for FileMaker Pro
1369: // container fields
1370: try
1371: {
1.26 rogo 1372: Double value =new Double(resultSet.getDouble(i));
1373:
1374:
1375: // tableRow.addElement(new Double(resultSet.getDouble(i)));
1376: String tVal =value.toString();
1377: tVal = tVal.substring(tVal.indexOf('.')+1);
1378: boolean checkMe = tVal.length()==1&&tVal.equals("0");
1.27 rogo 1379: // System.out.println("check was"+checkMe+" "+tVal);
1.26 rogo 1380: if(checkMe)
1381: tableRow.addElement(new Integer(value.intValue()));
1382: else
1383: tableRow.addElement(value);
1384:
1.1 rogo 1385: // m_columnClasses.addElement(Integer.class);
1386:
1387: } catch (Exception e)
1388: {
1389:
1390: StringBuffer number = new StringBuffer();
1391: String value = resultSet.getString(i);
1392: System.out.println(value);
1393: for (int c = 0; c < value.length(); ++c)
1394: {
1395: if (Character.isDigit(value.charAt(c)))
1396: {
1397: number.append(value.charAt(c));
1398: }
1399: }
1400: if (number.length() > 0)
1401: {
1402: tableRow.addElement(null);
1403: // m_columnClasses.addElement(Integer.class);
1404: } else
1405: tableRow.addElement(null);
1406: }
1407: } else
1408: {
1409: // all other field values are retrieved as strings and
1410: // added to the tableRow Vector
1411: // System.out.println("row "+resultSet.getString(i));
1412: try
1413: {
1414: byte[] b = null;
1415: if (metaData instanceof ResultSetMetaDataExt)
1416: b = resultSet.getBytes(i);
1.25 rogo 1417: /* if (b != null)
1418: {
1419: java.io.ByteArrayInputStream stream = (java.io.ByteArrayInputStream) resultSet.getBinaryStream(i);
1420: // System.out.println(" stream "+resultSet.getBinaryStream(i));
1421: byte[] c = new byte[stream.available()];
1422: int length = stream.read(c, 0, c.length);
1423: int count = 0;
1424: b = new byte[c.length];
1425: for (int n = 0; n < length; ++n)
1426: {
1427:
1428: if (c[n] != 0)
1429: {
1430: // System.out.println(c[n]+" "+(int)'?'+" "+(char)c[n]+" "+count+" "+b.length);
1431: b[count++] = c[n];
1432: }
1433: }
1434: byte[] bCopy = new byte[count];
1435: System.arraycopy(b, 0, bCopy, 0, count);
1436: b = bCopy;
1437: }*/
1.1 rogo 1438: String utf8 = null;
1.24 rogo 1439: utf8 = (b == null) ? null : new String(b);
1.1 rogo 1440: if (metaData instanceof ResultSetMetaDataExt)
1.24 rogo 1441: {
1442: String rowElement = "";
1443: if (b != null)
1444: {
1445: rowElement = resultSet.getString(i);
1.25 rogo 1446: if (useNormanToUnicodeMapper)
1447: rowElement = Convert.normanToUnicode(rowElement);
1.24 rogo 1448: tableRow.addElement(rowElement);
1449:
1450: } else
1451: tableRow.addElement(null);
1.25 rogo 1452: } else
1.1 rogo 1453: {
1.25 rogo 1454: if (url.toLowerCase().indexOf("odbc") >= 0)
1455: {
1456: byte[] val = resultSet.getBytes(i);
1457: for (int j = 0; j < val.length; ++j)
1458: System.out.println(Integer.toHexString(val[j]));
1459: tableRow.addElement((val == null) ? null : new String(val));
1460:
1461: } else
1462: // byte[] val = resultSet.getBytes(i);
1463: tableRow.add(resultSet.getString(i));
1.1 rogo 1464: //tableRow.addElement((val==null) ? null:new String(val,"UTF-8"));
1465: }
1466: } catch (Exception e)
1467: {
1.24 rogo 1468: System.out.println("Hey I got an error" + e);
1.1 rogo 1469: e.printStackTrace();
1470: }
1471: // m_columnClasses.addElement(java.lang.String.class);
1472: }
1473: }
1474: // tableData.addElement(tableRow);
1475: if (check)
1476: return tableRow;
1477: else
1478: return null;
1479: }
1480: class ConnectionPool
1481: {
1482: String user = "", passwd = "", url = "";
1483: Connection con;
1484: public ConnectionPool(String url, String user, String passwd, Connection con)
1485: {
1486: this.con = con;
1487: this.user = user;
1488: this.passwd = passwd;
1489: this.url = url;
1490: }
1491:
1492: }
1493: public String getQC()
1494: {
1495: // if (connection == null)
1496: // return "";
1497:
1498: // check if connection null if null try to get one
1499: if (connection == null)
1500: try
1501: {
1502: getConnection();
1503: } catch (Exception e)
1504: {
1505: if (FM2SQL.debug)
1506: System.out.println("cannot get a connection");
1507: }
1508: if (connection == null)
1509: {
1510: if (url.toLowerCase().indexOf("fmpro") >= 0 || url.toLowerCase().indexOf("postgres") >= 0)
1511: quoteChar = "\"";
1512: else if (url.toLowerCase().indexOf("mysql") >= 0)
1513: quoteChar = "`";
1514: }
1515: if (quoteChar == null)
1516: quoteChar = "\""; // needed for postgres
1517: return quoteChar;
1518: }
1519: public int getRowCount(String query) throws SQLException
1520: {
1521: String table = query.substring(query.indexOf("from") + 4).trim();
1522: int index = table.indexOf(" ");
1523: table = table.substring(0, (index >= 0) ? index : table.length());
1524: System.out.println(table);
1525: Statement stm = null;
1526:
1527: if (metaData instanceof ResultSetMetaDataExt)
1528: return 1000;
1529: if (!connection.isClosed())
1530: stm = connection.createStatement();
1531: stm.setMaxRows(1);
1532: ResultSet resultSet = stm.executeQuery("select count(*) from " + table);
1533: resultSet.next();
1534: return resultSet.getInt(1);
1535: }
1.25 rogo 1536: public TreeSet getIDVector(String id, String table, String query, int numHits) throws Exception
1537: {
1538: TreeSet t = new TreeSet();
1539: getConnection();
1540: ResultSet result = this.result;
1541: String subQuery = query.substring(query.lastIndexOf(table) + table.length() + 1);
1542: System.out.println("subQuery " + subQuery);
1.33 rogo 1543: makeQuery("select " + getQC()+id+getQC() + " from " + getQC() + table + getQC() + subQuery, numHits);
1.25 rogo 1544: while (true)
1545: {
1.6 rogo 1546: Vector vec = getNextRow();
1547: if (vec == null)
1548: break;
1549: t.add(vec.get(0));
1.25 rogo 1550: }
1551: this.result = result;
1552: metaData = (this.result == null) ? null : this.result.getMetaData();
1.6 rogo 1553: return t;
1.25 rogo 1554: }
1.24 rogo 1555: /**
1556: * @return
1557: */
1558: public boolean isUseNormanToUnicodeMapper()
1559: {
1560: return useNormanToUnicodeMapper;
1561: }
1562:
1563: /**
1564: * @param b
1565: */
1566: public void setUseNormanToUnicodeMapper(boolean b)
1567: {
1568: useNormanToUnicodeMapper = b;
1569: }
1.28 rogo 1570: /**
1571: *
1572: */
1573: protected void closeAllConnections()
1574: {
1575: Enumeration enumeration = connectionPool.elements();
1576: while(enumeration.hasMoreElements())
1577: {
1578: ConnectionPool conPol =(ConnectionPool)enumeration.nextElement();
1579: try
1580: {
1581: System.out.println(conPol);
1582: conPol.con.close();
1583: } catch (SQLException e)
1584: {
1585: // TODO Auto-generated catch block
1586: e.printStackTrace();
1587: }
1588: }
1589:
1590: }
1.31 rogo 1591: /**
1592: * @param indexField
1593: */
1594: public String getColumnType(String indexField) throws SQLException
1595: {
1596: Vector names=getColumnNames();
1597: for (int i = 0; i < names.size(); i++)
1598: {
1599: if(names.get(i).toString().equals(indexField))
1600: {
1601: System .out.println("found field "+names.get(i)+" "+metaData.getColumnTypeName(i+1));
1602: return metaData.getColumnTypeName(i+1);
1603:
1604: }
1605: }
1606: return "";
1607: }
1.24 rogo 1608:
1.5 rogo 1609: }
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/cvs/cvsweb/icons/back.gif){kind=icon}
Return to DBBean.java CVS log ![[TXT]](/cvs/cvsweb/icons/text.gif){kind=icon}
![[DIR]](/cvs/cvsweb/icons/dir.gif){kind=icon}
Up to [Repository] / FM2SQL