Skip to main content
R
robins13061-Visitor
1-Visitor
October 12, 2018
Solved

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

  • October 12, 2018
  • 2 replies
  • 2809 views

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:Table from SQL database.PNG

 

 

 

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!

Best answer by PaiChung

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

2 replies

P
PaiChung22-Sapphire I
22-Sapphire I
October 12, 2018

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.

    R
    robins13061-VisitorAuthor
    1-Visitor
    October 22, 2018

    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!

    M
    mgoel5-Regular Member
    5-Regular Member
    October 30, 2018

    @PaiChung

     

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

      P
      PaiChung22-Sapphire IAnswer
      22-Sapphire I
      October 30, 2018

      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

        Terms & ConditionsAccessibility statement