How to create and use a data source object to return a java.SQL.ResultSet.
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 |
This technique (Form and View from Tab-delimited file) shows how to use a DSO to obtain data from an object that implements java.SQL.ResultSet.
The frmDsoSqlResultSet form contains a subform called subfrmTabDelimitedfile, and a view, each displayed within a TabControl. This technique describes how the subfrm is populated, using the DSO called DSOTabDelimitedOrderFile.
The setResultSet implementation described in this technique includes these objects:
subfrmTabDelimitedFileForm
A SilverStream form containing an AgcData control called dataOrderFile. The dataOrderFile object is bound to the DSO called DSOTabDelimitedOrderFile.
DSOTabDelimitedOrderFile
A SilverStream data source object. It instantiates the utility object, called ObjTabDelimitedSQLFile, that implements the java.sql. ResultSet and java.sql.ResultSetMetaData interfaces.
boinvGetApplicationRootDirectory
The functionality of this class is outside the scope of DSOs, thus is not described in this technique.
ObjTabDelimitedSQLFile
A utility class that implements the java.sql.ResultSet and java.sql.ResultSetMetaData interfaces. It obtains the data from the external data source, formats it in the appropriate row and column format, and populates the server-side data buffer for download to the client.
This utility object is located in the com/sqlFile package.
The following diagram illustrates the flow of data among these objects.
Related reading
invokeQuery() method to invoke the DSOTabDelimitedOrderFile. It passes a hashtable that includes the string "File" as the key and a filename as the value. The filename is actually obtained by a call to an AgiInvokedListener object called boinvGetApplicationRootDirectory, which is outside of the scope of this description.
setResultSet() to specify the java.sql.ResultSet object to use.
setResult() to pass data back to the form. It can be any useful piece of information. In this case, it returns the string "Success" if the DSO can be started, or an exception if not.
goToFirst() method on the AgcData.
The form displays read-only orders to the user. The user can navigate the data set using the buttons provided. It is bound to the DSO through an AgcData control called dataOrderFile.
The form's Loaded event calls a custom method, called retrieveOrders(). The retrieveOrders() method creates and populates a hashtable that includes the key value pair "File", sfileName which it passes on the invokeQuery() method call. To display data when the form is loaded, the form must also issue a call to the AgcData.gotoFirst() method.
public void retrieveOrders()
{
String sFileName = getFileName();
if (sFileName == null)
return;
try
{
// Set up Orders parameters:
//filename of order file to be retrieved and displayed
Hashtable hshFile = new Hashtable();
hshFile.put ("file", sFileName);
// Cast hshfile to a String and call the DSO on the AgcData
String sQueryResult = (String)
dataOrderFile.invokeQuery(hshFile);
if (sQueryResult == null)
return;
if (!sQueryResult.equals("Success"))
{
agDialog.showMessage("File Error", sQueryResult +
" Likely cause is failure to find file " + sFileName);
return;
}
// The query for the order file worked. Download the first row
dataOrderFile.gotoFirst ();
}
catch (Exception excpError)
{
agDialog.displayError(excpError);
}
}
The DSOTabDelimitedFile is bound to the form's AgcData control. The DSO defines these columns:
This code instantiates ObjTabDelimitedSQLFile, a the utility object that implements the result set. Use the evt.setResultSet() to get a handle to the java.sql.ResultSet object. This example uses the executeQuery() method and passes the sFilename and sQueryParm variables. The executeQuery() method is implemented by the ObjTabDelimitedSQLFile (the java.sql.ResultSet object).
public void invokeQuery(AgoDataSourceEvent evt) throws Exception
{
// Instantiate the business object that implements the
// SQL.resultSet
ObjTabDelimitedSQLFile fileData = new ObjTabDelimitedSQLFile();
// get the data from the hashtable passed to the DSO from the
// calling form.
Hashtable hshParm = (Hashtable) evt.getParameter();
String sFilename = (String) hshParm.get ("file");
String sQueryParm = (String) hshParm.get ("queryparm");
try
{
// specify the result set
evt.setResultSet (fileData.executeQuery(sFilename,
sQueryParm));
evt.setResult ("Success");
}
catch (Exception e)
{
System.out.println (e.toString());
// Return some information to the calling form.
evt.setResult (e);
throw new Exception(e.getMessage());
}
}
The ObjTabDelimitedSQLFile is the utility class that implements the java.sql.ResultSet and java.sql.ResultSetMetaData interfaces.
The ObjTabSelimitedSQLFile reads from a text file called SampleOrders.txt. It is located in the samples subdirectory. The first record of the text file contains the column names. The remaining records contain the data. The data rows are delimited by the tab character:
The ObjTabSelimitedSQLFile needs to get the column name information and also the rows. It declares the following variables in the General Declarations section.
// Save the total column count int m_columnCount = 0; // An array to store the column names String[] m_columnNames; // String tokenizer StringTokenizer m_stringTokenizer; // A buffered reader BufferedReader m_bufferedReader; // The vector Conn result data Vector m_vctData; // only return rows for this customer String m_sQueryParm;
In the UserCode section, the executeQuery() method uses a file reader, a buffered reader and a StringTokenizer to get the column names from the first row:
public java.sql.ResultSet executeQuery(String psFileName, String psQueryParm) throws Exception
{
try
{
String sFileName = psFileName;
m_sQueryParm = psQueryParm;
FileReader fileReader = new FileReader (sFileName);
m_bufferedReader = new BufferedReader(fileReader);
m_stringTokenizer = new StringTokenizer
(m_bufferedReader.readLine(), "\t");
m_columnCount = m_stringTokenizer.countTokens();
m_columnNames = new String[m_columnCount];
for (int iCount=0; iCount < m_columnCount; iCount++)
{
m_columnNames[iCount] = m_stringTokenizer.nextToken();
}
}
catch (Exception excpError)
{
System.out.println (excpError.toString());
throw new Exception(excpError.toString() + "\r\nDetail: " +
excpError.getMessage());
}
return this;
}
The next() method processes the rows in the file.
public boolean next() throws java.sql.SQLException
{
try
{
String newLine;
while (true)
{
// Read a row from the file (up to a newline)
newLine = m_bufferedReader.readLine();
if (newLine == null)
// End of File? We're done.
return false;
if (newLine.length() <= 0)
// Nothing in the line, we're done.
return false;
// Get the tab delimited columns into a StringTokenizer
m_stringTokenizer = new StringTokenizer (newLine, "\t");
// If the first column doesn't equal the current query
// parameter, continue the loop.
// This will read another row and start over.
// A null query parameter means get the
// whole result set.
String sToken = m_stringTokenizer.nextToken();
if (m_sQueryParm != null &&
!m_sQueryParm.equals(sToken))
continue;
// Got a good one! Need to get the tokenizer again,
// since we already consumed the first column to do the
// test. Then break out of the loop.
else
{
m_stringTokenizer = new StringTokenizer
(newLine, "\t");
break;
}
}
// The row meets the selection criteria. Create a vector
// and populate it with columns.
m_vctData = new Vector();
for (int iCount = 0; iCount < m_columnCount; iCount++)
{
m_vctData.addElement (m_stringTokenizer.nextToken());
}
return true;
}
catch (Exception excpError)
{
System.out.println (excpError.toString());
return false;
}
}