Query property path

Ignition
1

INSERT INTO Notes (NoteText, Username, Timestamp, Attachment, AttachmentName, Sticky, equipID) VALUES (?, ‘%s’, CURRENT_TIMESTAMP, CAST(? AS VARBINARY(MAX)), ?, ?, {Root Container.MotorID})

This is query 1 of a comments panel. I would like to use a custom property of the root of one of my popup windows coerced as a string inside of this query editor.

It looks like the Username string is liinked underneath the component and referenced with WhoID, so, can I do this with the MotorID property?

Thanks in advance

2

Since the Insert Query 1 property just sends a string off to the DB, the DB won’t know what the {Root Container.MotorID} property is and error out. If you bind the Insert Query 1 property to a concat(“string1”,“string2”) expression, it will return the proper string with the value for MotorID inserted in:

concat("INSERT INTO notes (Note, WhoID, TStamp, Attachment, Filename, Sticky, Motor) VALUES (?, ‘%s’, CURRENT_TIMESTAMP, ?, ?, ?, ", {Root Container.MotorID}, “)”)

3

When I do this, I get this error

GatewayException: SQL error for "concat("INSERT INTO Notes (NoteText, Username, Timestamp, Attachment, AttachmentName, Sticky, equipID) VALUES (?, ‘admin’, CURRENT_TIMESTAMP, CAST(? AS VARBINARY(MAX)), ?, ?, “, {Root Container.MotorID} “)”)”: The index 1 is out of range.
caused by SQLServerException: The index 1 is out of range.

4

Are you sure you properly bound “Insert Query 1” to the following concat() expression?

concat("INSERT INTO Notes (NoteText, Username, Timestamp, Attachment, AttachmentName, Sticky, equipID) VALUES (?, '%s', CURRENT_TIMESTAMP, CAST(? AS VARBINARY(MAX)), ?, ?, ", {Root Container.MotorID}, ")")

Putting this in the expression section of the binding types will return a string to the “Insert Query 1” property, and the string should look like this:

INSERT INTO Notes (NoteText, Username, Timestamp, Attachment, AttachmentName, Sticky, equipID) VALUES (?, '%s', CURRENT_TIMESTAMP, CAST(? AS VARBINARY(MAX)), ?, ?, 2)

This will be the query passed to the database (with whatever your value for MotorID is in your project instead of “2” at the end). The error message you received was because you were passing the first string with concat(…), when you should be passing the returned value of the concat expression from the binding.

5

index 1 is out of range. I’ve tried modifying this and using it exactly how you gave this to me, I cant get it to work.

It also does not appear to matter whether or not MotorID has whitespace or not.

my data query looks like this:
SELECT ID, Username, Timestamp, NoteText, AttachmentName, Sticky, equipID
FROM dbo.Notes
WHERE equipID = {Root Container.MotorID}

6

The index 1 out of range means that the number of values being inserted doesn’t match the number of parameters, are you sure you have an equal number of column names and parameters in your INSERT statement? Should be 7 of each.

7

looks like I have 7 of each here.
It looks like my data binding query works properly… also, my custom property is being passed correctly into the database as root container.motorid… for some reason this will not work for the insert query yet.
It will work if I put quotes around my path and it will tag the string {root container.motorid} directly into the database…
Has anyone gotten this to work with a custom property of the root container?

8

Start with the basics, use the database browser and try to run a test Insert statement.

i.e.

INSERT INTO Notes (NoteText, Username, Timestamp, Attachment, AttachmentName, Sticky, equipID) VALUES ('testNoteText', 'testUsername', NOW(), CAST(? AS VARBINARY(MAX)), 'testAttachmentName', 'testSticky', 1234)I’m not sure what type of data the Attachment is suppose to be.

If your putting quotes around {root container.motorid} and the INSERT statement is putting the string value {root container.motorid} in the database then it sounds like the database column is a string column instead of an Int or Double

9

my equipID column is a VARCHAR(50)

It seems like I can pass strings into the column, but how do I assign the Root Container.MotorID String into a variable inside of this query and pass it?
I played with binding on query1 to a query type and use WHERE something = ‘{Root Container.MotorID}’
which is how I link in with my data Query if you look closely at that above.

10

To get the value for Root Container.MotorID to appear in your query, you have to bind to an expression, not a SQL query. Binding a property to a SQL query will return the resulting dataset from the query into that property's value, which will produce unexpected results in this case.

The reason binding to an expression is needed is merely to translate the {Root Container.MotorID} into it's actual value, and then form the query as a string, which is what the property value for "Insert Query 1" is supposed to be. That property merely shoots off the string to the DB, which is why you can't put {Root Container.MotorID} directly in the string, the DB has no idea what that means (other than a literal string interpretation). Ignition needs to handle the variable, and that is done through the expression binding in this case.

11

It seems like the expression binding was the problem, I understand this now but, I am getting a new error…Cannot insert…Syntax near 100 error…
My MotorID is two words with whitespace… Could this be the problem?
How can I make sure the ID can be written into SQL? I think I’m very close now.

12

I took the space out now and I get Invalid column name ‘MOTOR_100’
I even changed the equipID column from varchar(50) to varchar(max), no effect.

This is my query1 string now:
INSERT INTO Notes (NoteText, Username, Timestamp, Attachment, AttachmentName, Sticky, equipID) VALUES (?, ‘%s’, CURRENT_TIMESTAMP, CAST(? AS VARBINARY(MAX)), ?, ?, MOTOR_166)

and my data bind query looks like:
SELECT ID, Username, Timestamp, NoteText, AttachmentName, Sticky
FROM dbo.Notes
WHERE equipID = ‘{Root Container.MotorID}’

13

My original expression for the concat() only worked with integers, so I modified it for your case since it looks like you have MotorID as a string, right? Change your Insert Query 1 to bind to this expression:

concat("INSERT INTO Notes (NoteText, Username, Timestamp, Attachment, AttachmentName, Sticky, equipID) VALUES (?, '%s', CURRENT_TIMESTAMP, CAST(? AS VARBINARY(MAX)), ?, ?, '", {Root Container.MotorID}, "')")

The only change I made was to put a single quote before and after the variable reference to MotorID, so that when it creates the string, the query string will properly pass that value as a string with single quotes. The query string will now look like this:

INSERT INTO Notes (NoteText, Username, Timestamp, Attachment, AttachmentName, Sticky, equipID) VALUES (?, '%s', CURRENT_TIMESTAMP, CAST(? AS VARBINARY(MAX)), ?, ?, 'MOTOR_166')

Your data bind query looks fine.

14

Thanks James, I knew it must be something like that and it works great now.