SQL Syntax Error Help?

Ignition
1

I’m getting an error message when I’m running my code to update a table in my SQL db.

The Code is:

[code]string = event.source.parent.getComponent(‘Group’).getComponent(‘Text Field’).text
string2 = event.source.parent.getComponent(‘Group’).getComponent(‘Text Field 1’).text
string3 = event.source.parent.getComponent(‘Group’).getComponent(‘Text Field 3’).text
string4 = event.source.parent.getComponent(‘Group’).getComponent(‘Text Field 2’).text
string5 = event.source.parent.getComponent(‘Group’).getComponent(‘Text Area’).text
number = event.source.parent.getComponent(‘Group’).getComponent(‘Numeric Text Field’).intValue
table = event.source.parent.getComponent(‘Table’)
tableName = “workorders”
db = “DB”

Create the insert query

insertQuery = “”“INSERT INTO [%s] ([string], [string2], [string3], [string4], [string5], [number])
VALUES (’%s’, ‘%s’, ‘%s’, ‘%s’, ‘%s’, %d)”"" % (tableName, string, string2, string3, string4, string5, number)

Insert the record in the database table

system.db.runUpdateQuery(insertQuery,db)

Refresh the table data

system.db.refresh(table, “data”)[/code]

The error message is:

Traceback (most recent call last):

File “event:actionPerformed”, line 16, in 

         VALUES ('Test', '8/26/2015', 'SQL Update', 'Office Work', 'Syntax Error', 55), DB, , false)

java.lang.Exception: java.lang.Exception: Error executing system.db.runUpdateQuery(INSERT INTO [workorders] ([string], [string2], [string3], [string4], [string5], [number])

         VALUES ('Test', '8/26/2015', 'SQL Update', 'Office Work', 'Syntax Error', 55), DB, , false)


caused by Exception: Error executing system.db.runUpdateQuery(INSERT INTO [workorders] ([string], [string2], [string3], [string4], [string5], [number]) 
         VALUES ('Test', '8/26/2015', 'SQL Update', 'Office Work', 'Syntax Error', 55), DB, , false)
caused by GatewayException: SQL error for "INSERT INTO [workorders] ([string], [string2], [string3], [string4], [string5], [number]) 
         VALUES ('Test', '8/26/2015', 'SQL Update', 'Office Work', 'Syntax Error', 55)": You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[workorders] ([string], [string2], [string3], [string4], [string5], [number])

’ at line 1
caused by MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[workorders] ([string], [string2], [string3], [string4], [string5], [number])
’ at line 1

Ignition v7.7.5 (b2015071516)
Java: Oracle Corporation 1.8.0_51

Any thoughts?

2

Edit: Nevermind, I misread the code you had posted. Disregard what I posted before editing.

Are those column names correct? Are the columns in your table named string, string2, string3 etc.?

3

No, those are not the names of the columns. Should they be?

4

Yes they should.

Here is a good reference for using INSERT INTO statements: w3schools.com - The SQL INSERT INTO Statement

5

Thank you so much! That is a very useful resource to have! It did help me with my major issue, but now it is saying that i have a ValueError and I’m not sure why?

Traceback (most recent call last):

File “event:actionPerformed”, line 13, in

ValueError: unsupported format character ‘’’ (0x27) at index 167

I’m not sure why this keeps happening and on line 13, I don’t even have a ’ character :/ I have put the code below, just to reference it

[code]string = event.source.parent.getComponent(‘Group’).getComponent(‘Text Field’).text
string2 = event.source.parent.getComponent(‘Group’).getComponent(‘Text Field 1’).text
string3 = event.source.parent.getComponent(‘Group’).getComponent(‘Text Field 3’).text
string4 = event.source.parent.getComponent(‘Group’).getComponent(‘Text Field 2’).text
string5 = event.source.parent.getComponent(‘Group’).getComponent(‘Text Area’).text
number = event.source.parent.getComponent(‘Group’).getComponent(‘Numeric Text Field’).intValue
string6 = event.source.parent.getComponent(‘Text Field 3’).text
table = event.source.parent.getComponent(‘Table’)
tableName = “workorders”
db = “DB”

Create the insert query

