Database Interactions

Ignition
1

Full disclosure, I am not a database person.
The built-in alarm table does not seem to have the number of columns I need for my application (18).

I have one database with a table called igalarmdefinition. This database table contains a description of all the alarm information. I have another database called igalarmdisplay. This database contains all the information for any current alarms. When an alarm occurs, the information from igalarmdefinition is to be INSERTED into igalarmdisplay, igalarmdisplay is the dataset for a table. (some of the columns are hidden as I do not have the screen space to display everything on the displayed row).

I have tried the following queries:

#Triggered when a fault tag goes high
if "[.]Fault_ID1" == 1:

#Update the description database to reflect a unique number for IG_Alarm_Index
# '1234' is a test value

#Write the new Alarm Index into the description
UPDATE igalarmdefinition SET IG_Alarm_Index = 1234 WHERE IG_Alarm_ID = 1

#get the alarm information for alarm 1
[default]Alarm_Table_Tags/IG_Alarm_Index.value" WHERE IG_Alarm_ID = 1

#insert the row into igalarmdisplay

INSERT INTO igalarmdisplay (IG_Alarm_Index, IG_Alarm_ID, IG_Alarm_Icon, IG_Alarm_Description, IG_Alarm_Area, IG_Alarm_Device, IG_Alarm_Type, IG_Alarm_Controller, IG_Alarm_Module, IG_Alarm_Set_Time, IG_Alarm_Reset_Time, IG_Alarm_Cleared, IG_Alarm_State, IG_Alarm_Event_Type, IG_Alarm_Event_ID, IG_Alarm_Category, IG_Alarm_Group)

SELECT IG_Alarm_Index, IG_Alarm_ID, IG_Alarm_Icon, IG_Alarm_Description, IG_Alarm_Area, IG_Alarm_Device, IG_Alarm_Type, IG_Alarm_Controller, IG_Alarm_Module, IG_Alarm_Set_Time, IG_Alarm_Reset_Time, IG_Alarm_Cleared, IG_Alarm_State, IG_Alarm_Event_Type, IG_Alarm_Event_ID, IG_Alarm_Category, IG_Alarm_Group from igalarmdefinition where IG_Alarm_ID = 1

#Increment Alarm_Index
Alarm_Index = Alarm_Index + 1

Each line of the query can be triggered by itself. When I attempt to execute all three within the one transaction,

The response is:

GatewayException: SQL error for "UPDATE igalarmdefinition SET IG_Alarm_Index = '2020' WHERE IG_Alarm_ID = 1;

INSERT INTO igalarmdisplay (IG_Alarm_Index_Disp, IG_Alarm_ID_Disp, IG_Alarm_Icon_Disp, IG_Alarm_Description_Disp, IG_Alarm_Area_Disp, IG_Alarm_Device_Disp, IG_Alarm_Type_Disp, IG_Alarm_Controller_Disp, IG_Alarm_Module_Disp, IG_Alarm_Set_Time_Disp, IG_Alarm_Reset_Time_Disp, IG_Alarm_Cleared_Disp, IG_Alarm_State_Disp, IG_Alarm_Event_Type,_Disp IG_Alarm_Event_ID_Disp, IG_Alarm_Category_Disp, IG_Alarm_Group_Disp)

SELECT IG_Alarm_Index_Def, IG_Alarm_ID_Def, IG_Alarm_Icon_Def, IG_Alarm_Description_Def, IG_Alarm_Area_Def, IG_Alarm_Device_Def, IG_Alarm_Type_Def, IG_Alarm_Controller_Def, IG_Alarm_Module_Def, IG_Alarm_Set_Time_Def, IG_Alarm_Reset_Time_Def, IG_Alarm_Cleared_Def, IG_Alarm_State_Def, IG_Alarm_Event_Type_Def, IG_Alarm_Event_ID,_Def IG_Alarm_Category, IG_Alarm_Group_Def from igalarmdefinition where IG_Alarm_ID_Def = 1": You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO igalarmdisplay (IG_Alarm_Index_Disp, IG_Alarm_ID_Disp, IG_Alarm_Icon' at line 2
	caused by SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO igalarmdisplay (IG_Alarm_Index_Disp, IG_Alarm_ID_Disp, IG_Alarm_Icon' at line 2

Ignition v8.0.9 (b2020021812)
Java: Azul Systems, Inc. 11.0.5

I could really use some help from people who are smarter than I.

2

Many JDBC drivers don’t accept multiple statements in one operation. Also, your code isn’t clearly formatted–it’s hard to see the clear breaks between languages. Please use the forum’s code markers (a triple backquote line above and below the pasted code–these: ```) so we can see everything. (You can edit the existing comments.)