[Squirrel-sql-commits] SF.net SVN: squirrel-sql:[6184] trunk/sql12
A Java SQL client for any JDBC compliant database
Brought to you by:
colbell,
gerdwagner
From: <wi...@us...> - 2011-03-13 16:47:05
|
Revision: 6184 http://squirrel-sql.svn.sourceforge.net/squirrel-sql/?rev=6184&view=rev Author: wis775 Date: 2011-03-13 16:46:55 +0000 (Sun, 13 Mar 2011) Log Message: ----------- 3088572: Squirrel fails when user tries to edit DB fields which have particular characters. Some databases needs to escape special characters. e.g. MySql (see http://dev.mysql.com/doc/refman/5.1/en/string-syntax.html) Squirrel use now parameter values in the where clause, when counting the affected rows or updating data within a result table. With the use of parameters in the where clause, the JDBC driver is responsible for handling these special characters. Squirrel must no longer handle some escape sequences like PostgreSqlEscape. Modified Paths: -------------- trunk/sql12/app/src/main/java/net/sourceforge/squirrel_sql/client/session/DataSetUpdateableTableModelImpl.java trunk/sql12/doc/src/main/resources/changes.txt trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/BaseDataTypeComponent.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/CellComponentFactory.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeBigDecimal.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeBinary.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeBlob.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeBoolean.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeByte.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeClob.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeDate.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeDouble.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeFloat.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeInteger.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeJavaObject.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeLong.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeOther.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeShort.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeString.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeTime.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeTimestamp.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeUnknown.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/IDataTypeComponent.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/sql/SQLUtilities.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/util/StringUtilities.java trunk/sql12/fw/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeFloatTest.java trunk/sql12/fw/src/test/java/net/sourceforge/squirrel_sql/fw/util/StringUtilitiesTest.java trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/types/DB2XmlTypeDataTypeComponent.java trunk/sql12/plugins/derby/src/main/java/net/sourceforge/squirrel_sql/plugins/derby/types/DerbyClobDataTypeComponent.java trunk/sql12/plugins/oracle/src/main/java/net/sourceforge/squirrel_sql/plugins/oracle/types/OracleXmlTypeDataTypeComponent.java trunk/sql12/plugins/postgres/src/main/java/net/sourceforge/squirrel_sql/plugins/postgres/types/PostgreSqlOtherTypeDataTypeComponent.java trunk/sql12/plugins/postgres/src/main/java/net/sourceforge/squirrel_sql/plugins/postgres/types/PostgreSqlXmlTypeDataTypeComponent.java Added Paths: ----------- trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/ trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/ trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/ trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/ trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/AbstractAdhocTests.java trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/MySQLAdhocTests.java trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/MySQLnoBackslashEscapesAdhocTests.java trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/OracleAdhocTests.java trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/mysql.sql trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/oracle.sql trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/ trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/AbstractWhereClausePart.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/EmptyWhereClausePart.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/IWhereClausePart.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/IWhereClausePartUtil.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/IsNullWhereClausePart.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/NoParameterWhereClausePart.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/ParameterWhereClausePart.java trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/WhereClausePartUtil.java trunk/sql12/fw/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/ trunk/sql12/fw/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/EmptyWhereClausePartTest.java trunk/sql12/fw/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/IsNullWhereClausePartTest.java trunk/sql12/fw/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/NoParameterWhereClausePartTest.java trunk/sql12/fw/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/ParameterWhereClausePartTest.java trunk/sql12/fw/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/WhereClausePartUtilTest.java trunk/sql12/plugins/codecompletion/update/ trunk/sql12/plugins/codecompletion/update/backup/ trunk/sql12/plugins/codecompletion/update/backup/core/ trunk/sql12/plugins/codecompletion/update/backup/i18n/ trunk/sql12/plugins/codecompletion/update/backup/plugin/ trunk/sql12/plugins/codecompletion/update/downloads/ trunk/sql12/plugins/codecompletion/update/downloads/core/ trunk/sql12/plugins/codecompletion/update/downloads/i18n/ trunk/sql12/plugins/codecompletion/update/downloads/plugin/ trunk/sql12/plugins/laf/plugins/ trunk/sql12/plugins/laf/plugins/laf/ trunk/sql12/plugins/laf/plugins/laf/lafs/ trunk/sql12/plugins/laf/plugins/laf/skinlf-theme-packs/ trunk/sql12/plugins/sessionscript/plugins/ trunk/sql12/plugins/sessionscript/plugins/sessionscript/ trunk/sql12/plugins/sqlreplace/plugins/ trunk/sql12/plugins/sqlreplace/plugins/sqlreplace/ Removed Paths: ------------- trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DatabaseSpecificEscape.java Modified: trunk/sql12/app/src/main/java/net/sourceforge/squirrel_sql/client/session/DataSetUpdateableTableModelImpl.java =================================================================== --- trunk/sql12/app/src/main/java/net/sourceforge/squirrel_sql/client/session/DataSetUpdateableTableModelImpl.java 2011-03-11 17:20:23 UTC (rev 6183) +++ trunk/sql12/app/src/main/java/net/sourceforge/squirrel_sql/client/session/DataSetUpdateableTableModelImpl.java 2011-03-13 16:46:55 UTC (rev 6184) @@ -3,8 +3,9 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; -import java.sql.Statement; +import java.util.ArrayList; import java.util.HashMap; +import java.util.List; import java.util.Vector; import javax.swing.JOptionPane; @@ -12,9 +13,12 @@ import net.sourceforge.squirrel_sql.client.session.properties.EditWhereCols; import net.sourceforge.squirrel_sql.fw.datasetviewer.ColumnDisplayDefinition; import net.sourceforge.squirrel_sql.fw.datasetviewer.DataSetUpdateableTableModelListener; +import net.sourceforge.squirrel_sql.fw.datasetviewer.IDataModelImplementationDetails; import net.sourceforge.squirrel_sql.fw.datasetviewer.IDataSetUpdateableTableModel; -import net.sourceforge.squirrel_sql.fw.datasetviewer.IDataModelImplementationDetails; import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.CellComponentFactory; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IWhereClausePart; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IWhereClausePartUtil; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.WhereClausePartUtil; import net.sourceforge.squirrel_sql.fw.sql.ISQLConnection; import net.sourceforge.squirrel_sql.fw.sql.ISQLDatabaseMetaData; import net.sourceforge.squirrel_sql.fw.sql.ITableInfo; @@ -76,6 +80,11 @@ * ResultSet, and thus we never have any legal column index here. */ int _rowIDcol = -1; + + /** + * A util for handling parts of an where clause. + */ + private IWhereClausePartUtil whereClausePartUtil = new WhereClausePartUtil(); public void setTableInfo(ITableInfo ti) { @@ -205,15 +214,17 @@ if (ti == null) return TI_ERROR_MESSAGE; - String whereClause = getWhereClause(values, colDefs, col, oldValue); + List<IWhereClausePart> whereClauseParts = getWhereClause(values, colDefs, col, oldValue); + // It is possible for a table to contain only columns of types that // we cannot process or do selects on, so check for that. // Since this check is on the structure of the table rather than the contents, // we only need to do it once (ie: it is not needed in getWarningOnProjectedUpdate) - if (whereClause.length() == 0) + if (whereClausePartUtil.hasUsableWhereClause(whereClauseParts) == false){ // i18n[DataSetUpdateableTableModelImpl.confirmupdateallrows=The table has no columns that can be SELECTed on.\nAll rows will be updated.\nDo you wish to proceed?] return s_stringMgr.getString("DataSetUpdateableTableModelImpl.confirmupdateallrows"); + } final ISession session = _session; final ISQLConnection conn = session.getSQLConnection(); @@ -222,24 +233,7 @@ try { - Statement stmt = null; - ResultSet rs = null; - try - { - stmt = conn.createStatement(); - String countSql = "select count(*) from " + ti.getQualifiedName() + whereClause; - rs = stmt.executeQuery(countSql); - rs.next(); - count = rs.getInt(1); - } - finally - { - // We don't care if these throw an SQLException. Just squelch them - // and report to the user what the outcome of the previous statements - // were. - SQLUtilities.closeResultSet(rs); - SQLUtilities.closeStatement(stmt); - } + count = count(whereClauseParts, conn); } catch (SQLException ex) { @@ -270,6 +264,42 @@ } /** + * Counts the number of affected rows, using this where clause. + * @param whereClauseParts where clause to use + * @param conn connection to use + * @return number of rows in the database, which will be selected by the given whereClauseParts + * @throws SQLException if an SQLExcetpion occurs. + */ + private int count(List<IWhereClausePart> whereClauseParts, + final ISQLConnection conn) throws SQLException { + int count; + PreparedStatement pstmt = null; + ResultSet rs = null; + try + { + String whereClause = whereClausePartUtil.createWhereClause(whereClauseParts); + String countSql = "select count(*) from " + ti.getQualifiedName() + whereClause; + pstmt = conn.prepareStatement(countSql); + whereClausePartUtil.setParameters(pstmt, whereClauseParts, 1); + + rs = pstmt.executeQuery(); + rs.next(); + count = rs.getInt(1); + } + finally + { + // We don't care if these throw an SQLException. Just squelch them + // and report to the user what the outcome of the previous statements + // were. + SQLUtilities.closeResultSet(rs); + SQLUtilities.closeStatement(pstmt); + } + return count; + } + + + +/** * Link from fw to check on whether there are any unusual conditions * that will occur after the update has been done. */ @@ -285,8 +315,8 @@ if (ti == null) return TI_ERROR_MESSAGE; - String whereClause = getWhereClause(values, colDefs, col, newValue); - + List<IWhereClausePart> whereClauseParts = getWhereClause(values, colDefs, col, newValue); + final ISession session = _session; final ISQLConnection conn = session.getSQLConnection(); @@ -294,18 +324,8 @@ try { - final Statement stmt = conn.createStatement(); - try - { - final ResultSet rs = stmt.executeQuery("select count(*) from " - + ti.getQualifiedName() + whereClause); - rs.next(); - count = rs.getInt(1); - } - finally - { - stmt.close(); - } + count = count(whereClauseParts, conn); + } catch (SQLException ex) { @@ -376,8 +396,10 @@ // In some cases it may be possible for the DataType to use the // partial data, such as "matches <data>*", but that may not be // standard accross all Databases and thus may be risky. - String whereClause = getWhereClause(values, colDefs, -1, null); - + + + List<IWhereClausePart> whereClauseParts = getWhereClause(values, colDefs, -1, null); + String whereClause = whereClausePartUtil.createWhereClause(whereClauseParts); final ISession session = _session; final ISQLConnection conn = session.getSQLConnection(); @@ -385,14 +407,17 @@ try { - final Statement stmt = conn.createStatement(); - final String queryString = - "SELECT " + colDefs[col].getColumnName() +" FROM "+ti.getQualifiedName() + - whereClause; + final String queryString = + "SELECT " + colDefs[col].getColumnName() +" FROM "+ti.getQualifiedName() + + whereClause; + + final PreparedStatement pstmt = conn.prepareStatement(queryString); + whereClausePartUtil.setParameters(pstmt, whereClauseParts, 1); + try { - ResultSet rs = stmt.executeQuery(queryString); + ResultSet rs = pstmt.executeQuery(queryString); // There should be one row in the data, so try to move to it if (rs.next() == false) { @@ -417,7 +442,7 @@ } finally { - stmt.close(); + pstmt.close(); } } catch (Exception ex) @@ -454,8 +479,8 @@ return TI_ERROR_MESSAGE; // get WHERE clause using original value - String whereClause = getWhereClause(values, colDefs, col, oldValue); - + List<IWhereClausePart> whereClauseParts = getWhereClause(values, colDefs, col, oldValue); + String whereClause = whereClausePartUtil.createWhereClause(whereClauseParts); if (s_log.isDebugEnabled()) { s_log.debug("updateTableComponent: whereClause = "+whereClause); } @@ -476,10 +501,15 @@ { pstmt = conn.prepareStatement(sql); - // have the DataType object fill in the appropriate kind of value - // into the first (and only) variable position in the prepared stmt + /* + * have the DataType object fill in the appropriate kind of value of the changed data + * into the first variable position in the prepared stmt + */ CellComponentFactory.setPreparedStatementValue( colDefs[col], pstmt, newValue, 1); + + // Fill the parameters of the where clause - start at position 2 because the data which is updated is at position 1 + whereClausePartUtil.setParameters(pstmt, whereClauseParts, 2); count = pstmt.executeUpdate(); } catch (SQLException ex) @@ -550,7 +580,7 @@ * If the col number is < 0, then the colValue is ignored * and the WHERE clause is constructed using only the values[]. */ - private String getWhereClause( + private List<IWhereClausePart> getWhereClause( Object[] values, ColumnDisplayDefinition[] colDefs, int col, @@ -558,20 +588,19 @@ { try { - StringBuffer whereClause = new StringBuffer(""); // For tables that have a lot of columns, the user may have limited the set of columns // to use in the where clause, so see if there is a table of col names HashMap<String, String> colNames = (EditWhereCols.get(getFullTableName())); - - - ColumnDisplayDefinition editedCol = null; + + ColumnDisplayDefinition editedCol = null; if(-1 != col) { editedCol = colDefs[col]; } + List<IWhereClausePart> clauseParts = new ArrayList<IWhereClausePart>(); for (int i=0; i< colDefs.length; i++) { @@ -606,26 +635,16 @@ // do different things depending on data type ISQLDatabaseMetaData md = _session.getMetaData(); - String clause = CellComponentFactory.getWhereClauseValue(colDefs[i], value, md); + IWhereClausePart clausePart = CellComponentFactory.getWhereClauseValue(colDefs[i], value, md); - if (clause != null && clause.length() > 0) - if (whereClause.length() == 0) - { - whereClause.append(clause); - } - else - { - whereClause.append(" AND "); - whereClause.append(clause); - } + + if (clausePart.shouldBeUsed()) + // Now we know that the part should not we ignoredshould + clauseParts.add(clausePart); } + + return clauseParts; - // insert the "WHERE" at the front if there is anything in the clause - if (whereClause.length() == 0) - return ""; - - whereClause.insert(0, " WHERE "); - return whereClause.toString(); } catch (Exception e) { @@ -660,20 +679,14 @@ // get WHERE clause for the selected row // the -1 says to just use the contents of the values without // any substitutions - String whereClause = getWhereClause(rowData[i], colDefs, -1, null); - + List<IWhereClausePart> whereClauseParts = getWhereClause(rowData[i], colDefs, -1, null); + // count how many rows this WHERE matches try { - // do the delete and add the number of rows deleted to the count - final Statement stmt = conn.createStatement(); - try - { - ResultSet rs = stmt.executeQuery("SELECT count(*) FROM " + - ti.getQualifiedName()+whereClause); - - rs.next(); - if (rs.getInt(1) != 1) { - if (rs.getInt(1) == 0) { + + int count = count(whereClauseParts, conn); + if (count != 1) { + if (count == 0) { // i18n[DataSetUpdateableTableModelImpl.error.rownotmatch=\n Row {0} did not match any row in DB] rowCountErrorMessage += s_stringMgr.getString( @@ -684,14 +697,9 @@ rowCountErrorMessage += s_stringMgr.getString( "DataSetUpdateableTableModelImpl.error.rowmatched", - new Object[] { Integer.valueOf(i+1), Integer.valueOf(rs.getInt(1)) }); + new Object[] { Integer.valueOf(i+1), Integer.valueOf(count) }); } } - } - finally - { - stmt.close(); - } } catch (Exception e) { // some kind of problem - tell user @@ -727,20 +735,22 @@ // get WHERE clause for the selected row // the -1 says to just use the contents of the values without // any substitutions - String whereClause = getWhereClause(rowData[i], colDefs, -1, null); - + List<IWhereClausePart> whereClauseParts = getWhereClause(rowData[i], colDefs, -1, null); + String whereClause = whereClausePartUtil.createWhereClause(whereClauseParts); // try to delete try { // do the delete and add the number of rows deleted to the count - final Statement stmt = conn.createStatement(); + String sql = "DELETE FROM " + + ti.getQualifiedName() + whereClause; + final PreparedStatement pstmt = conn.prepareStatement(sql); + whereClausePartUtil.setParameters(pstmt, whereClauseParts, 1); try { - stmt.executeUpdate("DELETE FROM " + - ti.getQualifiedName() + whereClause); + pstmt.executeUpdate(); } finally { - stmt.close(); + pstmt.close(); } } catch (Exception e) { @@ -948,4 +958,8 @@ { _rowIDcol = rowIDCol; } + + public void setWhereClausePartUtil(IWhereClausePartUtil whereClausePartUtil) { + this.whereClausePartUtil = whereClausePartUtil; + } } Added: trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/AbstractAdhocTests.java =================================================================== --- trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/AbstractAdhocTests.java (rev 0) +++ trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/AbstractAdhocTests.java 2011-03-13 16:46:55 UTC (rev 6184) @@ -0,0 +1,169 @@ +package net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause; + +import static junit.framework.Assert.assertEquals; +import static junit.framework.Assert.assertFalse; +import static junit.framework.Assert.assertNotNull; + +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.List; + +import net.sourceforge.squirrel_sql.BaseSQuirreLJUnit4TestCase; +import net.sourceforge.squirrel_sql.fw.datasetviewer.ColumnDisplayDefinition; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.CellComponentFactory; +import net.sourceforge.squirrel_sql.fw.dialects.DialectType; +import net.sourceforge.squirrel_sql.fw.persist.ValidationException; +import net.sourceforge.squirrel_sql.fw.sql.ISQLAlias; +import net.sourceforge.squirrel_sql.fw.sql.ISQLConnection; +import net.sourceforge.squirrel_sql.fw.sql.SQLDatabaseMetaData; +import net.sourceforge.squirrel_sql.fw.sql.SQLDriver; +import net.sourceforge.squirrel_sql.fw.sql.SQLDriverManager; + +import org.easymock.EasyMock; +import org.junit.After; +import org.junit.Before; +import org.junit.Test; + +/** + * Abstract Test, which ensures the functionality of {@link IWhereClausePart} + * with an specific DB-driver. + * @author Stefan Willinger + * + */ +public abstract class AbstractAdhocTests extends BaseSQuirreLJUnit4TestCase { + + private ISQLConnection sqlConn; + private String user; + private String pw; + private SQLDriver sqlDriver; + private String url; + private DialectType dialect; + private String driverClassName; + private String dbName; + private String[] jarFileNames; + + + public AbstractAdhocTests(String user, String pw, String url, String driverClassName , DialectType dialect, String dbName, String[] jarFileNames) { + this.user = user; + this.pw = pw; + this.url = url; + this.dialect = dialect; + this.driverClassName = driverClassName; + this.dbName = dbName; + this.jarFileNames = jarFileNames; + } + + + /** + * SetUp the test environment. + * @throws Exception + */ + @Before + public void setUp() throws Exception{ + initTestDriver(); + + ISQLAlias mockAlias = mockHelper.createMock(ISQLAlias.class); + EasyMock.expect(mockAlias.getUrl()).andReturn(url); + mockHelper.replayAll(); + + SQLDriverManager mg = new SQLDriverManager(); + + sqlConn = mg.getConnection(sqlDriver, mockAlias, user, pw); + sqlConn.setAutoCommit(false); + + assertNotNull(dialect); + + } + + protected void initTestDriver() throws ValidationException{ + sqlDriver = new SQLDriver(); + sqlDriver.setDriverClassName(driverClassName); + sqlDriver.setName(dbName); + sqlDriver.setUrl(url); + sqlDriver.setJarFileNames(jarFileNames); + } + + + + + + @Test + public void testConnect(){ + assertNotNull(sqlConn); + } + + /** + * Each row of the testtable has unique data. + * This test ensures, that we can find each row with the data provided, when we use {@link IWhereClausePart}. Escaping the special + * characters is done by the JDBC-Driver + * @throws Exception + */ + @Test + public void testCount() throws Exception{ + + String sql = "select * from basicTypes"; + + ResultSet rs = sqlConn.createStatement().executeQuery(sql); + + + List<ColumnDisplayDefinition> cols = new ArrayList<ColumnDisplayDefinition>(); + List<List<Object>> values = new ArrayList<List<Object>>(); + + + // Create ColumnDisplayDefinitions for each selected row, so that we can later create the where clause + while (rs.next()) { + int columnCount = rs.getMetaData().getColumnCount(); + if(cols.isEmpty()){ + for (int i = 1; i <= columnCount; i++) { + cols.add(new ColumnDisplayDefinition(rs, i, dialect)); + } + } + List<Object> v = new ArrayList<Object>(); + for (int i = 1; i <= columnCount; i++) { + v.add(CellComponentFactory.readResultSet(cols.get(i-1), rs, i, false)); + } + values.add(v); + + } + + SQLDatabaseMetaData md = sqlConn.getSQLMetaData(); + + + /* + * Create for each selected row a where clause and ensure that we find exactly the same row with this where clause + * This is for testing the escape functionality of the jdbc-driver. + */ + for (List<Object> row : values) { + Object[] anRow = row.toArray(); + List<IWhereClausePart> whereClause = new ArrayList<IWhereClausePart>(); + for (int i = 0; i < anRow.length; i++) { + Object value = anRow[i]; + whereClause.add(CellComponentFactory.getWhereClauseValue(cols.get(i), value, md)); + } + assertFalse(whereClause.isEmpty()); + + + IWhereClausePartUtil whereClausePartUtil = new WhereClausePartUtil(); + String where = whereClausePartUtil.createWhereClause(whereClause); + + String countSql = "select count(*) from basicTypes " + where; + System.out.println(countSql); + PreparedStatement pstmt = sqlConn.prepareStatement(countSql); + whereClausePartUtil.setParameters(pstmt, whereClause, 1); + ResultSet countRS = pstmt.executeQuery(); + countRS.next(); + assertEquals(1, countRS.getInt(1)); + } + + + } + + + @After + public void tearDown() throws SQLException{ + sqlConn.rollback(); + sqlConn.close(); + } +} Property changes on: trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/AbstractAdhocTests.java ___________________________________________________________________ Added: svn:mime-type + text/plain Added: trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/MySQLAdhocTests.java =================================================================== --- trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/MySQLAdhocTests.java (rev 0) +++ trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/MySQLAdhocTests.java 2011-03-13 16:46:55 UTC (rev 6184) @@ -0,0 +1,15 @@ +package net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause; + +import net.sourceforge.squirrel_sql.fw.dialects.DialectType; + +public class MySQLAdhocTests extends AbstractAdhocTests { + + public MySQLAdhocTests() { + super( + "wis", + "wis", + "jdbc:mysql://localhost/wis?sessionVariables=sql_mode=NO_BACKSLASH_ESCAPES", + "com.mysql.jdbc.Driver", DialectType.MYSQL5, "MySQL", + new String[] { "/usr/share/java/mysql.jar" }); + } +} Property changes on: trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/MySQLAdhocTests.java ___________________________________________________________________ Added: svn:mime-type + text/plain Added: trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/MySQLnoBackslashEscapesAdhocTests.java =================================================================== --- trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/MySQLnoBackslashEscapesAdhocTests.java (rev 0) +++ trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/MySQLnoBackslashEscapesAdhocTests.java 2011-03-13 16:46:55 UTC (rev 6184) @@ -0,0 +1,12 @@ +package net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause; + +import net.sourceforge.squirrel_sql.fw.dialects.DialectType; + +public class MySQLnoBackslashEscapesAdhocTests extends AbstractAdhocTests { + + public MySQLnoBackslashEscapesAdhocTests() { + super("wis", "wis", "jdbc:mysql://localhost/wis", + "com.mysql.jdbc.Driver", DialectType.MYSQL5, "MySQL", + new String[] { "/usr/share/java/mysql.jar" }); + } +} Property changes on: trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/MySQLnoBackslashEscapesAdhocTests.java ___________________________________________________________________ Added: svn:mime-type + text/plain Added: trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/OracleAdhocTests.java =================================================================== --- trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/OracleAdhocTests.java (rev 0) +++ trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/OracleAdhocTests.java 2011-03-13 16:46:55 UTC (rev 6184) @@ -0,0 +1,17 @@ +package net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause; + +import net.sourceforge.squirrel_sql.fw.dialects.DialectType; + +public class OracleAdhocTests extends AbstractAdhocTests { + + public OracleAdhocTests() { + super( + "wis", + "wis", + "jdbc:oracle:thin:@localhost:1521:XE", + "oracle.jdbc.driver.OracleDriver", + DialectType.ORACLE, + "Oracle", + new String[] { "/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/jdbc/lib/ojdbc14.jar" }); + } +} Property changes on: trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/OracleAdhocTests.java ___________________________________________________________________ Added: svn:mime-type + text/plain Added: trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/mysql.sql =================================================================== --- trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/mysql.sql (rev 0) +++ trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/mysql.sql 2011-03-13 16:46:55 UTC (rev 6184) @@ -0,0 +1,34 @@ +drop table basicTypes; + +create table basicTypes( + cint integer, + clong bigint, + cpresc decimal(9,2), + cfloat float, + cvarchar2 varchar(50), + cdate date, + ctimestamp timestamp +); + + + +-- an simple +insert into basicTypes values (1,12, 12.98,12.98,'simple', current_date(), current_timestamp()); +-- escapaes within an mysql +insert into basicTypes values (2,12, 12.98,12.98,'insert '' an quote', current_date(), current_timestamp()); +insert into basicTypes values (3,12, 12.98,12.98,'insert an ''quote'' word', current_date(), current_timestamp()); +insert into basicTypes values (4,12, 12.98,12.98,'insert an double ''''quote'''' word', current_date(), current_timestamp()); +insert into basicTypes values (5,12, 12.98,12.98,'insert an \\ backslash', current_date(), current_timestamp()); +insert into basicTypes values (6,12, 12.98,12.98,'insert an \\'' backslash quote', current_date(), current_timestamp()); +insert into basicTypes values (7,12, 12.98,12.98,'insert an \r \n CR LF', current_date(), current_timestamp()); +insert into basicTypes values (8,12, 12.98,12.98,'insert an \t tab', current_date(), current_timestamp()); +insert into basicTypes values (9,12, 12.98,12.98,'insert an \b backspace', current_date(), current_timestamp()); +-- some others +insert into basicTypes values (20,12, 12.98,12.98,'insert an % percent', current_date(), current_timestamp()); +insert into basicTypes values (21,12, 12.98,12.98,'insert an _ underscore', current_date(), current_timestamp()); +insert into basicTypes values (22,12, 12.98,12.98,'insert an & ampercent', current_date(), current_timestamp()); +insert into basicTypes values (23,12, 12.98,12.98,'insert an ? question mark', current_date(), current_timestamp()); +insert into basicTypes values (24,12, 12.98,12.98,'insert an :1 double point 1', current_date(), current_timestamp()); +insert into basicTypes values (25,12, 12.98,12.98,'insert an | vertical bar', current_date(), current_timestamp()); +insert into basicTypes values (26,12, 12.98,12.98,'insert an $ dollar', current_date(), current_timestamp()); + Added: trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/oracle.sql =================================================================== --- trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/oracle.sql (rev 0) +++ trunk/sql12/app/src/test/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/whereClause/oracle.sql 2011-03-13 16:46:55 UTC (rev 6184) @@ -0,0 +1,38 @@ + + + +drop TABLE basicTypes; + +create table basicTypes( + cint number(7), + clong number(19), + cpresc NUMBER(9,2), + cfloat BINARY_FLOAT, + cvarchar2 varchar2(50), + cdate date, + ctimestamp timestamp +); + + +-- an simple +insert into basicTypes values (1,12, 12.98,12.98,'simple', sysdate, systimestamp); +-- escapaes within an mysql +insert into basicTypes values (2,12, 12.98,12.98,'insert '' an quote', sysdate, systimestamp); +insert into basicTypes values (3,12, 12.98,12.98,'insert an ''quote'' word', sysdate, systimestamp); +insert into basicTypes values (4,12, 12.98,12.98,'insert an double ''''quote'''' word', sysdate, systimestamp); +insert into basicTypes values (5,12, 12.98,12.98,'insert an \ backslash', sysdate, systimestamp); +insert into basicTypes values (6,12, 12.98,12.98,'insert an \'' backslash quote', sysdate, systimestamp); +insert into basicTypes values (7,12, 12.98,12.98,'insert an '|| CHR(13) || CHR(10) || ' CR LF', sysdate, systimestamp); +insert into basicTypes values (8,12, 12.98,12.98,'insert an '|| CHR(09) || ' tab', sysdate, systimestamp); +insert into basicTypes values (9,12, 12.98,12.98,'insert an '|| CHR(08) || ' backspace', sysdate, systimestamp); +-- some others +insert into basicTypes values (20,12, 12.98,12.98,'insert an % percent', sysdate, systimestamp); +insert into basicTypes values (21,12, 12.98,12.98,'insert an _ underscore', sysdate, systimestamp); +insert into basicTypes values (22,12, 12.98,12.98,'insert an & ampercent', sysdate, systimestamp); +insert into basicTypes values (23,12, 12.98,12.98,'insert an ? question mark', sysdate, systimestamp); +insert into basicTypes values (24,12, 12.98,12.98,'insert an :1 double point 1', sysdate, systimestamp); +insert into basicTypes values (25,12, 12.98,12.98,'insert an | vertical bar', sysdate, systimestamp); +insert into basicTypes values (26,12, 12.98,12.98,'insert an $ dollar', sysdate, systimestamp); + + + Modified: trunk/sql12/doc/src/main/resources/changes.txt =================================================================== --- trunk/sql12/doc/src/main/resources/changes.txt 2011-03-11 17:20:23 UTC (rev 6183) +++ trunk/sql12/doc/src/main/resources/changes.txt 2011-03-13 16:46:55 UTC (rev 6184) @@ -25,6 +25,8 @@ Bug-fixes: +3088572: Squirrel fails when user tries to edit DB fields which have particular characters. + 3175871: On export to Excel, some numeric values are not exported correctly. SQL Scripts Plugin: Increased multi table script configuration dialog's size. Modified: trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/BaseDataTypeComponent.java =================================================================== --- trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/BaseDataTypeComponent.java 2011-03-11 17:20:23 UTC (rev 6183) +++ trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/BaseDataTypeComponent.java 2011-03-13 16:46:55 UTC (rev 6184) @@ -35,6 +35,9 @@ import net.sourceforge.squirrel_sql.fw.datasetviewer.CellDataPopup; import net.sourceforge.squirrel_sql.fw.datasetviewer.ColumnDisplayDefinition; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IWhereClausePart; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IsNullWhereClausePart; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.EmptyWhereClausePart; import net.sourceforge.squirrel_sql.fw.sql.ISQLDatabaseMetaData; import net.sourceforge.squirrel_sql.fw.util.log.ILogger; import net.sourceforge.squirrel_sql.fw.util.log.LoggerController; @@ -297,11 +300,11 @@ * * @see net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.IDataTypeComponent#getWhereClauseValue(java.lang.Object, net.sourceforge.squirrel_sql.fw.sql.ISQLDatabaseMetaData) */ - public String getWhereClauseValue(Object value, ISQLDatabaseMetaData md) { + public IWhereClausePart getWhereClauseValue(Object value, ISQLDatabaseMetaData md) { if (value == null || value.toString() == null ) { - return _colDef.getColumnName() + " IS NULL"; + return new IsNullWhereClausePart(_colDef); } else { - return ""; + return new EmptyWhereClausePart(); } } Modified: trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/CellComponentFactory.java =================================================================== --- trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/CellComponentFactory.java 2011-03-11 17:20:23 UTC (rev 6183) +++ trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/CellComponentFactory.java 2011-03-13 16:46:55 UTC (rev 6184) @@ -22,7 +22,9 @@ import javax.swing.table.DefaultTableCellRenderer; import javax.swing.table.TableCellRenderer; + import net.sourceforge.squirrel_sql.fw.datasetviewer.ColumnDisplayDefinition; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IWhereClausePart; import net.sourceforge.squirrel_sql.fw.dialects.DialectType; import net.sourceforge.squirrel_sql.fw.gui.OkJPanel; import net.sourceforge.squirrel_sql.fw.sql.ISQLDatabaseMetaData; @@ -589,7 +591,7 @@ * "columnName is null" * or whatever is appropriate for this column in the database. */ - public static String getWhereClauseValue(ColumnDisplayDefinition colDef, + public static IWhereClausePart getWhereClauseValue(ColumnDisplayDefinition colDef, Object value, ISQLDatabaseMetaData md) { IDataTypeComponent dataTypeObject = getDataTypeObject(null, colDef); @@ -1087,5 +1089,5 @@ } return dataTypeComponent; } - + } Modified: trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeBigDecimal.java =================================================================== --- trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeBigDecimal.java 2011-03-11 17:20:23 UTC (rev 6183) +++ trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeBigDecimal.java 2011-03-13 16:46:55 UTC (rev 6184) @@ -38,6 +38,9 @@ import net.sourceforge.squirrel_sql.fw.datasetviewer.CellDataPopup; import net.sourceforge.squirrel_sql.fw.datasetviewer.ColumnDisplayDefinition; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IWhereClausePart; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IsNullWhereClausePart; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.ParameterWhereClausePart; import net.sourceforge.squirrel_sql.fw.sql.ISQLDatabaseMetaData; import net.sourceforge.squirrel_sql.fw.util.StringManager; import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory; @@ -125,6 +128,7 @@ // If we use _scale here some number displays go crazy. //_numberFormat.setMaximumFractionDigits(_scale); _numberFormat.setMaximumFractionDigits(maximumFractionDigits); + _numberFormat.setMinimumFractionDigits(0); @@ -494,11 +498,11 @@ * "columnName is null" * or whatever is appropriate for this column in the database. */ - public String getWhereClauseValue(Object value, ISQLDatabaseMetaData md) { + public IWhereClausePart getWhereClauseValue(Object value, ISQLDatabaseMetaData md) { if (value == null || value.toString() == null || value.toString().length() == 0) - return _colDef.getColumnName() + " IS NULL"; + return new IsNullWhereClausePart(_colDef); else - return _colDef.getColumnName() + "=" + value.toString(); + return new ParameterWhereClausePart(_colDef, value, this); } Modified: trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeBinary.java =================================================================== --- trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeBinary.java 2011-03-11 17:20:23 UTC (rev 6183) +++ trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeBinary.java 2011-03-13 16:46:55 UTC (rev 6184) @@ -35,6 +35,9 @@ import net.sourceforge.squirrel_sql.fw.datasetviewer.CellDataPopup; import net.sourceforge.squirrel_sql.fw.datasetviewer.ColumnDisplayDefinition; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IWhereClausePart; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IsNullWhereClausePart; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.EmptyWhereClausePart; import net.sourceforge.squirrel_sql.fw.sql.ISQLDatabaseMetaData; import net.sourceforge.squirrel_sql.fw.util.StringUtilities; @@ -408,13 +411,16 @@ * "columnName is null" * or whatever is appropriate for this column in the database. */ - public String getWhereClauseValue(Object value, ISQLDatabaseMetaData md) { - if (value == null || value.toString() == null || value.toString().length() == 0) - return _colDef.getColumnName() + " IS NULL"; - else + public IWhereClausePart getWhereClauseValue(Object value, ISQLDatabaseMetaData md) { + if (value == null || value.toString() == null || value.toString().length() == 0){ + return new IsNullWhereClausePart(_colDef); + }else{ //?? There does not seem to be any standard way to represent //?? binary data in a WHERE clause... - return null; // tell caller we cannot use this in Where clause + // tell caller we cannot use this in Where clause + // TODO Review, if this DataType could not be used in a where clause + return new EmptyWhereClausePart(); + } } Modified: trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeBlob.java =================================================================== --- trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeBlob.java 2011-03-11 17:20:23 UTC (rev 6183) +++ trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeBlob.java 2011-03-13 16:46:55 UTC (rev 6184) @@ -48,6 +48,9 @@ import net.sourceforge.squirrel_sql.fw.datasetviewer.CellDataPopup; import net.sourceforge.squirrel_sql.fw.datasetviewer.ColumnDisplayDefinition; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IWhereClausePart; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IsNullWhereClausePart; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.EmptyWhereClausePart; import net.sourceforge.squirrel_sql.fw.sql.ISQLDatabaseMetaData; import net.sourceforge.squirrel_sql.fw.util.StringManager; import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory; @@ -563,11 +566,14 @@ * "columnName is null" * or whatever is appropriate for this column in the database. */ - public String getWhereClauseValue(Object value, ISQLDatabaseMetaData md) { + public IWhereClausePart getWhereClauseValue(Object value, ISQLDatabaseMetaData md) { if (value == null || ((BlobDescriptor)value).getData() == null) - return _colDef.getColumnName() + " IS NULL"; - else - return ""; // BLOB cannot be used in WHERE clause + return new IsNullWhereClausePart(_colDef); + else{ + // BLOB cannot be used in WHERE clause + // TODO Review, if this DataType could not be used in a where clause + return new EmptyWhereClausePart(); + } } Modified: trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeBoolean.java =================================================================== --- trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeBoolean.java 2011-03-11 17:20:23 UTC (rev 6183) +++ trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeBoolean.java 2011-03-13 16:46:55 UTC (rev 6184) @@ -38,6 +38,9 @@ import net.sourceforge.squirrel_sql.fw.datasetviewer.CellDataPopup; import net.sourceforge.squirrel_sql.fw.datasetviewer.ColumnDisplayDefinition; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IWhereClausePart; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IsNullWhereClausePart; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.ParameterWhereClausePart; import net.sourceforge.squirrel_sql.fw.sql.ISQLDatabaseMetaData; /** @@ -363,17 +366,16 @@ * label so that its output is of the form: "columnName = value" or "columnName is null" or whatever is * appropriate for this column in the database. */ - public String getWhereClauseValue(Object value, ISQLDatabaseMetaData md) + public IWhereClausePart getWhereClauseValue(Object value, ISQLDatabaseMetaData md) { if (value == null || value.toString() == null || value.toString().length() == 0) { - return _colDef.getColumnName() + " IS NULL"; + return new IsNullWhereClausePart(_colDef); } else { - String bitValue = DatabaseSpecificBooleanValue.getBooleanValue(value.toString(), md); - return _colDef.getColumnName() + "=" + bitValue; + return new ParameterWhereClausePart(_colDef, value, this); } } Modified: trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeByte.java =================================================================== --- trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeByte.java 2011-03-11 17:20:23 UTC (rev 6183) +++ trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeByte.java 2011-03-13 16:46:55 UTC (rev 6184) @@ -35,6 +35,9 @@ import net.sourceforge.squirrel_sql.fw.datasetviewer.CellDataPopup; import net.sourceforge.squirrel_sql.fw.datasetviewer.ColumnDisplayDefinition; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IWhereClausePart; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IsNullWhereClausePart; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.ParameterWhereClausePart; import net.sourceforge.squirrel_sql.fw.sql.ISQLDatabaseMetaData; /** @@ -386,11 +389,11 @@ * "columnName is null" * or whatever is appropriate for this column in the database. */ - public String getWhereClauseValue(Object value, ISQLDatabaseMetaData md) { + public IWhereClausePart getWhereClauseValue(Object value, ISQLDatabaseMetaData md) { if (value == null || value.toString() == null || value.toString().length() == 0) - return _colDef.getColumnName() + " IS NULL"; + return new IsNullWhereClausePart(_colDef); else - return _colDef.getColumnName() + "=" + value.toString(); + return new ParameterWhereClausePart(_colDef, value, this); } Modified: trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeClob.java =================================================================== --- trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeClob.java 2011-03-11 17:20:23 UTC (rev 6183) +++ trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeClob.java 2011-03-13 16:46:55 UTC (rev 6184) @@ -47,6 +47,9 @@ import net.sourceforge.squirrel_sql.fw.datasetviewer.CellDataPopup; import net.sourceforge.squirrel_sql.fw.datasetviewer.ColumnDisplayDefinition; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IWhereClausePart; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IsNullWhereClausePart; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.EmptyWhereClausePart; import net.sourceforge.squirrel_sql.fw.gui.IntegerField; import net.sourceforge.squirrel_sql.fw.gui.OkJPanel; import net.sourceforge.squirrel_sql.fw.gui.ReadTypeCombo; @@ -591,11 +594,13 @@ * "columnName is null" * or whatever is appropriate for this column in the database. */ - public String getWhereClauseValue(Object value, ISQLDatabaseMetaData md) { + public IWhereClausePart getWhereClauseValue(Object value, ISQLDatabaseMetaData md) { if (value == null || ((ClobDescriptor)value).getData() == null) - return _colDef.getColumnName() + " IS NULL"; + return new IsNullWhereClausePart(_colDef); else - return ""; // CLOB cannot be used in WHERE clause + // CLOB cannot be used in WHERE clause + // TODO Review, if this DataType could not be used in a where clause + return new EmptyWhereClausePart(); } /** Modified: trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeDate.java =================================================================== --- trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeDate.java 2011-03-11 17:20:23 UTC (rev 6183) +++ trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeDate.java 2011-03-13 16:46:55 UTC (rev 6184) @@ -46,6 +46,9 @@ import net.sourceforge.squirrel_sql.fw.datasetviewer.CellDataPopup; import net.sourceforge.squirrel_sql.fw.datasetviewer.ColumnDisplayDefinition; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IWhereClausePart; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IsNullWhereClausePart; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.NoParameterWhereClausePart; import net.sourceforge.squirrel_sql.fw.dialects.DialectFactory; import net.sourceforge.squirrel_sql.fw.gui.OkJPanel; import net.sourceforge.squirrel_sql.fw.gui.RightLabel; @@ -529,11 +532,11 @@ * "columnName is null" * or whatever is appropriate for this column in the database. */ - public String getWhereClauseValue(Object value, ISQLDatabaseMetaData md) + public IWhereClausePart getWhereClauseValue(Object value, ISQLDatabaseMetaData md) { if (value == null || value.toString() == null || value.toString().length() == 0) { - return _colDef.getColumnName() + " IS NULL"; + return new IsNullWhereClausePart(_colDef); } else { @@ -545,17 +548,17 @@ if (hasTimeComponent && hasDateComponent) { // treat it like a timestamp - return _colDef.getColumnName() + "={ts '" + value.toString() + "'}"; + return new NoParameterWhereClausePart(_colDef, _colDef.getColumnName() + "={ts '" + value.toString() + "'}"); } else if (hasTimeComponent) { // treat it like a time - no date component - return _colDef.getColumnName() + "={t '" + value.toString() + "'}"; + return new NoParameterWhereClausePart(_colDef, _colDef.getColumnName() + "={t '" + value.toString() + "'}"); } else { if (DialectFactory.isOracle(md)) { // Oracle stores time information in java.sql.Types.Date columns // This tells Oracle that we are only talking about the date part. - return "trunc(" + _colDef.getColumnName() + ")={d '" + value.toString() + "'}"; + return new NoParameterWhereClausePart(_colDef, "trunc(" + _colDef.getColumnName() + ")={d '" + value.toString() + "'}"); } else { - return _colDef.getColumnName() + "={d '" + value.toString() + "'}"; + return new NoParameterWhereClausePart(_colDef, _colDef.getColumnName() + "={d '" + value.toString() + "'}"); } } } Modified: trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeDouble.java =================================================================== --- trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeDouble.java 2011-03-11 17:20:23 UTC (rev 6183) +++ trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeDouble.java 2011-03-13 16:46:55 UTC (rev 6184) @@ -38,6 +38,9 @@ import net.sourceforge.squirrel_sql.fw.datasetviewer.CellDataPopup; import net.sourceforge.squirrel_sql.fw.datasetviewer.ColumnDisplayDefinition; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IWhereClausePart; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IsNullWhereClausePart; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.EmptyWhereClausePart; import net.sourceforge.squirrel_sql.fw.sql.ISQLDatabaseMetaData; import net.sourceforge.squirrel_sql.fw.util.log.ILogger; import net.sourceforge.squirrel_sql.fw.util.log.LoggerController; @@ -101,7 +104,7 @@ _colDef = colDef; _isNullable = colDef.isNullable(); _numberFormat = NumberFormat.getInstance(); - + // If we use _scale here some number displays go crazy. _numberFormat.setMaximumFractionDigits(maximumFractionDigits); @@ -418,17 +421,17 @@ * label so that its output is of the form: "columnName = value" or "columnName is null" or whatever is * appropriate for this column in the database. */ - public String getWhereClauseValue(Object value, ISQLDatabaseMetaData md) + public IWhereClausePart getWhereClauseValue(Object value, ISQLDatabaseMetaData md) { if (value == null || value.toString() == null || value.toString().length() == 0) { - return _colDef.getColumnName() + " IS NULL"; + return new IsNullWhereClausePart(_colDef); } else { // since we cannot do exact matches on floating point // numbers, we cannot use this field in the WHERE clause. - return null; + return new EmptyWhereClausePart(); } } Modified: trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeFloat.java =================================================================== --- trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeFloat.java 2011-03-11 17:20:23 UTC (rev 6183) +++ trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeFloat.java 2011-03-13 16:46:55 UTC (rev 6184) @@ -37,6 +37,9 @@ import net.sourceforge.squirrel_sql.fw.datasetviewer.CellDataPopup; import net.sourceforge.squirrel_sql.fw.datasetviewer.ColumnDisplayDefinition; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IWhereClausePart; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IsNullWhereClausePart; +import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.EmptyWhereClausePart; import net.sourceforge.squirrel_sql.fw.sql.ISQLDatabaseMetaData; import net.sourceforge.squirrel_sql.fw.util.log.ILogger; import net.sourceforge.squirrel_sql.fw.util.log.LoggerController; @@ -421,13 +424,13 @@ * "columnName is null" * or whatever is appropriate for this column in the database. */ - public String getWhereClauseValue(Object value, ISQLDatabaseMetaData md) { + public IWhereClausePart getWhereClauseValue(Object value, ISQLDatabaseMetaData md) { if (value == null || value.toString() == null || value.toString().length() == 0) { - return _colDef.getColumnName() + " IS NULL"; + return new IsNullWhereClausePart(_colDef); } else { // since we cannot do exact matches on floating point // numbers, we cannot use this field in the WHERE clause. - return null; + return new EmptyWhereClausePart(); } } Modified: trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeInteger.java =================================================================== --- trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/DataTypeInteger.java 2011-03-11 17:20:23 UTC (rev 6183) +++ trunk/sql12/fw/src/main... [truncated message content] |