shorter usage of PocketQuery macro

[Silver Salonen]

Is it possible to use PocketQuery queries with something like {pocket-query myquery1 myparam1 myparam2} ?

[Felix Grund (Scandio)]

The parameters are encoded in GET syntax like key1=value1&key2=value2. So you can try like this: {code} <ac:macro ac:name="pocketquery"> <ac:parameter ac:name="name">mypocketquery</ac:parameter> <ac:parameter ac:name="parameters">key1=value1&key2=value2</ac:parameter> </ac:macro> {code} The @param statement doesn't make sense here. How did you get this idea? :)

[Silver Salonen]

I tried creating user macro for it, but I wonder how could I set my query name (set from PocketQuery configuration) in there?

## @param 0:title=hostname|type=string
<ac:macro ac:name="pocketquery">
	<ac:parameter ac:name="name">mypocketquery</ac:parameter>
	<ac:parameter ac:name="mypocketqueryparam1">someparameter</ac:parameter>
</ac:macro>

[Felix Grund (Scandio)]

I already replied. I think you shouldn't cross-refer to threads just because they are both about PocketQuery ;).

[Felix Grund (Scandio)]

In that case, Christian might be right and the GET parameter pass-through feature in PocketQuery might help you.

If your statement looks like this:

SELECT ipaddress FROM yourtable WHERE hostname = :hostname

If you check "Allow GET parameter pass-through" in your macro, you can pass the argument in the URL to the page like http://some-url/?pq_hostname=myhostname

However, this only works if you want to use one single hostname on the page. Otherwise, you can always build a custom template for your query in the PocketQuery admin and implement some logic that only the IP addresses you'd like are displayed. There is documentation on that here.

Let me know if you need further help!

[Kai Sun]

Hi Felix,

I have another question about PocketQuery, could you please help take a look at here: https://answers.atlassian.com/questions/279812/using-mysql-var-in-pocket-query-macro#

Thanks

[Silver Salonen]

This is almost like thread hijacking.

Can we please delete this comment from this question?

[Silver Salonen]

On the topic though.

So GET parameter pass-through is meant to work in the way that the macro is inserted onto one page and other pages then query the macro from that page?

[Felix Grund (Scandio)]

No, you insert the macro on the page. Then you provide the parameters to the URL in your browser address bar with an URL like http://some-url/?pq_hostname=myhostname

As mentioned earlier, this mechanism has the limitation that there must not be parameter names used for different macros on the page.

[Silver Salonen]

Sorry, I don't quite understand the use of this. Would it be really applicable to my use-case?

[Felix Grund (Scandio)]

I constructed your scenario in my example DB. I have a table ipaddresses that has a column "hostname" and a column "ip". Let me show you a few examples...

1.)

SELECT * FROM ipaddresses;

The result (without custom template) is:

2.)

SELECT ip FROM ipaddresses WHERE hostname = :hostname

The result (without custom template) if you provide 'myhost2' as hostname macro parameter is:

My URL for the page with the result above is:
http://felix-pc:1990/confluence/display/PQ/IP+Queries

3.)

I checked the "Allow GET parameter pass-through" macro parameter. If I now call the URL like:
http://felix-pc:1990/confluence/display/PQ/IP+Queries?pq_hostname=myhost3

The result will be this:

4.)

If I now use a custom template, I can render whatever I want...

The result will be:

If you know how to write Velocity/HTML/JavaScript code, you can use the result of your query and display whatever information you'd like.

[Silver Salonen]

Many thanks for the effort :)

Where do I get the URL for the macro?
And how will I then use these URL-based queries on Confluence pages?

[Felix Grund (Scandio)]

Do you mean different users execute different queries? Or is the same information displayed to all users? In other words: is it possible to maintain one query in the Confluence Admin and display the results to all users? Or do you mean: user1 must use a pocketquery macro with param x and user2 with param y? Sorry for the misunderstandings!

[Silver Salonen]

I mean that if I have the query "SELECT ip from hosts WHERE hostname = '$ARG1$'" in PocketQuery, then any user should be able to use that query as simply as possible,
eg: "Hostname of our fileserver is: {pocket-query ipquery ourfileserver}"

[Felix Grund (Scandio)]

I'd still like to stick with the "one pocketquery macro" approach. Is the user that has the hostnames Confluence admin? You could generally copy the list of hostnames as strings into the SQL query as IN-clause?

[Silver Salonen]

No, these queries should be usable by ordinary users too, that's why I also asked about using arguments in a shorter usage form.

I guess this "shorter usage" is really a question about Confluence, not PocketQuery specifically :)

[Felix Grund (Scandio)]

First, lets see if I understand you right: you have (1) a table in your database with one column for hostname and one column for IP address; (2) a list of hostnames you want to retrieve the IP addresses for?

I think generally, we should search for an approach to use one single PocketQuery macro. If you have the macro n times on the page, there will also be n queries to the database. Please confirm my assumptions and I will think about a good solution ;).

[Silver Salonen]

Point for "n queries per page" is a good one :)

I wonder whether it could be possible to "retrieve once, display many times"? Sounds utopic?

Anyway, if we just take the risk of "n queries", would it be possible to use certain PocketQuery inline in a shorter way?

[Christian Koch]

Two ideas:

select them all in a table without parameters (if possible)

SELECT * FROM hosts;

an show a table.

If you want to query a single host use the http GET parameters. PocketQuery supports them.

http://myhost/mypage?hostname=foo

Or am I completely wrong?

[Silver Salonen]

My question is not actually about how to create the necessary PocketQuery, but rather how to use that created query in a shorter way.

[Felix Grund (Scandio)]

Hi Silver,

I'm the developer of PocketQuery - sorry for the late answer. Do you have a template somewhere in which you want to execute the PocketQuery macro? What is the context? Is it a Confluence decorator or something similar?

Regards,
Felix

[Silver Salonen]

What I actually want is the most compact way to query IP-addresses for hostnames that we maintain in an external MySQL database.

Inserting PocketQuery macro via Insert->Macro in a table with 100 hostnames is quite a bit of pointing-clicking :)

So I imagine we could just create .xls containing these simple lines with very minimal handwork, and then copy-pasting from there into Confluence.
It would also be a simple way to use the query very quickly whenever needed.