I am trying to import a ttx file in to a sql table on change of file.
I am getting the following error
File "<module:Importing.File>", line 47, in CheckFileLanded Priority, WO, WC, Material, Description, Plant, StartTime_Planned, EndTime_Planned, Labour, Printed, Batch ) VALUES(?,?,?,?,?,?,?,?,?,?,?), [0, 105538664, 1, 35429-00, TOWERLATCH S.P., GBM1, 10/12/2022, 10/17/2022, 18.333, X, 112901], IGNITION_SQL_SERVER, , false, false) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268) at jdk.internal.reflect.GeneratedMethodAccessor292.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO [dbo].[mes_work_orders_DEM2]( Priority, WO, WC, Material, Description, Plant, StartTime_Planned, EndTime_Planned, Labour, Printed, Batch ) VALUES(?,?,?,?,?,?,?,?,?,?,?), [0, 105538664, 1, 35429-00, TOWERLATCH S.P., GBM1, 10/12/2022, 10/17/2022, 18.333, X, 112901], IGNITION_SQL_SERVER, , false, false)
Traceback (most recent call last):
File "<TimerScript:BERLIN_QDDC/Importing/File @100,000ms >", line 1, in <module>
File "<module:Importing.File>", line 47, in CheckFileLanded
Priority,
WO,
WC,
Material,
Description,
Plant,
StartTime_Planned,
EndTime_Planned,
Labour,
Printed,
Batch
) VALUES(?,?,?,?,?,?,?,?,?,?,?), [0, 105538664, 1, 35429-00, TOWERLATCH S.P., GBM1, 10/12/2022, 10/17/2022, 18.333, X, 112901], IGNITION_SQL_SERVER, , false, false)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)
at jdk.internal.reflect.GeneratedMethodAccessor292.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.base/java.lang.reflect.Method.invoke(Unknown Source)
java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO [dbo].[mes_work_orders_DEM2](
Priority,
WO,
WC,
Material,
Description,
Plant,
StartTime_Planned,
EndTime_Planned,
Labour,
Printed,
Batch
) VALUES(?,?,?,?,?,?,?,?,?,?,?), [0, 105538664, 1, 35429-00, TOWERLATCH S.P., GBM1, 10/12/2022, 10/17/2022, 18.333, X, 112901], IGNITION_SQL_SERVER, , false, false)
8.1.17 (b2022051210)
Azul Systems, Inc. 11.0.15`
`
Please see the gateway script here
type or paste code here
```import shutil, os, time
from datetime import datetime
fileINP = 'DEM2_01.txt'
baseDIR = '\FTP\\PowerBI\\ZMKBR02A\\'
fileDST = 'Processed_DEM2\\'
tagLast = '[MES_SCADA]Parameters/DED2_LAST_IMPORT_ORDER'
def CheckFileLanded():
firstRow = True
sql = 'SELECT COUNT(*) FROM mes_work_orders_DEM2_hist WHERE WO = ?'
current = os.path.getmtime(baseDIR + fileINP)
i = 0
if datetime.fromtimestamp(current).strftime('%Y-%m-%d %H:%M:%S') == system.tag.readBlocking(tagLast)[0].value:
print('No New File')
return
system.tag.writeBlocking([tagLast], [datetime.fromtimestamp(current).strftime('%Y-%m-%d %H:%M:%S')])
system.util.getLogger('Import (WO_dem2)').info('Import Started at: {}'.format(system.date.format(system.date.now(),'yyyy-MM-dd HH:mm:ss')))
with open(baseDIR + fileINP,'r') as f:
for line in f:
i += 1
cols = [x.strip() for x in line.replace('\n','').split('\t')]
if firstRow:
firstRow = False
system.db.runUpdateQuery("DELETE FROM [dbo].[mes_work_orders_DEM2] WHERE [Status] = 'Import'")
colPriority = cols.index('Priority')
colMRP = cols.index('MRP')
colWC = cols.index('Sched.')
colWO = cols.index('Order Numb')
colPlant = cols.index('Plnt')
colMat = cols.index('Material')
colDesc = cols.index('Description')
colLabour = cols.index('Labor')
colStart = cols.index('Sched. Sta')
colFinish = cols.index('Sched. Fin')
colPrinted = cols.index('Printed')
colBatch = cols.index('Batch')
else:
if system.db.runScalarPrepQuery(sql, [cols[colWO]]) == 0:
system.db.runPrepUpdate("""INSERT INTO [dbo].[mes_work_orders_DEM2](
Priority,
WO,
WC,
Material,
Description,
Plant,
StartTime_Planned,
EndTime_Planned,
Labour,
Printed,
Batch
) VALUES(?,?,?,?,?,?,?,?,?,?,?)""",
[
cols[colPriority],
cols[colWO],
cols[colWC],
cols[colMat],
cols[colDesc],
cols[colPlant],
cols[colStart],
cols[colFinish],
cols[colLabour].replace(',',''),
cols[colPrinted],
cols[colBatch],
])
f.close
system.util.getLogger('Import (WO_dem2)').info('Records ({}) imported at: {}'.format(i,system.date.format(system.date.now(),'yyyy-MM-dd HH:mm:ss')))
shutil.copy(baseDIR + fileINP, baseDIR + fileDST + datetime.fromtimestamp(current).strftime('%Y%m%d%H%M%S_') + fileINP)
type or paste code here
Not sure why it's not working....
any ideas?