cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Want the oppurtunity to discuss enhancements to PTC products? Join a working group! X

SQL Results missing in Service

jbaute
4-Participant

SQL Results missing in Service

Newb and first time poster.

 

I am creating a service that calls a stored procedure in SQL Server*. It uses the JDBC connector. The results I see when testing the service don't match what is returned when executed on the SQL Server. Some numbers are completely missing, presumably because of their format? If I change the query to return random integers in the Eff column, then I can get results back. It doesn't seem to matter if I bind my output to a data shape or just to text.

 

Thanks in advance for any tips.

 

* it doesn't matter if I am calling a stored proc or running the code in the service. Same result.

tw1.png

 
15 REPLIES 15
aregal01
5-Regular Member
(To:jbaute)

Hello,

 

Could you share some further details to help us to troubleshoot with you?  

  1. What version of ThingWorx are you using?
  2. What version of SQL are you using?
  3. Would you share the code of the Stored Proc/service that you are executing?

 

Thanks,

Angela 

 

jbaute
4-Participant
(To:aregal01)

Thanks for the reply.

  1. What version of ThingWorx are you using?
    8.5
  2. What version of SQL are you using?
    SQL 2017
  3. Would you share the code of the Stored Proc/service that you are executing?
    Code follows, sanitized, of course. It just calls some data from a linked server, then does a bit of math and returns results.

<code>

Create Procedure LineEff AS
SET NOCOUNT ON
--production efficiency

Declare @StartDate datetime, @EndDate datetime
Declare @NumShifts int=7
Set @EndDate=getdate()
Select @StartDate=DATEADD(hh,-1*8*@NumShifts,@EndDate)

Select Top 1 @StartDate=convert(datetime,StartDate) from Shifts where StartDate < @StartDate order by StartDate desc

--Select @StartDate, @EndDate

Declare @Counters table(ID int identity(1,1), Name varchar(50), TS datetime, Value int)
Declare @Down table(ID int identity(1,1), Name varchar(50), TS datetime, Value int)

Declare @s varchar(max)
Set @s= 'SELECT NAME, TS, VALUE FROM HISTORY WHERE NAME IN (''xxxxxxxxxx',''xxxxxxxxxx'',''xxxxxxxxxx'') AND TS BETWEEN '''
+ FORMAT( @StartDate, 'dd-MMM-yy HH:mm', 'en-US' ) + ''' AND ''' + FORMAT( @EndDate, 'dd-MMM-yy HH:mm', 'en-US' ) +''' AND PERIOD=''00:01:00'''

Insert @Counters(Name, TS, Value)
EXECUTE(@s) AT IP21

Declare @q varchar(max)
Set @q= 'SELECT NAME, TS, VALUE FROM HISTORY WHERE NAME IN (''xxxxxxxxxxx'') AND TS BETWEEN '''
+ FORMAT( @StartDate, 'dd-MMM-yy HH:mm', 'en-US' ) + ''' AND ''' + FORMAT( @EndDate, 'dd-MMM-yy HH:mm', 'en-US' ) +''' AND PERIOD=''00:01:00'''

Insert @Down(Name, TS, Value)
EXECUTE(@q) AT IP21


--get a table of the last N shifts
Declare @Results table(ID int identity(1,1),StartDate datetime, EndDate datetime, ShiftID int, Crew varchar(5), TotalBags int,BPH float, Eff float)

Insert @Results(StartDate, EndDate, ShiftID)
Select top (@NumShifts) StartDate, EndDate ,ShiftID from Shifts where convert(datetime,StartDate) < @EndDate
order by StartDate desc

--for each shift, compute metrics
Declare @i int=1

Declare @TotalBags int, @RunMins int, @ShiftEnd datetime, @ShiftNum int, @CountTagName varchar(50), @ShiftStart datetime, @DownMins int, @TotalMins int, @LineEff float

