Re: [jira] Commented: (DERBY-37) detection of incorrect types comparison is done at ? parameters

[Shreyas Kaushik]

There are two "?" in your query I see , check whether both of them are 
being set with types that are comparable with them. May be you should 
run over your table structures as well just to ensure that the columns 
have the appropriate data types.

You can also look at 
http://incubator.apache.org/derby/manuals/reference/sqlj125.html#HDRSII-SQLJ-58560

Hope this helps.
~ Shreyas
Mamta A. Satoor (JIRA) wrote:
    [ http://issues.apache.org/jira/browse/DERBY-37?page=comments#action_64845 ]
    
Mamta A. Satoor commented on DERBY-37:
--------------------------------------

I tried following on Derby and didn't get any comparison errors. 
   s.executeUpdate("create table xxxx (COLUMN_A INT, COLUMN_B INT )");
   PreparedStatement ps = con.prepareStatement("select * from xxxx where COLUMN_B >= ?");
   ps.setInt(1,1);
   ps.execute();

 

detection of incorrect types comparison is done at ? parameters
---------------------------------------------------------------
        Key: DERBY-37
        URL: http://issues.apache.org/jira/browse/DERBY-37
    Project: Derby
       Type: Bug
Environment: Cloudscape 10 beta 
   Reporter: Erik Bengtson
   

 

java code:
PreparedStatement ps = conn.prepareStatement(statement);
This statement:
SELECT THIS.CODE_EID,THIS.COMPOSED_EID,'org.jpox.samples.applicationidentity.ChildComposedIntID' as JPOXMETADATA ,THIS.INTEGER_IDX AS JPOXORDER0,ELEMENT_1.CODE,ELEMENT_1.COMPOSED,ELEMENT_1.DESCRIPTION,ELEMENT_1."NAME" FROM NORMALLISTAPPLICATIONID_COMPOS THIS INNER JOIN CHILDCOMPOSEDINTID "ELEMENT" ON THIS.CODE_EID = "ELEMENT".CODE AND THIS.COMPOSED_EID = "ELEMENT".COMPOSED INNER JOIN COMPOSEDINTID ELEMENT_1 ON "ELEMENT".CODE = ELEMENT_1.CODE AND "ELEMENT".COMPOSED = ELEMENT_1.COMPOSED WHERE THIS.NORMALLISTAPPLICATIONID_ID_OID = ? AND THIS.INTEGER_IDX >= ? ORDER BY JPOXORDER0
results in:
SQL Exception: Comparisons between 'VARCHAR' and 'INTEGER' are not supported.
possible cause:
The INTEGER_IDX is an INTEGER column. While running the prepareStatement, JDBC/Cloudscape thinks I'm comparing INTEGER_IDX with ? (question mark) (INTEGER vs VARCHAR). This is not true, ? (question mark) is a parameter that will be later substitued in my code by an integer value.