[FIXED] prepared statement missing bind variable within a LIKE clause

Issue

I have a prepared statement defined as:

selectLikeShortnameStmt = cassandraDatasource.getSession().prepare(" select  id,
     parent_id, ultimate_parent_id, internal_ref, short_name,
     long_name, controlling_team from counterparty where short_name like'%?%'");

but when I try to use this in the DAO like:

PreparedStatement pStmt = statementFactory.getSelectLikeShortnameStmt();
BoundStatement bStmt = pStmt.bind( short_name );

I get an error saying the Prepared statement has no bind variables. To implement the like clause I am using the SASI index functionality ie:

CREATE CUSTOM INDEX short_name_like_index ON counterparty (short_name)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = { 'mode': 'CONTAINS' };

Solution

The above post worked fine for straightforward PreparedStatement with bind variable but I had another similar issue. I am using the Apache Lucerne index builder for more complex queries ie. ranges with an index definition like:

CREATE CUSTOM INDEX counterparty_column_index ON counterparty (filter_column)
USING 'com.stratio.cassandra.lucene.Index'
WITH OPTIONS = {
    'schema' : '{
        fields : {
        controlling_team     : {type : "text", analyzer : "english"},
        relationship_manager : {type : "text", analyzer : "english"},
        review_date          : {type : "date", pattern : "dd-MM-yyyy"}
        }
    }'
};

and the statement defined as :

    log.info("CounterpartyStatement [selectByReviewDateAndTeamStmt]");
    selectByReviewDateAndTeamStmt       =  cassandraDatasource.getSession().prepare(" select  id, parent_id, ultimate_parent_id, internal_ref, short_name, long_name, controlling_team, " +
                                            " country_incorp, country_operate, company_reg, relationship_manager, credit_rating, rating_source, pd, lgd, review_date, sector, defaulted, own_bank_entity " +
                                            " from counterparty where filter_column = '{ " 
                                                         + " filter : {type:\"range\", field:\"review_date\", lower:\"01-01-2000\", upper:\"%s\"},"
                                                         + " query : {type:\"contains\", field:\"controlling_team\", values:[\"%s\"]},"
                                                         + " refresh:true"
                                                         + " }'");   

This formatting was necessary because the standard PreparedStatement bind was not picking up the usual ? placeholders.

The code then became:

log.info("CounterpartyDAO.getRmCounterpartiesForReview [" + rm + "]");
    PreparedStatement pStmt = statementFactory.getSelectByReviewDateAndRmStmt();

    String query = pStmt.getQueryString();
    Date maxDate = dateUtils.addDays( dateUtils.getToday(), 3);
    String maxDateString = dateUtils.getAnyDate(maxDate, "dd-MM-yyyy");
    query = String.format( query, maxDateString, rm );

    ResultSet rs = cassandraDatasource.getSession().execute( query );

which also worked fine, the dateUtils is just a package I wrote for manipulating dates etc.

Answered By - simteq
Answer Checked By - Terry (JavaFixing Volunteer)