Can Pocket Query work with more than 1 result set?

[Lawrence C]

1. From the documentation, it seems like PQ works with 1 result set. Our SQL searches normally return 2 data sets and we were wondering how we could work with both data sets.

[Felix Grund (Scandio)]

Hi Lawrence,

Welcome to the set of PocketQuery power users . You can achieve what you want by rendering a second PocketQuery macro from within the template of the first PocketQuery macro. This is based on the helper function renderPocketQuery macro described in the PocketQuery documentation. I prepared an example for you...

CountryCodeByName - retrieves the code of the country with the name given as parameter. In the template of the query, the code is read from the result and a second query LanguagesByCountryCode is executed with the read value. The second query has the dynamicLoad flag set for performance benefit.

Query CountryCodeByName:

SELECT Code
FROM Country
WHERE Name = :Name

Template CountryCodeByName:

## Our result only has length == 1 so the first row will contain our CountryCode.
## We can use "Code" since the column name is "Code".
#set($pq_countryCode = $result.get(0).Code)
$PocketQuery.renderPocketQueryMacro("LanguagesByCountryCode", {
	"page": $page,
	"dynamicload": true,
	"parameters": {"CountryCode": $pq_countryCode}
})

Query LanguagesByCountryCode (uses default template):

SELECT CountryCode, Language
FROM CountryLanguage
WHERE CountryCode = :CountryCode

===

We also had scenarios before where we had one "dummy" super-query that simply did a SELECT 1 only to execute arbitrarily many other PocketQuery macros from within its template. Each result can be extracted into a variable and logic can become arbitrarily complex.

Please tell me if this helps or if you need more help!

Regards, Felix (Scandio)

[Lawrence C]

Hey Felix, thanks for the answer. Since our queries will be arbitrary of one another, how would they be displayed when we execute them. Going back to the People and Country example, what we would like to see when a page loads are 2 tables, one table covering data for People and a separate table covering data for Country

[Felix Grund (Scandio)]

Hi Lawrence! If your queries are independent from each other and you simply want to display two tables, why do you not simply use two PocketQuery macros (one for each query)? Or do I misunderstand something here?

[Lawrence C]

Hey Felix,

My teammate has some more input on this:

"The stored procedures that we are executing have a rollback trans. Because of this, we get 2 result window when running in SSMS. A single piece of SQL code (a single PQ macro) executing our stored procedures will return a "Before Changes" result window as well as an "After Changes" result set. 
Currently, running that SQL in PQ will only return the Before Changes result window and not the "After Changes" window. Is it possible for a single query to return 2 result windows? If so, how does it work for putting a PQ macro inside of another? Would that happen on the actual Confluence page and not in the PQ Query Management window? Here's an example of our code if it can provide any clarity to you:
--

declare @partnerIds tt_StringId
insert into @partnerIds values (:partnerid)
exec Support.prc_SetViewabilityTrackingSamplingRate 
      @partnerIds
    , @samplingRate = 0.1
    , @videoSamplingRate = 1.0
    , @testOnly = :ToC
/* "ToC" needs the parameter type "Integer" */

--
That code returns 2 output windows of which we can only see one in PQ.
Thanks so much!"

[Felix Grund (Scandio)]

Thanks for the input! I've actually never worked with such stored procedures in PocketQuery and I'm not really sure if you can make it work like that. I would have to dive into that more. Maybe you could somehow simplify this to work with simple SQL statements without a stored procedure? What I meant with two PocketQuery Macros into one page and run the two different queries with these two macros. Not "one inside another". There will be simply two different tables in your page with the result for each query. You can use the PageLayout feature of the Confluence editor for some layout. I'd also recommend the "Enable dynamic load" macro parameter such that the PocketQuery macros won't delay your page to be loaded.

[Felix Grund (Scandio)]

Hi Lawrence,

Our SQL searches normally return 2 data sets

If you're talking in Java terms, our code will reach org.springframework.jdbc.core.JdbcOperations.query somewhere which gives us a list. This call then somewhere reaches org.springframework.jdbc.core.JdbcTemplate.query which returns an Object created from org.springframework.jdbc.core.ResultSetExtractor.extractData.

So yes, this will usually be one result set. I don't really understand yet how it should be two? Maybe you can elaborate 

Regards, Felix

[Lawrence C]

Hey Felix,

Sorry, I don't think I asked my question clearly.

For most of our sprocs, we have 2 or more data sets returning, that is we have 2 or more select statements ie:

select * from People

select * from Countries

Is it possible for PQ to go through both queries in a single page and return them both in separate tables/charts on the same page?

thanks,

Lawrence

[Pavel Potcheptsov]

It looks like it's not possible to have more than one SELECT in a PocketQuery.

[Felix Grund (Scandio)]

Hi Pavel! You are right, SQL queries in PQ must always be one statement. But there are other ways to send multiple statements with PQ. Please follow up if you need more details on that!