4 Replies Latest reply on Jul 17, 2017 5:22 AM by æ吴 RSS
    æ吴 Newbie

    the data will missing when store into stream

    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);

       }

    }

      • Re: the data will missing when store into stream
        carko Apprentice

        Hello, Hao Wu.

         

        Can you export the Thing involved here and attach it, please?

         

        Thank you,

         

        -- Craig A.

        • Re: the data will missing when store into stream
          carlesc Ninja

          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.

            • Re: the data will missing when store into stream
              æ吴 Newbie

              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.