While @i <= (Select max(ID) from @Results)
BEGIN
--get total bags for shift
--value of counter at shift end time
Select @ShiftStart=StartDate, @ShiftEnd = EndDate, @ShiftNum=ShiftID from @Results where ID=@i
If @ShiftNum=1
Set @CountTagName='xxxxxxxxxx'
If @ShiftNum=2
Set @CountTagName='xxxxxxxxxx'
If @ShiftNum=3
Set @CountTagName='xxxxxxxxxx'

If @i=1
BEGIN
Select top 1 @TotalBags=Value from @Counters where Name=@CountTagName order by TS desc
END
ELSE
BEGIN

Select TOP 1 @TotalBags=Value from @Counters where TS=@ShiftEnd and Name=@CountTagName

END

--get the total down minutes, calculate effeciency
If @i=1
BEGIN
Select @TotalMins=datediff(mi,@ShiftStart,getdate())
Select @DownMins=sum(Value) from @Down where TS between @ShiftStart and getdate()
Set @RunMins=@TotalMins-@DownMins
Set @LineEff=100.*@RunMins/@TotalMins

END
ELSE
BEGIN
Select @TotalMins=datediff(mi,@ShiftStart,@ShiftEnd)
Select @DownMins=sum(Value) from @Down where TS between @ShiftStart and @ShiftEnd
Set @RunMins=@TotalMins-@DownMins
Set @LineEff=100.*@RunMins/@TotalMins
END

If @LineEff <0
Set @LineEff=0


Update @Results set Totalbags=@TotalBags, BPH=@TotalBags*1./DATEDIFF(hh,@ShiftStart,@ShiftEnd), Eff=@LineEff where ID=@i

set @i=@i+1
END

 

Select ID, StartDate, EndDate,ShiftID, Totalbags, BPH, Eff as Eff,
Case when ShiftID=1 then 'A' when ShiftID=2 then 'B' when ShiftID=3 then 'C' end as Crew
from @Results

</ code>

 

aregal01
5-Regular Member
(To:jbaute)

Hi JBaute,

 

Thank you for your quick response.  Another question: Are you running this SQL code/Stored Proc as a SQL Query or SQL Command in ThingWorx?  (you want to run it as a query, screenshot attached)

 

 
jbaute
4-Participant
(To:aregal01)

I am ruining it as a SQL query.

aregal01
5-Regular Member
(To:jbaute)

Hello,

 

Thank you for your response.  This is unexpected behavior but we should be able to determine what might be causing it and/or resolve the issue.  SQL Server 2017 is indeed supported in ThingWorx 8.5 so that should not be our issue.  

 

Can you tell me what type/format you're using for the BPH and Eff columns in the SQL Server table?

 

Thanks,

Angela 

jbaute
4-Participant
(To:aregal01)

They are both floats.

jbaute
4-Participant
(To:jbaute)

Same result when I change to decimal(18,2)

aregal01
5-Regular Member
(To:jbaute)

Thank you.  Would you be able to share with me a screenshot of exactly how you're invoking the stored procedure in your ThingWorx instance?  I would like to see how you've wrapped the call and what your input/outputs look like.  Also, would you mind sharing the details of the datashape you're assigning to the result?  

 

 

jbaute
4-Participant
(To:aregal01)

I've tried two ways of executing the sql - using the code above, pasted in directly to the service code window, as well as 

 

Exec <stored proc>

 

SAME Result

 

I also get the same result using an Infotable data shape or TEXT in the output. However, attached is a screenshot of the datashape.

tw2.png

aregal01
5-Regular Member
(To:jbaute)

Thank you for the response/screenshot.  On the surface it seems that you have things structured correctly and are invoking properly.  It is possible that ThingWorx is sanitizing the SQL Query text as it's ingested from the stored procedure.  I'm going to do some further research on this topic and I'll get back to you with my findings.  Thank you for your patience while we work through this issue together.

 

Sincerely,

Angela 

