How to pass runtime parameters to MySQL Query from xMII server

[Former Member]

Please can anybody hellp in passing parameter at runtime to MySQL Query from xMII Server.

[erik_schrampf · Accepted solution]

If you are passing it in from a webpage to a SQL query here are the steps.

1. Use javascript to obtain your parameter.

2. Once that value is obtained used the document.applet.getQueryObject().setParam(1, value) to set the parameter value of your query.

3. Then in your query you would need to make sure that in either yourfixed query or standard query that you reference this

parameter in your sql code. For example if you wanted to filter on a where clause using a fixed query it would be

SELECT * FROM <database table name>
    WHERE id = [Param.1]

In this statement I am assuming Param.1 is a number if it was a string you would need quotes '[Param.1]' around it.

4. If you wanted to test this statement directly from SQL you would go to the Parameter section and enter a value for Param.1 and then run the query.

The same can be done with a BLS transaction as well you would just use the Link Editor to link the new value to Param.1 in the Query.

Edited by: Michael Appleby on Dec 15, 2010 2:59 PM

[Former Member · Accepted solution]

Hi,

Just for further clarification, once the query parameter is set via javascript, you will need to invoke a method to rerun the query. Most of the applets have an update method, for example, updateChart(true) or updateGrid(true), where true indicates that the query should be rerun. A value of false in these methods will only update the changes to the display properties. The iCommand uses the executeCommand() method.

You can also add additional parameters to the applet definition itself, for example,

<PARAM NAME="Param.1" VALUE="L1Speed">

When the page loads, this value will replace Param.1 in the actual query.

Or, you can make it dynamic, retrieving the value from a session property or from the URL itself. At runtime, the will be replaced by the actual value. <PARAM NAME="Param.1" VALUE="">

Kind Regards,

Diana Hoppe

Edited by: Diana Hoppe on Dec 15, 2010 9:24 AM

Edited by: Diana Hoppe on Dec 15, 2010 9:25 AM

[Former Member · Accepted solution]

Thakyou .

your answer is really helps me but The syntax SELECT * FROM <database table name>

WHERE id = [Param.1]

is not working in Oracle Database so can you please send the syntax for Oracle database also.

[Former Member · Accepted solution]

Hi ,

Your answer is really helps me but the syntax

SELECT * FROM <database table name>

WHERE id = [Param.1]

is not working for oracle database. So can you please send the syntax for oracle database also.

Thanks&Reagards,

RajKumar,

Sitacorp pvt Limited.

[erik_schrampf · Accepted solution]

SELECT * FROM <database table name>
WHERE id =  '[Param.1]' 

Not sure what happened it must of been got marked up when posted as I forgot to put the code markup around it.

Hope this helps.