How To: Display the Full Executed Query Text, Including Parameters

When trouble shooting a query in your report design, wouldn’t it be nice to be able to write the entire query out to your console…

OpenText profile picture

OpenText

May 12, 20153 minutes read

When trouble shooting a query in your report design, wouldn’t it be nice to be able to write the entire query out to your console or to a text control within your design, including the parameters? Or, maybe you want to write out the full queries that your users are running to a log file for monitoring.

If you are not using data set parameters, this is easy (and also unnecessary since the entire query text is static and available in the data set editor). You could just write the following script in a dynamic text control in your report design (or write it out to a log file or the console if running eclipsec.exe):

dataSetQueryDefinition

If you are using data set parameters, things are a little different.

With a query like:

queryText

You would get exactly that as your output, even after the user has entered parameter values or you’ve passed values through the data set parameters in another manner. The parameter values would not automatically replace the ‘?’ in the queryText property.

To get the values that are passed to the query, we can access the input parameter values from the data set’s afterOpen script with:

getInputParams

We can also access the queryText property from the afterOpen script with:

thisQueryText

With a little script, we can take the queryText and replace the ‘?’ markers with the actual parameter values:

afterOpen

This script takes the query text, replaces the ‘?’ markers with ‘–‘ (this is done because the split does not work on the ‘?’), splits the query text where the parameter values need to be inserted, then loops through these query pieces combining them with the query values.

If you had more query after the last data set parameter, you would need to add the last piece of the query back, but for our example, this is not needed. This also does not handle the single quotes that would surround a string or a date. You’d have to handle that by checking the class of the value:

parameterClass

This will return “class java.lang.Integer”, “class java.lang.String”, etc. allowing you to handle the insertion of quotes.

Now, we have our query with parameter values stored in the “newqry” variable to display in the report output, write to a log file, etc. In this example, we write it out to a global variable and recall it from a text control in the report, as you can see here:

reportOutput

Thanks for reading.

Share this post

Share this post to x. Share to linkedin. Mail to
OpenText avatar image

OpenText

OpenText, The Information Company, enables organizations to gain insight through market-leading information management solutions, powered by OpenText Cloud Editions.

See all posts

More from the author

Manutan combines digital services with the human touch to delight customers

Manutan combines digital services with the human touch to delight customers

At Manutan, we equip businesses and communities with the products and services they require to succeed. Headquartered in France, our company has three divisions, serving…

January 31, 2024 4 minutes read
Reaching new markets in Europe and beyond

Reaching new markets in Europe and beyond

How information management specialists at One Fox slashed time to market for innovative products with OpenText Cloud Platform Services At One Fox, we’ve driven some…

January 18, 2024 4 minutes read
SoluSoft helps government agencies tackle fraud faster

SoluSoft helps government agencies tackle fraud faster

Fraud, in all its forms, is a pervasive problem, spanning industries and preying on vulnerabilities in federal and state government systems. Each year in the…

November 21, 2023 3 minutes read

Stay in the loop!

Get our most popular content delivered monthly to your inbox.