Application Techniques

Using an executeSQL DSO

How to create and use a data source object for executing custom SQL at runtime.

About this technique



Data Access Techniques> Data source objects


You'll learn about:

You can run this technique code from:

NOTE   First make sure that database is running on your localhost SilverStream Server

Related reading

See the chapter on using data source business objects in the Programmer's Guide

Running frmDSO displays a view called vwResults, as shown below:

The view contains an AgcData control (agcDataISQL). The agcDataISQL is bound to a DSO named DSOISQL. The form contains logic that constructs a SQL Select statement based on the user's selection of a SilverStream Server, a database table, and one or more columns from the selected table.

The user executes the query by clicking the Run Query button (tbRunQuery). The user's click action fires the button's actionPerformed event. The actionPerformed event calls the user-defined method runQuery().

Invoking the DSO and passing the SQL statement   Top of page

The runQuery() method uses invokeQuery() to call DSOISQL, using a hashtable to pass the SQL statement and the database name. The code also does some error checking, verifying that a SQL statement was entered, and that result returned from the DSO is not `null'.

If the DSO returns the result successfully, the form builds the AgcView and displays the result in vwResult.

  public void runQuery(String query) 
   String sDatabaseName = null; 
   String sType = null; 
  if (gsCurrentDatabase == null) 
        agDialog.showMessage("Error!", "No Database specified!"); 
  // No passed SELECT statement, so return. 
  if (query.equals("")) 
        agDialog.showMessage("Error!", "No Query specified!"); 
  // If the user entered a query, pass the data source object a  
  // hashtable with: 
  //    A string with the SQL query 
  //    A String with the database name 
  Hashtable hshQueryInfo = new Hashtable(); 
  hshQueryInfo.put("Query", query); 
  hshQueryInfo.put("Database", m_sCurrentDatabase); 
     // Invoke the data source object, passing the SQL statement 
     Object queryResult = agcDataISQL.invokeQuery(hshQueryInfo); 
     // If the query returns null, display an error message in a  
    //dialog box 
     if (queryResult == null) 
        agDialog.showMessage("Error", "Invoke query returned 
     // If the Data source object returned an exception? Display 
     // error and return. 
     if (queryResult instanceof Exception) 
        Exception e = (Exception)queryResult; 
     // If the DSO was successful, build the AgcView control and 
     // display the data... 

Executing the SQL on the database   Top of page

The Data Source Object (DSOISQL) calls the evt.executeSQL() method to execute the SQL String against the database. This method allocates the connection, prepares and executes the statement, and saves the result set into the event. Then the DSO calls the evt.setResult() method to a String that indicates success or failure, or an Exception.

Underneath the covers, the DSO passes the request to the SilverStream Server, which makes the appropriate JDBC calls. The JDBC driver returns the result set as a java.SQL.ResultSet object. The server then constructs an AgiDataSource object which actually returns the data to the AgcData.

  public void invokeQuery(AgoDataSourceEvent evt) throws Exception 
    AgiDatabase dbObject = null;   // The database to connect to. 
    Hashtable hshQueryInfo = null;// Hashtable with query info. 
   // Get the SELECT statement and database from caller. 
     hshQueryInfo = (Hashtable) evt.getParameter(); 
  // Get the Hashtable parameters and cast them to Strings. 
     String sQuery = (String) hshQueryInfo.get("Query"); 
     String sDatabase = (String) hshQueryInfo.get("Database"); 
        // Get a database object.  
        dbObject = evt.getServer().getDatabase(sDatabase); 
        // If successful, Execute the SQL statement for this 
        // database. 
        if (dbObject != null) 
           evt.executeSQL(dbObject, sQuery); 
           // Return success. 
           // Return failure. 
           evt.setResult("Failed to get database:  " + sDatabase); 
     catch (Exception excpError) 
        // An exception occurred, so close everything and 
       // return the exception message. 

Copyright © 2000, SilverStream Software, Inc. All rights reserved.