Welcome to the new Schneider Electric Community

It's your place to connect with experts and peers, get continuous support, and share knowledge.

  • Explore the new navigation for even easier access to your community.
  • Bookmark and use our new, easy-to-remember address (community.se.com).
  • Get ready for more content and an improved experience.

Contact SchneiderCommunity.Support@se.com if you have any questions.

Close
Invite a Co-worker
Send a co-worker an invite to the Exchange portal.Just enter their email address and we’ll connect them to register. After joining, they will belong to the same company.
Send Invite Cancel
84601members
353876posts

Structured Text SQL Query with Direct Variable filter

EcoStruxure Geo SCADA Expert Forum

Find out how SCADA systems and networks, like EcoStruxure Geo SCADA Expert, help industrial organizations maintaining efficiency, processing data for smarter decision making with IoT, RTU and PLC devices.

Solved
Hardin4019
Lieutenant
Lieutenant
0 Likes
3
1578

Structured Text SQL Query with Direct Variable filter

I'm trying to make life a little easier on myself. I have a Mimic with some fields to enter information about a meter template. In particular this meter template doesn't have its own Outstation/Scanner, (this template is for a second, third, or forth meter run). I want to fill in a field "ScannerParentGroupName" that is a String type Variable that I want to use to filter a query in my structured text program that I will setup to fill in the ScannerID field for the points in the template. I have structured text program below, but for some reason, the program upon execution will ask you what value should be assigned to "Scan" since it is a VAR_INPUT. Moving the Scan := ScannerParentGroupName; up before the query doesn't work, and changing the VAR_INPUT to VAR doesn't work. What am I missing?

 

Code;

 

TYPE

SCANNER : DATABASE_OBJECT(TfScanner)
ID: STRING;
FULLNAME: STRING;
END_DATABASE_OBJECT;

END_TYPE

 

PROGRAM TEST

VAR_INPUT
Scan:STRING;
END_VAR

VAR
NEWSTRING AT %M(.New String):STRING;
ScannerParentGroupName AT %I(.Scanner Parent Group Name):STRING;
END_VAR

VAR NOCACHE
Os AT %S(SELECT Id, ID AS SCANNNERID, FULLNAME FROM TfScanner WHERE FULLNAME LIKE ?) : RESULTSET OF SCANNER WITH_PARAMS Scan;
END_VAR
Scan := ScannerParentGroupName;
NEWSTRING := Os.Value.ID;

END_PROGRAM


Accepted Solutions
geoffpatton
Commander
Commander
0 Likes
0
1576

Re: Structured Text SQL Query with Direct Variable filter

I have a ST program that on interval executes just fine, but if I right click on it and select execute then it prompts me for the ParentFolderInput. It was written in version 2010 R1.1

 

I was annoyed by it and thought it should not do that, but at the time I was trying to get the project finished and since it worked on a Interval as I needed it I just moved on and never inquired why it does this.

 

If you use a method button to execute it, you can set it to a null '' (single quote, single quote) for its initial value.

 

Here is what  I did for reference.

PROGRAM MyTextProgram

METHOD
Exec AT %M(.Trigger ST_EA.Execute):STRING;
END_METHOD
VAR
ParentFolder AT %M(..FullName) : STRING;
END_VAR

VAR_INPUT
ParentFolderInput : STRING;
END_VAR

ParentFolderInput := CONCAT(ParentFolder, '.BI.%');

Exec(ParentFolderInput);

END_PROGRAM

See Answer In Context

3 Replies 3
geoffpatton
Commander
Commander
0 Likes
0
1577

Re: Structured Text SQL Query with Direct Variable filter

I have a ST program that on interval executes just fine, but if I right click on it and select execute then it prompts me for the ParentFolderInput. It was written in version 2010 R1.1

 

I was annoyed by it and thought it should not do that, but at the time I was trying to get the project finished and since it worked on a Interval as I needed it I just moved on and never inquired why it does this.

 

If you use a method button to execute it, you can set it to a null '' (single quote, single quote) for its initial value.

 

Here is what  I did for reference.

PROGRAM MyTextProgram

METHOD
Exec AT %M(.Trigger ST_EA.Execute):STRING;
END_METHOD
VAR
ParentFolder AT %M(..FullName) : STRING;
END_VAR

VAR_INPUT
ParentFolderInput : STRING;
END_VAR

ParentFolderInput := CONCAT(ParentFolder, '.BI.%');

Exec(ParentFolderInput);

END_PROGRAM

BevanWeiss
Spock
Spock
0 Likes
1
1570

Re: Structured Text SQL Query with Direct Variable filter

When calling ST SQL with parameters, I normally end up having two logic routines.

The first just setups what the 'inputs' to the query are going to be, and then calls Execute on the second logic routine passing in the inputs which are then VAR_INPUTs on the second logic routine.

 

This is mostly related to the way that the 'sequence' of ST execution occurs.

The SQL Logic is part of the 'Input' scan, which means that it occurs at the same time as the VAR variables are initialised.. which is why the SQL Params can't be VAR variables, they MUST be VAR_INPUT variables, which are available to the ST logic PRIOR to the ST executing.


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
Hardin4019
Lieutenant
Lieutenant
0 Likes
0
1559

Re: Structured Text SQL Query with Direct Variable filter

Thanks for taking the time to reply and giving me the direction I needed guys.