SQL-J Tips
Page 1 of 1

10
SQL-J Tips

There are many neat things you can do with SQL-J because of its Java extensions and its support of the features that were new in SQL-92. This chapter provides some examples (it also includes one or two non-SQL tips):

Performing Case-Insensitive Sorts

Use the UPPER function or a Java method to perform a case-insensitive sort. For example:

SELECT name, name.toUpperCase() AS up
FROM mixedcasenames ORDER BY up

In your application, retrieve only the first column from the ResultSet.

If you want Cloudscape to return only the results you want, wrap the query in the ExternalQuery VTI (essentially making it a specialized kind of subquery, one that allows ordering). Then select only the desired column from that subquery. For example:

SELECT name
FROM NEW ExternalQuery('jdbc:cloudscape:;current=true',
    'SELECT name, name.toUpperCase() AS up FROM mixedcasenames ORDER BY up')
    AS eq(name, up)

Retrieving the Database Connection URL

Cloudscape does not have a built-in function that returns the name of the database. However, you can use DatabaseMetaData to return the database connection URL of any local Connection.

/* in java */
String myURL = conn.getMetaData().getURL();

-- in SQL
VALUES getCurrentConnection().getMetaData().getURL()

Using java.lang Methods as Functions

You can use methods of java.lang classes for all the functions that are built into other databases. For example, you can call the static method java.lang.Math.abs, which is similar to ABS in other database systems. You can call the method this way:

SELECT (CLASS java.lang.Math).abs(balance) FROM account

You can also define an alias for any static Java method, making it appear as a native built-in function. You use the CREATE METHOD ALIAS statement. Alias definitions are specific to a database; to use an alias when connected to a database, you must define the alias within that database. Once the aliases are defined, they are available to all connections.

CREATE METHOD ALIAS ABS FOR java.lang.Math.abs

After defining the alias, you can call the method this way:

SELECT ABS(balance) FROM account

Your installation includes a script that you can run to create aliases for all the java.lang.Math static methods. See readme.html in demo/util/methodalias for more details.

Appendix A, "Mapping of Functions to java.lang Methods", lists the ways the java.lang methods correspond to functions built into other database systems.

Getting Methods to Look and Work More Like Stored Procedures

