# How to implement a stored procedure within Ignition?

**URL:** <https://forum.inductiveautomation.com/t/how-to-implement-a-stored-procedure-within-ignition/85720>\
**Category:** Ignition\
**Created:** [February 14, 2024, 10:07pm UTC](https://forum.inductiveautomation.com/t/how-to-implement-a-stored-procedure-within-ignition/85720 "2024-02-14T22:07:27Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![Marcus\_Stanley](https://avatars.discourse-cdn.com/v4/letter/m/a9adbd/32.png) [@Marcus\_Stanley](https://forum.inductiveautomation.com/u/Marcus_Stanley)\
**Post date:** [February 14, 2024, 10:07pm UTC](https://forum.inductiveautomation.com/t/how-to-implement-a-stored-procedure-within-ignition/85720/1 "2024-02-14T22:07:27Z")

</div>

How to implement a stored procedure within Ignition?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 14, 2024, 10:15pm UTC](https://forum.inductiveautomation.com/t/how-to-implement-a-stored-procedure-within-ignition/85720/2 "2024-02-14T22:15:13Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Marcus\_Stanley](https://avatars.discourse-cdn.com/v4/letter/m/a9adbd/32.png) [@Marcus\_Stanley](https://forum.inductiveautomation.com/u/Marcus_Stanley)\
**Post date:** [March 4, 2024, 5:36pm UTC](https://forum.inductiveautomation.com/t/how-to-implement-a-stored-procedure-within-ignition/85720/3 "2024-03-04T17:36:09Z")

</div>

> [@pturmel](#):
>
> 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.

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;

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [March 4, 2024, 5:42pm UTC](https://forum.inductiveautomation.com/t/how-to-implement-a-stored-procedure-within-ignition/85720/4 "2024-03-04T17:42:01Z")

</div>

Marcus, please see [Wiki - how to post code on this forum](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831).

Can you edit your post (pencil icon), select each code block and hit the \</\> to format it correctly.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [March 4, 2024, 5:51pm UTC](https://forum.inductiveautomation.com/t/how-to-implement-a-stored-procedure-within-ignition/85720/5 "2024-03-04T17:51:58Z")

</div>

> [@Marcus\_Stanley](#):
>
> I'm trying to get this stored procedure to work

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.

```auto
exec YourStoredProcedure

```

If you want a better answer than that, then you need to provide some decent background in a question.

---

<div class="post-metadata">

**Author:** ![Marcus\_Stanley](https://avatars.discourse-cdn.com/v4/letter/m/a9adbd/32.png) [@Marcus\_Stanley](https://forum.inductiveautomation.com/u/Marcus_Stanley)\
**Post date:** [March 26, 2024, 2:15pm UTC](https://forum.inductiveautomation.com/t/how-to-implement-a-stored-procedure-within-ignition/85720/6 "2024-03-26T14:15:26Z")

</div>

Newbie to stored procedure in Ignition. sorry Irose. So also, didn't know I unformatted improperly. Thanks, for reply. Something's better than nothing

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [March 26, 2024, 2:17pm UTC](https://forum.inductiveautomation.com/t/how-to-implement-a-stored-procedure-within-ignition/85720/7 "2024-03-26T14:17:44Z")

</div>

There's a pencil icon under your post so you can edit to fix the code formatting. Select the block of code and press \</\>.

---

<div class="post-metadata">

**Author:** ![Marcus\_Stanley](https://avatars.discourse-cdn.com/v4/letter/m/a9adbd/32.png) [@Marcus\_Stanley](https://forum.inductiveautomation.com/u/Marcus_Stanley)\
**Post date:** [March 26, 2024, 2:19pm UTC](https://forum.inductiveautomation.com/t/how-to-implement-a-stored-procedure-within-ignition/85720/8 "2024-03-26T14:19:03Z")

</div>

> [@pturmel](#):
>
> Ignition doesn't have any mechanism to declare stored procedures in SQL, though most JDBC drivers will let you invoke DDL for that purpose.

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [March 26, 2024, 2:20pm UTC](https://forum.inductiveautomation.com/t/how-to-implement-a-stored-procedure-within-ignition/85720/9 "2024-03-26T14:20:15Z")

</div>

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. 🤷‍♂️

---

<div class="post-metadata">

**Author:** ![Marcus\_Stanley](https://avatars.discourse-cdn.com/v4/letter/m/a9adbd/32.png) [@Marcus\_Stanley](https://forum.inductiveautomation.com/u/Marcus_Stanley)\
**Post date:** [March 26, 2024, 2:23pm UTC](https://forum.inductiveautomation.com/t/how-to-implement-a-stored-procedure-within-ignition/85720/10 "2024-03-26T14:23:03Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Marcus\_Stanley](https://avatars.discourse-cdn.com/v4/letter/m/a9adbd/32.png) [@Marcus\_Stanley](https://forum.inductiveautomation.com/u/Marcus_Stanley)\
**Post date:** [March 26, 2024, 2:23pm UTC](https://forum.inductiveautomation.com/t/how-to-implement-a-stored-procedure-within-ignition/85720/11 "2024-03-26T14:23:48Z")

</div>

thanks for the info you did provide. i appreciate it

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [March 26, 2024, 2:24pm UTC](https://forum.inductiveautomation.com/t/how-to-implement-a-stored-procedure-within-ignition/85720/12 "2024-03-26T14:24:39Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Marcus\_Stanley](https://avatars.discourse-cdn.com/v4/letter/m/a9adbd/32.png) [@Marcus\_Stanley](https://forum.inductiveautomation.com/u/Marcus_Stanley)\
**Post date:** [March 26, 2024, 2:29pm UTC](https://forum.inductiveautomation.com/t/how-to-implement-a-stored-procedure-within-ignition/85720/13 "2024-03-26T14:29:01Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [March 26, 2024, 2:38pm UTC](https://forum.inductiveautomation.com/t/how-to-implement-a-stored-procedure-within-ignition/85720/14 "2024-03-26T14:38:13Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Marcus\_Stanley](https://avatars.discourse-cdn.com/v4/letter/m/a9adbd/32.png) [@Marcus\_Stanley](https://forum.inductiveautomation.com/u/Marcus_Stanley)\
**Post date:** [March 26, 2024, 2:47pm UTC](https://forum.inductiveautomation.com/t/how-to-implement-a-stored-procedure-within-ignition/85720/15 "2024-03-26T14:47:45Z")

</div>

when you say fix formatting do you mean for python

---

<div class="post-metadata">

**Author:** ![Marcus\_Stanley](https://avatars.discourse-cdn.com/v4/letter/m/a9adbd/32.png) [@Marcus\_Stanley](https://forum.inductiveautomation.com/u/Marcus_Stanley)\
**Post date:** [March 26, 2024, 2:48pm UTC](https://forum.inductiveautomation.com/t/how-to-implement-a-stored-procedure-within-ignition/85720/16 "2024-03-26T14:48:30Z")

</div>

Thanks pturmel

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [March 26, 2024, 2:51pm UTC](https://forum.inductiveautomation.com/t/how-to-implement-a-stored-procedure-within-ignition/85720/17 "2024-03-26T14:51:58Z")

</div>

> [@Marcus\_Stanley](#):
>
> do you mean for python

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?

---

<div class="post-metadata">

**Author:** ![Marcus\_Stanley](https://avatars.discourse-cdn.com/v4/letter/m/a9adbd/32.png) [@Marcus\_Stanley](https://forum.inductiveautomation.com/u/Marcus_Stanley)\
**Post date:** [March 28, 2024, 2:46pm UTC](https://forum.inductiveautomation.com/t/how-to-implement-a-stored-procedure-within-ignition/85720/18 "2024-03-28T14:46:44Z")

</div>

yes but newbie. easy to say do it but didn't know how to do it.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [March 28, 2024, 2:57pm UTC](https://forum.inductiveautomation.com/t/how-to-implement-a-stored-procedure-within-ignition/85720/19 "2024-03-28T14:57:51Z")

</div>

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.