Dynamic SQL query

[kapla]

Hi,

Is it possible to access specific variables in SQL with a dynamic link in ST ?

I can access specific variable in a folder or a group with the SQL querries but I need to tell where to search or it would be too generic.

For example if I have multiple site with the same tree : SiteX/DeviceX/power_setpoint

(power setpoint is a generic DNP3 output)

And I would like to get in a ST program the value of power setpoint if it exist in SiteX.

 ie: QueryState3 AT %S(SELECT Id,FullName,OutstationId FROM CDNP3AnalogOut WHERE FullName LIKE '%.%.%.testbench.%') : RESULTSET OF Point3;
At this moment I use a query but I need to change the query in each Site to match SiteX :

I know we can use {link} in VAR declaration and link in input but I can't make a fusion of both.

ie: P_FCR1 AT%I({Link1}.CAP1.R_CS_FCR_Cap.Pa_RT):DINT;

Is it possible to have one same ST who can use a query to check one specific Site.

For example in ST program 1 I got this:

 QueryState3 AT %S(SELECT Id,FullName,OutstationId FROM CDNP3AnalogOut WHERE FullName LIKE '%.%.%.testbench.%') : RESULTSET OF Point3;

I would like to have a dynamic variable (name link for example) to replace %.%.%.testbench.% by and so I can set this variable link to %.%.%.testbench.% outside the ST program (in ST program 2)

[AdamWoodlandToo · Accepted solution]

See https://community.se.com/t5/Geo-SCADA-Knowledge-Base/Accessing-Data-via-SQL-in-Structured-Text/ba-p/... for how you can do what I think you're trying to do.

You'll have to have a calling logic program to pass in the relevant variables for each site as the dynamicness need to come from the VAR_INPUT block not the VAR block

[kapla]

Thanks Adam, 

It's working well.

I was trying to use the VAR_INPUT but I didn't check correctly the WITH_PARAMS.

So now I got my site (string) in input, I need to concat the site Name and '.%' before then I use this string as param in my query and everything work like a charm.

ex:

QueryState AT %M(SELECT Id,FullName,CurrentValue,OutstationId FROM CDNP3AnalogIn WHERE FullName LIKE ?) : RESULTSET OF Point WITH_PARAMS Link;

With Link as my site FullName.%

[AdamWoodlandToo]

The double bar || is the SQL concatenate function, in the resource centre article there is an example of this

Points AT %S(SELECT Id, FullName, CurrentValue FROM CPointAlgManual WHERE FullName LIKE '%' || ? OR FullName LIKE ? || '%') : RESULTSET OF Point WITH_PARAMS Name, Name;

[kapla]

Thanks,

Do you know if it's possible to use WITH_PARAMS to adress a specific Data Table not just to specify the values.

Like not declare it but with a '?' and a Var_input ?

"D1 AT %D(DELETE FROM table01);" from this 

"D1 AT %D(DELETE FROM ?) WITH_PARAMS linkTable01;" to this 

---

[sbeadle]

No, it is not possible to do that - the tables in the query are fixed. Perhaps use a system command to run an external program for this?