Some Cloudscape users may be used to working with another RDBMS's stored procedures. As you know, Cloudscape does not use stored procedures. Instead, it allows you to execute any Java method. When such a Java method itself accesses the database, it is called a database-side JDBC method. Database-side JDBC methods are the ones that most closely resemble stored procedures, because they are executed by the DBMS and they access data. However, they still work a bit differently than traditional stored procedures. For example:

  • They do not have to be stored in the database (though they can be).
  • Although they are precompiled to the extent that all Java classes are (executing a method within an SQL-J statement doesn't take any longer than executing the same method from your application), the SQL-J statements they execute are not precompiled (though they can be).
  • You may have to cast the data types of dynamic parameters when calling them.
  • The syntax for calling them can be a bit unwieldy.

Here are some tips for making a method call look and work more like a traditional stored procedure, if that is what you want:

  • Create a stored prepared statement for any complex SQL-J statement the method executes:

    CREATE STATEMENT getDirectFlights AS
    yourSQLStatementHere

  • Make a method alias for it to simplify the syntax of calling the method:

CREATE METHOD ALIAS myMethodAlias FOR
myClass.myMethodThatCallsThatStatement

To call the method, instead of the following:

CALL (CLASS myClass).myMethodThatCallsThatStatement(
    getCurrentConnection(), ?, CAST (? as int))

you can execute:

CALL MyMethodAlias(getCurrentConnection(), ?, CAST (? as int))

  • If the need to cast the data types of the parameters is still bothersome, you can create a stored prepared statement for executing it. This simplifies calling the method, because you can cast the data type of the dynamic parameter "inside" the stored prepared statement:

    CREATE STATEMENT George AS CALL MyMethodAlias(
        getCurrentConnection(), ?, CAST (? as int))

    You have to create the statement only once. The call is simplified; you will not have to cast the data type of the dynamic parameter:

    EXECUTE STATEMENT George

  • Store the actual Java class in the database to avoid problems with deployed classes. That way, you really are executing a "stored procedure."

Here are some samples of Java code:

Connection conn;
PreparedStatement = conn.prepareStatement(
    "CALL (CLASS myClass).myMethodThatCallsThatStatement(" +
    "getCurrentConnection(), ?, CAST (? AS INT))");
ps.setString(1, "a string");
ps.setInt(2, 2);
ps.executeUpdate();
*    *    *
Statement s = conn.createStatement();
s.executeUpdate(
    "CREATE METHOD ALIAS myMethodAlias " +
    "FOR myClass.myMethod;");
PreparedStatement = conn.prepareStatement(
    "CALL MyMethodAlias(getCurrentConnection(), ?, CAST (? AS INT))");
ps.setString(1, "a string");
ps.setInt(2, 2);
ps.executeUpdate();
*    *    *
ps = conn.prepareStatement(
    "CREATE STATEMENT George AS CALL MyMethodAlias(" + 
    "getCurrentConnection(), ?, CAST (? AS INT))");
ps.executeUpdate();
*   
*    *    *
PreparedStatement georgeStatement = conn.prepareStatement(
    "EXECUTE STATEMENT George");
ps.setString(1, "a string");
ps.setInt(2, 2);
ps.executeUpdate();
*    *    *

Using Cloudscape-Supplied Methods to Work with Bits

Cloudscape provides a utility class for working with bits, COM.cloudscape.util.BitUtil. It also provides a script to load the static methods of this class into method aliases. See readme.html in /demo/util/methodalias for more details.

The methods allow you to do bit manipulation on flags stored in a bit field.

ij> create table tmp(b bit(16));
0 rows inserted/updated/deleted
ij> create method alias bitSet for COM.cloudscape.util.BitUtil.set;
ij> create method alias bitClear for COM.cloudscape.util.BitUtil.clear;
ij> create method alias bitGet for COM.cloudscape.util.BitUtil.get;

ij> insert into tmp VALUES (B'1101000000000000');
1 row inserted/updated/deleted
-- checks the value of the bit at position 2
ij> SELECT bitGet(b, 2) FROM tmp;
SQLC&
-----
false
-- sets the bit at position 2
ij> update tmp set b = bitSet(b,2);
1 row inserted/updated/deleted
-- checks the value of the bit at position 2
ij> SELECT bitGet(b,2) FROM tmp;
SQLC&
-----
true
-- clears the bit at position 2
ij> update tmp set b = bitClear(b,2);
1 row inserted/updated/deleted
ij> SELECT bitGet(b, 2) FROM tmp;
SQLC&
-----
false
ij>

Working with Arrays

SQL-J also does not yet have built-in support for arrays. However, java.lang.reflect.Array provides for dynamic array manipulation, and its methods can be applied to array objects that are returned by other methods. An example of an array-rich library is JNL, a public-domain numeric library available from VisualNumerics (www.vni.com/products/wpd/jnl/jnl_1_0.html).

Managing External Resources

You can store a java.io.File or java.net.URL object in a column to manage external resources from within a database. See /demo/programs/media in the installation directory for a more complete example.

CREATE TABLE images (
    imageFile SERIALIZE(java.io.File),
    imageDesc VARCHAR(100))

INSERT INTO images VALUES (NEW java.io.File('Cloudscape.gif'),
    'Cloudscape Splash Logo')

SELECT NEW java.io.FileOutputStream(imageFile)
    FROM images
    WHERE imageDesc.toLowerCase() LIKE '%logo%'

Accessing Data from Another DBMS with Cloudscape's VTI

It is always possible for a Java program to load two JDBC drivers, establish a connection to each database, and import data "by hand" from one database to the other with a construct like this:

ResultSet rs = queryToFirstDatabase.executeQuery();
while (rs.next()) {
    /* get each column from each row here, and store in table
       in the second database*/
    insertIntoSecondDatabase.setInt(1, rs.getInt(1));
insertIntoSecondDatabase.executeUpdate();

Cloudscape's Virtual Table Interface allows you to avoid all that coding. Create a class that fulfills the VTI requirements; the main requirement is that the class implement the java.sql.ResultSet interface. (For complete details about the requirements, see ExternalVirtualTable in the Cloudscape Reference Manual.)

When a class fulfills the VTI requirements, you can construct an instance of it in the FROM clause of an SQL-J statement to construct an ExternalVirtualTable. Cloudscape then uses the ResultSet it creates to access data.

For an example of a class that does this, see COM.cloudscape.vti.ExternalQuery. This class is provided as an example, but you can also use it as a simple gateway.

This class has a constructor that takes two arguments: a JDBC URL and a String query. Provided that you load the appropriate driver first, you can use this class to import data from any DBMS that has a JDBC driver. Instantiating the class connects to the JDBC URL, executes the query, and returns the ResultSet to the SQL-J statement that called it. For example:

-- loads the driver in Cloudscape
VALUES (new sun.jdbc.odbc.JdbcOdbcDriver() IS NOT NULL)

INSERT INTO MusicCategories SELECT * FROM NEW COM.cloudscape.vti.ExternalQuery('jdbc:odbc:CloudscapeTutorial', 'SELECT * FROM MusicCategories') AS EQ

You can simplify the complexity of the grammar, if you wish, by creating a view:

CREATE VIEW MSMusicCategories (id, category) AS
SELECT * FROM NEW COM.cloudscape.vti.ExternalQuery('jdbc:odbc:CloudscapeTutorial', 'SELECT * FROM MusicCategories') AS EQ

Then just select from the view:

SELECT * FROM MSMusicCategories

Working with Date/Time Formats

Note that java.text.SimpleDateFormat provides complete control over turning dates into strings in a wide variety of formats. See Table A-3, "Mapping of Time and Date Functions to Java Methods" for details.

Here's an example:

SELECT NEW java.text.SimpleDateFormat(
    'MMMM d, yyyy').format(travel_date)
FROM FlightBookings

Supplying a Parameter Only Once

If you want to supply a parameter value once and use it multiple times within a query, put it in the FROM clause with an appropriate CAST:

SELECT phonebook.*
FROM phonebook,
   ((VALUES (CAST(? AS INT), 
        CAST(? AS VARCHAR(255)
        )
        )) AS Choice(choice, search_string)
WHERE
   (choice = 1)? firstname.equalsIgnoreCase(search_string) :
   (choice = 2)? lastname.equalsIgnoreCase(search_string) :
   (choice = 3)? phonenumber.equalsIgnoreCase(search_string) :
        FALSE

This query selects what the second parameter will be compared to based on the value in the first parameter. Putting the parameters in the FROM clause means that they need to be applied only once to the query, and you can give them names so that you can refer to them elsewhere in the query. In the example above, the first parameter is given the name choice, and the second parameter is given the name search_string.

Defining a Sequence-Number Column

A sequence-number column is a column that stores numbers that increment by one with each insertion. Columns like this are sometimes called autoincrement columns. Cloudscape provides autoincrement as a built-in feature; see CREATE TABLE statement in the Cloudscape Reference Manual.

For a detailed example of using autoincrement columns, see the toursDB sample database and the JBMSTours.serializabletypes.Person and JBMSTours.Group classes.

Generating Unique Keys

In some situations, such as in a distributed system, sequential integers are not appropriate for primary keys. Cloudscape provides a utility for generating a CHAR(32) key that is unique across databases. This utility is the getUniversalKeyStringValue method in the COM.cloudscape.util.KeyGen class, which generated a string of 32 characters.

You can use this utility in a way similar to the one shown in the example in Defining a Sequence-Number Column:

s.executeUpdate("CREATE TABLE keytester2 (mykey CHAR(32), "+
    "people VARCHAR(10))");
PreparedStatement ps = conn.prepareStatement(
    "INSERT INTO keytester2 VALUES (?, 'janets')");
String b = COM.cloudscape.util.KeyGen.getUniversalKeyStringValue();
ps.setString(1, b);
int j = ps.executeUpdate();
//since we know what the value of the generated key, we will
// be able to find our row again.
ps = conn.prepareStatement(
    "SELECT mykey, people FROM keytester2 WHERE mykey = ?");
ps.setString(1, b);

Using Literals to Save Keystrokes

You can use SQL-J literals to avoid having to type out (CLASS java.lang....) constructs:


(0)->MAX_VALUE
-- instead of (CLASS java.lang.Integer)->MAX_VALUE

''.valueOf(number)
-- instead of (CLASS java.lang.String).valueOf(number)

TRUE.valueOf(string)
-- instead of (CLASS java.lang.Boolean).valueOf(string)

Remember that you can define method aliases to save on keystrokes for invoking static methods.

Integrating External Libraries

You can immediately integrate external libraries such as JGL (from ObjectSpace) and JNL (from Visual Numerics) into your database.

All of the JGL classes, version 2.0 and higher, implement java.io.Serializable, so they can be stored in columns.

JNL provides matrix manipulation, statistical functions to complete those of java.lang.Math, and also a complex type, VisualNumerics.math.Complex, with full math support for it. Here's an example:

(CLASS VisualNumerics.math.SFun).log10(double)

Your installation includes a script that you can run to create aliases for some JNL static methods. See readme.html in /util/methodalias for more details.

Using Third-Party Tools

You can hook into any JDBC tool with just our JDBC Driver class name (COM.cloudscape.core.JDBCDriver) and Cloudscape's JDBC database connection URL.

Cloudscape also provides an ODBC driver, which allows you to use ODBC tools with Cloudscape databases. See http://www.cloudscape.com/support/Downloads/ for information.

Accessing and Modifying System Properties

You can access Java system properties within your queries (most useful when running in embedded mode; in server mode, you will see the server's properties, not the client's).

VALUES (CLASS java.lang.System).getProperty('jdbc.drivers')

Also, there are useful utility functions on the basic types, so you can get system properties in the desired data type:

VALUES TRUE.getBoolean('weblogic.verbose')

To find out the system directory:

VALUES (CLASS java.lang.System).getProperty(
    'cloudscape.system.home',
(CLASS java.lang.System).getProperty('user.dir'))

To find out the canonical path of the system directory:

VALUES NEW java.io.File(
    (CLASS java.lang.System).getProperty(
        'cloudscape.system.home',
        (CLASS java.lang.System).getProperty(
            'user.dir'))).getCanonicalPath()

You can also modify system properties this way:

CALL (CLASS java.lang.System).getProperties().put(
    'cloudscape.storage.pageSize', '8192')

NOTE: For more information on working with Cloudscape system properties, see Tuning Cloudscape.

Tricks of the VALUES Clause

Multiple Rows

Cloudscape supports the complete SQL-92 VALUES clause; this is very handy in several cases. The first useful case is that it can be used to insert multiple rows:

INSERT INTO OneColumnTable VALUES 1,2,3,4,5,6,7,8

INSERT INTO TwoColumnTable VALUES
    (1, 'first row'),
    (2, 'second row'),
    (3, 'third row')

Dynamic parameters reduce the number of times execute requests are sent across:

-- send 5 rows at a time:
INSERT INTO ThreeColumnTable VALUES
(?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?)

Mapping Column Values to Return Values

Multiple-row VALUES tables are useful in mapping column values to desired return values in queries:

-- get the names of all departments in Ohio
SELECT DeptName
FROM Depts,
(VALUES (1, 'Shoe'),
    (2, 'Laces'),
    (4, 'Polish'))
AS DeptMap(DeptCode,DeptName)
WHERE Depts.DeptCode = DeptMap.DeptCode
AND Depts.DeptLocn LIKE '%Ohio%'

You may also find it useful to store values used often for mapping in a persistent table and then using that table in the query.

Creating Empty Queries

Developers using Cloudscape in existing applications may need to create "empty" queries with the right result shape for filling in bits of functionality Cloudscape does not supply. Empty queries of the right size and shape can be formed off a single values table and a "WHERE FALSE" condition:

SELECT *
FROM (VALUES ('',1,TRUE)) AS ProcedureInfo(ProcedureName,     NumParameters, ProcedureValid)
WHERE FALSE

Inserting a Row of Nulls

You can insert a row of nulls into a table by inserting a null into a single column if all the columns are nullable:

INSERT INTO ManyColumnsTable (FirstColumn) VALUES (NULL)

Forcing Garbage Collection and Finalization

In SQL-J, you can force garbage collection and finalization to run:

CALL (CLASS java.lang.System).runFinalization()

CALL (CLASS java.lang.System).gc()

Using a Derived Table to Avoid Redundant Method Calls

If a query has the same method calls both in the SELECT list and in the WHERE clause, you may want to avoid that redundancy. You can do that with a derived table.

For example:

SELECT city.toString() FROM Cities
WHERE city.toString().indexOf('antia') > -1

You can rewrite the query like this:

SELECT c FROM (SELECT city.toString() FROM Cities) C (c)
WHERE c.indexOf('antia') > -1

Using RunTimeStatistics to Get an Approximate Row Count for a Table

If you need a fairly good estimate of the number of rows in a large table (but don't want to incur the cost of doing a count(*) or count(indexedColumn), then make use of the new method getEstimatedRowCount() on RunTimeStatistics. Execute a query that selects all rows from the table, then call the method.

st.executeQuery("SELECT * FROM table");
// no need to actually retrieve the results, that may be expensive
st.close();
ResultSet rs = st.executeQuery(
    "VALUES runtimestatistics.getEstimatedRowCount()");
// just retrieve the return value
rs.next();
int prettyGoodEstimate = rs.getInt(1);