Stored procedures returning multiple cursors


When adding support for Sybase ASE to jOOQ, I came across a peculiar procedure that can be used instead of the dictionary tables, to query for schema meta information (see also my previous post about database schema navigation). With Sybase ASE, you can call a procedure (or pragma?) called sp_help, which will return a cursor containing all tables in the schema:

> sp_help

+--------------------+-----+------------+
|Name                |Owner|Object_type |
+--------------------+-----+------------+
|sysquerymetrics     |dbo  |view        |
|v_author            |dbo  |view        |
|v_book              |dbo  |view        |
|v_library           |dbo  |view        |
|t_639_numbers_table |dbo  |user table  |
+--------------------+-----+------------+

Now you can also specify the name of one of your tables as a parameter. Then the procedure returns detail information about that table, as well as a list of columns. In some cases, it may even return constraint and index information. For instance, to know more about t_author:

> sp_help 't_author'

+--------+-----+-----------+-------------+-------------------+
|Name    |Owner|Object_type|Object_status|Create_date        |
+--------+-----+-----------+-------------+-------------------+
|t_author|dbo  |user table | -- none --  |Sep 22 2011 11:20PM|
+--------+-----+-----------+-------------+-------------------+

+-------------+-------+------+----+-----+-----+
|Column_name  |Type   |Length|Prec|Scale|...  |
+-------------+-------+------+----+-----+-----+
|id           |int    |     4|NULL| NULL|    0|
|first_name   |varchar|    50|NULL| NULL|    1|
|last_name    |varchar|    50|NULL| NULL|    0|
|date_of_birth|date   |     4|NULL| NULL|    1|
|year_of_birth|int    |     4|NULL| NULL|    1|
+-------------+-------+------+----+-----+-----+

Luckily, the people who designed JDBC have taken this feature into account in their API design and all of the following RDBMS’ JDBC drivers seem to implement it correctly (even if not all of them support stored procedures):

  • DB2
  • Derby
  • H2
  • HSQLDB
  • Ingres
  • MySQL
  • Postgres
  • SQLServer
  • Sybase ASE (with the jTDS driver)

These drivers don’t implement this API:

  • Oracle
  • SQLite
  • Sybase SQL Anywhere (with the jconn3 driver)

Here’s how fetching several cursors can be done with JDBC:

ResultSet rs = statement.executeQuery();

// Repeat until there are no more result sets
for (;;) {

  // Empty the current result set
  while (rs.next()) {
    // [ .. do something with it .. ]
  }

  // Get the next result set, if available
  if (statement.getMoreResults()) {
    rs = statement.getResultSet();
  }
  else {
    break;
  }
}

// Be sure that all result sets are closed
statement.getMoreResults(Statement.CLOSE_ALL_RESULTS);
statement.close();

Of course, this would be a very nice enhancement for jOOQ. With version 1.6.7, it is possible to call the above “sp_help” procedure in Sybase ASE directly, using this piece of code

Factory create = new ASEFactory(connection);

// Get a list of tables, a list of user types, etc
List<Result<Record>> tables = create.fetchMany("sp_help");

// Get some information about the t_author table, its
// columns, keys, indexes, etc
List<Result<Record>> results = create.fetchMany("sp_help 't_author'");

Tags: , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,901 other followers

%d bloggers like this: