Although Stonefield Query has a command-line interface that allows you to easily run a report from another application, that mechanism has the downside that Stonefield Query starts and terminates with every report run. Since it can take several seconds to start Stonefield Query (or even longer, depending on any scripts you've created), this can impact performance if several reports are run in a row. In that case, you may wish to use the SQProxy object.

SQProxy is a lightweight COM object included in SQProxy.EXE (which means, of course, that you need to distribute and register that EXE if you want to use this object on your users' systems). It acts as a manager for the Stonefield Query application so it can be started and kept in memory, without displaying any user interface, meaning the startup tasks are only performed once rather than on every report run.

SQProxy has a single method: LoadProject. After instantiating the SQProxy object, call its LoadProject method to load the specified project. This method accepts four parameters: the path for the project files, the user name, the password, and the name of the data source to query against. The last three parameters are optional. Don't pass the user name and password if you want the user to be able to open the Stonefield Query application at the same time as using SQProxy in your application. If you don't pass the data source name, the data source used last time Stonefield Query was run is used. Once you've called LoadProject, you can reference its SQApplication member, which is an instance of the Stonefield Query Application object, giving you full access to all of its members.

In addition to SQApplication, SQProxy has several other properties:

  • ProjectLoaded: contains True if a project was successfully loaded with the LoadProject method or False if not.

  • ProxyTimeout: the number of seconds to wait after launching Stonefield Query before SQApplication contains a valid object or a timeout occurs. The default is 5.

  • Parameters: a collection of parameters to pass to Stonefield Query. This provides the ability to pass the same parameters as you'd pass on the command line. The Add method adds a parameter to the collection and returns a parameter object. Set the Name and Value properties of the object to the desired values.

  • ErrorMessage: the text of any error that occurs.

  • ErrorCode: the code returned by SFQuery.EXE if LoadProject fails. This is the same value it returns when running a report from the command line.

  • Ready: your code using the SQProxy object and SFQuery.EXE run asynchronously. If you need to do some additional tasks after calling LoadProject but before the Application.AfterSetup script executes, set SQProxy.Ready to false before calling LoadProject, then set it to true after the additional tasks are done and you're ready for Application.AfterSetup to execute.

  • ProcessID: the process ID for the SQProxy object. Note that SFQuery.exe runs in its own process so it has a separate process ID, which you can obtain using something like loProxy.SQApplication.ProcessID.

Here's an example that instantiates SQProxy, loads the Northwind project, and runs the Customers and History reports to a couple of PDF files:

loQuery = createobject('SQProxy.SQProxy')

* Load the project. if we can't, display the error code
* (see the Command-Line Interface topic for a list of error codes).

try
  loQuery.LoadProject('\MyProjects\Northwind')
catch
  messagebox(loQuery.ErrorCode)
endtry

* If we loaded the project, try to run a couple of reports.

if loQuery.ProjectLoaded
  try
    llSuccess = loQuery.SQApplication.ReportEngine.RunReportToFile('Customers', ;
      '\MyReports\customers.pdf')
    if not llSuccess
      messagebox(loQuery.SQApplication.ReportEngine.ErrorMessage)
    endif
    llSuccess = loQuery.SQApplication.ReportEngine.RunReportToFile('History', ;
      '\MyReports\history.pdf')
    if not llSuccess
      messagebox(loQuery.SQApplication.ReportEngine.ErrorMessage)
    endif

* An error occurred, so display the error message.

  catch
      messagebox(loQuery.SQApplication.ErrorMessage)
  endtry
endif

Here's an example that shows adding an additional filter condition to the report by adding a Filter item to the Parameters collection:

loQuery = createobject('SQProxy.SQProxy')

* Create some XML for the new filter condition and add it as a parameter.

text to lcConditions noshow
<conditions>
  <condition>
    <field>Customers.Country</field>
    <operator>equals</operator>
    <value>Germany</value>
  </condition>
</conditions>
endtext
loQuery.SQApplication.Parameters.AddItem('filter', lcConditions)

* Load the project. if we can't, display the error code

try
  loQuery.LoadProject('\MyProjects\Northwind')
catch
  messagebox(loQuery.ErrorCode)
endtry

* If we loaded the project, run a report.

if loQuery.ProjectLoaded
  try
    loQuery.SQApplication.ReportEngine.RunReportToFile('Customers', ;
      '\SomeFolder\customers.pdf')

* An error occurred, so display the error message.

  catch
      messagebox(loQuery.SQApplication.ErrorMessage)
  endtry
endif

If you want the ask-at-runtime dialog to appear, you must set the AllowDialogs parameter to "yes". The dialog may appear behind your application's window, with the icon flashing in the Windows TaskBar. Automatically bringing the dialog to the front is a little complicated since recent Windows versions don't allow a window to bring itself to the front. Instead, use a callback object that does it. Here's an example (preceding and error handling code omitted for brevity):

loQuery.SQApplication.Parameters.AddItem('AllowDialogs', 'yes')
loCallBack = createobject('CallBack')
loQuery.SQApplication.ReportEngine.RunReportToFile('Customers', ;
  '\SomeFolder\customers.pdf', , , , loCallBack)

define class CallBack as Custom
  function OnShowWindow(tnHWnd)
    declare integer SetForegroundWindow in user32 integer hwnd
    SetForegroundWindow(tnhWnd)
  endfunc
enddefine

See the ReportEngine Object help topic for methods available to run reports.

If an error occurs when running a report using SQProxy, an error is raised in the calling application, so you should use error handing in your applications using SQProxy.

If you want to use SQProxy in .NET applications, see the Using SQProxy in .Net Applications help topic.


© Stonefield Software Inc., 2023 • Updated: 02/05/20
Comment or report problem with topic