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
84248members
353348posts

[Imported] ST Logic: SQL Query Indirect Addressing

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.

sbeadle
Janeway Janeway
Janeway
0 Likes
0
449

[Imported] ST Logic: SQL Query Indirect Addressing

>>Message imported from previous forum - Category:ClearSCADA Software<<
User: florian, originally posted: 2018-10-24 20:31:44 Id:200
This is a re-posting from the obsoleted (October 2018) "Schneider Electric Telemetry & SCADA" forum.

-------------------

**_Applez00800:_**
**_Hi all,_**

**_I know that indirect addressing can be used to address points in an ST logic programme based on a variable input parameter, but is this also possible to do in a SQL query?_**

_VAR_INPUT_
_SQLTable : STRING;_
_END_VAR_

_VAR NOCACHE_
_SQLQuery AT %S(SELECT FullName FROM {SQLTable}) : RESULTSET OF Query_
_END_VAR_

**_Trying this tells me I can't, but is there another format that I can use to achieve this?_**

**_Thanks,_**

--------------------

rwallace:
Try this:

_VAR_INPUT_
_SQLTable : STRING;_
_END_VAR_
_VAR NOCACHE_
_SQLQuery AT %S(SELECT FullName FROM ?) : RESULTSET OF Query WITH_PARAMS SQLTable;_
_END_VAR_

--------------------------

**_Applez00800:_**
**_Hey Richard,_**

**_I had a mental blank about using ? and WITH_PARAMS.. cheers!_**
**_However, it doesn't seem to like being used to replace the table name. It works fine with parts in a WHERE clause though. I seem to remember reading something about this before._**

---------------------

AWoodland:
They do occasionally increase the scope in which ? is allowed, it is a fight between flexibility and keeping ST with its strict typing.

If the ? doesn't work you could try getting the table name from a lookup table of some sort, that might trick ST into accepting it, e.g. something like:

SQLQuery AT %S(SELECT FullName FROM (SELECT TableName FROM SomeDataTable WHERE SomeKey='xyz')) : RESULTSET OF Query;

-----------------------

shiftyhead:
but how to use the DELETE?

_VAR_
_Name AT %I(.TABLE.Name):STRING;_
_END_VAR_

_VAR NOCACHE_
_Init AT %D(DELETE FROM (SELECT TableName FROM Table_Names WHERE TableName = ?)) WITH_PARAMS Name;_
_END_VAR_

Tag error: invalid SQL - expected (Line = 1, Col = 13)

--------------------------

rwallace:
Hi Shifty 🙂

You can't use a direct variable as a "WITH_PARAMS" argument - you'll have to split it into two logic programs, and use a VAR_INPUT (as shown in the example above).

 

shiftyhead:
still does not compile 😞

_PROGRAM TST_

_VAR_INPUT_
_TABLE:STRING;_
_END_VAR_

_VAR NOCACHE_
_DEL AT %D(DELETE FROM ?) WITH_PARAMS TABLE;_
_END_VAR_

_DEL.Execute();_
_END_PROGRAM_

Tag error: invalid SQL - expected (Line = 1, Col = 13)

------------------------

**_Applez00800:_**
**_Hi Shifty,_**

**_It's not possible at the moment to use WITH_PARAMS for the table part of a query. This will only work for things such as conditions in a WHERE clause (and I'm fairly sure it will only work for the comparison expression as well E.g. WHERE FullName LIKE ?, not WHERE ? LIKE '%Hello%')_**

**_Your other option would be to do this using Scripting instead._**

_Sub DeleteStuff()_

_Dim saTables_
_Dim sTable_
_Dim sSQLQuery_
_Dim oRecordSet_

_saTables = Array("CDNP3AnalogIn", "CUsers", "Test")_

_For Each sTable in saTables_
_sSQLQuery = "DELETE from " & sTable & " WHERE FullName LIKE 'Path%'"_
_Set oRecordSet = Server.Query(sSQLQuery)_
_Next_

_End Sub_

--------------------

YVSemenov:
Hi guys, I need to select the data from the database using the data stored in some external variable.

I have a program:

TYPE
CardsTable : STRUCT
id : INT;
id_card : STRING;
id_wor : STRING;
id_card_t : INT;
id_car : INT;
id_free : INT;
DateBeg : DATE;
DateRec :DATE;
nomer : INT;
status : INT;
Guest: INT;
END_STRUCT;
END_TYPE

PROGRAM CardRead

VAR
kod AT %I(.kod_Cards.CurrentValue): INT;
data AT %Q(.data_Cards.CurrentValue): STRING;
END_VAR
VAR_INPUT
ReadCod: INT;
END_VAR
VAR NOCACHE
Card AT %S(SELECT * FROM MySQL WHERE id = ?) : RESULTSET OF CardsTable WITH_PARAMS ReadCod;
END_VAR
data:=Card.Value.id_card;
END_PROGRAM

It works, but the specified data must be entered manually. Such a program is not working:

TYPE
CardsTable : STRUCT
id : INT;
id_card : STRING;
id_wor : STRING;
id_card_t : INT;
id_car : INT;
id_free : INT;
DateBeg : DATE;
DateRec :DATE;
nomer : INT;
status : INT;
Guest: INT;
END_STRUCT;
END_TYPE

PROGRAM CardRead
VAR
kod AT %I(.kod_Cards.CurrentValue): INT;
data AT %Q(.data_Cards.CurrentValue): STRING;
END_VAR
VAR NOCACHE
Card AT %S(SELECT * FROM MySQL WHERE id = ?) : RESULTSET OF CardsTable WITH_PARAMS kod;
END_VAR
data:=Card.Value.id_card;
END_PROGRAM

How this problem can be solved most efficient to? Can I command "SELECT" to do inside the "PROGRAM"?

----------------------

evandew:
You would need another ST program to call your program CardRead and use the "kod AT %I(.kod_Cards.CurrentValue)" as a input parameter.

For an example of this, check this post.

----------------------------

bevanweiss:
Just a revisit on this..
SQLQuery AT %S(SELECT FullName FROM (SELECT TableName FROM SomeDataTable WHERE SomeKey='xyz')) : RESULTSET OF Query;
unfortunately doesn't work.

(SELECT FullName FROM SomeDataTable WHERE SomeKey='xyz') returns a STRING temporary table, which is all that is used for the outer SELECT.

Still keen on other ideas however...
At this stage reading columns / rows of a DataGrid in ST and putting them into VECTORs would be ideal (but also seems not supported)