jGuru: Determining Available Scalar Functions

Print-friendly Version

by

[Help | API Docs | Short Course| Exercises]

Duane and Chrissie know that future operations for 4J will require some work best done with scalar functions. They also want to be sure that they understand how escape syntax works. To this end, they decide to write a Swing program that will show the Numeric, Date and Time, String, and System scalar functions supported by the current database and invoke selected functions. Because scalar functions are used in conjunction with values in SQL statements, they decide to hardcode a query against the JJJJData table that will select one row, and return the result of the selected function.

Prerequisites

• Creating and Populating a Table
• Generalizing Connection Information - Interactive
• Generalizing Connection Information - Batch

Skeleton Code

• ConnectJ.properties
• ScalarJ.java

Tasks

1. in doConnect(), after connecting and obtaining a Statement, add code to get DatabaseMetaData into dbmd.

2. In doInvokeFn(), inside a try block, add code to execute a query that returns a scalar value from the function name contained in sTemp2. Use the following query to serve as a vehicle for the scalar function:

   SELECT <scalar function expression>
   FROM JJJJData
   WHERE entry = 10

   Get the value returned in sTemp3, then close the ResultSet rs.

3. In LoadFunctions(), given the following code set,

   if( jrbNumeric.isSelected() ) { } else if( jrbString.isSelected() ) { } else if( jrbTD.isSelected() ) { } else if( jrbSystem.isSelected() ) { }

   
   

   retrieve the appropriate group of scalar function names into sFunctions. For jrbNumeric get the Numeric Functions, for jrbString get the String Functions, for jrbTD get the Time and Date Functions, and for jrbSystem get the System Functions.

4. In LoadFunctions(), add code to get each function name from the sFunctions with a StringTokenizer st, then load the names into the data model dcbm using dcbm.addElement(). For portability, put the Strings to upper case.

Where help exists, the task numbers above are linked to the step-by-step help page.

Solution Source

• ConnectJ.properties
• ScalarJ.java

Demonstration

After connecting to the database, the scalar functions available for the default selection, Date/Time, will be loaded into the combobox for viewing. Other available function groups can be loaded by selecting the appropriate radio button and pressing the "Get Functions" button. The program has hardcoded queries against the JJJJData table for entry 10. The program makes use of the query to obtain a scalar value when the function is available for the selected group. The lower button will be enabled and contain a preselected function name when the function is available, otherwise it will be disabled. Clicking the "Get <function name>"button will invoke the function and report the result in the textarea beside the "Connect" button. The application display should look like this:

Next Exercise

Exercises

Short Course

Copyright 1996-2000 jGuru.com. All Rights Reserved.