I would like to use a parameter as part of a table name. Is it possible to do this with PocketQuery? The database I am sending queries to has one set of tables for each customer of ours such as ABC, DEF, GHI.
If I need to run the query for customer ABC, the table would be ABC.EMPLOYEES.
If I need to run the query for customer DEF, the table would be DEF.EMPLOYEES.
Can I do this something like :customer.EMPLOYEES?
Thank you for your interest in PocketQuery! This doesn't work with the latest release of PQ on the marketplace, but it should work with PocketQuery 2.1 which is not released yet. Could you install our current release candidate version 2.1-m7? With this one I got my example work...
SELECT Name, Population FROM :Table WHERE Continent = :Continent
The only thing to take care: in the field Parameter Types you must enter YourTableParameter=Constant so in my case I put Table=Constant.
PocketQuery 2.1 will be released very soon!
Regards, Felix (Scandio)
It seems to work, but I had an issue in that I cannot give the table an alias such as in the example below, I alias the table as apl and use it in other places such as:
SELECT * FROM ABCOBJ.TAPRRRPF utm inner join abcobj.tapdefpf uex on utm.UTID = uex.UTID inner join :Table apl on apl.applicationnumber = utm.applicationnumber and apl.employeeid = utm.employeeid and utm.CLIENTLIBRARY = 'xxx' where utm.UTID = '0105200015117555'
The queries in PocketQuery only seem to work if you put :Table everywhere such as:
SELECT * FROM ABCOBJ.TAPRRRPF utm inner join abcobj.tapdefpf uex on utm.UTID = uex.UTID inner join :Table on :Table.applicationnumber = utm.applicationnumber and :Table.employeeid = utm.employeeid and utm.CLIENTLIBRARY = 'xxx' where utm.UTID = '0105200015117555'
Another thing that would be needed is to let the result set have horizontal scrolling:
This seems to be a matter of replacement order of the query template with the Spring library we are using under the hood. I don't see a straight forward solution to this. Most probably you'll have to do it as you wrote in your second example.
Regarding the styles, you will have to create your own layout with CSS in a <style> tag in the PocketQuery template. We cannot deal with possible custom layouts in the macro. A good starting point could be: https://css-tricks.com/fixing-tables-long-strings/
My developer gave me this info:
Validated. Determined not to be exploitative. This only matters if they are using string concatenation.
If the query was "select * from users where :username"
If a user typed: "test'; drop table users;--" into the username field in the confluence page, would the code build the query string as something similar to: "select * from users where username = 'test'; drop table users;--"?
I understand. Fear not:
That said, I still recommend to use a read-only DB user for the PocketQuery connection. PocketQuery is designed to be a read-only tool and assigning a user with privileges for DB manipulation is unclean.
I accept. I will create a new thread to ask about the possibility of allowing a query to be run against multiple servers instead of just one. If more than one server is available, then the user can select which one instead of an auto-load occurring (a default could be specified if auto-load was selected).
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...
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!
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