# SQL query syntax when using scripting

**URL:** <https://forum.inductiveautomation.com/t/sql-query-syntax-when-using-scripting/9538>\
**Category:** Ignition\
**Created:** [January 13, 2015, 1:58pm UTC](https://forum.inductiveautomation.com/t/sql-query-syntax-when-using-scripting/9538 "2015-01-13T13:58:37Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![TomPedlar](https://avatars.discourse-cdn.com/v4/letter/t/9dc877/32.png) [@TomPedlar](https://forum.inductiveautomation.com/u/TomPedlar)\
**Post date:** [January 13, 2015, 1:58pm UTC](https://forum.inductiveautomation.com/t/sql-query-syntax-when-using-scripting/9538/1 "2015-01-13T13:58:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Chris\_Taylor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/chris_taylor/32/3315_2.png) [@Chris\_Taylor](https://forum.inductiveautomation.com/u/Chris_Taylor)\
**Post date:** [January 13, 2015, 2:13pm UTC](https://forum.inductiveautomation.com/t/sql-query-syntax-when-using-scripting/9538/2 "2015-01-13T14:13:09Z")

</div>

Tom

very quickly, I notice that you have

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

and you don’t use it in the select query

you use

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

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

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

---

<div class="post-metadata">

**Author:** ![Tim](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim/32/3361_2.png) [@Tim](https://forum.inductiveautomation.com/u/Tim)\
**Post date:** [January 13, 2015, 5:05pm UTC](https://forum.inductiveautomation.com/t/sql-query-syntax-when-using-scripting/9538/3 "2015-01-13T17:05:44Z")

</div>

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")
`

---

<div class="post-metadata">

**Author:** ![TomPedlar](https://avatars.discourse-cdn.com/v4/letter/t/9dc877/32.png) [@TomPedlar](https://forum.inductiveautomation.com/u/TomPedlar)\
**Post date:** [January 13, 2015, 8:48pm UTC](https://forum.inductiveautomation.com/t/sql-query-syntax-when-using-scripting/9538/4 "2015-01-13T20:48:45Z")

</div>

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:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e26afb081ddde6df016df6ee49159cf56aaca688.gif ":thumb\_left:")