shorter usage of PocketQuery macro

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

9 answers

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

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.

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?

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

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 ;).

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?

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?

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 :)

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!

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}"

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!

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

This is almost like thread hijacking.

Can we please delete this comment from this question?

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?

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.

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

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.

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?

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

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>

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? :)

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Oct 24, 2018 in Confluence

Atlassian Research opportunity with Confluence templates

Do you use templates with Confluence? Take part in a remote 1-hr workshop. You'll receive USD $100 for your time!   We're looking for people to participate in a   remote 1-hr workshop...

1,131 views 17 14
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you