Generation of a MS compact database
Download "Example generation of a database" sample1.zip
In this example the generation of a database out of the PLC will be shown.
In addition a table will be added and the generated database in the XML-configuration data declared.
Used database type | MS SQL Compact |
Compatible database type | MS SQL, MS Compact SQL |
Used function blocks | FB_DBCreate, FB_DBConnectionAdd, FB_DBTableCreate |
Download data list | FB_DBCreate_Sample.pro |
To use this example you need to pass over the NetID of the ADS device (on which the TwinCAT Database Server is installed) to the input sNetID. And to the generated database a table named "myTable" will be added. This will have the following table structure:
Column name | Data type | Property |
---|---|---|
ID | bigint | IDENTITY(1,1) |
Timestamp | datetime |
|
Name | ntext |
|
Value | float |
|
This table structure will be generated with the following array:
tablestrc: ARRAY [0..3] OF ST_DBColumnCfg := (sColumnName:='ID',sColumnProperty:='IDENTITY(1,1)',eColumnType:=EDBCOLUMN_BIGINT),
(sColumnName:='Timestamp',eColumnType:=EDBCOLUMN_DATETIME),
(sColumnName:='Name',eColumnType:=EDBCOLUMN_NTEXT),
(sColumnName:='Value',eColumnType:=EDBCOLUMN_FLOAT);
Variable declaration
PROGRAM MAIN
VAR
R_TRIG1: R_TRIG;
bSTART: BOOL;
FB_FileDelete1: FB_FileDelete;
FB_DBCreate1: FB_DBCreate;
FB_DBConnectionAdd1: FB_DBConnectionAdd;
FB_DBTableCreate1: FB_DBTableCreate;
bBusy_Delete: BOOL;
bBusy_CreateDB: BOOL;
bBusy_ConnAdd: BOOL;
bBusy_CreateTable: BOOL;
bErr: BOOL;
nErrid: UDINT;
nDBid: UDINT;
arrTablestrc: ARRAY [0..3] OF ST_DBColumnCfg := (sColumnName:='ID',sColumnProperty:='IDENTITY(1,1)',eColumnType:=EDBCOLUMN_BIGINT),
(sColumnName:='Timestamp',eColumnType:=EDBCOLUMN_DATETIME),
(sColumnName:='Name',eColumnType:=EDBCOLUMN_NTEXT),
(sColumnName:='Value',eColumnType:=EDBCOLUMN_FLOAT);
nState:BYTE := 0;
END_VAR
PLC Program
CASE nState OF
0:
(*To start this sample you have to set a rising edge to the variable bSTART*)
R_TRIG1(CLK:=bSTART);
IF R_TRIG1.Q THEN
nState := 1;
FB_FileDelete1(bExecute:=FALSE);
FB_DBCreate1(bExecute:=FALSE);
FB_DBConnectionAdd1(bExecute:=FALSE);
FB_DBTableCreate1(bExecute:=FALSE);
bSTART := FALSE;
END_IF
1:
(*It isn't possible to overwrite an existing database file. If the database file exist the FB_FileDelete block will delete the file*)
FB_FileDelete1(
sNetId:= ,
sPathName:= 'C:\TwinCAT\TcDatabaseSrv\Samples\TestDB1000SPS.sdf',
ePath:= PATH_GENERIC,
bExecute:= TRUE,
tTimeout:= T#5s,
bBusy=> bBusy_Delete,
bError=> ,
nErrId=> );
IF NOT bBusy_Delete THEN
nState := 2;
END_IF
2:
(*The FB_DBCreate block will create the database file "C:\TwinCAT\TcDatabaseSrv\Samples\TestDB1000SPS.sdf"*)
FB_DBCreate1(
sNetID:= ,
sPathName:= 'C:\TwinCAT\TcDatabaseSrv\Samples',
sDBName:= 'TestDB1000SPS',
eDBType:= eDBType_Mobile_Server,
bExecute:= TRUE,
tTimeout:= T#15s,
bBusy=> bBusy_CreateDB,
bError=> bErr,
nErrID=> nErrid);
IF NOT bBusy_CreateDB AND NOT bErr THEN
nState := 3;
END_IF
3:
(*The FB_DBConnectionAdd adds the connection information to the XML - configuration file*)
FB_DBConnectionAdd1(
sNetID:= ,
eDBType:= eDBType_Mobile_Server,
eDBValueType:= eDBValue_Double,
sDBServer:= ,
sDBProvider:= ,
sDBUrl:= 'C:\TwinCAT\TcDatabaseSrv\Samples\TestDB1000SPS.sdf',
sDBTable:= 'myTable',
bExecute:= TRUE,
tTimeout:= T#15s,
bBusy=> bBusy_ConnAdd,
bError=> bErr,
nErrID=> nErrid,
hDBID=> nDBid);
IF NOT bBusy_ConnAdd AND NOT bErr THEN
nState := 4;
END_IF
4:
(*The FB_DBTableCreate create the table "myTable"*)
FB_DBTableCreate1(
sNetID:= ,
hDBID:= nDBid,
sTableName:= 'myTable',
cbTableCfg:= SIZEOF(arrTablestrc),
pTableCfg:= ADR(arrTablestrc),
bExecute:= TRUE,
tTimeout:= T#15s,
bBusy=> bBusy_CreateTable,
bError=> bErr,
nErrID=> nErrid);
IF NOT bBusy_CreateTable AND NOT bErr THEN
nState := 0;
END_IFEND_CASE
Included library is the "TcDatabase.lib", "TcSystem.lib", "TcBase.lib" and "STANDARD.lib"