/*
* DBBean.java -- Class that ecapsulates all database actions
* Filemake to SQL Converter
* Copyright (C) 2004 Robert Gordesch (rogo@mpiwg-berlin.mpg.de)
* This program is free software; you can redistribute it and/or modify it
* under the terms of the GNU General Public License as published by the Free
* Software Foundation; either version 2 of the License, or (at your option)
* any later version. Please read license.txt for the full details. A copy of
* the GPL may be found at http://www.gnu.org/copyleft/lgpl.html You should
* have received a copy of the GNU General Public License along with this
* program; if not, write to the Free Software Foundation, Inc., 59 Temple
* Place, Suite 330, Boston, MA 02111-1307 USA Created on 15.09.2003 by
* rogo
*/
import java.sql.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.util.*;
import com.fmi.jdbc.*;
/**
*
*
* DBBean - Database bean
*
*<p> a Javabean to perform queries on a JDBC Database,
* or excute any other SQL statement
* </p>
* <p>
* Usage:
* <pre>
* DBBean bean = new DBBean();
* // setting user and passwd
* bean.setUserAndPasswd("bla","bla");
* try
* {
* bean.setConnection("jdbc:fmpro:http://localhost");
* Vector names=bean.getTableNames();
* Vector[] result=bean.getQueryData(names.get(0).toString());
* // print results to screen
* for(int i=0;i<result[1].size();++i)
* {
* //print Header
* System.out.print(" "+result[1].get(i));
* }
* System.out.println();
* for(int j=0;j<result[0].size();++j)
* {
* Vector row=(Vector)result[0].get(j);
* //print rows
* for(int k=0;k<row.size();++k)
* System.out.print(" "+row.get(k));
* System.out.println();
* }
* } catch(Exception e)
* {
* System.out.println("Error while connecting to database"+ e);
* }
* </pre>
*
* </p>
* @author rogo
*/
public class DBBean
{
private boolean useNormanToUnicodeMapper = false;
Connection connection;
String url = "";
DatabaseMetaData dbMetaData;
Vector columnNames;
Vector ids = new Vector();
String user = (System.getProperty("user.name") == null) ? "" : System.getProperty("user.name"); //"postgres";
String passwd = ""; //"3333";//"rogo";
public int maxHits = 10;
ResultSet result;
String quoteChar = "";
Hashtable connectionPool = new Hashtable();
public ResultSetMetaData metaData;
// register DataBase Drivers
public static String[] jdbcDrivers = {
"com.fmi.jdbc.JdbcDriver",
"org.postgresql.Driver",
"com.mysql.jdbc.Driver",
"sun.jdbc.odbc.JdbcOdbcDriver",
"com.ddtek.jdbc.sequelink.SequeLinkDriver",
"acs.jdbc.Driver"
};
static {
for (int i = 0; i < jdbcDrivers.length; i++) {
String driverName = jdbcDrivers[i];
try {
System.out.println("trying to load jdbc driver "+driverName);
DriverManager.registerDriver((Driver) Class.forName(driverName).newInstance());
System.out.println(driverName+" loaded OK");
} catch (Exception e) {
e.printStackTrace();
}
}
// wait a maximum of 10 seconds when attempting to establish a connection
DriverManager.setLoginTimeout(10);
}
/**
* Constructs a database bean
*/
public DBBean()
{
}
/**
*Constructs a database bean
* and tries to connect to database
*specified in the jdbcConnectionURL
* @param jdbcConnectionURL url to connect to database
*/
public DBBean(String jdbcConnectionURL) throws Exception
{
this.url = jdbcConnectionURL;
connection = getConnection();
if (connection == null)
return;
// get the meta data for the current connection
DatabaseMetaData dbMetaData = (DatabaseMetaData) connection.getMetaData();
quoteChar = dbMetaData.getIdentifierQuoteString();
if (quoteChar == null)
quoteChar = "\""; // needed for postgres
// create the root node of the tree
// get the open tables from the current connection; the FileMaker Pro
// JDBC driver ignores all of the parameters to this method
// iterate over the table names and add them to the root node of the tree
}
/**
* sets the maximum number of hits
*/
public void setMaxHits(int maxHits)
{
this.maxHits = maxHits;
}
/**
* gets the maximum number of hits
*/
public int getMaxHits()
{
return maxHits;
}
/**
* returns the Connection if null creates a new one from the url property.
*
*/
public Connection getConnection() throws Exception
{
ConnectionPool conPool = (ConnectionPool) connectionPool.get(url);
if (conPool == null)
{
createConnection();
} else
{
if (!conPool.user.equals(user) || !conPool.passwd.equals(passwd))
{
conPool.con.close();
conPool.user = user;
conPool.passwd = passwd;
}
connection = conPool.con;
if (connection.isClosed())
{
System.out.println("Made new connection!!!");
createConnection();
// connection = DriverManager.getConnection(conPool.url, conPool.user, conPool.passwd);
conPool.con = connection;
}
}
if (url != "" && connection == null)
createConnection();
//connection = DriverManager.getConnection(url, user, passwd);
dbMetaData = connection.getMetaData();
quoteChar = dbMetaData.getIdentifierQuoteString();
if (quoteChar == null)
quoteChar = "\""; // needed for postgres
return connection;
}
private void createConnection() throws SQLException
{
// setup the properties
java.util.Properties prop = new java.util.Properties();
// prop.put("charSet", "MacCentralEurope");
prop.put("user", user);
prop.put("password", passwd);
System.out.println("url " + url);
if (url.indexOf("fmpro") >= 0)
{
// Connect to the database
connection = DriverManager.getConnection(url, prop);
System.out.println("odbc with properties inited");
} else
connection = DriverManager.getConnection(url, user, passwd);
connectionPool.put(url, new ConnectionPool(url, user, passwd, connection));
}
/**
* sets the connection of this DBBean to the database specified in the url
* property
*/
public void setConnection(String url) throws Exception
{
this.url = url;
if (url != "")
//connection = DriverManager.getConnection(url, user, passwd);
createConnection();
dbMetaData = connection.getMetaData();
quoteChar = dbMetaData.getIdentifierQuoteString();
if (quoteChar == null)
quoteChar = "\""; // needed for postgres
}
/**
* sets the connection of this DBBean to the database specified in the url
* and the url,user and passwd property of this DBBean instance
*/
public void setConnection(String url, String user, String passwd) throws Exception
{
this.user = user;
this.passwd = passwd;
this.url = url;
if (url != "")
createConnection();
// connection = DriverManager.getConnection(url, user, passwd);
dbMetaData = connection.getMetaData();
quoteChar = dbMetaData.getIdentifierQuoteString();
if (quoteChar == null)
quoteChar = "\""; // needed for postgres
}
public void setIDVector(Vector ids)
{
this.ids = ids;
}
/**
* returns a Vector containing the ID Row Name
**/
public Vector getIDVector()
{
return ids;
}
/**
* returns a Vector containing the Tablenames or an error message in the Vector
*/
public Vector getTableNames()
{
Vector tableNameVec = new Vector();
try
{
if (connection == null)
{
Vector vec = new Vector();
vec.add("no database connection");
return vec;
}
if (dbMetaData == null)
dbMetaData = connection.getMetaData();
ResultSet tableNames = dbMetaData.getTables(null, null, null, null);
// System.out.println(dbMetaData.supportsAlterTableWithAddColumn());
// iterate over the table names and add them to the root node of the tree
while (tableNames.next())
{
String tableName = tableNames.getString("TABLE_NAME");
tableNameVec.add(tableName);
}
} catch (Exception e)
{
e.printStackTrace();
}
return tableNameVec;
}
/**
* returns a Vector containing the Tablenames or an error message in the Vector
*/
public Vector getTableNames(String catalog)
{
Vector tableNameVec = new Vector();
try
{
if (connection == null)
{
Vector vec = new Vector();
vec.add("no database connection");
return vec;
}
setConnection(url.substring(0, url.lastIndexOf("/") + 1) + catalog);
if (dbMetaData == null)
dbMetaData = connection.getMetaData();
System.out.println("catalog " + catalog + " " + dbMetaData.getCatalogSeparator() + " " + url.substring(0, url.lastIndexOf("/") + 1));
ResultSet tableNames = dbMetaData.getTables(null, null, null, null);
// System.out.println(dbMetaData.supportsAlterTableWithAddColumn());
// iterate over the table names and add them to the root node of the tree
while (tableNames.next())
{
String tableName = tableNames.getString("TABLE_NAME");
tableNameVec.add(tableName);
}
} catch (Exception e)
{
e.printStackTrace();
}
return tableNameVec;
}
/**
* returns a Vector containing the Catalog or an error message in the Vector
*/
public Vector getCatalogs()
{
Vector tableNameVec = new Vector();
try
{
if (connection == null)
{
Vector vec = new Vector();
vec.add("no database connection");
return vec;
}
if (dbMetaData == null)
dbMetaData = connection.getMetaData();
ResultSet tableNames = dbMetaData.getCatalogs();
// System.out.println(dbMetaData.supportsAlterTableWithAddColumn());
// iterate over the table names and add them to the root node of the tree
System.out.println(tableNames.getMetaData().getColumnName(1));
while (tableNames.next())
{
String tableName = tableNames.getString(1);
tableNameVec.add(tableName);
//tableName = tableNames.getString(1);
//tableNameVec.add(tableName);
}
} catch (Exception e)
{
e.printStackTrace();
}
return tableNameVec;
}
/**
* returns a Vector containing the layoutNames for the specified Table
* if the database supports this otherwise Vector containing an empty String
*/
public Vector getLayoutNames(String tableName) throws SQLException
{
Vector layouts = new Vector();
if (dbMetaData instanceof DatabaseMetaDataExt)
layouts.add("");
if (dbMetaData == null)
dbMetaData = connection.getMetaData();
if (dbMetaData instanceof DatabaseMetaDataExt)
{
ResultSet layoutNames = ((DatabaseMetaDataExt) dbMetaData).getLayouts(null, null, tableName, null);
// iterate over the layout names and add them to the "Layouts" node
while (layoutNames.next())
layouts.add(layoutNames.getString("LAYOUT_NAME"));
}
return layouts;
}
/**
* Returns the result for select * from table
* with maxHits = 500 default value
*/
public Vector[] getQueryData(String table) throws SQLException, ParseException,Exception
{
return getQueryData("SELECT * from " + quoteChar + table + quoteChar, maxHits);
}
/**
* Returns the result of the query
* or an Vector array of Vectors containing error messages
*/
public Vector[] getQueryData(String query, FM2SQL.ProgressDialog dialog, int maxHits) throws SQLException,Exception
{
long timeStart = System.currentTimeMillis();
ResultSet resultSet = null;
if (connection == null)
{
Vector[] noData = new Vector[2];
//System.out.println("Exception occured");
noData[1] = new Vector();
Vector vec2 = new Vector();
noData[0] = new Vector();
vec2.add("no Connection available");
noData[0].add(vec2);
noData[1].add("Exception occured! No results available");
//noData[1].add("no Results were produced");
return noData;
}
if (dialog != null)
dialog.progress.setValue(0);
resultSet = makeQuery(query, maxHits);
metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
int rowCount = 0;
if (maxHits == 0)
rowCount = (metaData instanceof ResultSetMetaDataExt) ? 1000 : getRowCount(query);
else
rowCount = maxHits;
int counter = 0;
Vector tableData = new Vector();
Vector tableRow = new Vector();
// System.out.println("rowCount "+rowCount+" "+maxHits);
try
{
while ((tableRow = getNextRow()) != null)
{
counter++;
if (dialog != null)
dialog.progress.setValue((int) ((double) counter / (double) rowCount * 100.0));
tableData.add(tableRow);
}
} catch (Exception e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
// retrieve the column names from the result set; the column names
// are used for the table header
columnNames = new Vector();
for (int i = 1; i <= columnCount; i++)
columnNames.addElement(metaData.getColumnName(i));
Vector data[] = new Vector[2];
data[0] = tableData;
data[1] = columnNames;
System.out.println("Rows " + tableData.size() + " " + ((Vector) tableData.get(0)).size());
long timeEnd = System.currentTimeMillis();
System.out.println("Time needed for query and data retrieval " + (timeEnd - timeStart) + " ms");
return data;
}
/**
* Returns the result of the query
* or an Vector array of Vectors containing error messages
*/
public Vector[] getQueryData(String query, int maxHits) throws SQLException, ParseException,Exception
{
return getQueryData(query,null,maxHits);
}
public Vector getColumnNames()
{
if (result == null)
return null;
try
{
ResultSetMetaData metaData = result.getMetaData();
int columnCount = metaData.getColumnCount();
columnNames = new Vector();
for (int i = 1; i <= columnCount; i++)
columnNames.addElement(metaData.getColumnName(i));
} catch (Exception e)
{
}
return columnNames;
}
/**
* makes the database Query
* with the numberOfHits as maximum
* @return the result as an ResultSet object
*/
public ResultSet makeQuery(String query, int numberOfHits) throws SQLException,Exception
{
result = null;
Statement stm = null;
// System.out.println("Query " + query);
if (!connection.isClosed())
stm = connection.createStatement();
else
{
try
{
connection = getConnection();
stm = connection.createStatement();
} catch (Exception e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
stm.setMaxRows(numberOfHits);
long time = System.currentTimeMillis();
try
{
stm.execute(query);
long time2 = System.currentTimeMillis();
System.out.println("time to execute " + (time2 - time));
// stm.setMaxRows(numberOfHits);
result = stm.getResultSet();
// System.out.println(result+" "+stm.getUpdateCount());
metaData = result.getMetaData();
} catch (Exception e)
{
// TODO remove
if (Convert.isGUI&&Convert.debug)
FM2SQL.showErrorDialog("Error caught!! \n Query was " + query + " \n", "Debug Info");
e.printStackTrace();
throw e;
}
return result;
}
/**
* sets the database user
*/
public void setUser(String user)
{
this.user = user;
}
/**
* sets the database passwd
*/
public void setPasswd(String passwd)
{
this.passwd = passwd;
}
/**
* sets the database user and passwd
*/
public void setUserAndPasswd(String user, String passwd)
{
this.user = user;
this.passwd = passwd;
}
/**
* just sets the connection URL
*/
public void setURL(String url)
{
this.url = url;
}
/**
* Test the database drivers features given by the DatabaseMetaData object
*/
public Vector[] TestDB(DatabaseMetaData d) throws SQLException
{
Vector data[] = new Vector[2];
Vector[] rows = new Vector[120];
for (int i = 0; i < rows.length; ++i)
rows[i] = new Vector();
Vector columnNames = new Vector();
columnNames.add("Feature");
columnNames.add("Supported");
Vector cols = new Vector();
rows[0].add("allProceduresAreCallable");
rows[0].add(new Boolean(d.allProceduresAreCallable()));
//boolean allProceduresAreCallable() throws SQLException;
rows[1].add("allTablesAreSelectable");
rows[1].add(new Boolean(d.allTablesAreSelectable()));
// boolean allTablesAreSelectable() throws SQLException;
rows[2].add("isReadOnly");
rows[2].add(new Boolean(d.isReadOnly()));
// boolean isReadOnly() throws SQLException;
rows[3].add("nullsAreSortedHigh");
rows[3].add(new Boolean(d.nullsAreSortedHigh()));
// boolean nullsAreSortedHigh() throws SQLException;
rows[4].add("nullsAreSortedLow");
rows[4].add(new Boolean(d.nullsAreSortedLow()));
// boolean nullsAreSortedLow() throws SQLException;
rows[5].add("nullsAreSortedAtStart");
rows[5].add(new Boolean(d.nullsAreSortedAtStart()));
// boolean nullsAreSortedAtStart() throws SQLException;
rows[6].add("nullsAreSortedAtEnd");
rows[6].add(new Boolean(d.nullsAreSortedAtEnd()));
// boolean nullsAreSortedAtEnd() throws SQLException;
rows[7].add("usesLocalFiles");
rows[7].add(new Boolean(d.usesLocalFiles()));
// boolean usesLocalFiles() throws SQLException;
rows[8].add("usesLocalFilePerTable");
rows[8].add(new Boolean(d.usesLocalFilePerTable()));
// boolean usesLocalFilePerTable() throws SQLException;
rows[9].add("supportsMixedCaseIdentifiers");
rows[9].add(new Boolean(d.supportsMixedCaseIdentifiers()));
//boolean supportsMixedCaseIdentifiers() throws SQLException;
rows[10].add("storesUpperCaseIdentifiers");
rows[10].add(new Boolean(d.storesUpperCaseIdentifiers()));
// boolean storesUpperCaseIdentifiers() throws SQLException;
rows[11].add("storesLowerCaseIdentifiers");
rows[11].add(new Boolean(d.storesLowerCaseIdentifiers()));
// boolean storesLowerCaseIdentifiers() throws SQLException;
rows[12].add("storesMixedCaseIdentifiers");
rows[12].add(new Boolean(d.storesMixedCaseIdentifiers()));
// boolean storesMixedCaseIdentifiers() throws SQLException;
rows[13].add("supportsMixedCaseQuotedIdentifiers");
rows[13].add(new Boolean(d.supportsMixedCaseQuotedIdentifiers()));
// boolean supportsMixedCaseQuotedIdentifiers() throws SQLException;
rows[14].add("storesUpperCaseQuotedIdentifiers");
rows[14].add(new Boolean(d.storesUpperCaseQuotedIdentifiers()));
// boolean storesUpperCaseQuotedIdentifiers() throws SQLException;
rows[15].add("storesLowerCaseQuotedIdentifiers");
rows[15].add(new Boolean(d.storesLowerCaseQuotedIdentifiers()));
//boolean storesLowerCaseQuotedIdentifiers() throws SQLException;
rows[16].add("storesMixedCaseQuotedIdentifiers");
rows[16].add(new Boolean(d.storesMixedCaseQuotedIdentifiers()));
// boolean storesMixedCaseQuotedIdentifiers() throws SQLException;
rows[17].add("supportsAlterTableWithAddColumn");
rows[17].add(new Boolean(d.supportsAlterTableWithAddColumn()));
// boolean supportsAlterTableWithAddColumn() throws SQLException;
rows[18].add("supportsAlterTableWithDropColumn");
rows[18].add(new Boolean(d.supportsAlterTableWithDropColumn()));
// boolean supportsAlterTableWithDropColumn() throws SQLException;
rows[19].add("nullPlusNonNullIsNull");
rows[19].add(new Boolean(d.nullPlusNonNullIsNull()));
// boolean nullPlusNonNullIsNull() throws SQLException;
rows[20].add("supportsConvert");
rows[20].add(new Boolean(d.supportsConvert()));
// boolean supportsConvert() throws SQLException;
// boolean supportsConvert(int fromType, int toType) throws SQLException;
rows[21].add("supportsTableCorrelationNames");
rows[21].add(new Boolean(d.supportsTableCorrelationNames()));
// boolean supportsTableCorrelationNames() throws SQLException;
rows[22].add("supportsDifferentTableCorrelationNames");
rows[22].add(new Boolean(d.supportsDifferentTableCorrelationNames()));
// boolean supportsDifferentTableCorrelationNames() throws SQLException;
rows[23].add("supportsExpressionsInOrderBy");
rows[23].add(new Boolean(d.supportsExpressionsInOrderBy()));
// boolean supportsExpressionsInOrderBy() throws SQLException;
rows[24].add("supportsOrderByUnrelated");
rows[24].add(new Boolean(d.supportsOrderByUnrelated()));
// boolean supportsOrderByUnrelated() throws SQLException;
rows[25].add("supportsGroupBy");
rows[25].add(new Boolean(d.supportsGroupBy()));
// boolean supportsGroupBy() throws SQLException;
rows[26].add("supportsGroupByUnrelated");
rows[26].add(new Boolean(d.supportsGroupByUnrelated()));
// boolean supportsGroupByUnrelated() throws SQLException;
rows[27].add("supportsGroupByBeyondSelect");
rows[27].add(new Boolean(d.supportsGroupByBeyondSelect()));
// boolean supportsGroupByBeyondSelect() throws SQLException;
rows[28].add("supportsLikeEscapeClause");
rows[28].add(new Boolean(d.supportsLikeEscapeClause()));
// boolean supportsLikeEscapeClause() throws SQLException;
rows[29].add("supportsMultipleResultSets");
rows[29].add(new Boolean(d.supportsMultipleResultSets()));
// boolean supportsMultipleResultSets() throws SQLException;
rows[30].add("supportsMultipleTransactions");
rows[30].add(new Boolean(d.supportsMultipleTransactions()));
// boolean supportsMultipleTransactions() throws SQLException;
rows[31].add("supportsNonNullableColumns");
rows[31].add(new Boolean(d.supportsNonNullableColumns()));
// boolean supportsNonNullableColumns() throws SQLException;
rows[32].add("supportsMinimumSQLGrammar");
rows[32].add(new Boolean(d.supportsMinimumSQLGrammar()));
// boolean supportsMinimumSQLGrammar() throws SQLException;
rows[33].add("supportsCoreSQLGrammar");
rows[33].add(new Boolean(d.supportsCoreSQLGrammar()));
// boolean supportsCoreSQLGrammar() throws SQLException;
rows[34].add("supportsExtendedSQLGrammar");
rows[34].add(new Boolean(d.supportsExtendedSQLGrammar()));
// boolean supportsExtendedSQLGrammar() throws SQLException;
rows[35].add("supportsANSI92EntryLevelSQL");
rows[35].add(new Boolean(d.supportsANSI92EntryLevelSQL()));
// boolean supportsANSI92EntryLevelSQL() throws SQLException;
rows[36].add("supportsANSI92IntermediateSQL");
rows[36].add(new Boolean(d.supportsANSI92IntermediateSQL()));
//boolean supportsANSI92IntermediateSQL() throws SQLException;
rows[37].add("supportsANSI92FullSQL");
rows[37].add(new Boolean(d.supportsANSI92FullSQL()));
//boolean supportsANSI92FullSQL() throws SQLException;
rows[38].add("supportsIntegrityEnhancementFacility");
rows[38].add(new Boolean(d.supportsIntegrityEnhancementFacility()));
//boolean supportsIntegrityEnhancementFacility() throws SQLException;
rows[39].add("supportsOuterJoins");
rows[39].add(new Boolean(d.supportsOuterJoins()));
//boolean supportsOuterJoins() throws SQLException;
rows[40].add("supportsFullOuterJoins");
rows[40].add(new Boolean(d.supportsFullOuterJoins()));
//boolean supportsFullOuterJoins() throws SQLException;
rows[41].add("supportsLimitedOuterJoins");
rows[41].add(new Boolean(d.supportsLimitedOuterJoins()));
//boolean supportsLimitedOuterJoins() throws SQLException;
rows[42].add("isCatalogAtStart");
rows[42].add(new Boolean(d.isCatalogAtStart()));
//boolean isCatalogAtStart() throws SQLException;
rows[43].add("supportsSchemasInDataManipulation");
rows[43].add(new Boolean(d.supportsSchemasInDataManipulation()));
//boolean supportsSchemasInDataManipulation() throws SQLException;
rows[44].add("supportsSchemasInProcedureCalls");
rows[44].add(new Boolean(d.supportsSchemasInProcedureCalls()));
//boolean supportsSchemasInProcedureCalls() throws SQLException;
rows[45].add("supportsSchemasInTableDefinitions");
rows[45].add(new Boolean(d.supportsSchemasInTableDefinitions()));
//boolean supportsSchemasInTableDefinitions() throws SQLException;
rows[46].add("supportsSchemasInIndexDefinitions");
rows[46].add(new Boolean(d.supportsSchemasInIndexDefinitions()));
//boolean supportsSchemasInIndexDefinitions() throws SQLException;
rows[47].add("supportsSchemasInPrivilegeDefinitions");
rows[47].add(new Boolean(d.supportsSchemasInPrivilegeDefinitions()));
//boolean supportsSchemasInPrivilegeDefinitions() throws SQLException;
rows[48].add("supportsCatalogsInDataManipulation");
rows[48].add(new Boolean(d.supportsCatalogsInDataManipulation()));
//boolean supportsCatalogsInDataManipulation() throws SQLException;
rows[49].add("supportsCatalogsInProcedureCalls");
rows[49].add(new Boolean(d.supportsCatalogsInProcedureCalls()));
//boolean supportsCatalogsInProcedureCalls() throws SQLException;
rows[50].add("supportsCatalogsInTableDefinitions");
rows[50].add(new Boolean(d.supportsCatalogsInTableDefinitions()));
//boolean supportsCatalogsInTableDefinitions() throws SQLException;
rows[51].add("supportsCatalogsInIndexDefinitions");
rows[51].add(new Boolean(d.supportsCatalogsInIndexDefinitions()));
//boolean supportsCatalogsInIndexDefinitions() throws SQLException;
rows[52].add("supportsCatalogsInPrivilegeDefinitions");
rows[52].add(new Boolean(d.supportsCatalogsInPrivilegeDefinitions()));
//boolean supportsCatalogsInPrivilegeDefinitions() throws SQLException;
rows[53].add("supportsPositionedDelete");
rows[53].add(new Boolean(d.supportsPositionedDelete()));
//boolean supportsPositionedDelete() throws SQLException;
rows[54].add("supportsPositionedUpdate");
rows[54].add(new Boolean(d.supportsPositionedUpdate()));
//boolean supportsPositionedUpdate() throws SQLException;
rows[55].add("supportsSelectForUpdate");
rows[55].add(new Boolean(d.supportsSelectForUpdate()));
//boolean supportsSelectForUpdate() throws SQLException;
rows[56].add("supportsStoredProcedures");
rows[56].add(new Boolean(d.supportsStoredProcedures()));
//boolean supportsStoredProcedures() throws SQLException;
rows[57].add("supportsSubqueriesInComparisons");
rows[57].add(new Boolean(d.supportsSubqueriesInComparisons()));
//boolean supportsSubqueriesInComparisons() throws SQLException;
rows[58].add("supportsSubqueriesInExists");
rows[58].add(new Boolean(d.supportsSubqueriesInExists()));
//boolean supportsSubqueriesInExists() throws SQLException;
rows[59].add("supportsSubqueriesInIns");
rows[59].add(new Boolean(d.supportsSubqueriesInIns()));
//boolean supportsSubqueriesInIns() throws SQLException;
rows[60].add("supportsSubqueriesInQuantifieds");
rows[60].add(new Boolean(d.supportsSubqueriesInQuantifieds()));
//boolean supportsSubqueriesInQuantifieds() throws SQLException;
rows[61].add("supportsCorrelatedSubqueries");
rows[61].add(new Boolean(d.supportsCorrelatedSubqueries()));
//boolean supportsCorrelatedSubqueries() throws SQLException;
rows[62].add("supportsUnion");
rows[62].add(new Boolean(d.supportsUnion()));
//boolean supportsUnion() throws SQLException;
rows[63].add("supportsUnionAll");
rows[63].add(new Boolean(d.supportsUnionAll()));
//boolean supportsUnionAll() throws SQLException;
rows[64].add("supportsOpenCursorsAcrossCommit");
rows[64].add(new Boolean(d.supportsOpenCursorsAcrossCommit()));
//boolean supportsOpenCursorsAcrossCommit() throws SQLException;
rows[65].add("supportsOpenCursorsAcrossRollback");
rows[65].add(new Boolean(d.supportsOpenCursorsAcrossRollback()));
//boolean supportsOpenCursorsAcrossRollback() throws SQLException;
rows[66].add("supportsOpenStatementsAcrossCommit");
rows[66].add(new Boolean(d.supportsOpenStatementsAcrossCommit()));
//boolean supportsOpenStatementsAcrossCommit() throws SQLException;
rows[67].add("supportsOpenStatementsAcrossRollback");
rows[67].add(new Boolean(d.supportsOpenStatementsAcrossRollback()));
//boolean supportsOpenStatementsAcrossRollback() throws SQLException;
rows[68].add("doesMaxRowSizeIncludeBlobs");
rows[68].add(new Boolean(d.doesMaxRowSizeIncludeBlobs()));
//boolean doesMaxRowSizeIncludeBlobs() throws SQLException;
rows[69].add("supportsTransactions");
rows[69].add(new Boolean(d.supportsTransactions()));
//boolean supportsTransactions() throws SQLException;
rows[70].add("supportsTransactionIsolationLevel");
rows[70].add(new Boolean(d.supportsTransactionIsolationLevel(1)));
//boolean supportsTransactionIsolationLevel(int level) throws SQLException;
rows[71].add("supportsDataDefinitionAndDataManipulationTransactions");
rows[71].add(new Boolean(d.supportsDataDefinitionAndDataManipulationTransactions()));
//boolean supportsDataDefinitionAndDataManipulationTransactions() throws SQLException;
rows[72].add("supportsDataManipulationTransactionsOnly");
rows[72].add(new Boolean(d.supportsDataManipulationTransactionsOnly()));
//boolean supportsDataManipulationTransactionsOnly() throws SQLException;
rows[73].add("dataDefinitionCausesTransactionCommit");
rows[73].add(new Boolean(d.dataDefinitionCausesTransactionCommit()));
//boolean dataDefinitionCausesTransactionCommit() throws SQLException;
rows[74].add("dataDefinitionIgnoredInTransactions");
rows[74].add(new Boolean(d.dataDefinitionIgnoredInTransactions()));
//boolean dataDefinitionIgnoredInTransactions() throws SQLException;
rows[75].add("getMaxBinaryLiteralLength");
rows[75].add(new Integer(d.getMaxBinaryLiteralLength()));
// int getMaxBinaryLiteralLength() throws SQLException;
rows[76].add("getMaxCharLiteralLength");
rows[76].add(new Integer(d.getMaxCharLiteralLength()));
//int getMaxCharLiteralLength() throws SQLException;
rows[77].add("getMaxColumnNameLength");
rows[77].add(new Integer(d.getMaxColumnNameLength()));
// int getMaxColumnNameLength() throws SQLException;
rows[78].add("getMaxColumnsInGroupBy");
rows[78].add(new Integer(d.getMaxColumnsInGroupBy()));
//int getMaxColumnsInGroupBy() throws SQLException;
rows[79].add("getMaxColumnsInIndex");
rows[79].add(new Integer(d.getMaxColumnsInIndex()));
//int getMaxColumnsInIndex() throws SQLException;
rows[80].add("getMaxColumnsInOrderBy");
rows[80].add(new Integer(d.getMaxColumnsInOrderBy()));
//int getMaxColumnsInOrderBy() throws SQLException;
rows[81].add("getMaxColumnsInSelect");
rows[81].add(new Integer(d.getMaxColumnsInSelect()));
//int getMaxColumnsInSelect() throws SQLException;
rows[82].add("getMaxColumnsInTable");
rows[82].add(new Integer(d.getMaxColumnsInTable()));
//int getMaxColumnsInTable() throws SQLException;
rows[83].add("getMaxConnections");
rows[83].add(new Integer(d.getMaxConnections()));
//int getMaxConnections() throws SQLException;
rows[84].add("getMaxCursorNameLength");
rows[84].add(new Integer(d.getMaxCursorNameLength()));
// int getMaxCursorNameLength() throws SQLException;
rows[85].add("getMaxIndexLength");
rows[85].add(new Integer(d.getMaxIndexLength()));
//int getMaxIndexLength() throws SQLException;
rows[86].add("getMaxSchemaNameLength");
rows[86].add(new Integer(d.getMaxSchemaNameLength()));
//int getMaxSchemaNameLength() throws SQLException;
rows[87].add("getMaxProcedureNameLength");
rows[87].add(new Integer(d.getMaxProcedureNameLength()));
//int getMaxProcedureNameLength() throws SQLException;
rows[88].add("getMaxCatalogNameLength");
rows[88].add(new Integer(d.getMaxCatalogNameLength()));
//int getMaxCatalogNameLength() throws SQLException;
rows[89].add("getMaxRowSize");
rows[89].add(new Integer(d.getMaxRowSize()));
//int getMaxRowSize() throws SQLException;
rows[90].add("getMaxStatementLength");
rows[90].add(new Integer(d.getMaxStatementLength()));
//int getMaxStatementLength() throws SQLException;
rows[91].add("getMaxStatements");
rows[91].add(new Integer(d.getMaxStatements()));
//int getMaxStatements() throws SQLException;
rows[92].add("getMaxTableNameLength");
rows[92].add(new Integer(d.getMaxTableNameLength()));
//int getMaxTableNameLength() throws SQLException;
rows[93].add("getMaxTablesInSelect");
rows[93].add(new Integer(d.getMaxTablesInSelect()));
//int getMaxTablesInSelect() throws SQLException;
rows[94].add("getMaxUserNameLength");
rows[94].add(new Integer(d.getMaxUserNameLength()));
// int getMaxUserNameLength() throws SQLException;
rows[95].add("getDefaultTransactionIsolation");
rows[95].add(new Integer(d.getDefaultTransactionIsolation()));
//int getDefaultTransactionIsolation() throws SQLException;
rows[96].add("getDatabaseProductName");
rows[96].add(d.getDatabaseProductName());
// String getDatabaseProductName() throws SQLException;
rows[97].add("getDatabaseProductVersion");
rows[97].add(d.getDatabaseProductVersion());
//String getDatabaseProductVersion() throws SQLException;
rows[98].add("getURL");
rows[98].add(d.getURL());
//String getURL() throws SQLException;
rows[99].add("getUserName");
rows[99].add(d.getUserName());
//String getUserName() throws SQLException;
rows[100].add("getDriverName");
rows[100].add(d.getDriverName());
// String getDriverName() throws SQLException;
rows[101].add("getIdentifierQuoteString");
rows[101].add(d.getIdentifierQuoteString());
//String getIdentifierQuoteString() throws SQLException;
rows[102].add("getDriverVersion");
rows[102].add(d.getDriverVersion());
//String getDriverVersion() throws SQLException;
rows[103].add("getDriverMajorVersion");
rows[103].add(new Integer(d.getDriverMajorVersion()));
//int getDriverMajorVersion();
rows[104].add("getDriverMinorVersion");
rows[104].add(new Integer(d.getDriverMinorVersion()));
//int getDriverMinorVersion();
rows[105].add("getSQLKeywords");
rows[105].add(d.getSQLKeywords());
//String getSQLKeywords() throws SQLException;
rows[106].add("getNumericFunctions");
rows[106].add(d.getNumericFunctions());
//String getNumericFunctions() throws SQLException;
rows[107].add("getStringFunctions");
rows[107].add(d.getStringFunctions());
// String getStringFunctions() throws SQLException;
rows[108].add("getSystemFunctions");
rows[108].add(d.getSystemFunctions());
//String getSystemFunctions() throws SQLException;
rows[109].add("getTimeDateFunctions");
rows[109].add(d.getTimeDateFunctions());
//String getTimeDateFunctions() throws SQLException;
rows[110].add("getSearchStringEscape");
rows[110].add(d.getSearchStringEscape());
//String getSearchStringEscape() throws SQLException;
rows[111].add("getExtraNameCharacters");
rows[111].add(d.getExtraNameCharacters());
//String getExtraNameCharacters() throws SQLException;
rows[112].add("getSchemaTerm");
rows[112].add(d.getSchemaTerm());
//String getSchemaTerm() throws SQLException;
rows[113].add("getProcedureTerm");
rows[113].add(d.getProcedureTerm());
//String getProcedureTerm() throws SQLException;
rows[114].add("getCatalogTerm");
rows[114].add(d.getCatalogTerm());
// String getCatalogTerm() throws SQLException;
rows[115].add("getCatalogSeparator");
rows[115].add(d.getCatalogSeparator());
//String getCatalogSeparator() throws SQLException;
/*
boolean supportsResultSetType(int type) throws SQLException;
boolean supportsResultSetConcurrency(int type, int concurrency) throws SQLException;
boolean ownUpdatesAreVisible(int type) throws SQLException;
boolean ownDeletesAreVisible(int type) throws SQLException;
boolean ownInsertsAreVisible(int type) throws SQLException;
boolean othersUpdatesAreVisible(int type) throws SQLException;
boolean othersDeletesAreVisible(int type) throws SQLException;
boolean othersInsertsAreVisible(int type) throws SQLException;
boolean updatesAreDetected(int type) throws SQLException;
boolean deletesAreDetected(int type) throws SQLException;
boolean insertsAreDetected(int type) throws SQLException;
*/
// not in filemaker
// rows[96].add("supportsBatchUpdates");
// rows[96].add(new Boolean(d.supportsBatchUpdates()));
//boolean supportsBatchUpdates() throws SQLException;
/*
ResultSet getProcedures(String catalog, String schemaPattern, String procedureNamePattern) throws SQLException;
ResultSet getProcedureColumns(String catalog, String schemaPattern, String procedureNamePattern, String columnNamePattern) throws SQLException;
ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String types[]) throws SQLException;
ResultSet getSchemas() throws SQLException;
ResultSet getCatalogs() throws SQLException;
ResultSet getTableTypes() throws SQLException;
ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) throws SQLException;
ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException;
ResultSet getImportedKeys(String catalog, String schema, String table) throws SQLException;
ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException;
ResultSet getCrossReference(String primaryCatalog, String primarySchema, String primaryTable, String foreignCatalog, String foreignSchema, String foreignTable) throws SQLException;
ResultSet getTypeInfo() throws SQLException;
ResultSet getIndexInfo(String catalog, String schema, String table, boolean unique, boolean approximate) throws SQLException;
ResultSet getUDTs(String catalog, String schemaPattern, String typeNamePattern, int[] types) throws SQLException;
Connection getConnection() throws SQLException;
*/
for (int i = 0; i < rows.length; ++i)
cols.add(rows[i]);
data[0] = cols;
data[1] = columnNames;
return data;
}
public Vector getNextRow() throws Exception
{
if (result == null)
return null;
boolean check = false;
ResultSet resultSet = result;
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
Vector tableData = new Vector();
check = resultSet.next();
// System.out.println("hallo check "+check);
if (!check)
return null;
Vector tableRow = new Vector(), m_columnClasses = new Vector();
for (int i = 1; i <= columnCount; i++) {
// repeating fields and fields from related databases may contain
// multliple data values; the data values are stored using
// a Vector which is then added to the tableRow
// if (metaData instanceof ResultSetMetaDataExt)
if ((metaData instanceof ResultSetMetaDataExt)
&& (((ResultSetMetaDataExt) metaData).isRelated(i) || ((ResultSetMetaDataExt) metaData)
.isRepeating(i))) {
// System.out.println("Related fields");
// retrieve the repeating or related field contents as a
// com.fmi.jdbc.Array via the ResultSet.getObject method
com.fmi.jdbc.Array array = (com.fmi.jdbc.Array) resultSet
.getObject(i);
// create a Vector for storing all of the data values
ArrayList columnData = new ArrayList();
try {
// call the Array.getStringArray method since the data will
// only be displayed
Object[] fieldData = (Object[]) array.getArray();
if (fieldData != null) {
// add each value to the Vector
for (int j = 0; j < fieldData.length; j++) {
if (fieldData[j] != null)
columnData.add(fieldData[j]);
}
}
} catch (Exception e) {
// System.out.println(e);
}
if (columnData.isEmpty())
tableRow.add(null);
else
tableRow.addElement(columnData);
// System.out.println(columnData);
// System.out.println("Related fields"+columnData.size()+"
// "+tableRow.size());
// m_columnClasses.addElement(java.util.Vector.class);
} else if (metaData.getColumnType(i) == Types.LONGVARBINARY) {
// use the ResultSet.getObject method for retrieving images
// from FileMaker Pro container fields; the ResultSet.getObject
// method returns a java.awt.Image object for FileMaker Pro
// container fields
try {
tableRow.addElement(null);
// image objects removed from resultset !!!
// tableRow.addElement(resultSet.getObject(i));
} catch (Exception e) {
// TODO Auto-generated catch block
// e.printStackTrace();
tableRow.addElement(null);
}
// m_columnClasses.addElement(java.awt.Image.class);
} else if (metaData.getColumnType(i) == Types.TIME) {
// use the ResultSet.getObject method for retieving images
// from FileMaker Pro container fields; the ResultSet.getObject
// method returns a java.awt.Image object for FileMaker Pro
// container fields
try {
tableRow.addElement(resultSet.getTime(i).toString());
// m_columnClasses.addElement(java.sql.Time.class);
} catch (Exception e) {
String value = resultSet.getString(i);
if (value != null) {
// System.out.println("SQLTime new
// "+Time.valueOf("17:00:00").toString());
int index = 0;
for (int j = 0; j < value.length(); ++j) {
if (!Character.isLetter(value.charAt(j)))
index = j + 1;
else
break;
}
tableRow.addElement(value.substring(0, index));
// m_columnClasses.addElement(java.sql.Time.class);
} else
tableRow.add(null);
// m_columnClasses.addElement(String.class);
} // to catch
} else if (metaData.getColumnType(i) == Types.INTEGER) {
// use the ResultSet.getObject method for retieving images
// from FileMaker Pro container fields; the ResultSet.getObject
// method returns a java.awt.Image object for FileMaker Pro
// container fields
tableRow.addElement(new Integer(resultSet.getInt(i)));
// m_columnClasses.addElement(java.sql.Date.class);
} else if (metaData.getColumnType(i) == Types.DATE) {
// use the ResultSet.getObject method for retieving images
// from FileMaker Pro container fields; the ResultSet.getObject
// method returns a java.awt.Image object for FileMaker Pro
// container fields
try {
tableRow.addElement(resultSet.getDate(i));
} catch (Exception e) {
// work around for parse bug in FM JDBC Driver
// for dates of format dd-mm-yyyy
String date = resultSet.getString(i);
date = date.replace('-', '.');
java.text.DateFormat dateFormat = DateFormat
.getDateInstance(DateFormat.SHORT, Locale.GERMAN);
java.util.Date d = dateFormat.parse(date);
// Calendar cal=Calendar.getInstance(Locale.GERMAN);
// cal.setTime(d);
// date=(cal.get(Calendar.YEAR))+"-"+(cal.get(Calendar.MONTH)+1)+"-"+cal.get(Calendar.DATE);
tableRow.addElement(new java.sql.Date(d.getTime()));
System.out.println("Date " + date);
}
// m_columnClasses.addElement(java.sql.Date.class);
} else if (metaData.getColumnTypeName(i) == "NUMBER") {
// use the ResultSet.getObject method for retieving images
// from FileMaker Pro container fields; the ResultSet.getObject
// method returns a java.awt.Image object for FileMaker Pro
// container fields
try {
Double value = new Double(resultSet.getDouble(i));
// tableRow.addElement(new Double(resultSet.getDouble(i)));
String tVal = value.toString();
tVal = tVal.substring(tVal.indexOf('.') + 1);
boolean checkMe = tVal.length() == 1 && tVal.equals("0");
// System.out.println("check was"+checkMe+" "+tVal);
if (checkMe)
tableRow.addElement(new Integer(value.intValue()));
else
tableRow.addElement(value);
// m_columnClasses.addElement(Integer.class);
} catch (Exception e) {
StringBuffer number = new StringBuffer();
String value = resultSet.getString(i);
System.out.println(value);
for (int c = 0; c < value.length(); ++c) {
if (Character.isDigit(value.charAt(c))) {
number.append(value.charAt(c));
}
}
if (number.length() > 0) {
tableRow.addElement(null);
// m_columnClasses.addElement(Integer.class);
} else
tableRow.addElement(null);
}
} else {
// all other field values are retrieved as strings and
// added to the tableRow Vector
// System.out.println("row "+resultSet.getString(i));
try {
byte[] b = null;
if (metaData instanceof ResultSetMetaDataExt)
b = resultSet.getBytes(i);
/*
* if (b != null) { java.io.ByteArrayInputStream stream =
* (java.io.ByteArrayInputStream)
* resultSet.getBinaryStream(i); // System.out.println("
* stream "+resultSet.getBinaryStream(i)); byte[] c = new
* byte[stream.available()]; int length = stream.read(c, 0,
* c.length); int count = 0; b = new byte[c.length]; for
* (int n = 0; n < length; ++n) {
*
* if (c[n] != 0) { // System.out.println(c[n]+"
* "+(int)'?'+" "+(char)c[n]+" "+count+" "+b.length);
* b[count++] = c[n]; } } byte[] bCopy = new byte[count];
* System.arraycopy(b, 0, bCopy, 0, count); b = bCopy; }
*/
String utf8 = null;
utf8 = (b == null) ? null : new String(b);
if (metaData instanceof ResultSetMetaDataExt) {
String rowElement = "";
if (b != null) {
rowElement = resultSet.getString(i);
if (useNormanToUnicodeMapper)
rowElement = Convert
.normanToUnicode(rowElement);
tableRow.addElement(rowElement);
} else
tableRow.addElement(null);
} else {
if (url.toLowerCase().indexOf("odbc") >= 0) {
byte[] val = resultSet.getBytes(i);
for (int j = 0; j < val.length; ++j)
System.out.println(Integer.toHexString(val[j]));
tableRow.addElement((val == null) ? null
: new String(val));
} else {
tableRow.add(resultSet.getString(i));
//byte[] val = resultSet.getBytes(i);
//System.out.println("string data:"+resultSet.getString(i));
/*if (val != null) {
tableRow.add(new String(val,"UTF-8"));
} else {
tableRow.add(null);
}*/
}
}
} catch (Exception e) {
System.out.println("Hey I got an error" + e);
e.printStackTrace();
}
// m_columnClasses.addElement(java.lang.String.class);
}
}
// tableData.addElement(tableRow);
if (check)
return tableRow;
else
return null;
}
class ConnectionPool
{
String user = "", passwd = "", url = "";
Connection con;
public ConnectionPool(String url, String user, String passwd, Connection con)
{
this.con = con;
this.user = user;
this.passwd = passwd;
this.url = url;
}
}
public String getQC()
{
// if (connection == null)
// return "";
// check if connection null if null try to get one
if (connection == null)
try
{
getConnection();
} catch (Exception e)
{
if (FM2SQL.debug)
System.out.println("cannot get a connection");
}
if (connection == null)
{
if (url.toLowerCase().indexOf("fmpro") >= 0 || url.toLowerCase().indexOf("postgres") >= 0)
quoteChar = "\"";
else if (url.toLowerCase().indexOf("mysql") >= 0)
quoteChar = "`";
}
if (quoteChar == null)
quoteChar = "\""; // needed for postgres
return quoteChar;
}
public int getRowCount(String query) throws SQLException
{
String table = query.substring(query.indexOf("from") + 4).trim();
int index = table.indexOf(" ");
table = table.substring(0, (index >= 0) ? index : table.length());
System.out.println(table);
Statement stm = null;
if (metaData instanceof ResultSetMetaDataExt)
return 1000;
if (!connection.isClosed())
stm = connection.createStatement();
stm.setMaxRows(1);
ResultSet resultSet = stm.executeQuery("select count(*) from " + table);
resultSet.next();
return resultSet.getInt(1);
}
public TreeSet getIDVector(String id, String table, String query, int numHits) throws Exception
{
TreeSet t = new TreeSet();
getConnection();
ResultSet result = this.result;
String subQuery = query.substring(query.lastIndexOf(table) + table.length() + 1);
System.out.println("subQuery " + subQuery);
makeQuery("select " + getQC()+id+getQC() + " from " + getQC() + table + getQC() + subQuery, numHits);
while (true)
{
Vector vec = getNextRow();
if (vec == null)
break;
t.add(vec.get(0));
}
this.result = result;
metaData = (this.result == null) ? null : this.result.getMetaData();
return t;
}
/**
* @return
*/
public boolean isUseNormanToUnicodeMapper()
{
return useNormanToUnicodeMapper;
}
/**
* @param b
*/
public void setUseNormanToUnicodeMapper(boolean b)
{
useNormanToUnicodeMapper = b;
}
/**
*
*/
protected void closeAllConnections()
{
Enumeration enumeration = connectionPool.elements();
while(enumeration.hasMoreElements())
{
ConnectionPool conPol =(ConnectionPool)enumeration.nextElement();
try
{
System.out.println(conPol);
conPol.con.close();
} catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* @param indexField
*/
public String getColumnType(String indexField) throws SQLException
{
Vector names=getColumnNames();
for (int i = 0; i < names.size(); i++)
{
if(names.get(i).toString().equals(indexField))
{
System .out.println("found field "+names.get(i)+" "+metaData.getColumnTypeName(i+1));
return metaData.getColumnTypeName(i+1);
}
}
return "";
}
}
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 / src