Bug in the SQLDataSource or (more likely) in the Oracle SQL driver?

[nikolayo]

I have boolean fields declared in the data source descriptor with sqlStorageStrategy "integer". For example:

        <field
            name="BOOLEAN_VALUE"
            type="boolean"
            sqlStorageStrategy="integer"
            title="Boolean"/>

This works fine for fetches and updates but upon delete operations the generated SQL has string values "true" and "false" rather than numbers in the where clause. Like this (with some formatting of mine) :

DELETE FROM DATA_TYPE WHERE (
DATA_TYPE.NAME='name' AND 
DATA_TYPE.VERSION='28' AND 
DATA_TYPE.DATE_TIME_VALUE=TO_DATE('2010-11-22 00:00:00','YYYY-MM-DD HH24:MI:SS') 
AND DATA_TYPE.BOOLEAN_VALUE='false' 
AND DATA_TYPE.ID='62' AND 
DATA_TYPE.LONG_VALUE='1111' AND 
DATA_TYPE.STRING_VALUE='asd' AND 
DATA_TYPE.DATE_VALUE=TO_DATE('2009-11-10 00:00:00','YYYY-MM-DD HH24:MI:SS'))

This ends up with "java.sql.SQLException: ORA-01722: invalid number" because BOOLEAN_VALUE is INTEGER in the database.

Regards
Nikolay

[nikolayo]

P.S. Sorry, the correct sqlStorageStrategy for my case appears to be "number" rather than "integer". I experience the same problem with "number" though.

[Isomorphic]

Where's that SQL coming from? A customSQL or whereClause you've written that references something like $criteria.BOOLEAN_VALUE? Or something we're generating automatically for a normal client-side "remove" request? If the latter, please post your .ds.xml file.

[nikolayo]

The SQL is generated entirely by the framework. There is nothing special in the data source (see below) other than use of custom data source class inheriting from SQLDataSource. I did not realize that it can impact SQL generation but meanwhile I found out that it actually can do that by setting allowMultiUpdate to true. I do this so that I can include in the where clause a version field and implement optimistic locking. In order to localize the problem with certainty I further reduced my custom code to the following:

    @Override
    public DSResponse execute(DSRequest req)
        throws Exception
    {
        String operationType = req.getOperationType();
        if ("remove".equals(operationType)) {
            req.setAllowMultiUpdate(true);
        }
        return super.execute(req);
    }

With this custom data source code I observe generation of the kind of SQL reported and the sql error.

And the data source descriptor is:

<?xml version="1.0" encoding="UTF-8"?>
<DataSource
    ID="data_typeDS"
    tableName="DATA_TYPE"
    serverConstructor="com.sample.demo.server.TSMSQLDataSource">
    <fields>
        <field
            name="ID"
            type="sequence"
            sequenceName="HIBERNATE_SEQUENCE"
            primaryKey="true"
            hidden="true"/>
        <field
            name="VERSION"
            type="integer"
            hidden="true"/>
        <field
            name="NAME"
            type="text"/>
        <field
            name="BOOLEAN_VALUE"
            type="boolean"
            sqlStorageStrategy="number"
            title="Boolean"/>
        <field
            name="LONG_VALUE"
            type="integer"
            title="Long"/>
        <field
            name="DOUBLE_VALUE"
            type="float"
            title="Double"/>
        <field
            name="M_MONETARY_VALUE"
            type="float"
            title="Monetary"/>
        <field
            name="STRING_VALUE"
            type="text"
            length="255"
            title="Text"/>
        <field
            name="TIME_VALUE"
            type="time"
            title="Time"/>
        <field
            name="DATE_VALUE"
            type="date"
            title="Date"/>
        <field
            name="DATE_TIME_VALUE"
            type="datetime"
            title="Date-Time"/>
        <field
            name="ENUM_VALUE"
            type="enum"
            length="255"
            title="Enum">
            <valueMap>
                <value
                    id="One">One</value>
                <value
                    id="Two">Two</value>
                <value
                    id="Three">Тhree</value>
            </valueMap>
        </field>
        <field
            name="INT_ENUM_VALUE"
            type="intEnum"
            title="Int Enum">
            <valueMap>
                <value
                    id="1">One</value>
                <value
                    id="2">Two</value>
                <value
                    id="3">Three</value>
            </valueMap>
        </field>
    </fields>
    <operationBindings>
        <operationBinding
            operationType="fetch">
            <tableClause>DATA_TYPE</tableClause>
            <whereClause>$defaultWhereClause</whereClause>
            <orderClause>NAME</orderClause>
        </operationBinding>
    </operationBindings>
</DataSource>

Regards
Nikolay

[Isomorphic]

This was a bug in SQLDataSource, now fixed - will be present in nightly builds as of tomorrow. Thanks for the clear report.

[nikolayo]

Thank you!

May I ask whether the fix affect the way where clause is generated when allowMultiUpdate is true (with all fields included rather than just those explicitly set in criteria) or just the way booleans are converted?

Regards
Nikolay

[Isomorphic]

Just the way booleans are converted

[nikolayo]

So the where clause is intentionally generated this way and is here to stay or is to be changed/fixed at later time?

Regards
Nikolay

[Isomorphic]

The client will send the complete record values, if available, as criteria for a "remove" operation. If you want to delete by PK only, leave allowMultiUpdate false (the default). If you want to delete by some other specific criteria, use dsRequest.setCriteria() to set it.

[nikolayo]

Yes, indeed. Thank you. This works:

        Map criteria = req.getCriteria();
        criteria.clear();
        criteria.put(getPrimaryKey(), req.getValues.get(getPrimaryKey()));
        criteria.put(VERSION_FIELD_NAME, oldVersion);

Regards
Nikolay