How to implement a stored procedure within Ignition?
Ignition has Named Queries, which holds a JDBC statement that can be referenced in various places in Ignition.
To perform a series of related statements, you'd use multiple calls in a jython script to the various system.db.* functions, possibly using the transaction wrapping functions.
Some JDBC drivers permit full SQL scripts instead of single SQL statements in database calls, which may suit your purposes.
Ignition doesn't have any mechanism to declare stored procedures in SQL, though most JDBC drivers will let you invoke DDL for that purpose.
What are you really trying to do?
I'm trying to get this stored procedure to work: CREATE TABLE Operator (
operator_id INT PRIMARY KEY,
total_produced_parts INT,
total_failed_parts INT
);
-- Table for the TL_Supervisor class
CREATE TABLE TL_Supervisor (
supervisor_id INT PRIMARY KEY,
total_operators_monitored INT
);
-- Table for the Management class
CREATE TABLE Management (
management_id INT PRIMARY KEY,
total_supervisors INT
);
-- Table for the PacingMechanism class
CREATE TABLE PacingMechanism (
pacing_mechanism_id INT PRIMARY KEY,
dynamic_pacing_activated BIT,
static_pacing_activated BIT,
cycle_information_id INT,
report_card_id INT,
warning_message_id INT,
lights_controller_id INT,
FOREIGN KEY (cycle_information_id) REFERENCES CycleInformation(cycle_information_id),
FOREIGN KEY (report_card_id) REFERENCES ReportCard(report_card_id),
FOREIGN KEY (warning_message_id) REFERENCES WarningMessage(warning_message_id),
FOREIGN KEY (lights_controller_id) REFERENCES LightsController(lights_controller_id)
);
-- Table for the CycleInformation class
CREATE TABLE CycleInformation (
cycle_information_id INT PRIMARY KEY,
cycle_time INT
);
-- Table for the ReportCard class
CREATE TABLE ReportCard (
report_card_id INT PRIMARY KEY
-- Add additional fields as needed
);
-- Table for the WarningMessage class
CREATE TABLE WarningMessage (
warning_message_id INT PRIMARY KEY
-- Add additional fields as needed
);
-- Table for the LightsController class
CREATE TABLE LightsController (
lights_controller_id INT PRIMARY KEY,
status VARCHAR(20) -- 'Green', 'Red', 'Yellow', etc.
);
-- Stored procedure to update the LightsController status based on cycle time
CREATE PROCEDURE usp_UpdateLightsControllerStatus
@pacingMechanismId INT
AS
BEGIN
DECLARE @cycleTime INT;
DECLARE @elapsedTime INT;
-- Get cycle time from CycleInformation table
SELECT @cycleTime = cycle_time
FROM CycleInformation
WHERE cycle_information_id = (
SELECT cycle_information_id
FROM PacingMechanism
WHERE pacing_mechanism_id = @pacingMechanismId
);
-- Calculate elapsed time for the last cycle
-- For simplicity, let's assume you have a CycleLog table with start_time and end_time columns
SELECT TOP 1 @elapsedTime = DATEDIFF(SECOND, start_time, end_time)
FROM CycleLog
WHERE pacing_mechanism_id = @pacingMechanismId
ORDER BY end_time DESC;
-- Update LightsController status
IF @elapsedTime <= 120
UPDATE LightsController SET status = 'Green';
ELSE
UPDATE LightsController SET status = 'Red';
END;
-- Trigger to automatically update LightsController status after each cycle
CREATE TRIGGER trg_AfterCycleCompletion
ON CycleLog
AFTER INSERT
AS
BEGIN
DECLARE @pacingMechanismId INT;
SELECT @pacingMechanismId = pacing_mechanism_id
FROM inserted;
EXEC usp_UpdateLightsControllerStatus @pacingMechanismId;
END;
-- Example of how to use the stored procedure and trigger in your application logic
-- You would call usp_UpdateLightsControllerStatus after each cycle completion
-- The trigger will automatically update LightsController status after each cycle log insertion
-- You need to adapt the cycle start and end time tracking based on your application logic
-- To manually update LightsController status (for testing purposes, etc.)
UPDATE LightsController
SET status = 'Green'
WHERE lights_controller_id = 1; -- Adjust the WHERE clause based on your LightsController record
-- To check the LightsController status
SELECT * FROM LightsController;
-- Ignition security
CREATE LOGIN operator_user WITH PASSWORD = 'strong_password';
CREATE LOGIN supervisor_user WITH PASSWORD = 'strong_password';
CREATE LOGIN management_user WITH PASSWORD = 'strong_password';
USE Ignition; -- Replace with your actual database name
CREATE USER operator_user FOR LOGIN operator_user;
CREATE USER supervisor_user FOR LOGIN supervisor_user;
CREATE USER management_user FOR LOGIN management_user;
GRANT SELECT, INSERT ON PacingMechanism TO operator_user;
GRANT SELECT, INSERT, .UPDATE, DELETE ON PacingMechanism TO supervisor_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON PacingMechanism, LightsController TO management_user;
-- Procedure to check performance
CREATE PROCEDURE usp_CheckPerformance
@operatorId INT
AS
BEGIN
-- Implementation of the procedure
END;
-- Grant Execute Permission
GRANT EXECUTE ON PROCEDURE usp_CheckPerformance TO operator_user;
-- Example of TDE
-- SQL Server Transparent Data Encryption (TDE) is managed at the database level, not per table
-- Enable audit for the PacingMechanism table
CREATE TABLE PacingMechanism_audit
(
-- Audit table columns
);
ALTER TABLE PacingMechanism
ADD CONSTRAINT fk_PacingMechanism_audit FOREIGN KEY (pacing_mechanism_id)
REFERENCES PacingMechanism_audit (pacing_mechanism_id);
-- Example of parameterized query in a stored procedure
CREATE PROCEDURE usp_GetCycleInformation
@cycleId INT
AS
BEGIN
SELECT * FROM CycleInformation WHERE cycle_information_id = @cycleId;
END;
Marcus, please see Wiki - how to post code on this forum.
Can you edit your post (pencil icon), select each code block and hit the </> to format it correctly.
That's a huge block of unformatted code, and I'm not going to weed through it.
You didn't provide any information about your system, what you have tried, or what isn't working.
The simplest way to implement a stored procedure in Ignition is to write a Named Query.
exec YourStoredProcedure
If you want a better answer than that, then you need to provide some decent background in a question.
4 Likes
Newbie to stored procedure in Ignition. sorry Irose. So also, didn't know I unformatted improperly. Thanks, for reply. Something's better than nothing
There's a pencil icon under your post so you can edit to fix the code formatting. Select the block of code and press </>.
Ignition doesn't have any mechanism to declare stored procedures in SQL, though most JDBC drivers will let you invoke DDL for that purpose. Can you explain a little more on the topic of invoking DDL.
When commenters here make suggestions to make your question better, and/or fill in the gaps in our understanding, and you don't follow through, that's pretty much the end of the conversation. 
sorry pturmel. In manufacturing, plant managers have a million one projects and they think its "I dream of Jeanie" just blink and you can create a program that works automatically. Sorry, I got pulled away on plant emergency then had to now jump back to this
thanks for the info you did provide. i appreciate it
DDL is "Data Definition Language", typically CREATE ... or ALTER ... as described in your database-specific documentation.
DML is "Data Manipulation Language", the normal SELECT ..., UPDATE ..., INSERT ... and DELETE ... operations.
JDBC is very standardized for DML. It generally accepts DDL, but not quite so standardized.
JDBC explicitly supports single statements. Creating a stored procedure is a single statement, but has embedded statements that have to be passed undisturbed to the database. How that works varies. In many cases, normal script quoting that you'd use in a branded management interface has to be replaced with custom quoting.
how to create a graphic cycle timer like a square clock that will count backwards and keep track of the last 10 cycles in Ignition. I created a stored procedure to be able to give access to supervisors and managers. Don't know if this was the correct way to do this in ignition.
You still haven't fixed the formatting on that block of code you posted, so no-one will have specifics.
But fundamentally, the only thing the database needs to do is record the finish timestamps of each cycle in a table. A typical developer would use an external tool to run the DDL to get the table the way you want it, though the Ignition designer does have a tool for running arbitrary SQL statements.
You would then use an Ignition tag change script to record the cycle events based on an OPC tag pointing at a suitable signal in your machine PLC.
You would use a query against that table to retrieve the last ten rows, regularly, in a user interface, to produce a display. That display would typically compare the current time against the last timestamp and a forecast cycle length to give you a forecast countdown.
(This is unlikely to be handed to you on a platter. Start doing, and where you hit roadblocks, ask for specific help.)
Whatever else you do, make sure you go through all of IA's free online training. That will answer all the simple questions.
2 Likes
when you say fix formatting do you mean for python
Thanks pturmel
Not just python, but any code you post on this forum. It needs to be put in a "Preformatted text" block so that indentation and various other aspects don't get corrupted in the forum display. Also do this for error reports and tracebacks. Did you not read the instructions linked in Transistor's comment above?
3 Likes
yes but newbie. easy to say do it but didn't know how to do it.
In post #3 I gave you a link to "How to post code" and how to edit your post. That was 24 days ago. It's still not fixed.
2 Likes