If you’re familiar with SmartClient you’re probably aware of how it handles database queries for you. That’s one of its main selling points. However, what if you need to capture a query that’s already been built and send it to a batch process to create an export or a PDF file? That requires a bit more effort.
There are two ways to solve this problem. One is to capture the SQL generated in Java using a DMI. This method will be discussed in a separate blog. The other method, described here, simply uses some database manipulation within the SmartClient datasource file to capture the records needed for batch processing. The steps to do so are to execute the query using the where clause SmartClient generates, capture the data as an XML object, then save the XML to a database table that is polled for work by the batch process.
Capturing the Criteria
To start with we’ll assume there is a page where some SmartClient control, say a Nested filterBuilder, is utilized to set the criteria for batch processing. We’ll also assume there are unique primary keys that can be returned for batch process to work on. Once the user clicks the submit button, SmartClient automatically generates the appropriate query which can then be changed in the datasource file’s fetch operation. This is where we put our SQL code to capture the unique primary keys and insert a record.
Keep in mind, even though the goal is to insert a record, this operation is implemented as a “fetch”. The fetch gives access to the $defaultWhereClause string provided by SmartClient. The consequence of this is that one and only one Select must return rows or SmartClient will complain. Take note, also, how SQL Server’s NoCount session variable is being handled so the transaction masks the initial Select and Insert and reports back the results of the final Select.
<operationBinding operationType="fetch" operationId="BatchJobInsert"> <customSQL> Declare @xmlData NVarchar(Max) Declare @userId Integer Declare @BatchJobId Integer Set @userId = $session.userId
-- Turn off the rowcount. SmartClient only expects one
-- rowcount from a fetch.
Set NoCount On
-- Capture the primary keys for the data -- that will appear in the report. Set @xmlData = ( Select dataId From dbo.dataTable Where ( 1 = 1 ) -- Below is the criteria SmartClient generates. And $defaultWhereClause For XML PATH )
Insert Into dbo.BatchJob ( userId, created, started, completed, batchJobTypeId, -- Specifies the batch job to run. details ) Values ( @userId, GetDate(), null, null, 12, @xmlData )
-- Get the newly create BatchJob record's primry key.
Select @BatchJobId = SCOPE_IDENTITY()
-- Turn on the rowcount. Report back that the
-- Batch Job has been created. Set NoCount Off
Select BatchJobId, userId, created, started, completed, details </customSQL> </operationBinding> |
Then it’s just a matter of having the batch job to read from the table and, using the list of primary keys stored as the detail field, process the results. Don’t forget that SQL Server supports XML data and can be processed directly in the database. This is a prime opportunity to utilize a stored procedure to process the XML data and return the detailed results for your batch job. An example can be found in a previous blog titled Leveraging SQL Server’s XML Support to Improve Application Performance.
Like all solutions, this method has its good and bad points. There are some factors that allow us to use it in our particular case:
- The query producing the data is quick. We’re already displaying the results of our reports on screen, so the queries are already known to execute in a reasonable time. Always keep in mind that this operation happens in real time and the user is going along for the ride.
- There are built in limits to the number of records that can be returned. In our particular case, the application limits the number of records to a few thousand. Storing the XML data temporarily in a table won’t impact storage significantly.
- The batch job table is cleared periodically. This also impacts the storage footprint of the batch jobs.
If any of these aren’t the case and need to run the query in the background, you’ll want to consider using a SmartClient’s Java DMI to capture the whole query. So long as you’re aware of the possible “gotcha”’s, this technique is a quick and easy way to implement batch processing within SmartClient.