Community Tip - You can subscribe to a forum, label or individual post and receive email notifications when someone posts a new topic or reply. Learn more! X
Hi all,
I face a problem in stream and need your help.
I have a database in local,and i have to put about a million rows data from database into stream of thingworx, so I create a query service in a database thing,as we know the query service can only query 500 rows at a time,so I create a service to store data to stream: in this stream,I store one row in one milliseconds to the stream,and for four times to store 2000 rows.
However,after store to stream,I find only about 1000 rows store to stream.I can't find the reason.Please find the attachments.
This is my sql code:
select * from (select row_number() over (order by KeyID) as ID,* from StatusLog) as d where ID between [[GetRowStart]] and [[GetRowEnd]] order by KeyID
This is my javascript code:
var m=4;
for(var k=0;k<m;k++){
var timestamp = new Date();
var params = {
tags : tags,
timestamp : timestamp,
source : me.name,
values : values
};
var d =new Date().getMilliseconds();
var params = {
GetRowStart: (k*500+1)/* NUMBER */,
GetRowEnd: (k+1)*500 /* STRING */
};
var result = me.QueryStatusLog(params);
for (var i = 0; i <500;i++) {
var timestamp = new Date().setMilliseconds(d+i);
var row = result.getRow(i);
var tags = new Array();
// values:INFOTABLE(Datashape: CCLAStatusLogDS)
var values = Things["CCLAStatusLogStream"].CreateValues();
values.KeyID=row.KeyID;
values.FixtureStatus=row.FixtureStatus;
values.ProductRejectCode=row.ProductRejectCode;
values.ProductSerialCode=row.ProductSerialCode;
values.ProductRange=row.ProductRange;
values.Recipe=row.Recipe;
values.BatchNumber=row.BatchNumber;
values.FixtureProcessingCnt=row.FixtureProcessingCnt;
values.FixtureProcessingCnt_Dispense=row.FixtureProcessingCnt_Dispense;
values.BarrelmapOffsetX=row.BarrelmapOffsetX;
values.BarrelmapOffsetY=row.BarrelmapOffsetY;
values.ForceInsert1=row.ForceInsert1;
values.ForceInsert2=row.ForceInsert2;
values.ForceInsert3=row.ForceInsert3;
values.ForceInsert4=row.ForceInsert4;
values.ForceInsert5=row.ForceInsert5;
values.ForceInsert6=row.ForceInsert6;
values.ForceInsert7=row.ForceInsert7;
values.ForceInsert8=row.ForceInsert8;
values.ForceInsert9=row.ForceInsert9;
values.ForceInsert10=row.ForceInsert10;
values.ForceInsert11=row.ForceInsert11;
values.ForceInsert12=row.ForceInsert12;
values.ForceInsert13=row.ForceInsert13;
values.ForceInsert14=row.ForceInsert14;
values.ForceInsert15=row.ForceInsert15;
values.ForceInsert16=row.ForceInsert16;
values.ForceInsert17=row.ForceInsert17;
values.PositionInsert1=row.PositionInsert1;
values.PositionInsert2=row.PositionInsert2;
values.PositionInsert3=row.PositionInsert3;
values.PositionInsert4=row.PositionInsert4;
values.PositionInsert5=row.PositionInsert5;
values.PositionInsert6=row.PositionInsert6;
values.PositionInsert7=row.PositionInsert7;
values.PositionInsert8=row.PositionInsert8;
values.PositionInsert9=row.PositionInsert9;
values.PositionInsert10=row.PositionInsert10;
values.PositionInsert11=row.PositionInsert11;
values.PositionInsert12=row.PositionInsert12;
values.PositionInsert13=row.PositionInsert13;
values.PositionInsert14=row.PositionInsert14;
values.PositionInsert15=row.PositionInsert15;
values.PositionInsert16=row.PositionInsert16;
values.PositionInsert17=row.PositionInsert17;
values.LastRotation=row.LastRotation;
values.PressForce=row.PressForce;
values.PressPositionInsert=row.PressPositionInsert;
values.ProductHeight1=row.ProductHeight1;
values.ProductHeight2=row.ProductHeight2;
values.ProductHeight3=row.ProductHeight3;
values.ProductHeight4=row.ProductHeight4;
values.Tilt1=row.Tilt1;
values.Tilt2=row.Tilt2;
values.LastProcessingPosition=row.LastProcessingPosition;
values.DateTime=row.DateTime;
var params = {
tags : tags,
timestamp : timestamp,
source : me.name,
values : values,
};
// AddStreamEntry(tags:TAGS, timestamp:DATETIME, source:STRING("me.name"), values:INFOTABLE(CCLAStatusLogDS), location:LOCATION):NOTHING
Things["CCLAStatusLogStream"].AddStreamEntry(params);
}
}
Hello, Hao Wu.
Can you export the Thing involved here and attach it, please?
Thank you,
-- Craig A.
OK.
Please kindly find the attachment.I use MSSQL for my local database.
Thank you.
Hi Hao,
On thing first:
You can query more than 500 rows from a SQL Database with TW, who told you that you can't? On the "SQL (Query)" service on Composer you have a Max Rows parameter to increase the 500 rows limit.
Second about your loop:
You can't insert two rows at the same Timestamp, and as you are setting:
var d =new Date().getMilliseconds();
On each outer iteration, and then you add up to 500 milliseconds to var d, you end up on the next outer loop iteration on one new Date() that you already had inserted on the previous inner lop.
You can move out var d =new Date().getMilliseconds(); of the outer loop and it will work ( at least you don't execute this service twice in a row, as you already had inserted rows in 4*500 milliseconds on the future )
Other things:
Don't use result variable name for internal usage, as it's used to return Service values on TW.
Your code will crash if you have fewer than 4*500 rows in the Table.
Best Regards,
Carles.
Hi Carles,
Thank you very much.
I learned thingworx by myself, I got misunderstanding for some knowledge, .Now I have found the point for changing the Max Rows,and as your information, I change my code:I move out var d =new Date().getMilliseconds();, and change rows to 1000,var timestamp = new Date().setMilliseconds(d+i); also change to var timestamp = new Date().setMilliseconds(k*1000+1+i). Now, it's works well.