Derby
  1. Derby
  2. DERBY-37

detection of incorrect types comparison is done at ? parameters

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Cannot Reproduce
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Cloudscape 10 beta

      Description

      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.

        Activity

        Hide
        Jan Hlavatý added a comment -

        If this is the whole error message then error reporting in Derby needs an overhaul I hate error messages of the type "Some error occured, but you have to guess what it is".

        Are you sure its not something else? It's hard for us to tell without all the tables we don't have. Did you try removing the part with ? params? If you could strip the statement down to something reproducible on our side, it would help

        Show
        Jan Hlavatý added a comment - If this is the whole error message then error reporting in Derby needs an overhaul I hate error messages of the type "Some error occured, but you have to guess what it is". Are you sure its not something else? It's hard for us to tell without all the tables we don't have. Did you try removing the part with ? params? If you could strip the statement down to something reproducible on our side, it would help
        Hide
        Erik Bengtson added a comment -

        "SQL Exception: Comparisons between 'VARCHAR' and 'INTEGER' are not supported."

        The above text between quotes is the Derby exception. I suggest that you try this to reproduce:

        create table xxxx ( COLUMN_A INT, COLUMN_B INT )

        conn.prepareStatement("SELECT * FROM xxxx where COLUMN_B >= ?");

        -------------------

        The same code runs fine with MSSQL, MySQL, Postgresql, Oracle and others. If you need more information, ask me.

        Show
        Erik Bengtson added a comment - "SQL Exception: Comparisons between 'VARCHAR' and 'INTEGER' are not supported." The above text between quotes is the Derby exception. I suggest that you try this to reproduce: create table xxxx ( COLUMN_A INT, COLUMN_B INT ) conn.prepareStatement("SELECT * FROM xxxx where COLUMN_B >= ?"); ------------------- The same code runs fine with MSSQL, MySQL, Postgresql, Oracle and others. If you need more information, ask me.
        Hide
        Mamta A. Satoor added a comment -

        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();

        Show
        Mamta A. Satoor added a comment - 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();
        Hide
        Daniel John Debrunner added a comment -

        Should this be resolved as 'cannot reproduce'? Unless the sql definitions for the tables involved in the original query are supplied I don't see any way to move forward on this.

        Show
        Daniel John Debrunner added a comment - Should this be resolved as 'cannot reproduce'? Unless the sql definitions for the tables involved in the original query are supplied I don't see any way to move forward on this.
        Hide
        Erik Bengtson added a comment -

        please resolve it. if I get this again, I report it with complete test case

        Show
        Erik Bengtson added a comment - please resolve it. if I get this again, I report it with complete test case
        Hide
        Satheesh Bandaram added a comment -

        Looks like Mamta tried to reproduce the problem and wasn't successful. Erik (orignial reporter) said OK to close this for now.

        Show
        Satheesh Bandaram added a comment - Looks like Mamta tried to reproduce the problem and wasn't successful. Erik (orignial reporter) said OK to close this for now.

          People

          • Assignee:
            Unassigned
            Reporter:
            Erik Bengtson
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development