customSelectExpression for boolean field is always false

[kamet4u]

Hi Team,
I am using customSelectExpression as given below and executing the call on service but the field is always false.

<field name="pair" type="boolean" sqlStorageStrategy="integer"  customSelectExpression="IFNULL((select pair from price where price_id=product_price.price_id),0)">
        </field>

 ListGridField pair = new ListGridField("pair");

On service :

LOGGER.debug(SQLDataSource.getSQLClause(SQLClauseType.All, dsRequest));
/*if i run this query on mysql then the data comes correct , boolean is true as in DB*/

 DSResponse response= dsRequest.execute();
/*this response always contains the field as false*/

I have tried removing sqlStorageStrategy and using type integer instead of boolean but it's always false. Am I missing any property here. Kindly suggest

[Isomorphic]

What is that query producing as a field value? Boolean false, or perhaps a string value? Just look in the records, ideally on the server.

All we are doing is asking the JDBC driver for the value, so we should be getting the same as you see when you run the query yourself.

[kamet4u]

Query on the server is correct it's giving 1 as result. The DB column field is bit(1)



In query response the result is coming as 1 and in DSresposne is always boolean false. There is no string value.

[Isomorphic]

As covered above, we are simply asking the JDBC driver for a value for the column, specifically, we're calling the JDBC API resultSet.getObject(columnNumber).

Then we'll interpret anything that toStrings() to "true" or "1" as a true value, anything else as false.

Your results suggest that the JDBC driver is just returning something wrong or unexpected, and we have no control over this return value, so we would suggest you work with the database vendor to figure out how to get the driver to return what you want (which may also involve changing your SQL expression), and then everything on the SmartGWT side will simply work once the JDBC layer is returning correct values.

[kamet4u]

The issue is resolved using below code.

 <field name="pair" type="boolean" sqlStorageStrategy="integer"  customSelectExpression="IFNULL((select cast(pair as unsigned) from price where price_id=product_price.price_id),0)">         </field>

Thanks.

[Isomorphic]

Thanks for letting us know - for any other readers, the key part seems to be "cast(pair as unsigned)" (misspelled above) which presumably gets this particular DB to report the values correctly through JDBC.