How to create and use a data source object for executing custom SQL at runtime.
You can run this technique code from:
NOTE First make sure that database is running on your localhost SilverStream Server | |
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().
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.
Related reading
public void runQuery(String query)
{
String sDatabaseName = null;
String sType = null;
if (gsCurrentDatabase == null)
{
agDialog.showMessage("Error!", "No Database specified!");
return;
}
// No passed SELECT statement, so return.
if (query.equals(""))
{
agDialog.showMessage("Error!", "No Query specified!");
return;
}
// 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);
try
{
// 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
null.");
return;
}
// If the Data source object returned an exception? Display
// error and return.
if (queryResult instanceof Exception)
{
Exception e = (Exception)queryResult;
agDialog.displayError(e);
return;
}
// If the DSO was successful, build the AgcView control and
// display the data...
}
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");
try
{
// 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.
evt.setResult("Success");
}
else
{
// Return failure.
evt.setResult("Failed to get database: " + sDatabase);
}
}
catch (Exception excpError)
{
// An exception occurred, so close everything and
// return the exception message.
evt.setResult(excpError.toString());
}
}