[jira] [Created] (CAY-1745) Support for LIKE expressions against numeric columns

["Andrus Adamchik (JIRA)" <ji...@apache.org>]

Andrus Adamchik created CAY-1745:
------------------------------------

             Summary: Support for LIKE expressions against numeric columns
                 Key: CAY-1745
                 URL: https://issues.apache.org/jira/browse/CAY-1745
             Project: Cayenne
          Issue Type: Improvement
          Components: Core Library
            Reporter: Andrus Adamchik
            Assignee: Andrus Adamchik


assume this schema: CREATE TABLE X ( C1 INT, ...);
assume these expressions: 
    c1 like '%1%'
    c1 likeIgnoreCase '%2%'

Presumably here we are asking to convert C1 to VARCHAR and then do a match against the LIKE pattern. 

MySQL: This works with MySQL out of the box. 

SQLServer: This works with SQLServer when running raw SQL. When running a SelectQuery, it fails with exception [1]. My understanding is that the cause of the exception is Cayenne binding the pattern as INT to PreparedStatement. So I am thinking we can fix it by always doing LIKE and LIKE (ignore case) pattern parameter binding as String (which logically makes sense anyways). 

Other databases: no information.

So let's maybe create conditional unit tests, fix the issue with SQLServer and then test with other databases to see how they'd react to this scenario. Worst case the new binding approach will work in all cases where the old one worked, but fail against numeric columns on some DBs that are strict about type checking and implicit conversions.

[1] [09/Oct/2012:15:47:35] qtp1186221748-19  INFO  CommonsJdbcEventLogger: *** error.
com.microsoft.sqlserver.jdbc.SQLServerException: Error converting data type nvarchar to int.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
4:00
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
    at org.apache.cayenne.access.jdbc.SelectAction.performAction(SelectAction.java:75)





--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira