Back in July, I wrote a blog describing a quick and easy method to capture a dynamic query’s resultset for processing later in a batch job in SmartClient. While this method still works, there are two disadvantages to using it, including the fact that the query has to be run live to get the primary keys and the storage penalty incurred by using this method.
To avoid this, you can use SmartClient DMI to get the same result, but by capturing the actual SQL query that is generated by the SmartClient framework. The key to this is the getSQLClause of the SQLDataSource object.
Capturing the Query in SmartClient
For those who haven’t used a DMI (Direct Method Invocation) before, it is structured as a Java class that sits between the DataSource xml file and the database. It allows you to intercept the data request and modify it before and after it’s execution.
In the previous article I wrote, the majority of the work was done with SQL statements in a “fetch” operation within the SmartClient datasource. In this version there is no custom SQL on the fetch, just a call to the DMI which will be discussed after:
<operationBinding operationType="fetch"> <serverObject className="com.mysite.smartclient.dmi.BatchJobDMI" methodName="insert"/> </operationBinding> |
And here is a much abbreviated Java function that handles the request:
import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
public class BatchJobDMI {
public DSResponse insert(DSRequest dsRequest,
HttpServletRequest servletRequest) throws Exception {
String query =
SQLDataSource.getSQLClause(SQLClauseType.All, dsRequest);
// Convert the query string into a gson object to be stored to
// the database batch job table.
Map<String, Object> values = new HashMap<String, Object>();
Map<String, Object> details = new HashMap<String, Object>();
details.put("requestName", “someReport”);
details.put("query", query);
values.put("JOBDETAILS", gson.toJson(details));
// This JOBDETAILS data should be saved to the batch
// processing queue table in the database.
DSRequest batchRequest = new DSRequest("BATCHPROCESSES",
"add", dsRequest.getRPCManager());
batchRequest.setValues(values);
batchRequest.execute();
// Return… well, something.
DSResponse response = new DSResponse();
response.setSuccess();
return response;
}
}
This class is an incomplete example at best. However, the main point of interest is, of course, the call to the SQLDataSource getSQLClause function. This returns the SQL that will be called if the dsRequest object (passed in as a parameter to the insert function) is executed. Also note the SQLClause enumerated type of “All”, which gets the entire SQL statement. If you only wanted a part of the SQL there are other options to get, say, just the “Where” clause.
At this point the query variable now contains the string of SQL that you’ll want to store for your batch process. You’ll need to be careful handling that string, though. It may contain single quotes that can cause SQL server to complain if they aren’t escaped properly.
In our particular case, we chose to save our batch requests as a JSON object. We add the query string to a HashMap of key/value pairs and then save the JSON representation of that, utilizing Google’s GSON libraries. The” JOBDETAILS” key/value pair map to the appropriate field in our batch queue table.
The batch request is finally saved to the BATCHPROCESSES table, utilizing the datasource set up specifically for that table and named identically. It only has two fields, BATCH_PROCESS_ID which is the primary key and an IDENTITY field and JOBDETAILS which holds the details as JSON. Using a SmartClient datasourse to save the data will also handle the single quotes in the query string, so that also saves several steps of manipulation to handle the query value properly.
Finally, the function returns an empty DSResponse. Remember the point of this was to avoid executing the expensive batch query. We’re saving that for the batch process. So creating a new DSResponse object and artificially setting its status to “Success” bypasses the normal query execution while satisfying the DMI class’s requirement that it return a response object.
While the previous solution is still viable, the Java DMI-based solution resolves two of the biggest pitfalls associated with running the query and capturing its resultset. It runs a potentially time-consuming query in the background while consuming less storage in the database to do so.
- Transferring a Dynamic Query to a Batch Job in SmartClient
- Leveraging SQL Server’s XML Support to Improve Application Performance
- Why Choose SmartClient Web Development Software?
- Producing Reports with the SmartClient JavaScript Framework
- Advantages and Drawbacks of Using Stored Procedures for Processing Data