Hello,
Currently we are looking for dynamically solution which will be work like Calculation engine(Which generate different formulas dynamically )
I this case we divided 2 approch
- Creating simple querries by step 1
- Based on simple queries creating store procedure for individual parameters required for generating final result/Reports.
So step by step user will configure parameters and with required operations & map into sequential manner by store procedure
Then created trigger which will executing store procedure (Parametrs) in defined sequence & generate reports
This is final goal.
While creating store procedure we are passing variables , query & calculation required for generate store procedure
Here's the most reasonable way to do it though I am still wholeheartedly against giving Ignition a user account that can create/drop tables or otherwise is the db_owner/root due to security risk involved (if your ignition server gets owned, then so now is your database immediately).
I tried this on my local sql server and it worked.
Create update named queries like
createTable
CREATE TABLE cm_shiftSchedule (
scheduleID INT IDENTITY(1,1) PRIMARY KEY,
entityID INT NOT NULL,
shiftDay DATE NOT NULL,
pdtCode VARCHAR(10) NOT NULL,
pdt VARCHAR(100),
Duration DECIMAL(16,4) NOT NULL,
createdAt DATETIME DEFAULT GETDATE()
)
and
createSP
CREATE PROCEDURE PlannedDowntime
@Int INT,
@Int1 INT,
@Result DECIMAL(16,4) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Result_TPM DECIMAL(16,4);
SET @Result_TPM = (
SELECT SUM(Duration)
FROM cm_shiftSchedule
WHERE entityID = 13
AND shiftDay = CONVERT(DATE, GETDATE())
AND pdtCode = 'TPM'
AND pdt IS NOT NULL
);
DECLARE @Result_Break DECIMAL(16,4);
SET @Result_Break = (
SELECT SUM(Duration)
FROM cm_shiftSchedule
WHERE entityID = 13
AND shiftDay = CONVERT(DATE, GETDATE())
AND pdtCode = 'BRK'
AND pdt IS NOT NULL
);
DECLARE @Result_WGBT DECIMAL(16,4);
SET @Result_WGBT = (
SELECT SUM(Duration)
FROM cm_shiftSchedule
WHERE entityID = 13
AND shiftDay = CONVERT(DATE, GETDATE())
AND pdtCode = 'WGBT'
AND pdt IS NOT NULL
);
SET @Result = (
ISNULL(@Result_TPM, 0) +
ISNULL(@Result_Break, 0) +
ISNULL(@Result_WGBT, 0)
);
RETURN @Result;
END;
Then you can run
system.db.runNamedQuery("createTable")
system.db.runNamedQuery("createSP")
and it will create your table/stored procedures.
Permissions for DDL are necessary for the tag historian and usually for the SQL Bridge module (unless you pre-create everything yourself).
1 Like
Yea fair enough. Suppose that is necessary for new historian tables. That still happens in the background though and managed by the server unaccessible by the userbase, not in a vision/perspective UI like OP is asking for.
1 Like
Hello @bkarabinchak.psi ,
Can you help this approach in detail How you created name query & passed parameter to generate table or store procedure
Make a query string parameter.
Not a good idea for this. Named queries get screwed up by SQL comments much worse than "Prep" methods. The prep methods pass the entire SQL as a statement to the JDBC driver. NQ calls do parameter munging first.
I already don't tink this entire thing is a good idea so 
I still think the best method is to predefine their stored procedures in a .sql file one time and run that via SSMS.
Having said that I did just test this with query strings and it did create a new table and stored proceudre. I'm sure there are instances where it would fail but just new names of tables and sotred procedures (which seems to be OP's aim) worked for me.
Hi,
For trail purpose i setup your query in name query but it getting procedure keyword
error
Can you please help with that
I think all of your parameters for this will need to be query strings, not value parameters. I think that's what the error is referring to.
1 Like
