# Scripting Issue trying to input a bunch of rows of data into SQL

**URL:** <https://forum.inductiveautomation.com/t/scripting-issue-trying-to-input-a-bunch-of-rows-of-data-into-sql/25741>\
**Category:** Ignition\
**Created:** [June 11, 2019, 7:08pm UTC](https://forum.inductiveautomation.com/t/scripting-issue-trying-to-input-a-bunch-of-rows-of-data-into-sql/25741 "2019-06-11T19:08:16Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![dholt](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dholt/32/4458_2.png) [@dholt](https://forum.inductiveautomation.com/u/dholt)\
**Post date:** [June 11, 2019, 7:08pm UTC](https://forum.inductiveautomation.com/t/scripting-issue-trying-to-input-a-bunch-of-rows-of-data-into-sql/25741/1 "2019-06-11T19:08:16Z")

</div>

Hello. We currently have a process that we're wanting to move into Ignition where we register our part numbers for any new numbers that come through to my company. It's not uncommon for there to be 40 new part numbers issued to a sales order at one time. I'm designing a window with multiple text boxes for part numbers, customer part numbers, descriptions and other relevant data. The part number, customer part number, description 1 and description 2 are unique to each part number and the rest of the data is redundant as it applies to a single customer. I've made a window and wrote this script to a button and it's failing:

> q1 = event.source.parent.getComponent('q1').text  
> q2 = event.source.parent.getComponent('q2').text  
> q3 = event.source.parent.getComponent('q3').text  
> q4 = event.source.parent.getComponent('q4').text  
> q5 = event.source.parent.getComponent('q5').text  
> q6 = event.source.parent.getComponent('q6').text  
> q7 = event.source.parent.getComponent('q7').text  
> q8 = event.source.parent.getComponent('q8').text  
> q9 = event.source.parent.getComponent('q9').text  
> q10 = event.source.parent.getComponent('q10').text
> 
> c1 = event.source.parent.getComponent('c1').text  
> c2 = event.source.parent.getComponent('c2').text  
> c3 = event.source.parent.getComponent('c3').text  
> c4 = event.source.parent.getComponent('c4').text  
> c5 = event.source.parent.getComponent('c5').text  
> c6 = event.source.parent.getComponent('c6').text  
> c7 = event.source.parent.getComponent('c7').text  
> c8 = event.source.parent.getComponent('c8').text  
> c9 = event.source.parent.getComponent('c9').text  
> c10 = event.source.parent.getComponent('c10').text
> 
> da1 = event.source.parent.getComponent('da1').text  
> da2 = event.source.parent.getComponent('da2').text  
> da3 = event.source.parent.getComponent('da3').text  
> da4 = event.source.parent.getComponent('da4').text  
> da5 = event.source.parent.getComponent('da5').text  
> da6 = event.source.parent.getComponent('da6').text  
> da7 = event.source.parent.getComponent('da7').text  
> da8 = event.source.parent.getComponent('da8').text  
> da9 = event.source.parent.getComponent('da9').text  
> da10 = event.source.parent.getComponent('da10').text
> 
> db1 = event.source.parent.getComponent('db1').text  
> db2 = event.source.parent.getComponent('db2').text  
> db3 = event.source.parent.getComponent('db3').text  
> db4 = event.source.parent.getComponent('db4').text  
> db5 = event.source.parent.getComponent('db5').text  
> db6 = event.source.parent.getComponent('db6').text  
> db7 = event.source.parent.getComponent('db7').text  
> db8 = event.source.parent.getComponent('db8').text  
> db9 = event.source.parent.getComponent('db9').text  
> db10 = event.source.parent.getComponent('db10').text
> 
> um =event.source.parent.getComponent('um').text  
> pl =event.source.parent.getComponent('pl').selectedStringValue  
> pro =event.source.parent.getComponent('prg').selectedStringValue  
> it =event.source.parent.getComponent('it').selectedStringValue  
> st =event.source.parent.getComponent('sta').selectedStringValue  
> gr =event.source.parent.getComponent('grp').selectedStringValue  
> rc =event.source.parent.getComponent('rtg').selectedStringValue  
> abc =event.source.parent.getComponent('abc').selectedStringValue  
> si = '1000'  
> loc =event.source.parent.getComponent('loc').selectedStringValue  
> pm =event.source.parent.getComponent('pm').selectedStringValue  
> comm = event.source.parent.getComponent('cmt').text  
> un =[System]Client/User/Username.Value  
> system.db.runPrepUpdate("INSERT INTO Part\_registration (QAD\_pn,Customer\_pn,Desc1,Desc2,UM,Pline,Program,Itype,Status,Group,Rcode,ABC,Site,Location,PM,Comments,Username)  
> VALUES  
> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),  
> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),  
> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),  
> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),  
> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),  
> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),  
> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),  
> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),  
> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),  
> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)  
> ",[  
> q1,c1,da1,db1,um,pl,pro,it,st,gr,rc,abc,si,loc,pm,comm,un,  
> q2,c2,da2,db2,um,pl,pro,it,st,gr,rc,abc,si,loc,pm,comm,un,  
> q3,c3,da3,db3,um,pl,pro,it,st,gr,rc,abc,si,loc,pm,comm,un,  
> q4,c4,da4,db4,um,pl,pro,it,st,gr,rc,abc,si,loc,pm,comm,un,  
> q5,c5,da5,db5,um,pl,pro,it,st,gr,rc,abc,si,loc,pm,comm,un,  
> q6,c6,da6,db6,um,pl,pro,it,st,gr,rc,abc,si,loc,pm,comm,un,  
> q7,c7,da7,db7,um,pl,pro,it,st,gr,rc,abc,si,loc,pm,comm,un,  
> q8,c8,da8,db8,um,pl,pro,it,st,gr,rc,abc,si,loc,pm,comm,un,  
> q9,c9,da9,db9,um,pl,pro,it,st,gr,rc,abc,si,loc,pm,comm,un,  
> q10,c10,da10,db10,um,pl,pro,it,st,gr,rc,abc,si,loc,pm,comm,un  
> ])