insertQuery = “”“INSERT INTO [%s] ([clientname], [dateofwork], [problem], [resolution], [sitelocation], [progress], [time spent])
VALUES (’%s’, ‘%s’, ‘%s’, ‘%s’, ‘%s’, ‘%’, %d)”"" % (tableName, string, string2, string3, string4, string5, number, string6)

Insert the record in the database table

system.db.runUpdateQuery(insertQuery,db)

Refresh the table data

system.db.refresh(table, “data”)[/code]

6

It’s because of an error in the string formatting symbols.

#This:
VALUES ('%s', '%s', '%s', '%s', '%s', '%', %d)

# should be this:
VALUES ('%s', '%s', '%s', '%s', '%s', '%s', %d)

Notice the missing “s” at the 6th argument in parenthesis.

You will still have an error if you change that to %s. Your string formatting and supplied variables list do not match up.

In your string you are telling Jython you will have 7 strings to insert followed by a integer but the values you are supplying are 6 strings followed by an int followed by another string.

Assuming that all of your columns are of type VARCHAR except for [time spent] being an INT you would need to change to this

insertQuery = """INSERT INTO [%s] ([clientname], [dateofwork], [problem], [resolution], [sitelocation], [progress], [time spent]) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', %d)""" % (tableName, string, string2, string3, string4, string5, string6, number)

One thing that would help is using better variable names for your inputs. It’s easy to get confused with variable names like string, string2 etc. For example, it will be easier to spot mismatches if the text input for the client name is assigned to a varable called “clientName” instead of “string”.

I think you got those from somebody here who was posting psuedo code for you and didn’t name the variables anything meaningful because they didn’t know what your data looked like. They were just for use as an example.

7

Yes! Thank you for the suggestion about better names, that definitely helped! The only error I have now is that it is saying that I don’t have enough arguments for my format string. I tried taking one of the ‘%s’ out to see if that would fix it, but it just had a different error. I renamed my variables and changed them accordingly in the script you posted.

Thanks for all the help so far! This has been my first experience working with SQL coding ever, not just specifically in ignition, so I’m learning a ton!

8

Oops! I see that in my example I have an extra %s in the format string. If you take one of those out it should work.

9

Yeah, I noticed that too, but it keeps giving me different errors when I change that. I’ve had an error saying it needed and integer value, so i changed the order to make sure the integer was in the correct spot, and then i had an error saying that I had a SQL Syntax error again -_-

At this point, I may just call Ignition and see if we can’t pinpoint the issue with a tech guy. It’s going to be a simple fix I’m sure, but it’s really frustrating me at this point.

10

Try this:

# Create the insert query
insertQuery = """
			INSERT INTO %s ([clientname], [dateofwork], [problem], [resolution], [sitelocation], [progress], [time spent]) 
			VALUES (?,?,?,?,?,?,?)
			""" % (tableName)
# Insert the record in the database table
system.db.runPrepUpdate(insertQuery,[string, string2, string3, string4, string5, number, string6],db)
11

[quote=“alewayhar”]Yeah, I noticed that too, but it keeps giving me different errors when I change that. I’ve had an error saying it needed and integer value, so i changed the order to make sure the integer was in the correct spot, and then i had an error saying that I had a SQL Syntax error again -_-

At this point, I may just call Ignition and see if we can’t pinpoint the issue with a tech guy. It’s going to be a simple fix I’m sure, but it’s really frustrating me at this point.[/quote]

Trying to get both the SQl and the Igntion scripting right is likely confusing the matter.

Can you just work on the SQL insert in your MySQL environment and get that to work to make sure you have a good understanding of how the insert statement works?

Once you have that down you can focus on how to make it work through scripting.

jpark’s method using system.db.runPrepUpdate is my preferred method of doing an insert. system.db.runPrepUpdate will prevent SQL injection exploits.

12

If you’re only inserting one row of data, I prefer the alternative format for INSERT statements:insertQuery = """ INSERT INTO %s SET clientname=?, dateofwork=?, problem=?, resolution=?, sitelocation=?, progress=?, time_spent=? """ % tablename system.db.runPrepUpdate(insertQuery, [string, string2, string3, string4, string5, number, string6], db) At least you’re less likely to have the wrong number of question marks!