software-mariodiana
11/22/2014 - 2:39 AM

Handle SQL query returning pages of data (multiple ResultSet objects).

Jython snippet that demonstrates getting the results of a complex SQL query that returns multiple ResultSet instances (pages of data). One instance of such a query might involve using a database API cursor object. In this example, I assume that you already have a zxJDBC.connection object open and need to access the underlying JDBC connection object to use the advanced features.

FancySQLQuery.py
content_copyfile_download
import com.ziclix.python.sql.zxJDBC as zxJDBC

# In place of this, imagine a complicated query returning multiple ResultSet instances.
fancyApiCursorQuery = 'SELECT MyColumn FROM MyTable'

url='jdbc:sqlserver://sqlserver-host:1433;database=MyBigDatabase'
user='mario'
pw='myP@ssw0rd'
driver='com.microsoft.sqlserver.jdbc.SQLServerDriver'

conn = zxJDBC.connect(url, user, pw, driver)
try:
    # We're going to have to use the JDBC connection directly for this fancy stuff.
    jdbcConn = conn.__connection__
    stmt = jdbcConn.createStatement()
    stmt.execute(fancyApiCursorQuery)
    rs = None
    while True:
        # Loop through the set of ResultSet objects.
        rs = stmt.getResultSet()
        while rs.next():
            print rs.getString('MyColumn')
        if stmt.getMoreResults() == False and stmt.getUpdateCount() == -1:
            # We have no more ResultSet objects.
            break
    if rs:
        rs.close()
    stmt.close()
finally:
    if conn:
        conn.close()