[Imported] ST Logic: SQL Query Indirect Addressing

[sbeadle]

>>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)