Fast logging with data buffer

In order to log data in a database at millisecond intervals, the data must first be consolidated before it is transferred to the database via the TwinCAT Database Server. These data buffers can vary in size according to requirements. In the sample, 100 data samples are combined in a buffer before they are transferred with the TwinCAT Database Server. To avoid gaps during the write process, several buffers must be created in which the data samples are combined. In the sample, a total of 20 buffers are created using a 2-dimensional array.

TYPE ST_Data :
STRUCT
    Timestamp        : LINT;
    fAM              : LREAL;
    fPeak            : LREAL;
    fPulse           : LREAL;
    fSawtooth        : LREAL;
    fSine            : LREAL;
    fSquare          : LREAL;
    fStairs          : LREAL;
    fTriangular      : LREAL;
END_STRUCT
END_TYPE

Each cycle fills one element of the data buffer. In the sample this happens at 10 ms intervals. Thus a buffer contains data of a period of 1 s. If a buffer is filled with 100 elements, a further array indicates that the 100 elements can now be transferred with the function block FB_PLCDBCmdEvt. To this end, the entire buffer can be transferred to the function block. Each individual element is then transferred from the TwinCAT Database Server to the database. This sample can also be implemented with other function blocks. Note that not all function blocks support arrays.

…
    2://Recording
        bRecording := TRUE;

        //Fill buffer
        stData[nWriteBufferIndex, nWriteIndex].Timestamp := nTimestamp;
        stData[nWriteBufferIndex, nWriteIndex].fAM := fAM;
        stData[nWriteBufferIndex, nWriteIndex].fPeak := fPeak;
        stData[nWriteBufferIndex, nWriteIndex].fPulse := fPulse;
        stData[nWriteBufferIndex, nWriteIndex].fSawtooth := fSawtooth;
        stData[nWriteBufferIndex, nWriteIndex].fSine := fSine;
        stData[nWriteBufferIndex, nWriteIndex].fSquare := fSquare;
        stData[nWriteBufferIndex, nWriteIndex].fStairs := fStairs;
        stData[nWriteBufferIndex, nWriteIndex].fTriangular := fTriangular;

        //Set buffer index
        nWriteIndex := nWriteIndex + 1;
        IF nWriteIndex = 100 THEN
            nWriteIndex := 0;
            aWriteSQL[nWriteBufferIndex]:= TRUE;
            nWriteBufferIndex := nWriteBufferIndex + 1;

            IF nWriteBufferIndex = 20 THEN
                nWriteBufferIndex := 0;
            END_IF

            IF aWriteSQL[nWriteBufferIndex] THEN
                nState := 255;
                RETURN;
            END_IF
        END_IF

        //Write buffer element (100 samples) to database
        IF aWriteSQL[nSQLIndex] THEN
            IF fbPLCDBCmd.Execute(nDBID, ADR(sCmd), SIZEOF(sCmd),
                                 ADR(stData[nSQLIndex,0]), SIZEOF(stData[nSQLIndex,0]) * 100,
                                 ADR(aPara), SIZEOF(aPara)) THEN
                IF fbPLCDBCmd.bError THEN
                    nState := 255;
                ELSE
                    nRecords := nRecords + 100;

                    aWriteSQL[nSQLIndex] := FALSE;

                    nSQLIndex := nSQLIndex + 1;
                    IF nSQLIndex = 20 THEN
                        nSQLIndex := 0;
                    END_IF

                    IF NOT bRecord THEN
                        bRecording := FALSE;
                        nState := 0;
                    END_IF
                END_IF
            END_IF
        END_IF
….

Appendix:

In this best practice example, a function generator block is used to generate various signals that can be logged in a database. The syntax of the INSERT command is generally valid, but has been specifically tested with an MS SQL database. The ZIP file attached below contains the complete program code in Tnzip format.

Download: TF6420_BestPractise_Buffer.zip