custom sql, simple example

[evansmith]

Hello,

Using GWT 2.5 and the latest Smart GWT 3.1 release.

So I defined my .ds.xml with an operation id wrapping my sql call.
I was not sure if I should define fields for this ds file, and if I should have a table name.

Then in my java code I defined my data source, a single criteria, and then bound it to my list grid for query. However, when it run the code the custom query is not run. In fact
=== 2012-08-13 21:14:36,017 [l0-4] DEBUG SQLDataSource - [builtinApplication.queryDashboard] SQL windowed select rows 0->75, result size 75. Query: SELECT * FROM relayBDLive.dashboard_info WHERE ('1'='1') LIMIT 0, 75

is run which is really not what I had in mind. attached is the full log. I am sure my attempt (see java code) was run, as I stepped through it in the debugger.

Do I need to define fields for a custom query?
What about the entries for the data source like table name?

Thanks!
Evan

here is the ds ad the code.

<DataSource
schema="relayBDLive"
dbName="Mysql"
tableName="dashboard_info"
ID="queryDashboardMySQL"
dataSourceVersion="1"
generatedBy="Evan Smith"
serverType="sql"
>
<fields>
<field name="dashboard_title" primaryKey="true" title="Title" length="128" type="text"></field>
<field name="dashboard_hover" title="Information" length="225" type="text"></field>
<field name="ui_page" type="integer"></field>
<field name="tab_position" type="integer"></field>
<field name="url" title="URL" length="512" type="text"></field>
</fields>

<!-- http://www.smartclient.com/smartgwtee/javadoc/com/smartgwt/client/docs/CustomQuerying.html -->
<operationBinding operationId="queryDashboard" operationType="fetch">
<customSQL>
SELECT dashboard_info.dashboard_title,
dashboard_info.ui_page,
dashboard_info.tab_position,
dashboard_info.url,
dashboard_info.dashboard_hover,
FROM ( relaybdlive.dash_roles dash_roles
INNER JOIN
relaybdlive.user_roles user_roles
ON (dash_roles.role_name = user_roles.role_name))
INNER JOIN
relaybdlive.dashboard_info dashboard_info
ON (dash_roles.dash_name = dashboard_info.dash_name)
WHERE (user_roles.user_name = $criteria.user_name)
</customSQL>
</operationBinding>


</DataSource>

private boolean loadURLsFromMySQL() {
// Tricky code to do the join across tables so we can show only the dashboard the users is allowed to see
final String dsUserInfoPathID = "queryDashboardMySQL";
DataSource ds = DataSource.get(dsUserInfoPathID);
Criteria userCrit = new Criteria("user_name", objUserData.getStrUsername());

// looks like on the data source one sets the "combineCriteria"
// and then binds the data source to get a
// custom SQL query to join the tables to get the role and dash board that is legal

// now we request the data
DSRequest dataRequest = new DSRequest();

ListGrid lg = new ListGrid();
lg.setFetchOperation("queryDashboard");
lg.setAutoDraw(false);
lg.setDataSource(ds);
lg.setSortField("tab_position");
lg.setSortDirection(SortDirection.ASCENDING); // sort on tab field
lg.setVisible(false);
DSCallback callback = new DSCallback() {
@Override
public void execute(DSResponse dsResponse, Object o, DSRequest dsRequest) {
updateTabsFromDSResponse(dsResponse);
}
};
lg.fetchData(userCrit, callback, dataRequest);

return true;
}

[evansmith]

got it all set!!

well I feel like I graduated! totally sweet to have this working.

My example had two problems.

1. I left out the "OperationsBindings" around my ds file.
2. A typo or two in the from clause.

Attached is the working ds

really sweet job with this smart gwt!

amazing!!

[MageshkumarM]

fetchOperationID is not taking gwtcomplier

Hi All,

Have the same problem. In my sample.ds.xml have operationBinding block for custom fetch operation (OperationId :"customfetch".
while run the code it takes the default ID ("sample_fetch").

In ListGird datasource operation ID setted as Listgrid.setFetchOperation("customfetch"), but its not reflect in server console

Server Console:
***********
criteria:{
},
operationConfig:{
dataSource:"sample",
operationType:"fetch",
textMatchStyle:"startsWith"
},
startRow:290,
endRow:340,
componentId:"List_girdUI",
appID:"builtinApplication",
operation:"sample_fetch",
oldValues:{
}
}


sample.ds.xml
**********

<DataSource ID="sample" serverType="sql" tableName="iso_sample">
<fields>
<field name="iso_sample_id" title="ID" type="text" hidden="true"
primaryKey="true" />
<field name="sample_code" title="sample" type="text" length="35" />
</fields>
<operationBindings>
<operationBinding operationId="customFetch" operationType="fetch">
<customSQL> SELECT sample_code
FROM testDB.iso_sample WHERE iso_sample_id IN (SELECT id_value
FROM id it_prf
WHERE type_id = '6')
</customSQL>
<orderClause>
sample_code
</orderClause>
</operationBinding>
</operationBindings>
</DataSource>

what is the exact issue?

Regards,
Mag

[Blama]

Hi MageshkumarM,

you are writing "customfetch" for your ListGrid and "customFetch" for your .ds.xml.

Also, check your server logs for an entry of the like:
"operation id xyz not found, switching to default fetch operation" (wording may be different).

Best regards,
Blama


EDIT:
Just got the log entry myself. It is:

=== 2014-08-25 11:05:48,179 [c-10] WARN  DataSourceDMI - DataSource MYDS: received a request to execute an operation of type 'fetch' named 'xyz', but this operation is not defined to the dataSource.  Falling back to default behavior for operationType 'fetch'

[Blama]

Also, I'm pretty sure you should SELECT all the fields you list in <fields> in your <customSQL>.

[MageshkumarM]

Respone

Thank you for your great response Mr. blama..

I want to write custom SQL for update operation?

Can we use normal SQL update statement..

Example :-
<customSQL>
update table_name set columnName = 'value';
</customSQL>

is it make sense to do update operation using operation Binding?