Community Tip - Learn all about the Community Ranking System, a fun gamification element of the PTC Community. X
Hi,
I have a problem binding a property of a thing to an sql-dataquery. Sorry for the "unadvanced explanation" in forehand.
What I would like to do:
In General: I have a dataset in an sql database. My things have a property called LineID. I want to get a dataset for the specific LineID, e.g. 5.
What I did:
In the composer, in a Template I wrote a sql code called BottelneckCurrentOrderARRS:
SELECT [LineID]
,[mcName]
,[BCR]
,[CycleTime]
,[ShiftOEE]
FROM OEE_CurrentOrder //This connection is established and works fine
where LineID= [[LineID]]
order by CycleTime desc
This code should invoke data, from a specific Line. Lets say the lineID is 5. The database contains data about several Lines but in this case I am only interested in the attributes of Line5.
In the thing template of the line, the property LineID is stored under Properties->Thingshape.->LineID
In the LineThing itself the service I wrote looks like this:
var params = {
LineID: me.LineID /* INTEGER */
};
var result = Things[me.linedbThing].BottleneckCurrentOrderARRS(params);
The result should be a infotable ( as seen in the picture. But this table is created via mysql). The datashape is created and has the same "values" as the sql is asking for, so
[LineID]
,[mcName]
,[BCR]
,[CycleTime]
,[ShiftOEE].
In my opinion, the javascript asks for the Thingproperty LineID, in this example 5, and then runs the sql code BottleneckCurrentOrderARRS for the specific line 5 ( This is why I wrote the comparison)
If i excecute this service now, I would expect a table like this:
What I get as a result is this error message: Unable to Invoke Service BottleneckCurrentOrder on XXX.XXX.Line.5 : null.
What could be my mistake? Do you have a suggestion to erase the line:
var result = Things[me.linedbThing].BottleneckCurrentOrderARRS(params);
to something easier, that performs the service I want, so run the SQL-code for the specific line?
Thanks a lot!
Solved! Go to Solution.
If you have that comment in there, my first suggestion is to try it without the comment.
Also maybe with quotes vs. brackets like:
SELECT "LineID"
,"mcName"
,"BCR"
,"CycleTime"
,"ShiftOEE"
FROM OEE_CurrentOrder
where LineID = [[LineID]]
order by CycleTime desc
Looks like you are doing everything correctly.
In testing the service manually and putting in the line ID manually it works correctly?
If that works correctly then you are probably not passing in what needs to be passed in
if the manual test of the sql directly doesn't work then something is wrong in your query.
What do you mean by putting the ID manually in? Where can I do this?
My sql query is working in mysql, as shown in the last picture.
Can I write a comparison like this:
where LineID= [[LineID]]
That the code then only invokes data with the specific lineID, which resolved from the following code?
var params = {
LineID: me.LineID /* INTEGER */
};
Thanks!
If you have that comment in there, my first suggestion is to try it without the comment.
Also maybe with quotes vs. brackets like:
SELECT "LineID"
,"mcName"
,"BCR"
,"CycleTime"
,"ShiftOEE"
FROM OEE_CurrentOrder
where LineID = [[LineID]]
order by CycleTime desc