So, first time trying to execute an SP. Wondering if I think this is right:
I create a project, in the Gateway Event Scripts, set a scheduled script - based on whatever occurrence I want it to execute at, then the script would look like this:
system.db.execSProcCall(dbo.UpsertPlt65Users)
?
No; you first need to create the stored procedure call object, then execute it. Take a look at the examples:
https://docs.inductiveautomation.com/display/DOC81/system.db.createSProcCall
Not even close.
The documentation for system.db.execSProcCall clearly states that its one argument must come from a call to system.db.createSProcCall.
That latter page has examples for you to examine.
something like this:
call = system.db.createSProcCall("dbo.UpsertPlt65Users", [IgnitionHud])
system.db.execSProcCall(call)
The table created from the SP is already existing in the DB, is that an issue?
Honestly, just avoid the headache* and call it directly either from a Named Query, or PrepQuery. Particularly if you have many parameters that will need to be registered.
*If your flavor of DB's JDBC driver happens to be somewhat "agnostic" to SQL scripts. If not you may have issues not using the SProcCall family of functions.
What is [IgntionHud] ? The second argument to createSProcCall() must be a data source name, not a list of parameters.
Parameters have to be registered one by one. As shown in the examples. (!!!!)
Alright,
system.db.createSProcCall(procedureName, [database], [tx], [skipAudit])
my procedureName is dbo.UpsertPlt65Users,
my [database] is IgnitionHud, that just what our DB name is in SQL
call = sysetem.db.createSProcCall('dbo.UpsertPlt65Users','IgnitionHud')
system.db.execSProcCall(call)
Assuming that the DataSource is also named 'IgnitionHud'.
In this instance that is the name given to the Database Connection when it was added to the gateway. It can be completely different from the DB name in SQL.
The square brackets in the documentation indicate that the parameter is optional. So technically the default data source would be used if you left the parameter out. I recommend always suppling the data source name.
You can find the actual data source name in the gateway at Config>Database>Database Connections
This depends on the Stored Procedure and what it is doing.
OK, so it seems, I needed single quotes instead of double quotes.
The SP, according to contents should add and remove users if they are considered "active"
Thanks everyone for their help!
No, you needed either kind of quotes around the string database name, instead of square brackets.
Yes sir, and that also.