SQL query syntax when using scripting

Ignition
1

Hi,
I’m trying to export data from an SQL database to an excel spread sheet. Using posts on the forum this is working OK, the problem is when I try to select a data range using the “Where” command I keep getting syntax errors!
My database table has 7 colums and I am selecting the data using the colum LogName and a Dropdown List called Location with a returned value in “where” (as per demo project)

Code
Loc = event.source.parent.getComponent(‘Location’).where

Filename = “Report.xls”

results = system.db.runQuery("SELECT RejectData_UpperThreshold, RejectData_SystemNoise, RejectData_Trigger, RejectDateTime, RejectData_LowerThreshold, RejectData_LeakSize,LogName FROM report WHERE (event.source.parent.getComponent(‘Location’).where) ORDER BY LogName ASC ",“NewConnection”)

result = system.dataset.toDataSet(results)

HeaderRow = [0,1,2,3,4,5,6]

Heading = system.dataset.addRow(result,0,HeaderRow)

spreadsheet = system.dataset.dataSetToExcel(1, [Heading])

filePath = “C:\output\” + Filename

system.file.writeFile(filePath, spreadsheet)

system.net.openURL(“C:\output\” + Filename)[/color]

The error is in line 3 results= …

Any help would be appreciated as this is driving me mad

Regards Tom

2

Tom

very quickly, I notice that you have

Loc = event.source.parent.getComponent('Location').where

and you don’t use it in the select query

you use

event.source.parent.getComponent('Location').where

which is going to cause trouble as SQL has no idea what that is :slight_smile:

if thats not it, what exactly are the errors? are they SQL syntax errors

3

Try

results = system.db.runQuery("SELECT RejectData_UpperThreshold, RejectData_SystemNoise, RejectData_Trigger, RejectDateTime, RejectData_LowerThreshold, RejectData_LeakSize,LogName FROM report WHERE LogName = '%s' ORDER BY LogName ASC " %(Loc),"NewConnection")

4

Hi Thanks for the quick replies

Tim
I had only to remove “LogName=” and the code worked a treat

Working Query

results = system.db.runQuery("SELECT RejectData_UpperThreshold, RejectData_SystemNoise, RejectData_Trigger, RejectDateTime, RejectData_LowerThreshold, RejectData_LeakSize,LogName FROM report WHERE %s ORDER BY LogName ASC " %(Loc),“NewConnection”)

Thanks Guys :thumb_left: