CODEWITHSUNDEEP
sqlgroovyresultset
Steve
Steve

Reputation: 4691

Groovy SQL Multiple ResultSets

I am calling a stored procedure from my Groovy code. The stored proc looks like this

SELECT * FROM blahblahblah

SELECT * FROM suchAndsuch

So basically, two SELECT statements and therefore two ResultSets.

sql.eachRow("dbo.testing 'param1'"){ rs ->
    println rs
}

This works fine for a single ResultSet. How can I get the second one (or an arbitrary number of ResultSets for that matter).

Upvotes: 0

Views: 1769

Answers (2)

Hatem Jaber
Hatem Jaber

Reputation: 2402

This question is kind of old, but I will answer since I came across the same requirement recently and it maybe useful for future reference for me and others.

I'm working on a Spring application with SphinxSearch. When you run a query in sphinx, you get results, you need to run a second query to get the metadata for number of records etc...

// the query
String query = """
    SELECT * FROM INDEX_NAME WHERE MATCH('SEARCHTERM')  
    LIMIT 0,25 OPTION MAX_MATCHES=25;
    SHOW META LIKE 'total_found';
"""

// create an instance of our groovy sql (sphinx doesn't use a username or password, jdbc url is all we need)
// connection can be created from java, don't have to use groovy for it
Sql sql = Sql.newInstance('jdbc:mysql://127.0.0.1:9306/?characterEncoding=utf8&maxAllowedPacket=512000&allowMultiQueries=true','sphinx','sphinx123','com.mysql.jdbc.Driver')

// create a prepared statement so we can execute multiple resultsets
PreparedStatement ps = sql.getConnection().prepareStatement(query)

// execute the prepared statement
ps.execute()

// get the first result set and pass to GroovyResultSetExtension
GroovyResultSetExtension rs1 = new GroovyResultSetExtension(ps.getResultSet())
rs1.eachRow {
    println it
}

// call getMoreResults on the prepared statement to activate the 2nd set of results
ps.getMoreResults()

// get the second result set and pass to GroovyResultSetExtension
GroovyResultSetExtension rs2 = new GroovyResultSetExtension(ps.getResultSet())
rs2.eachRow {
    println it
}

Just some test code, this needs some improving on. You can loop the result sets and do whatever processing...

Comments should be self-explanatory, hope it helps others in the future!

Upvotes: 1

dmahapatro
dmahapatro

Reputation: 50245

You would need callWithAllRows() or its variant.

The return type of this method is List<List<GroovyRowResult>>.

Use this when calling a stored procedure that utilizes both output parameters and returns multiple ResultSets.

Upvotes: 3

Related Questions