Adding Parameters to Your Query

In “https://help.livelyapps.com/pocketquery-for-confluence-cloud/creating-your-first-sql-query” you learned to use PocketQuery to execute basic SQL statements. Understanding how to use parameters is the first step to becoming a PocketQuery power user! Let’s go back to the Queries tab in the PocketQuery administration and select our “Rfam Authors” Query to edit it:

PocketQuery allows you to insert parameters int your Query statement via the :-syntax. Let’s try this out by changing the SQL of our Query to this:

SELECT author_id, name, last_name
FROM author
WHERE author_id > :min_author_id

We have now added a parameter to our Query that the user can supply values for. If we head back to our page again, and edit the PocketQuery macro, we will find that there is now a field which allows us to enter a value for min_author_id. Our table will only display authors with an id bigger than our supplied value of 100.

Parameter Types

PocketQuery will treat Query parameters as if they are of type string by default. Now, in our specific case, MySQL is smart and automatically converts our parameter to the right type (integer). But usually, you will need to set the types of your parameters correctly yourself. This can be done in the “advanced settings” section of your Query:

You are now able to utilize Query parameters. But there’s still more to learn! How about trying out REST Datasources next?

Continue with “Working With REST Datasources“