Jasper Test Sql

For use jasper report for report/print, sometime you must test sql query.

How to you test it?

With plant query, it's simple just copy and paste to almost query tool as "pgAdmin III".

But normal, your query always has parameter with jasper format.

example: Select * from c_invoice where c_invoice = $P{C_Invoice_ID}

no problem, just copy/paste to query editor and replace $P{C_Invoice_ID} with a value. ready?

no. it's hell when your query has 5 parameter and you modify it a lot.

when you replace new query in jasper report. you will must replace literal value with parameter name.

it's terrible job and potential mistake.

I just find out a query editor for postgresql with support parameter but i nearly can't.

at the end, i has two acceptable option.

default, for identify a variable use "$[" and "]" example Select * from c_invoice where c_invoice = $[C_Invoice_ID].

see isn't helpful, we still convert format of jasper to sql workbench.

sure, but we can use search and replace with regular expression and finish in 5s. (search with \$P\{([\w_]*)\} and replace with \$[$1])

has other interesting method.

   open ~/.sqlworkbench/workbench.settings

   add two below line: workbench.sql.parameter.prefix=$P{
                       workbench.sql.parameter.suffix=}

for now just copy/page from jasper report and run test.

remember when sql query has multiple parameter format as:

   Select * from c_invoice where c_invoice = $[C_Invoice_ID] and name = $V{name}

we still must use find/replace for $V{name}

(*) this is acceptable not perfect, has weak feature as below:

it use Variable substitution method, isn't truly parameter.

you can't test for pass null value, value follow rule auto convert type of postgresql

2. navicat for postgresql.

it's total support parameter but must pay. it isn't free or opensource software.

and i don't see option for change parameter format. must use find and replace, its format is [$name].

what's your solution?