调用 Sybase Adaptive Server Enterprise 的“sp_help”来自 JDBC

发布于 2024-12-02 21:07:20 字数 770 浏览 1 评论 0 原文

为了查询 Sybase ASE 中的数据库元数据,我发现这个相关答案(不是公认的答案)是理想的:

如何从 Sybase 数据库获取表描述(字段名称和类型)?

不幸的是,我似乎找不到任何文档,说明我应该如何从 JDBC 调用 sp_help 。根据 文档sp_help 返回多个游标/结果集。第一个包含有关表本身的信息,第二个包含有关列的信息等。当我这样做时:

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

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

我只从第一个游标获得结果。如何访问其他的?

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?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

放低过去 2024-12-09 21:07:20

当您有多个结果集时,您需要使用 execute() 方法而不是executeQuery()。
这是一个示例

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 

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 
你不是我要的菜∠ 2024-12-09 21:07:20

您还需要调用 getUpdateCount() 和 getMoreResults() 来读取整个结果集。以下是我用来调用 sp_helpartition 从 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();
}

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();
}
黄昏下泛黄的笔记 2024-12-09 21:07:20

感谢 Martin Clayton 的回答,我可以弄清楚如何一般性地查询 Sybase ASE 的 sp_help 函数。我在我的 jOOQ 中加入了对多个 JDBC 结果集的支持。对于 sp_help,使用 jOOQ API 调用该函数可能如下所示:

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'");

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'");
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文