Sap Hana issue by Boolean field in a query

[ramon_varela]

Hi, we have a problem with our test enviroment where we used a Sap Hana database. The problem happends when we go to the first step at checkout. Our application is a Marketplace with 6.7.0.3 hybris version and OOTB configuration with litle personalizations. The issue happends with a query owned by hybris platform over the field saleable at product model. Sap suggest a workaround by redeclare atttibute type with specific definition for a sap database like describes next:

   <itemtype code="Product" autocreate="false" generate="false">
      <attributes>
         <attribute qualifier="saleable" type="java.lang.Boolean" redeclare="true" autocreate="false">
            <persistence type="property">
               <columntype database="sap">
                  <value>BOOLEAN</value>
               </columntype>
            </persistence>
         </attribute>
      </attributes>
    </itemtype>

After initialize system this doesn´t work and returns the same error like describres next:

 com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [266]: inconsistent datatype: BOOLEAN type is not comparable with DECIMAL type.: line 1 col 187 (at pos 186)
     at com.sap.db.jdbc.exceptions.SQLExceptionSapDB._newInstance(SQLExceptionSapDB.java:201)
     at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.newInstance(SQLExceptionSapDB.java:45)
     at com.sap.db.jdbc.packet.HReplyPacket._buildExceptionChain(HReplyPacket.java:934)
     at com.sap.db.jdbc.packet.HReplyPacket.getSQLExceptionChain(HReplyPacket.java:130)
     at com.sap.db.jdbc.packet.HPartInfo.getSQLExceptionChain(HPartInfo.java:39)
     at com.sap.db.jdbc.ConnectionSapDB._receive(ConnectionSapDB.java:3079)
     at com.sap.db.jdbc.ConnectionSapDB.exchange(ConnectionSapDB.java:1642)
     at com.sap.db.jdbc.PreparedStatementSapDB._prepare(PreparedStatementSapDB.java:2745)
     at com.sap.db.jdbc.PreparedStatementSapDB._doParse(PreparedStatementSapDB.java:2667)
     at com.sap.db.jdbc.PreparedStatementSapDB.<init>(PreparedStatementSapDB.java:132)
     at com.sap.db.jdbc.PreparedStatementSapDBFinalize.<init>(PreparedStatementSapDBFinalize.java:37)
     at com.sap.db.jdbc.PreparedStatementSapDBFinalize.newInstance(PreparedStatementSapDBFinalize.java:23)
     at com.sap.db.jdbc.ConnectionSapDB._prepareStatement(ConnectionSapDB.java:2917)
     at com.sap.db.jdbc.ConnectionSapDB.prepareStatement(ConnectionSapDB.java:254)

Query wich produces the error is at the platform is at the java file DefaultMarketplaceCartEntryDao.java and seems like this way:

 protected static final String FIND_UNSALEBALE_CARTENTRIES_IN_CART = "select {A:" + CartEntryModel.PK + "} from {"
         + CartEntryModel._TYPECODE + " as A JOIN " + ProductModel._TYPECODE + " AS B ON {A:" + CartEntryModel.PRODUCT + "}={B:"
         + ProductModel.PK + "} JOIN " + CartModel._TYPECODE
         + " as C on {A:" + CartEntryModel.ORDER + "} = {C:" + CartModel.PK + "}} WHERE {B:" + ProductModel.SALEABLE
         + "}=false and {C:" + CartModel.CODE + "}=?" + CART_CODE;

Does anybody know a workaround or any aproch to solution?

Thanks.

[ramon_varela · Accepted solution]

We just find a partial solution by changing query replacing false value at query for a parameter with a BOOLEAN value that is been received by the query inside set of parameters. This is a partial solution because we should replace all queries with has a false/true value inside by a boolean parameter. For queries than compared a boolean field with 0 or 1 there is no problem, it runs fine with sap hana but only fail wiith queries with true/false value.

The next is solution for this query in our particular case:

 protected static final String CART_CODE = "code";

 protected static final String PRODUCT_SALEABLE = "saleable";

 protected static final String FIND_UNSALEBALE_CARTENTRIES_IN_CART = "select {A:" + CartEntryModel.PK + "} from {"
         + CartEntryModel._TYPECODE + " as A JOIN " + ProductModel._TYPECODE + " AS B ON {A:" + CartEntryModel.PRODUCT + "}={B:"
         + ProductModel.PK + "} JOIN " + CartModel._TYPECODE + " as C on {A:" + CartEntryModel.ORDER + "} = {C:" + CartModel.PK
         + "}} WHERE {B:" + ProductModel.SALEABLE + "}=?" + PRODUCT_SALEABLE + " and {C:" + CartModel.CODE + "}=?" + CART_CODE;

 @Override
 public List<CartEntryModel> findUnSaleableCartEntries(final CartModel cart)
 {
     validateParameterNotNull(cart, "Cart must not be null");
     final FlexibleSearchQuery query = new FlexibleSearchQuery(FIND_UNSALEBALE_CARTENTRIES_IN_CART);
     //        query.addQueryParameter(CART_CODE, cart.getCode());
     final Map<String, Object> params = new HashMap<>();
     params.put(CART_CODE, cart.getCode());
     params.put(PRODUCT_SALEABLE, Boolean.FALSE);
     query.addQueryParameters(params);
     return getFlexibleSearchService().<CartEntryModel> search(query).getResult();
 }

[ramon_varela]

That's for me no works because you create a new attribute but i need to redeclare database type for an existing attribute at product model which must have column type at hana database BOOLEAN instead of DECIMAL. Hybris converts automatically Boolean type to INT type at oracle databases but not with hana databases.

[VinayKumarS]

i just gave an example.. can u modify and check it for redeclare? in that case is it still not working.. what exception/probem you are facing. can u provide more details.

[ramon_varela]

That´s not exception throws but at initialize at sap hana must create field saleable with type BOOLEAN like at the type declaration indicates for SAP database but still create field like a DECIMAL. After that query not runs and throws exception because it could not compare a DECIMAL with a BOOLEAN value. Finally with a redeclare property at declaration not changes the database field type that is the core problem.

[VinayKumarS]

I have tried below snippet. it works fine for me. with SAP Hana and Hybris 6.7. But this I declared in my custom core under Product item type

 <attribute qualifier="isSellable" type="java.lang.Boolean">
                     <modifiers read="true" write="true" search="true" optional="false"/>
                     <description>Verify if product is sellable</description>
                     <defaultvalue>Boolean.TRUE</defaultvalue>
                     <persistence type="property"/>
                 </attribute>