How to write a SQL DML UPDATE query to update an array of reference field

[Experimentalist]

Hi

This one has me baffled, how do you write an SQL DML UPDATE query to update a field that is an array of references?

For instance to add a user to multiple user groups. Say I have 3 groups (CDBUserGroup) with IDs of 8159, 8160 & 8161 how can I update a user with ID of 16479 to add the groups to their UserGroupIds:

UPDATE

    "CDBUser"

SET

    "UserGroupIds" = ???

WHERE

    "Id" = 16479

I can't figure out how to specify the array, I've tried:

"UserGroupIds" = (8159, 8160, 8161)

"UserGroupIds" = [8159, 8160, 8161]

"UserGroupIds" = 8159, 8160, 8161

"UserGroupIds" = Values(8159, 8160, 8161)

Stumped, any ideas?

[geoffpatton]

Probably the same way as the Bulk Edit tool.

It's worth a try.

The Bulk Edit Tool sets it by the user Group's Full Name

Setting Multiple in it is entered with comma's and no spaces.

Like this Security.User Groups.My Group1,Security.User Groups.My Group2

[Experimentalist]

It turns out that it is not possible to set array of reference fields from a DML SQL query. It can only be achieved through code. I have now written a Powershell script to do the job. Once I've polished it I will probably publish it here for the benefit of others with a similar task. I've got the job done but it is functional but not yet suitable for publishing. 

[sbeadle · Accepted solution]

Code in C# to do this:

			ClearScada.Client.Simple.Connection SimpleConnection;
			ClearScada.Client.ServerNode node = new ClearScada.Client.ServerNode("127.0.0.1", 5481);
			SimpleConnection = new ClearScada.Client.Simple.Connection("Utility");
			SimpleConnection.Connect(node);
			var secure = new SecureString();
			// *** Modify to set your password and user name
			var user = "";
			var pass = "";
			foreach (var c in pass)
			{
				secure.AppendChar(c);
			}
			SimpleConnection.LogOn(user, secure);
			var userObject = SimpleConnection.GetObject("Demo Items.GEO Demo.Mobile.Darren");
			var userGroupIds = userObject.GetProperty("UserGroupIds");
			// You could read the old Ids and append, for example
			var newGroup = SimpleConnection.GetObject("Demo Items.ExtAuth.LDAP.Groups.External WWW");
			object [] gids = { newGroup.Id };
			userObject.SetProperty("UserGroupIds", gids);

Or in Python with Pythonnet (I'm using Python 3.12)

# Import .Net runtime support - needs "pip install pythonnet" Works on Python 3.8 & 3.12
import clr
# Get Geo SCADA Library (could use the namespace if the dll is on PATH)
CS = clr.AddReference( "c:\\Program Files\\Schneider Electric\\ClearSCADA\\ClearSCADA.Client.dll" )
import ClearScada.Client as CSClient 

# Create node and connect, then log in. (Could read net parameters from SYSTEMS.XML)
node = CSClient.ServerNode( CSClient.ConnectionType.Standard, "127.0.0.1", 5481 )
connection = CSClient.Simple.Connection( "Utility" )
u = ""
p = ""
connection.Connect( node )
connection.LogOn( u, p )

userObject = connection.GetObject("Demo Items.GEO Demo.Mobile.Darren" )
userGroupIds = userObject.GetProperty("UserGroupIds")
from System import Object, Array, Byte
for i in userGroupIds:
    gid = CSClient.ObjectId( i)
    g = connection.GetObject( gid)
    print(i, g.FullName)
newGroup = connection.GetObject("Demo Items.ExtAuth.LDAP.Groups.Test1")
newGroupIds = Array.CreateInstance( Object, 1)
newGroupIds.SetValue(newGroup.Id, 0)
userObject.SetProperty( "UserGroupIds", newGroupIds )