Thread: [Squirrel-sql-develop] Ways to solve Bug 3088572 - "Error while editing some fields in Squirrel"

A Java SQL client for any JDBC compliant database

Brought to you by: colbell, gerdwagner

squirrel-sql-develop

[Squirrel-sql-develop] Ways to solve Bug 3088572 - "Error while editing some fields in Squirrel"

From: Stefan W. <ste...@ao...> - 2010-10-25 14:14:40

Hi Rob,

we have already discussed the reason, why Squirrel cannot edit db-fields 
with an backslash on an MySql-DB.

It seems that for an PostgresSQL-DB the same problem was solved by using 
an DatabaseSpecificEscape  e.g. PostgreSQLEscape

Now sure, we could do the same for an MySql-DB like this:

  private static class MySqlSQLEscape implements IEscape
    {
       public boolean productMatches(ISQLDatabaseMetaData md)
       {
           return DialectFactory.isMySQL5(md);
       }
     /**
      *  For a MySQL Database we need to escape some characters.
      * <a 
href="http://dev.mysql.com/doc/refman/5.1/de/string-syntax.html">See the 
MySQL Manual </a
      * @see 
net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DatabaseSpecificEscape.IEscape#escapeSQL(java.lang.String)
      */
     public String escapeSQL(String sql)
       {
          return sql.replaceAll("\\\\","\\\\\\\\");
       }
   }

Sure, this code would be very simple, but it doesn't work on all MySql 
DB-Configuration. At an MySQL DB you can set the SQL-MODE to 
NO_BACKSLASH_ESCAPES. Enabling this mode, than an escaped Backslash 
would be treated as two backslashes. Then this code would fail.

Sure, Squirrel could check, if escaping an Backslash is necessary. e.g . 
with this SQL: SELECT @@SESSION.sql_mode

Another drawback of using an DatabaseSpecificEscape is, that the 
escapeSQL(string) method is applied to the whole where clause, not only 
to string-fields, which might be a problem.

*Another solution:*
On the other side, when using PreparedStatements, then the DB-Driver is 
responsible for escaping special characters. But what changes would this 
mean:

    * Changing the signature of
      IDataTypeComponent.getWhereClauseValue(Object, ISQLDatabaseMetaData)
    * Changing the implementation of all DataTypes
    * Changing the implementation of DataSetUpdateableTableModelImpl
          o deleteRows
          o getWarningOnCurrentData
          o getWarningOnProjectedUpdate
          o getWhereClause
          o reReadDatum
          o updateTableComponent

When doing so, some duplicate code about counting affected rows could be 
removed.

The solution I would discuss is following:

    * The DataType should return an Interface WhereClausePart instead of
      an String representation like column=value.
    * The WhereClausePart should have following methods
          o public String getWhereClause();
          o public void appendToClause(StringBuilder whereClause);
          o public void setParameter(PreparedStatement pstmt, int
            position) throws SQLException;
          o public boolean isParameterUsed();
    * The WhereClausePart could have several implementations:
          o AbstractWhereClausePart: Basic implementation which have a
            reference to IDataTypeComponent for the capability to set
            the parameters into the PreparedStatements.
          o NopWhereClausePart: Instead of returning an empty String,
            Return this for an "No Operation" meaning.
          o IsNullWhereClausePart: This part of the where-clause
            represents an "is null" statement
          o ParameterWhereClausePart: This clause use an JDBC-Parameter
            for the where clause
          o NoParameterWhereClausePart: For all special situations like
            DataTypeDate with the trunc-method. The benefit of this
            class is, that each DataType could decide if it want to use
            an parameter or the hard-coded part of the where clause.
    * Change DataSetUpdateableTableModelImpl to use PreparedStatements.

So, what did you think, is this an appropriated solution for this bug 
and an additional value for Squirrel?

When yes, let it me know, I have already done most of the work :-)

Stefan