This is the Error message I get when I try to apply it:

> Parse error for event handler "mouseClicked" SyntaxError: ("no viable alternative at character '?'", (' ', 60, 1, '(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),\n'))

I have no idea where it's getting the 60 and the 1...and I'm lost. I realize it's ugly but it's the best way I could figure out to do it. If anyone has a suggestion on a script fix, or a better way to do it I'm all ears. Any help would be greatly appreciated! Thank you!

---

<div class="post-metadata">

**Author:** ![dholt](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dholt/32/4458_2.png) [@dholt](https://forum.inductiveautomation.com/u/dholt)\
**Post date:** [June 11, 2019, 7:45pm UTC](https://forum.inductiveautomation.com/t/scripting-issue-trying-to-input-a-bunch-of-rows-of-data-into-sql/25741/3 "2019-06-11T19:45:29Z")

</div>

I don’t know if this helps but this is what my designer shows:

[Image](https://imgur.com/GM4QHKu)

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 11, 2019, 7:46pm UTC](https://forum.inductiveautomation.com/t/scripting-issue-trying-to-input-a-bunch-of-rows-of-data-into-sql/25741/4 "2019-06-11T19:46:20Z")

</div>

Use a multiline string (triple single or double quotes) to format your SQL properly - assuming you copied and pasted into the forum verbatim, the issue is the dangling line breaks in your query.

```python
    system.db.runPrepUpdate("""INSERT INTO Part_registration (QAD_pn,Customer_pn,Desc1,Desc2,UM,Pline,Program,Itype,Status,Group,Rcode,ABC,Site,Location,PM,Comments,Username)
    VALUES
    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),
    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),
    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),
    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),
    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),
    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),
    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),
    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),
    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),
    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    """,[
    q1,c1,da1,db1,um,pl,pro,it,st,gr,rc,abc,si,loc,pm,comm,un,
    q2,c2,da2,db2,um,pl,pro,it,st,gr,rc,abc,si,loc,pm,comm,un,
    q3,c3,da3,db3,um,pl,pro,it,st,gr,rc,abc,si,loc,pm,comm,un,
    q4,c4,da4,db4,um,pl,pro,it,st,gr,rc,abc,si,loc,pm,comm,un,
    q5,c5,da5,db5,um,pl,pro,it,st,gr,rc,abc,si,loc,pm,comm,un,
    q6,c6,da6,db6,um,pl,pro,it,st,gr,rc,abc,si,loc,pm,comm,un,
    q7,c7,da7,db7,um,pl,pro,it,st,gr,rc,abc,si,loc,pm,comm,un,
    q8,c8,da8,db8,um,pl,pro,it,st,gr,rc,abc,si,loc,pm,comm,un,
    q9,c9,da9,db9,um,pl,pro,it,st,gr,rc,abc,si,loc,pm,comm,un,
    q10,c10,da10,db10,um,pl,pro,it,st,gr,rc,abc,si,loc,pm,comm,un
    ])

```

---

<div class="post-metadata">

**Author:** ![dholt](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dholt/32/4458_2.png) [@dholt](https://forum.inductiveautomation.com/u/dholt)\
**Post date:** [June 11, 2019, 7:47pm UTC](https://forum.inductiveautomation.com/t/scripting-issue-trying-to-input-a-bunch-of-rows-of-data-into-sql/25741/5 "2019-06-11T19:47:16Z")

</div>

It is verbatim, so should I move the bracket down to the values level? Sorry I’m very rough on scripting.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 11, 2019, 7:48pm UTC](https://forum.inductiveautomation.com/t/scripting-issue-trying-to-input-a-bunch-of-rows-of-data-into-sql/25741/6 "2019-06-11T19:48:07Z")

</div>

Sorry - I edited my post; the issue’s not the square bracket but just the multiline nature of your query. It should work fine as long as you switch to `"""` instead of `"` around the entire query.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 11, 2019, 7:50pm UTC](https://forum.inductiveautomation.com/t/scripting-issue-trying-to-input-a-bunch-of-rows-of-data-into-sql/25741/7 "2019-06-11T19:50:31Z")

</div>

Oh, you also need to read the system tag containing the username, not attempt to reference it directly:  
`un = system.tag.read("[System]Client/User/Username.Value").value`

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 11, 2019, 7:58pm UTC](https://forum.inductiveautomation.com/t/scripting-issue-trying-to-input-a-bunch-of-rows-of-data-into-sql/25741/8 "2019-06-11T19:58:27Z")

</div>

You can eliminate a lot of the duplicate using some python concepts like list comprehensions:  
(disclaimer: untested)

```python
from itertools import chain

qs = [event.source.parent.getComponent("q" + i) for i in range(1, 11)]
cs = [event.source.parent.getComponent("c" + i) for i in range(1, 11)]
das = [event.source.parent.getComponent("da" + i) for i in range(1, 11)]
dbs = [event.source.parent.getComponent("db" + i) for i in range(1, 11)]

um = event.source.parent.getComponent("um").text
pl = event.source.parent.getComponent("pl").selectedStringValue
pro = event.source.parent.getComponent("prg").selectedStringValue
it = event.source.parent.getComponent("it").selectedStringValue
st = event.source.parent.getComponent("sta").selectedStringValue
gr = event.source.parent.getComponent("grp").selectedStringValue
rc = event.source.parent.getComponent("rtg").selectedStringValue
abc = event.source.parent.getComponent("abc").selectedStringValue
si = "1000"
loc = event.source.parent.getComponent("loc").selectedStringValue
pm = event.source.parent.getComponent("pm").selectedStringValue
comm = event.source.parent.getComponent("cmt").text
un = system.tag.read("[System]Client/User/Username.Value").value

query = """
INSERT INTO Part_registration
    (QAD_pn,Customer_pn,Desc1,Desc2,UM,Pline,Program,Itype,Status,Group,Rcode,ABC,Site,Location,PM,Comments,Username)
VALUES
    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),
    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),
    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),
    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),
    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),
    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),
    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),
    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),
    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),
    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
"""

ordered_values = [
    [qs[i], cs[i], das[i], dbs[i], um, pl, pro, it, st, gr, rc, abc, si, loc, pm, comm, un ] for i in range(10)
]

values = chain(*ordered_values)
# alternate: values = sum(ordered_values, [])

system.db.runPrepUpdate(query, values)

```

---

<div class="post-metadata">

**Author:** ![dholt](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dholt/32/4458_2.png) [@dholt](https://forum.inductiveautomation.com/u/dholt)\
**Post date:** [June 12, 2019, 11:26am UTC](https://forum.inductiveautomation.com/t/scripting-issue-trying-to-input-a-bunch-of-rows-of-data-into-sql/25741/9 "2019-06-12T11:26:34Z")

</div>

This got me thank you very much for your help!

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [October 25, 2019, 5:17pm UTC](https://forum.inductiveautomation.com/t/scripting-issue-trying-to-input-a-bunch-of-rows-of-data-into-sql/25741/10 "2019-10-25T17:17:51Z")

</div>

A post was split to a new topic: [Script Timing Questions](https://forum.inductiveautomation.com/t/script-timing-questions/29400)