ModDBExport

Forum Home Forums Uncategorized Issues ModDBExport

Tagged: 

This topic contains 36 replies, has 3 voices, and was last updated by Mikhail Mikhail 4 months, 2 weeks ago.

Viewing 15 posts - 1 through 15 (of 37 total)
  • Author
    Posts
  • #3398
    Avatar
    sabas
    Participant

    Hello,

    I’m exporting data to SQL with your queries at Documentation but records are being sent twice.

    DBExport

    I just got the query “INSERT INTO CnlData (DateTime, CnlNum, Val, Stat) VALUES (@dateTime, @cnlNum, @val, @stat)” and the “Export” option active in module.

    Tryed to export manually with same results (2 copies for every value)

    Any suggestion?

    Thanks in advance.

    #3401
    Mikhail
    Mikhail
    Moderator

    Hi,
    The time stamps are different. It means there were 2 inserts close by time for every channel. Communicator may send data twice, because it sends all the data again by the specified interval. You can change this interval in the settings of Communicator.

    #3405
    Avatar
    sabas
    Participant

    I’ve been working on Communicator settings, and also Server settings (“writting period”, “writting data”, “writting data copy” .

    I realized that current data is being sent when data have changed and data is taken, and the timer marked as “Sending all device data” send all data even if it didn’t change.

    So, there are 2 different timers writing to database. One is “current value” (when you adquire data), and the other is “sending all data” (depending on timer).

    What I saw is that if you put a time value lower than the total cycle of data adquisition for all devices, you get 2 records each time. Different timestamps but same value, because of query queue (not more than 100-150ms of difference, less time than the time you use to adquire a new value).

    If time is higher you still get 2 records whith all data, but only when “Sending all device data” query write. Then you get only changed data for each device and channel till “Sending all data” write again.

    I can write an SQL query to clean this in my database but I think something is not well defined by my side (I’ve been playing with timers and options all the weekend and didn’t find the way), unless it is intended to work this way for any reasing I can’t understand.

    Thank you for your help.

    #3411
    Mikhail
    Mikhail
    Moderator

    I saw is that if you put a time value lower than the total cycle of data adquisition for all devices, you get 2 records each time

    Could you explain this case more detailed and provide screenshots of your settings?

    #3412
    Avatar
    sabas
    Participant

    In my case I got 6 devices at Modbus RTU line with a 20secs delay to get data. They are Temperature in rooms so I don’t need to record them every second.

    This means 120s+ to read all devices

    (20 seconds x 6 devices) = 120 seconds + Time asking/receiving

    If you put 120s in “Sending all device data” you get 2 records in SQL export.

    In sample, i’ve added email device with a delay of 100s. If you don’t send email current value for this channel has not changed, so the double record comes every 2 cycles (100+100>120).

    Here are screenshots and an excel with records (3 sheets). Note that first time after reset you only get 1 record, but after that, the double record comes.

    View post on imgur.com

    https://drive.google.com/open?id=1xQCqHSY4QazgZgOAToAhYowXGc7T3Ydn

    Thank you

    #3418
    Mikhail
    Mikhail
    Moderator

    Thank you for the details.
    Set “Sending all device data” to 0 to switch off this feature.
    Also you can set “Period” to 0:00:20 in devices properties instead of a delay. In this case, devices will be polled more predictable, may be.

    #3429
    Avatar
    sabas
    Participant

    Hello again,

    I did what you told and it worked, but I think is not the best solution, because I only get the record of values that have changed, so while the response for 10 requests is the same you only record the value at the first response. There are cases when this could be a trouble.

    I’m currently working on a production counter. It counts the times that a digital input is activated and record it as an analogical value you get in an output.

    Think in an inductive sensor counting boxes in a conveyor belt. In a regular production flow you count 10 boxes per minute during 15 minutes and only 5 at the minute 16.

    With this solution you get the 10 value in your first response and export it to the sql database. Then while you keep reading 10 units every minute you are not recording anything (in sql database) until the minute 16, when response is 5.

    It seems that you only countered 15 boxes, while 155 boxes is the real value.

    The “double record” issue would give 310 boxes.

    I’ll keep working on it. I’ll try to study your code and find a solution.
    Tryed to set @val as null at Current Data Tab in ModDBexport module but it didn’t work, because I think the onchange() evaluation is done by scada code and I got nothing to do in SQL instruction.

    Hope you can understand me, because I’m more engineer than software developer.

    Thanks in advance.

    #3435
    Avatar
    sabas
    Participant

    I’m trying but some of your source code files have your comments only in Russian Language and it’s being difficult for me and Google Translator, ;D

    Could it be that when you get current data, you evaluate if newVal is equal to oldVal before writting and you discard saving newVal in positive case?

    (Think I saw something like that at MainLogic.cs)

    Thank you

    #3440
    Mikhail
    Mikhail
    Moderator

    Could you open the code you interested in by GitHub and copy link to a particular line of code? Of course, I will help to understand.
    What happens if PC is off? Do you loose some switching?

    #3442
    Avatar
    sabas
    Participant

    MainLogic.cs

    Lines 2149-2238

    /// <summary>
    /// Обработать новые текущие данные
    /// </summary>
    public bool ProcCurData(SrezTableLight.Srez receivedSrez)
    {
    try
    {
    if (serverIsReady)
    {
    int cnlCnt = receivedSrez == null ? 0 : receivedSrez.CnlNums.Length;

    if (cnlCnt > 0)
    {
    lock (curSrez) lock (calculator)
    {
    try
    {
    procSrez = curSrez;

    for (int i = 0; i < cnlCnt; i++)
    {
    int cnlNum = receivedSrez.CnlNums[i];
    int cnlInd = curSrez.GetCnlIndex(cnlNum);
    InCnl inCnl;

    if (inCnls.TryGetValue(cnlNum, out inCnl) && cnlInd >= 0) // входной канал существует
    {
    if (inCnl.CnlTypeID == BaseValues.CnlTypes.TS ||
    inCnl.CnlTypeID == BaseValues.CnlTypes.TI)
    {
    // вычисление новых данных входного канала
    SrezTableLight.CnlData oldCnlData = curSrez.CnlData[cnlInd];
    SrezTableLight.CnlData newCnlData = receivedSrez.CnlData[i];
    CalcCnlData(inCnl, oldCnlData, ref newCnlData);

    // расчёт данных для усреднения
    if (inCnl.Averaging &&
    newCnlData.Stat > BaseValues.CnlStatuses.Undefined &&
    newCnlData.Stat != BaseValues.CnlStatuses.FormulaError &&
    newCnlData.Stat != BaseValues.CnlStatuses.Unreliable)
    {
    minAvgData[cnlInd].Sum += newCnlData.Val;
    minAvgData[cnlInd].Cnt++;
    hrAvgData[cnlInd].Sum += newCnlData.Val;
    hrAvgData[cnlInd].Cnt++;
    }

    // запись новых данных в текущий срез
    curSrez.CnlData[cnlInd] = newCnlData;

    // генерация события
    GenEvent(inCnl, oldCnlData, newCnlData);

    // обновление информации об активности канала
    activeDTs[cnlInd] = DateTime.Now;
    }
    else
    {
    // запись новых данных в текущий срез без вычислений для дорасчётных каналов
    curSrez.CnlData[cnlInd] = receivedSrez.CnlData[i];
    }
    }
    }
    }
    finally
    {
    procSrez = null;
    curSrezMod = true;
    }
    }

    // выполнение действий модулей
    RaiseOnCurDataProcessed(receivedSrez.CnlNums, curSrez);
    }

    return true;
    }
    else
    {
    return false;
    }
    }
    catch (Exception ex)
    {
    AppLog.WriteException(ex, Localization.UseRussian ?
    “Ошибка при обработке новых текущих данных” :
    “Error processing new current data”);
    return false;
    }
    }

    • This reply was modified 1 year, 3 months ago by Avatar sabas.
    #3444
    Avatar
    sabas
    Participant

    The PC OFF question is related to the counter case? At this moment, when PC is off I’m not requesting data so i’m not getting anything.

    It is just an example, because I’m thinking about to link the counter to SCADA.

    I got an old device that can store the “counted” value (number of DI activations) in 1 or 2 bytes. In first case I got to read value in maximum cycles of 255 and then reset counter to 0, so if process work as is supposed to do I need to read the value every 5 minutes. If my Pc shut down and I request value later than 5 mins I’ll get 255 as a maximum value. If I use 1 byte instead of 2 bytes i can have more counters working on device, but anyway I want to take values every minute because I can use the value to calculate process speed (number of items done per minute) and to know when process start and stop with datetime values.

    But, for example, if the quantity in minute 5 and minute 6 is the same I’ll only get the value of 5th minute, skipping 6th minute because is equal to last value, and then getting 7th minute data. In this case, some data is lost and result is not correct.

    I use sql export because I need an standard database to integrate data to an ERP.

    Thanks in advance.

    #3453
    Mikhail
    Mikhail
    Moderator

    procSrez = curSrez;

    Save the reference to a snapshot which is processed.

    // вычисление новых данных входного канала

    Calculate new data of the input channel

    #3454
    Mikhail
    Mikhail
    Moderator

    Why you don’t use hardware modules that counts off/on cycles? This would be much reliable and transparent.

    #3456
    Avatar
    sabas
    Participant

    Thank you. There were 2 areas in bold. I asume that these are the functions that calculate the new data, but seems that in case new data are the same as old data, there are no new data or are not written as the values for SQL Instruction.

    INSERT INTO CnlData (DateTime, CnlNum, Val, Stat)
    VALUES (@dateTime, @cnlNum, @val, @stat)

    // вычисление новых данных входного канала
    SrezTableLight.CnlData oldCnlData = curSrez.CnlData[cnlInd];
    SrezTableLight.CnlData newCnlData = receivedSrez.CnlData[i];
    CalcCnlData(inCnl, oldCnlData, ref newCnlData);

    Related to hardware, I’m using a hardware like this.

    DAS 8000

    View post on imgur.com

    These images are from v2.5 hardware which is not exactly mine (this version can count more units till counter is full), but you can see what I’m talking about.

    You can read counter 1 values at 82&83 addreses (dec.).

    The exporting trouble is about to calculating speed process, not about the capacity or reliability of data or device.

    With this hardware you can count up to 9999 cycles.

    If you produce 20 items per minute you can take the value every 60secs without troubles, getting a serie of {20,40,60,80,100,120,140} in the perfect case during 7 minutes.
    In case of procesing stop you can get {20,40,60,80,80,80,100}. The export will be
    {20,40,60,80,”no data”,”no data”,100} for minutes 1 to 7.
    Speed process is still 20 units per minute, but you stopped for 2 minutes. You can easily calculate by dividing the maximum value by the number of registries(for channel 611)

    //maximum value//
    SELECT MAX(Val) AS MaximumValue
    FROM Cnldata where cnlnum=”611″;

    //number of values//
    SELECT Count(Val) AS NumberOfValues
    FROM Cnldata where cnlnum=”611″;

    You can also evaluate datetime registries to detect that there are 2 minutes without record.

    But with this 9999 units counter, if you registry 8000 cycles in a minute you need to reset counter every time you read.

    You are probably getting a series like this every time you read {8000,7994,8002,…}
    Then you need to do SUM instead of MAX to evaluate.

    But here comes the trouble, if you get {8000,7994,8002,8002,8000,…} the export module will write {8000,7994,8002,”no data”,8000,…} and you are losing 8002 units.

    Ins’t it?

    Thank you.

    #3459
    Mikhail
    Mikhail
    Moderator

    in case new data are the same as old data, there are no new data or are not written as the values for SQL Instruction

    As I remember, if data don’t change, Communicator doesn’t send them to Server, so they are not passed to DB.

    To fully understand your task, I have to do a research. This is not a part of free support. I can reproduce a simple example, and if it doesn’t work as expected, fix it. Also I can explain how Rapid SCADA works.
    Another option is connecting to your machine remotely and find out the cause of the problem. This is a commercial service.
    I hope I can help you in a one form or another.

    Anyway, I think I understand what you are talking about. How would you like the software to work to make your task clear without tricks? Should Communicator send all data, that were read, after each communication session?

Viewing 15 posts - 1 through 15 (of 37 total)

You must be logged in to reply to this topic.