Connection between sql- database and java script, HELP! I am lost.

[robins1306]

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. 

Question 1: Can I write a comparison like i wrote ( where LineID= [[LineID]])) ?

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!

[PaiChung]

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.

[robins1306]

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!

[mgoel]

@PaiChung

Could you please check this if you can reply on this. Thank you in advance.

[robins1306]

@PaiChung @mgoel Thank you for your help! It wasn't a problem in the code but in the connection to the database. So the code itself works like this. Thanks anyway!

[PaiChung · Accepted 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