Using XML as database
The TwinCAT Database Server provides the ability to use an XML file as a database. Apart from the "Stored Procedure" functions, the XML database type supports all known function blocks for reading and writing in a database. Even SQL commands that can be issued with the function blocks FB_DBRecordInsert or FB_DBRecordSelect are interpreted by the TwinCAT Database Server and applied to the XML file.
This sample demonstrates how an XML database is created, filled with the function block FB_DBWrite and subsequently read with an SQL SELECT command and the function block FB_DBRecordSelect.
Download: TcDBSrv_InfoSysSamples.zip
Database type used | XML |
Compatible database types | MS SQL, MS Compact SQL, MS Access, XML |
Function blocks used | FB_DBCreate, FB_DBConnectionAdd, FB_DBTableCreate, FB_DBWrite, FB_DBRecordSelect |
Libraries to be integrated | "Tc2_Database", "Tc2_System", "Tc2_Standard", "Tc2_Utilities" |
Download file list | TcDBSrv_InfoSysSamples.tszip |
MAIN program
PROGRAM MAIN
VAR
nState :BYTE := 0;
R_TRIG1 : R_TRIG;
bSTART : BOOL;
nCounter : INT;
FB_FileDelete1 : FB_FileDelete;
FB_DBCreate1 : FB_DBCreate;
FB_DBConnectionAdd1: FB_DBConnectionAdd;
FB_DBTableCreate1 : FB_DBTableCreate;
FB_DBWrite1 : FB_DBWrite;
FB_DBRecordSelect1 : FB_DBRecordSelect;
bBusy_Delete : BOOL;
bBusy_CreateDB : BOOL;
bBusy_ConnAdd : BOOL;
bBusy_CreateTable : BOOL;
bBusy_WriteDB : BOOL;
bBusy_SelectRecord : BOOL;
bErr : BOOL;
nErrid : UDINT;
stSQLState : ST_DBSQLError;
nRecs : 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',sColumnProperty:='80',eColumnType:=EDBCOLUMN_NTEXT),
(sColumnName:='Value',eColumnType:=EDBCOLUMN_FLOAT)];
rTestValue : LREAL := 1234.56789;
stRecord : ST_Record;
END_VAR
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);
FB_DBWrite1(bExecute:=FALSE);
FB_DBRecordSelect1(bExecute:=FALSE);
bSTART := FALSE;
nCounter:= 0;
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\XMLTestDB.xml',
ePath := PATH_GENERIC,
bExecute := TRUE,
tTimeout := T#5s,
bBusy => bBusy_Delete,
bError => ,
nErrId => );
IF NOT bBusy_Delete THEN
nState := 10;
END_IF
10:
(*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\XMLTestDB.xsd',
ePath := PATH_GENERIC,
bExecute := TRUE,
tTimeout := T#5s,
bBusy => bBusy_Delete,
bError => ,
nErrId => );
IF NOT bBusy_Delete THEN
FB_FileDelete1(bExecute:=FALSE);
nState := 2;
END_IF
2:
(*The FB_DBCreate block will create the database file
"C:\TwinCAT\TcDatabaseSrv\Samples\XMLTestDB.xml" and
C:\TwinCAT\TcDatabaseSrv\Samples\XMLTestDB.xsd "*)
FB_DBCreate1(
sNetID := ,
sPathName:= 'C:\TwinCAT\TcDatabaseSrv\Samples',
sDBName := 'XMLTestDB',
eDBType := eDBType_XML,
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*)
(*ATTENTION: Each database type has his own connection information*)
FB_DBConnectionAdd1(
sNetID := ,
eDBType := eDBType_XML,
eDBValueType:= eDBValue_Double,
sDBServer := 'XMLTestDB',
sDBProvider := ,
sDBUrl := 'C:\TwinCAT\TcDatabaseSrv\Samples\XMLTestDB.xml',
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 NOTbBusy_CreateTable AND NOT bErr THEN
nState := 5;
END_IF
5:
(*The FB_DBWrite write five times the value of the plc variable "rTestValue" to
the database table "myTable"*)
FB_DBWrite1(
sNetID := ,
hDBID := nDBid,
hAdsID := 1,
sVarName := 'MAIN.rTestValue',
nIGroup := ,
nIOffset := ,
nVarSize := ,
sVarType := ,
sDBVarName := 'rTestValue',
eDBWriteMode := eDBWriteMode_Append,
tRingBufferTime := ,
nRingBufferCount:= ,
bExecute := TRUE,
tTimeout := T#15s,
bBusy => bBusy_WriteDB,
bError => bErr,
nErrID => nErrid,
sSQLState => stSQLState);
IF NOT bBusy_WriteDB AND NOT bErr THEN
FB_DBWrite1(bExecute := FALSE);
nCounter := nCounter + 1;
IFnCounter = 5 THEN
nState := 6;
END_IF
END_IF
6:
(*The FB_DBRecordSelect select one record of the database table "myTable""*)
FB_DBRecordSelect1(
sNetID := ,
hDBID := nDBid,
sSelectCmd := 'SELECT * FROM myTable WHERE Name = $'rTestValue$'',
nRecordIndex := 0,
cbRecordSize := SIZEOF(stRecord),
pDestAddr := ADR(stRecord),
bExecute := TRUE,
tTimeout := T#15s,
bBusy => bBusy_SelectRecord,
bError => bErr,
nErrID => nErrid,
sSQLState => stSQLState,
nRecords => nRecs);
IF NOT bBusy_SelectRecord AND NOT bErr THEN
nState := 0;
END_IF
END_CASE
The process is started with a positive edge at the toggle variable bSTART.
The following files are created:
XMLTestDB.xml (XML database file)
<?xmlversion="1.0"encoding="UTF-8"?>
<XMLTestDBxmlns:xs="http://www.w3.org/2001/XMLSchema-instance"xs:noNamespaceSchemaLocation="XMLTestDB.xsd">
<myTable>
<rowID="1"Timestamp="2012-05-10T13:48:47"Name="rTestValue"Value="1234.56789" />
<rowID="2"Timestamp="2012-05-10T13:48:47"Name="rTestValue"Value="1234.56789" />
<rowID="3"Timestamp="2012-05-10T13:48:47"Name="rTestValue"Value="1234.56789" />
<rowID="4"Timestamp="2012-05-10T13:48:47"Name="rTestValue"Value="1234.56789" />
<rowID="5"Timestamp="2012-05-10T13:48:47"Name="rTestValue"Value="1234.56789" />
</myTable>
</XMLTestDB>
XMLTestDB.xsd (XML Schema)
<?xmlversion="1.0"?>
<xsd:schemaxmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:simpleTypename="bigint">
<xsd:restrictionbase="xsd:long" />
</xsd:simpleType>
<xsd:simpleTypename="datetime">
<xsd:restrictionbase="xsd:dateTime" />
</xsd:simpleType>
<xsd:simpleTypename="ntext_80">
<xsd:restrictionbase="xsd:string">
<xsd:maxLengthvalue="80" />
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleTypename="float">
<xsd:restrictionbase="xsd:double" />
</xsd:simpleType>
<xsd:complexTypename="myTable_Type">
<xsd:sequence>
<xsd:elementminOccurs="0"maxOccurs="unbounded"name="row">
<xsd:complexType>
<xsd:attributename="ID"type="bigint" />
<xsd:attributename="Timestamp"type="datetime" />
<xsd:attributename="Name"type="ntext_80" />
<xsd:attributename="Value" type="float" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
<xsd:elementname="XMLTestDB">
<xsd:complexType>
<xsd:sequenceminOccurs="1"maxOccurs="1">
<xsd:elementname="myTable"type="myTable_Type" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Requirements
|
Development environment |
Target platform |
PLC libraries to be linked |
|---|---|---|
|
TwinCAT v3.0.0 |
PC or CX (x86) |
Tc2_Database |