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
84546members
353802posts

Tables and Record Id's

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
DavidSkilbeck
Lt. Commander
Lt. Commander
0 Likes
10
2170

Tables and Record Id's

Hi,

I am trying to use a table as a queue.

 

Using the automation interface I grab the contents of RecordId 0.

xxxxxId = Xobj.Interface.GetValue(1, RecordId);

Then once the code completes actions, RecordId 0 is deleted.

Xobj.Interface.DeleteRecord(0);

The next time round it causes issues as there is not a RecordId = 0 it seems.

If I add to the table, then the new record takes the number 0 and the code runs again for an iteration.

 

My question is, is there a way of getting the table to re order its recordId's.

If the table could shuffle its record Id's. So recordId 1 became recordId 0 etc.

Or do I have to create a column for Id's and reorder this column via code.

Thanks,

 


Accepted Solutions
BevanWeiss
Spock
Spock
0 Likes
9
2157

Re: Tables and Record Id's

Does the queue need to be shared amongst all clients, or is it just for a single client?

For a single client, I'd recommend using something like a Scripting Dictionary, or even just a VBscript array or such.

 

If you need a queue across the whole system, then you could have a look at the CVariableArray objects... they might suit your needs better if you only need a few objects.  NOTE: You will almost always have some kind of issues with concurrency here, since there is really no way (I know of) to perform the atomic operations needed for concurrent queue access within GeoSCADA Expert scripting.

 

I'm unsure how the RecordId calculations occur.  I suspect it's got a bit of a disconnect between the client and the server.

So when you do the DeleteRecord(0).. on the server at that exact moment there is still a RecordId 0, but on the client there is no RecordId 0, however there is a RecordId 1.  Some later time the client will synchronise its modification with the server, and then update its data, and you might end up in the situation where there is a RecordId 0 on both the client and the server again... with all other Records having dropped down by an index.

 

I'd really try to avoid it if at all possible.

What you could do is using a DataTable and another column with something like a DateTime (or just a self managed incrementing value).  I'd personally go for the DateTime column, since it is much easier to handle.

When you add something to the 'queue' then you add it with the current DateTime.

To take something out of the queue you just delete the item that matches the TOP (1) FROM DataTable ORDER BY EntryDate ASC.

And do the INSERT / SELECT / DELETE using SQL.

 

There's still the possibility of things going a bit wrong... since you could have multiple clients actioning a remove at the same time, in which case only one can clearly win.  Likewise multiple clients adding something to the queue at the same time brings up the race condition around entry values if using an incrementing value... hence my recommendation to just use a DateTime.  You're more likely to have a unique DateTime than to be able to ensure global consistency on a shared variable. 


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

See Answer In Context

10 Replies 10
BevanWeiss
Spock
Spock
0 Likes
9
2158

Re: Tables and Record Id's

Does the queue need to be shared amongst all clients, or is it just for a single client?

For a single client, I'd recommend using something like a Scripting Dictionary, or even just a VBscript array or such.

 

If you need a queue across the whole system, then you could have a look at the CVariableArray objects... they might suit your needs better if you only need a few objects.  NOTE: You will almost always have some kind of issues with concurrency here, since there is really no way (I know of) to perform the atomic operations needed for concurrent queue access within GeoSCADA Expert scripting.

 

I'm unsure how the RecordId calculations occur.  I suspect it's got a bit of a disconnect between the client and the server.

So when you do the DeleteRecord(0).. on the server at that exact moment there is still a RecordId 0, but on the client there is no RecordId 0, however there is a RecordId 1.  Some later time the client will synchronise its modification with the server, and then update its data, and you might end up in the situation where there is a RecordId 0 on both the client and the server again... with all other Records having dropped down by an index.

 

I'd really try to avoid it if at all possible.

What you could do is using a DataTable and another column with something like a DateTime (or just a self managed incrementing value).  I'd personally go for the DateTime column, since it is much easier to handle.

When you add something to the 'queue' then you add it with the current DateTime.

To take something out of the queue you just delete the item that matches the TOP (1) FROM DataTable ORDER BY EntryDate ASC.

And do the INSERT / SELECT / DELETE using SQL.

 

There's still the possibility of things going a bit wrong... since you could have multiple clients actioning a remove at the same time, in which case only one can clearly win.  Likewise multiple clients adding something to the queue at the same time brings up the race condition around entry values if using an incrementing value... hence my recommendation to just use a DateTime.  You're more likely to have a unique DateTime than to be able to ensure global consistency on a shared variable. 


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..
DavidSkilbeck
Lt. Commander
Lt. Commander
0 Likes
8
2145

Re: Tables and Record Id's

Thanks Bevan.

Interesting points

 

I'm unsure how the RecordId calculations occur.  I suspect it's got a bit of a disconnect between the client and the server.

 

DateTime

There's still the possibility of things going a bit wrong... since you could have multiple clients actioning a remove at the same time, in which case only one can clearly win.  Likewise multiple clients adding something to the queue at the same time brings up the race condition around entry values if using an incrementing value... hence my recommendation to just use a DateTime.  You're more likely to have a unique DateTime than to be able to ensure global consistency on a shared variable. 

 

Thanks,

 

AdamWoodland
Commander Commander
Commander
0 Likes
7
2134

Re: Tables and Record Id's

