Community Tip - Your Friends List is a way to easily have access to the community members that you interact with the most! X
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.
Hello,
Could you share some further details to help us to troubleshoot with you?
Thanks,
Angela
Thanks for the reply.
<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>
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)
I am ruining it as a SQL query.
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
They are both floats.
Same result when I change to decimal(18,2)
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?
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.
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
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 secured. You'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:
Please let me know if this approach resolves your issue and/or if you have any questions.
Sincerely,
Angela
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.
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
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);
}
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.
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