Calling Sybase Adaptive Server Enterprise's "sp_help" from JDBC

Question

In order to query the database meta data in Sybase ASE, I found this relevant answer (not the accepted one), to be ideal:

From a Sybase Database, how I can get table description ( field names and types)?

Unfortunately, I can't seem to find any documentation, how I'm supposed to call sp_help from JDBC. According to the documentation, sp_help returns several cursors / result sets. The first one contains information about the table itself, the second one about the columns, etc. When I do this:

PreparedStatement stmt = getConnection().prepareStatement("sp_help 't_language'");
ResultSet rs = stmt.executeQuery();

while (rs.next()) {
    System.out.println(rs.getObject(1));
    // ...
}

I only get the results from the first cursor. How to access the other ones?

score 2 · Accepted Answer · answered Sep 04 '11 at 11:12

When you have multiple result sets you need to use the execute() method rather than executeQuery(). Here's an example:

CallableStatement cstmt;
ResultSet rs;
int i;
String s;
...
cstmt.execute();                        // Call the stored procedure       1 
rs = cstmt.getResultSet();              // Get the first result set        2 
while (rs.next()) {                     // Position the cursor             3 
 i = rs.getInt(1);                      // Retrieve current result set value
 System.out.println("Value from first result set = " + i);  
                                        // Print the value
}
cstmt.getMoreResults();                 // Point to the second result set  4a 
                                        // and close the first result set
rs = cstmt.getResultSet();              // Get the second result set       4b 
while (rs.next()) {                     // Position the cursor             4c 
 s = rs.getString(1);                   // Retrieve current result set value
 System.out.println("Value from second result set = " + s); 
                                        // Print the value
}
rs.close();                             // Close the result set
cstmt.close();                          // Close the statement

Very nice, I didn't know about those standard JDBC methods. Note: For this case (with Sybase ASE's `sp_help`), before step 2, I also seem to have to call `cstmt.getMoreResults()`. Otherwise, `rs` will be `null`. So I don't know if the DB2 example is wrong or if that's special for Sybase ASE — Lukas Eder, Sep 04 '11 at 11:17
Do you happen to know the answer to this question, too? http://stackoverflow.com/questions/7299550/discover-primary-unique-keys-in-sybase-ase — Lukas Eder, Sep 04 '11 at 13:48

score 1 · Answer 2 · answered Mar 25 '13 at 04:00

You also need to call getUpdateCount() as well as getMoreResults() to read the entire result set. Here is some code I used to call sp_helpartition to retrieve partition information from a SYBASE DB.

try {
    connection = getPooledConnection(poolName);
    statement = connection.createStatement();
    CallableStatement callable = connection.prepareCall(
        "{ call sp_helpartition(?) }");
    callable.setString(1,tableName);
    callable.execute();

    int partitions = 0;

    /*
     * Loop through results until there are no more result sets or
     * or update counts to read. The number of partitions is recorded
     * in the number of rows in the second result set.
     */
    for (int index = 0 ; ; index ++){
        if (callable.getMoreResults()){
            ResultSet results = callable.getResultSet();
            int count = 0 ;
            while (results.next()){
                count++;
            }
            if (index == 1){
                partitions = count;
            }
        } else if (callable.getUpdateCount() == -1){
            break ;
        }
    }
    return partitions ;
} catch (Exception e) {
    return 0 ;
} finally {
    statement.close();
    connection.close();
}

Hmm, why do I need `getUpdateCount()`? The currently accepted answer (http://stackoverflow.com/a/7298790/521799) seems to work nicely... — Lukas Eder, Mar 25 '13 at 15:40
I found that if the results consisted of result sets mixed with update counts, you need to read the update counts or you will not get full number of result sets. — John Buckle, Apr 01 '13 at 05:48
Interesting! I'll have to work that into [jOOQ](http://www.jooq.org), I guess. Thanks a lot for the hint! — Lukas Eder, Apr 01 '13 at 07:50

score 0 · Answer 3 · edited May 23 '17 at 11:55

Thanks to Martin Clayton's answer here, I could figure out how to query Sybase ASE's sp_help function generically. I documented some more details about how this can be done in my blog here. I worked support for multiple JDBC result sets into jOOQ. In the case of sp_help, calling that function using the jOOQ API might look like this:

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 my_table table, its
// columns, keys, indexes, etc
List<Result<Record>> results = create.fetchMany("sp_help 'my_table'");

Calling Sybase Adaptive Server Enterprise's "sp_help" from JDBC

3 Answers3

Linked