aregal01
5-Regular Member
(To:aregal01)

Hello,

 

I've come across a tactic we can use which may bypass any potential ThingWorx sanitation of the sql code.  It's important to note that this could open your application to sql injection if not properly securedYou'll need to wrap the call so that it's executed by the System User and not a typical end user.  All told you'll have 2 services:

  • Service A will execute the query as a string param
  • Service B will be a wrapper service which will simply call Service A
  1. Create Service A on the Relational Database thing
    1. Single String Input
    2. Body of service is << service param >>
      1. You will paste your entire sql query as the service param
    3. Result is Infotable of Datashape you were using previously
    4. Do NOT allow End Users to execute this service - set security appropriately
  2. Create Service B on the Relational Database thing
    1. No Inputs
    2. Body of service is a simple call to "me.ServiceA"
      1. Using the built in snippets to call a service on the entity will pre-format your required inputs/outputs simplifying the process
    3. Result is Infotable of Datashape you were using previously
    4. Per your use case, allow appropriate End Users to execute the service  
  3. Save the Services as well as the Relational Database Thing
  4. Test Service B
    1. Result should be the same (info)table you receive when you execute directly on SQL Server.

Please let me know if this approach resolves your issue and/or if you have any questions.  

 

Sincerely,

Angela 

jbaute
4-Participant
(To:aregal01)

Thanks much for the response, Angela. I will try this solution shortly.

 

However, this seems like a lot of hoops to jump through to get the results of a SQL query. Can I expect to have to do this for every database call I make? I would think a mature product such as ThnigWorx should be able to handle numeric results from a SQL query.

 

Thanks.

aregal01
5-Regular Member
(To:jbaute)

Hello,

 

You make a valid point, this is most definitely a work around, and a long winded one at that.  Typically the sanitizing of SQL query text performed by ThingWorx does not yield any performance issues and a functional JDBC connector will perform as expected.  However in rare instances we've seen unexpected results and have identified the sanitization process as the cause.  It's a known issue and is on R&D's radar.  

 

Given that your query returned unexpected results when run as a stored procedure, I'm not 100% confident that text sanitization is the actual issue, but since we know it causes the occasional unexpected outcome it's helpful to rule it out.  

 

On another note, did you have any issues with your JDBC connector at install?  I'm wondering if something went wonky with one of your files during install.  

 

Please do let me know if the proposed work around solves your issue.  

 

Sincerely,

Angela 

jbaute
4-Participant
(To:aregal01)

I created a wrapper service with the code below. The results are the same as in the original service.

 

//create result set info table
var params = {
infoTableName : "InfoTable",
dataShapeName : "TblEff"
};

// CreateInfoTableFromDataShape(infoTableName:STRING("InfoTable"), dataShapeName:STRING):INFOTABLE(zz.LineOperationData)
var result = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(params);

var SQLData=me.Eff();
var tableLength = SQLData.rows.length;
for (var x=0; x < tableLength; x++) {
var row = SQLData.rows[x];
var NewRow = {};
NewRow.ID = row.ID;
NewRow.StartDate = row.StartDate;
NewRow.EndDate = row.EndDate;
NewRow.ShiftID = row.ShiftID;
NewRow.Crew = row.Crew;
NewRow.Eff = row.Eff;
NewRow.BPH = row.BPH;

result.AddRow(NewRow);
}

aregal01
5-Regular Member
(To:jbaute)

Thank you for attempting the proposed solution.  I'm sorry that it didn't resolve your issue. 

 

It seems that you will require more in-depth investigation and support to determine the cause of your data discrepancy/unexpected behavior.  You can open a support ticket at: https://www.ptc.com/en/support/ .  The link to open a new support case is found mid-page.

11-22-2019 4-06-46 PM.jpg

 

Best of luck in resolving your issue.  It would be great if you could return to this thread and post the final resolution.

 

Sincerely,

Angela 

Top Tags