Sample with FB_DBRecordInsert/FB_DBRecordSelect
Download "Example to log with the FB_DBRecordInsert" sample7.zip
In this example the log of several values into a database out of a PLC with the function block FB_DBRecordInsert will be shown.
Especially in this example several PLC variables will be logged in one data set. Furthermore a data set can be read out of the database with the function block FB_DBRecordSelect.
Used database type | MS Access |
Compatible database types | MS SQL, MS Compact SQL, MS Access, MySQL, PostgreSQL, DB2, Oracle, InterBase/Firebird |
Used function blocks | FB_DBRecordInsert, FB_DBRecordSelect |
Integrated libraries | "TcDatabase.lib", "TcSystem.lib","TcBase.lib","TcStandard.lib","TcUtilities.lib" |
Download data list | FB_DBRecodInsertSelectSample.pro, CurrentConfigDataBase.xml, TestDB_Access.mdb |
To use this example, you need to declare the access database "Sample7.mdb" in the XML-configuration data.
By generating a positive edge at the variable "bStartstopInsert ", a data set with four PLC values and the timestamp will be attached in the database.
The following table structure will be written:
Column name | Data type |
|---|---|
Timestamp | datetime |
PLC_TestValue1 | float |
PLC_TestValue2 | float |
PLC_TestValue3 | float |
PLC_TestValue4 | String |
Variable declaration
(* Declaration *)PROGRAM MAIN
VAR
eState: E_SQLStatement;
NT_GetTime1: NT_GetTime;
bTimestart: BOOL;
tTime: TIMESTRUCT;
FB_FormatStringDateTime: FB_FormatString;
sDateTimeString: T_MaxString;
TestValue1: REAL := 123.456;
TestValue2: REAL := 234.567;
TestValue3: REAL := 345.678;
TestValue4: STRING(255) := 'No error occurred';
FB_FormatString1: FB_FormatString;
sInsertString: T_MaxString;
bError: BOOL;
nErrid: UDINT;
FB_DBRecordInsert1: FB_DBRecordInsert;
bStartstopInsert: BOOL;
bBusyInsert: BOOL;
bErrInsert: BOOL;
nErridInsert: UDINT;
stSQLStateInsert: ST_DBSQLError;
stRecord: ST_Record;
FB_DBRecordSelect1: FB_DBRecordSelect;
nRecIndex: UDINT := 0;
bStartstopSelect: BOOL;
bBusySelect: BOOL;
bErrorSelect: BOOL;
nErrIDSelect: UDINT;
stSQLStateSelect: ST_DBSQLError;
nRecordCount: UDINT;
END_VAR
Enum E_SQLStatement
TYPE E_SQLStatement :(
eSQL_INSERT := 0,
eSQL_SELECT := 1
);
END_TYPE
Struct ST_Record
TYPE ST_Record :
STRUCT
Timestamp : DT;
PLC_Value1 : REAL;
PLC_Value2 : REAL;
PLC_Value3 : REAL;
PLC_Value4 : STRING;
END_STRUCT
END_TYPE
PLC Program
CASE eState OF
eSQL_INSERT:
(*Create the timestamp*)
NT_GetTime1( START:= bTimestart, TIMESTR=> tTime);
IF NOT NT_GetTime1.BUSY THEN
bTimestart := NOT bTimestart;
END_IF
FB_FormatStringDateTime(
sFormat:= '%D.%D.%D %D:%D:%D',
arg1:= F_WORD(tTime.wYear),
arg2:= F_WORD(tTime.wMonth),
arg3:= F_WORD(tTime.wDay),
arg4:= F_WORD(tTime.wHour),
arg5:= F_WORD(tTime.wMinute),
arg6:= F_WORD(tTime.wSecond),
sOut=> sDateTimeString);
(*-----------------------------------------------------------------------*)(*Create the SQL-INSERT command*)
FB_FormatString1(
sFormat:= 'INSERT INTO tbl_Test VALUES($'%S$',%F,%F,%F,$'%S$')',
arg1:= F_STRING(sDateTimeString),
arg2:= F_REAL(TestValue1),
arg3:= F_REAL(TestValue2),
arg4:= F_REAL(TestValue3),
arg5:= F_STRING(TestValue4),
sOut=> sInsertString,
bError=> bError,
nErrId=> nErrid);
(*-------------------------------------------------------------------------*)(*Write the record to the database*)
FB_DBRecordInsert1(
sNetID:= ,
hDBID:= 1,
sInsertCmd:= sInsertString,
bExecute:= bStartstopInsert,
tTimeout:= T#15s,
bBusy=> bBusyInsert,
bError=> bErrInsert,
nErrID=> nErridInsert,
sSQLState=> stSQLStateInsert);
eSQL_SELECT:
(*Read one record from the database*)
FB_DBRecordSelect1(
sNetID:= ,
hDBID:= 1,
sSelectCmd:= 'SELECT * FROM tbl_Test',
nRecordIndex:= nRecIndex,
cbRecordSize:= SIZEOF(stRecord),
pDestAddr:= ADR(stRecord),
bExecute:= bStartstopSelect,
tTimeout:= T#15s,
bBusy=> bBusySelect,
bError=> bErrorSelect,
nErrID=> nErrIDSelect,
sSQLState=> stSQLStateSelect,
nRecords=> nRecordCount);
END_CASE
Screenshot tbl_Test:
