Getting the complete SQL to be executed

[Warren.Menezes]

Hi

I am unable to get the complete SQL to be executed from a ds.xml file. My ds.xml file contains $rawValue variables whos values are set in its DMI using DSRequest.addToTemplateContext. I have tried using SQLDataSource.getSQLClause(SQLClauseType.All, DSRequest ) but the values of the $rawValue variables are not substituted and the string returned looks something like this

SELECT $rawValue.var1 FROM table WHERE foo>5

If I execute the query the correct SQL is generated with all the $rawValue variables substituted. But I need to capture the complete SQL as a string to reuse it as an inner query in another ds.xml file

So my question is how can I capture the complete SQL which will be executed with all the $rawValue variables replaced with its respective values?I am using SmartGWT as a standalone Sever application.

Thanks

[Isomorphic]

In order to have the same SQL generated, you must pass a DSRequest that has the same information available as the DSRequest you are using via DMI.

If you think you are already doing this, please show us a way to reproduce a problem where data is available but substitution does not occur.

[Warren.Menezes]

Here are few of the things I have tried

My DMI:

public class MyDMI {
    public DSResponse fetch(DSRequest dsRequest) {
        DSResponse dsResponse = new DSResponse();
        try {
            addClauseToContext(dsRequest);
            dsResponse = dsRequest.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            return dsResponse;
        }
    }

    public void addClauseToContext(DSRequest dsRequest) {
        dsRequest.addToTemplateContext("additionalFields", " FIELD4, FIELD5");
    }
}

My Query in the ds.xml

<operationBinding operationType="fetch" operationId="myOperation">
    <serverObject lookupStyle="new" className="com.smartgwt.server.dmi.MyDMI" />
    <selectClause>
        FIELD1, FIELD2,$rawValue.additionalFields
    </selectClause>
    <tableClause>
        MYTABLE
    </tableClause>
    <whereClause>
       FIELD3='123'
    </whereClause>
</operationBinding>

My Main program

public class MISampleMain {

    public static void main(String[] args) {
        try {
            DSRequest dsRequest = new DSRequest("mydatasource", "fetch");
            dsRequest.setOperationId("myOperation");

            String clause = SQLDataSource.getSQLClause(SQLClauseType.All, dsRequest);
            System.out.println("Clause is : " + clause);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

CASE 1: Here the DSRequest does not contain any values in the Template Context when i call getSQLClause, but if it goes to the DMI internally, then it should get the values for the $rawValue variables. However I dont see this happening. This is the OUTPUT I get,i.e. without any substitution.

Clause is : SELECT 
        FIELD1, FIELD2,$rawValue.additionalFields
     FROM 
        MYTABLE
     WHERE 
       FIELD3='123'

Kindly note, If I perform DSRequest.execute(), the query generated has the $rawValue variables substituted.

CASE 2: Even if I explicitly set the templateContext before I call getSQLClause(using addToTemplateContext), the substitutions do not happen generating the same output as shown above.

public class MISampleMain {

    public static void main(String[] args) {
        try {
            DSRequest dsRequest = new DSRequest("mydatasource", "fetch");
            dsRequest.setOperationId("myOperation");
            dsRequest.addToTemplateContext("additionalFields"," FIELD4, FIELD5");

            String clause = SQLDataSource.getSQLClause(SQLClauseType.All, dsRequest);
            System.out.println("Clause is : " + clause);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

I have also noticed the following
If I have an if else statement in my ds.xml like

<operationBinding operationType="fetch" operationId="myOperation">
    <serverObject lookupStyle="new" className="com.smartgwt.server.dmi.MyDMI" />
    <selectClause>
        FIELD1, FIELD2,$rawValue.additionalFields
        #if($rawValue.additionalFields)
            , TRUE_FIELD
        #else
            , FALSE_FIELD
        #end
    </selectClause>
    <tableClause>
        MYTABLE
    </tableClause>
    <whereClause>
       FIELD3='123'
    </whereClause>
</operationBinding>

on using SQLDataSource.getSQLClause, the condition always returns false and I have an output like

Clause is : SELECT 
        FIELD1, FIELD2,$rawValue.additionalFields
                    , FALSE_FIELD
             FROM 
        MYTABLE
     WHERE 
       FIELD3='123'

but on DSRequest.execute(), the condition is checked properly and the condition returns true

INFO  SQLDataSource - [builtinApplication.myOperation] Executing SQL query on 'Oracle': SELECT 
        FIELD1, FIELD2, FIELD4, FIELD5
                    , TRUE_FIELD
             FROM 
        MYTABLE
     WHERE 
       FIELD3='123'

The documentation at http://www.smartclient.com/smartgwte...ataSource.html states that the full-formed SQL query suitable for passing straight to the executeQuery() is returned. But this doesnt seem to be the case. How can I get the full-formed SQL query?

I am using smartGWT version SNAPSHOT_v8.3d_2012-09-25/Enterprise Deployment 2012-09-25.

[Isomorphic]

This was a bug - thanks for the report. We have fixed the problem on the 8.3d branch, tomorrow's nightly build (dated November 16) will contain the fix.