Announcement Announcement Module
Collapse
No announcement yet.
like clause in select statement Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • like clause in select statement

    Hi
    I am having a problem when I execute the following sql statement.
    I am using Firebird1.5 as application database.

    select * from specimen where specimen_Id like '%xyz% ;

    This statement when executed on a sql prompt returns 3 records.

    but the same statement when executed in the code returns 0 records.

    Heres the code that gets executed.

    Code:
     public static final String GET_SPECIMEN_BY_SPECIMEN_ID = 
        "SELECT  * FROM specimen WHERE specimen_id like ?";
    
    SpecimenMappingQuery custQry = new SpecimenMappingQuery();
                custQry.setSql(SpecimenDAO.GET_SPECIMEN_BY_SPECIMEN_ID);
                custQry.setDataSource(dataSource);
                custQry.declareParameter(new SqlParameter("specimen_id", Types.VARCHAR));
                custQry.compile();
                Object[] parm = new Object[1];
                parm[0] = new String(ISpectrackDAO.START_SINGLE_QUOTE + 
                                     specimenId +
                                     ISpectrackDAO.END_SINGLE_QUOTE );
                specimenList = custQry.execute(parm);
                if (specimenList != null) {
                    logger.debug(" DEBUG : getSpecimensBySpecimenId() : Records fetched =  \t " + specimenList.size()) ;
                 }

    How do I resolve this issue?

    Thanks in advance

    -Manju

  • #2
    Manju, you don't need to quote strings if you're using a prepared statment.

    Try changing this

    Code:
    parm[0] = new String(ISpectrackDAO.START_SINGLE_QUOTE +
                                     specimenId +
                                     ISpectrackDAO.END_SINGLE_QUOTE );
    to this

    Code:
    parm[0] = specimenId;
    Ollie

    Comment


    • #3
      The static variable START_SINGLE_QUOTE in the code refers to a string
      "'%" and END_SINGLE_QUOTE refers to "%'"
      The strings include a single_quote and a % sign

      I need to execute the select statement with the like clause.
      I want the query to select all the specimens with specimen id matching the substring '12'
      sp my sql query should look like
      select * from specimen where specimenId like '%12%'
      this query should select specimen id like 12abc, a12bc, abc12

      I get the same results when I execute the query from the sql prompt.
      but when executed from the code, it returns 0 records..No error is reported.

      Any help is appreciated.
      Thanks

      Comment


      • #4
        Like I said you DON'T need to quote the string.

        Code:
        START_SINGLE_QUOTE = "%"
        END_SINGLE_QUOTE = "%"
        Note that there is NO single quote. If you put in the quotes it will get escaped by the JDBC driver and the query that gets run on the server is this:

        Code:
        select * from specimen where specimenId like '''%12%'''

        Ollie

        Comment


        • #5
          Thanks
          It worked..I removed the single quotes and it worked like a charm

          Comment

          Working...
          X