I currently have several SQL queries I am executing with a mariadb database stored locally on the same machine running ignition.
They now want to use an external oracle database along with stored procedures that I will call/execute within Ignition. I can’t seem to find any direct information on how to call Oracle procedures and how to pass in parameters for the queries. Also I have most of these queries within project scripts, but I also have one query as a named query, and then two other queries in a report as data sources, and not sure I can call them the same way within each instance.
Below are a couple of the queries in a script:
txId = system.db.beginTransaction(timeout=10000)
# Create furnace cycle record
sql = """INSERT INTO rpt_furnace_cycles (PartID, OperationID, Alloy, Username, FurnaceBTNum, r_LagTime, r_ReqTotNegTime, r_ReqTotPosTime,
r_ReqProcessTime1, r_ReqProcessTemp1, r_ReqTempTol1, r_ReqProcessTime2, r_ReqProcessTemp2, r_ReqTempTol2, r_ReqProcessTime3,
r_ReqProcessTemp3, r_ReqTempTol3) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"""
tags = []
for tag in ['part_number', 'operation_number', 'alloy', 'username']:
tags.append('[BladesideHT]BS/FurnaceBT{}/Recipe/Lots/{}'.format(furnace_brass_tag_num, tag))
for tag in ['LagTime', 'ReqTotNegTime', 'ReqTotPosTime', 'ReqProcessTime1', 'ReqProcessTemp1', 'ReqTempTol1', 'ReqProcessTime2',
'ReqProcessTemp2', 'ReqTempTol2', 'ReqProcessTime3', 'ReqProcessTemp3', 'ReqTempTol3']:
tags.append('[BladesideHT]BS/FurnaceBT{}/Recipe/Setpoints/in/{}'.format(furnace_brass_tag_num, tag))
params = [tag.value for tag in system.tag.readAll(tags)]
params.insert(4, furnace_brass_tag_num)
furnace_cycle_id = system.db.runPrepUpdate(sql, params, tx=txId, getKey=1)
system.tag.write('[BladesideHT]BS/FurnaceBT{}/Recipe/Lots/furnace_cycle_id'.format(furnace_brass_tag_num), furnace_cycle_id)
# Get number of jobs and create records for each
number_of_jobs = system.tag.read('[BladesideHT]BS/FurnaceBT{}/Recipe/Control/lot_number_index'.format(furnace_brass_tag_num)).value
for i in range(0, number_of_jobs):
# Job table record
sql = 'INSERT INTO rpt_jobs VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE ProcessNum=VALUES(ProcessNum), HeatCode=VALUES(HeatCode), NumParts=VALUES(NumParts)'
tags = []
for tag in ['job_number', 'process_number', 'heat_code', 'number_of_parts']:
tags.append('[BladesideHT]BS/FurnaceBT{}/Recipe/Lots/{}[{}]'.format(furnace_brass_tag_num, tag, i))
params = [tag.value for tag in system.tag.readAll(tags)]
system.db.runPrepUpdate(sql, params, tx=txId)
job_number = params[0]
# Mapping table record
sql = 'INSERT INTO rpt_jobs_furnace_cycles VALUES (?, ?)'
params = [job_number, furnace_cycle_id]
system.db.runPrepUpdate(sql, params, tx=txId)
Here is a query within the reports data source:
SELECT rpt_jobs.JobID, rpt_jobs.ProcessNum, rpt_jobs.HeatCode, rpt_jobs.NumParts
FROM rpt_jobs
JOIN rpt_jobs_furnace_cycles ON rpt_jobs.JobID=rpt_jobs_furnace_cycles.rpt_jobs_JobID
WHERE rpt_jobs_furnace_cycles.rpt_furnace_cycles_id={FurnaceCycleID}
and here is the named query using one parameter ‘job_id’ as an input parameter:
SELECT rpt_furnace_cycles.id, rpt_furnace_cycles.FurnaceBTNum, rpt_furnace_cycles.e_RecipeDownloaded_ts AS cycle_start,
rpt_furnace_cycles.e_DoorOpenedUnload_ts AS cycle_end
FROM rpt_jobs_furnace_cycles
JOIN rpt_furnace_cycles ON rpt_jobs_furnace_cycles.rpt_furnace_cycles_id=rpt_furnace_cycles.id
WHERE rpt_jobs_furnace_cycles.rpt_jobs_JobID=:job_id
Any general or specific help would be greatly appreciated!