Remove support for PersistenceFactory.supportsSetNullInWhere()

Hi Ralf,

I am little bit un-clear about this task. Kindly Can you please elaborate it little bit. What exactly we mean by two separate implementations for SQLStatementStore.

Thanks and Regards.

Hi Ahmad, when creating the issue my original intention was to make 2 implementations based upon the return value of

_factory.supportsSetNullInWhere()

If these method returns true we use a the prepared statement as build during construction of SQLStatementStore independend of the values to bind. If the method returnes false we have to build a new statement dependend on the values to bind. If one of the values is null the original condition (x=?) has to be replaced by a check for null (x is null).

Reading some docs about JDBC today and looking at the database engines that support _factory.supportsSetNullInWhere()=true (only sybase), I am now thinking if it realy makes sense to support the true case. According to JDBC docs all database engines will work when we use 'is null' while I do not know when it has been tested last if sybase works correctly with x=? when null is bound to this parameter.

Any opinions?

Hi Ralf, I studied the documentation of 'TYPE 4 JDBC Driveer for sybase database'. It says the following

"When the Sybase driver establishes a connection, the driver sets the Sybase database option ansinull to on. Setting ansinull to on ensures that the driver is compliant with the ANSI SQL standard and is consistent with the behavior of other DataDirect Connect for JDBC drivers, which simplifies developing cross-database applications.

By default, Sybase does not evaluate NULL values in SQL equality (=) comparisons in an ANSI SQL-compliant manner. For example, the ANSI SQL specification defines that col1=null always evaluates to false. Using the default database setting (ansinull=off), if the value of col1 in the following statement is NULL, the comparison evaluates to true instead of false:

SELECT * FROM table WHERE col1 = NULL

Setting ansinull to on changes the default database behavior so that SQL statements must use IS NULL instead of =NULL. For example, using the Sybase driver, if the value of col1 in the following statement is NULL, the comparison evaluates to true:

SELECT * FROM table WHERE col1 IS NULL"

As mentioned above, this is an issue of ANSI SQL standard. As the JDBC driver is now making sure that the SYBASE should behave according to ANSI SQL standard, it means that 'is NULL' will work in sybase too. I didn't work on sybase at all so I cannot comment on whether old jdbc drivers supports that or not.

What do you comment?

Regards, Ahmad

According to Ahmad's studies of Sybase documentation I think we should also omit to use:

UPDATE table SET ... WHERE field = NULL

and always use:

UPDATE table SET ... WHERE field IS NULL

This means the call to _factory.supportsSetNullInWhere() will always return FALSE for all database engines. Therefore we can completle remove this method and the different handling of updates in SQLStatementStore related to the result of this method.

As there now is no reason any more to split SQLStatementStore into 2 implementations with regard to PersistenceFactory.supportsSetNullInWhere() we don't need this issue any more. I therefore will rename it to:

Remove support for PersistenceFactory.supportsSetNullInWhere()

As a result of this refactoring _statementDirty will almost never be executed as is. Instead in most cases it will be modified in getStoreStatement() method dependend on the values of the old entity being null or not. I personally do not like code that modifies strings if there is a chance to build from scratch which is less sensitive to errors and much easier to understand. Therefore I tend to only build _statementLazy at buildStatement() method and add the whole where condition during getStoreStatement() method. I'll also create a new subtask for this work.

Changed summary of issue as outlined in previous comment.

I would like to add the reference of the stuff I mentioned in the last comment.

http://e-docs.bea.com/wls/docs103/jdbc_drivers/sybase.html

The document is about WebLogic Type 4 JDBC Sybase driver

Regards, Ahmad

The patch involved the removal of supportsSetNullInWhere method calls from SQLStatementStore. The patch also includes the removal of methods supportsSetNullInWhere() from factory classes shown in the attached image.

Regards, Ahmad

Just to add one comment, The submitted patch includes some reformatting according to checkstyle which involves the moving of else block code outside the else in getStoreStatement method.

Regards, Ahmad

Committed patch as is.