Tables and Record Id's

[DavidSkilbeck]

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,

[DavidSkilbeck]

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,

[BevanWeiss · Accepted solution]

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. 

[DavidSkilbeck]

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]

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]

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]

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

[BevanWeiss]

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.

[DavidSkilbeck]

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]

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.

[DavidSkilbeck]

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]

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.