Calling SQL Stored Procedure from binding expression

Ignition
1

I am trying to bind an opc tag path to a customer property dynamically by calling a stored procedure that builds the path.

the entry below in an expression for the property works:

{Industrial_HiVision/F_Project/F_My_Network/D_192_168_3_38/P_Port_1/V_Link/Value}

My query builds the exact same path.

I tried just putting the select statement in a sql query for the binding but the syntax of creating the script causes an issue with java syntax because I have to add +’}’ to the end of the final select statement.

I then tried just calling it from a stored procedure call in the sql query and returns exactly what I want, but it does not treat path returned as the binded value but just text in that field:

exec sp_GetHVPortConfigByIPIA

So I assume I need to call the stored procedure in an expression for the binding of that property for it treat the returned opc path as the binded value?

So I now I am trying to figure out how to call the stored procedure in the expression. I have seen a few entries about using executeQuery, etc… but when I attempt that in an expression, it treats executeQuery as an unknown function.

executeQuery("exec sp_GetHVPortConfigByIPIA ")

Beyond that, I don’t see how I direct it to a specific connection source in the expression, which means I am missing the point entirely on calling sql from an expression as a whole. Gets back to my lacking java exposure. :smiley:

Sorry for long post, any pointers or links I can read up on specifically to expresions would be greatly appreciated.

2

hmmmm… after reading more about the uses of scripting with expressions, I don’t think my post jives. Time to rethink the approach.

3

While I know I am just answering myself, thought it might be helpful to other noobs working out similar issues…

I decided to create custom properties for my template, and then used an indirect tag to make the path generation more dynamic.

for example:
if my tag needs to be:
/topic/project/folder/device/attribute/value

my indirect tag is built up of custom properties from my template

template custom properties:
topic - becomes indirect reference 1
project - becomes indirect reference 2
folder - becomes indirect reference 3
device - becomes indirect reference 4
attribute - becomes indirect reference 5
value - actual text reference

Indirect Tag:
{1}/{2}/{3}/{4}/P_Port_{5}/V_Link/Value

Then I can use sql query to assign custom properties. Anyway, probably better ways to handle this, but I got this working pretty well.