If you do go down the path of a table, you should probably have a datetime and a unique reference like a GUID per row. Then you can use the datetime as a sort and a GUID as a manipulation.

DavidSkilbeck
Lt. Commander
Lt. Commander
0 Likes
6
2130

Re: Tables and Record Id's

Hi, have I got the syntax wrong to this


SQLStatement = "DELETE TOP (1) FROM XQueue ORDER BY EntryDate ASC"

 

Or is it because I don't have a unique reference like a GUID per row.

 

Thanks,

BevanWeiss
Spock
Spock
0 Likes
5
2115

Re: Tables and Record Id's

If you look at the quite complicated SQL help you'll see that unfortunately your syntax is invalid.

 

You'd need to have something like 

DELETE FROM MyDataTable

WHERE MyGuidGolum =

(

  SELECT TOP(1) MyGuidGolum

  FROM MyDataTable

  ORDER BY EntryDate ASC

)

 

Since you would have already needed to 'peek' the data, you probably should already have a 'cache' of the GUID to reference in the DELETE


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..
BevanWeiss
Spock
Spock
0 Likes
4
2102

Re: Tables and Record Id's

I should probably clarify what I mean in regards to my comment:

I'm unsure how the RecordId calculations occur.  I suspect it's got a bit of a disconnect between the client and the server.

 

I don't mean that the client 'calculates' Record ID separately from the server.  But where a record is removed from the record set, I don't think the client will recalculate new record ids at all.  In which case, the client knows that it has removed the record id in question (say record id 0), but it won't shuffle down all of the other record ids.. since that would likely make it inconsistent with the (authoritative) server version of the table.

When the server gets the update for the record deletion, it would then process that, and send out the updated version to clients after the update.  At that time the client will update its record, and then it might gain a 'new' record id 0 (when the other records 'drop down a slot').

 

This is all hear-say... since I don't know precisely what the code for this looks like in GeoSCADA.. but for a multithreaded environment, it would be quite bad for performance if all client-server interactions had to be synchronised and locking (i.e. if a client thread change to one object prevented all other client access to that object, and all related objects until the server had advised that it had fully processed the change).

More common would be that the client would use an 'offline' or cached version of objects, apply modifications to these, and have a background process to synchronise and update, with locking only performed when absolutely needed for consistency.


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..
DavidSkilbeck
Lt. Commander
Lt. Commander
0 Likes
3
2094

Re: Tables and Record Id's

Hi,

I have tried the below. With no success.

 

'SQLStatement = "DELETE FROM XQueue WHERE EntryDate = (SELECT TOP(1) EntryDate FROM XQueue ORDER BY EntryDate ASC)"

Didn't do anything, just passed through.

 

Then I tried


SQLStatement = "SELECT Top(1) EntryDate FROM XQueue ORDER BY EntryDate ASC"
Set GuideColumn = Server.Query(SQLStatement)


*SQLStatement = "DELETE FROM XQueue WHERE EntryDate =" &GuideColumn &""
Set DeleteRowTable = Server.Query(SQLStatement)

 

* Object does not support this property or method. I have got the end of the statement wrong &"".

But don't know if it would work anyhow?

Thanks,

BevanWeiss
Spock
Spock
0 Likes
2
2083

Re: Tables and Record Id's

If you're using the GUID as the DELETE filter then it will need to be wrapped based on the datatype.

i.e this is invalid DELETE FROM Table WHERE TextItem = cats and dogs

this is also invalid DELETE FROM Table WHERE DateTimeItem = January 20th 2019

 

The error that you got suggests that you've got a typo or something on the "Server.Query" part.

Since if it were strictly a query issue then I would expect a different error on assignment, or on analysis of the assignment.


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..
DavidSkilbeck
Lt. Commander
Lt. Commander
0 Likes
1
2036

Re: Tables and Record Id's

Hi,

SQLStatement = "SELECT Top(1) EntryDate FROM XQueue ORDER BY EntryDate ASC"
Set GuideColumn = Server.Query(SQLStatement)
GuideColumnRows = GuideColumn.Rows
LatestDate = GuideColumnRows(0,0)
LatestDateLocal = (CStr(UTCToLocalTime(LatestDate)))
AMPM = Right(LatestDateLocal, 2)
If AMPM = "PM" Then
DateLeft = Left(LatestDateLocal, 17)
End If
LatestDateLocal = DateLeft + ".000 " + "PM"
SQLStatement = "DELETE FROM xQueue WHERE EntryDate ='"&LatestDateLocal&"'"
Set DeleteRowTable = Server.Query(SQLStatement)


Gave up in the end. Even when dates appeared to match, the row was not deleted. May be it was because I was trying to look up a string in a datetime column.

Thanks,

 

BevanWeiss
Spock
Spock
0 Likes
0
1656

Re: Tables and Record Id's

DateTime constants are tricky (in all SQL server implementations I've used).

I think in ClearSCADA / Geo SCADA you're probably better using the formal { TS '' } version (I think that's the syntax... the help is always your friend of course).

 

I'd try it in QueryPad manually typed in to start with, and then make code which generates strings, breakpoint the code once it has the string, and copy/paste that into QueryPad to make sure it works that way.

Then I can be pretty confident that if the code did the SQL Execute invocation itself it should all work too.


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