Use XML as Database
It is possible with TF6420 Database Server to use XML files as database. The TwinCAT3 Database Server supports all known function blocks for reading and writing to a database down to the function blocks for stored procedures. SQL queries which will be send with the function blocks FB_DBRecordInsert or FB_DBRecordSelect are interpreted of the TwinCAT3 Database Server and adequate use to the XML file.
This sample demonstrate how to create an XML database, fill with the function block FB_DBWrite and read the items with an SQL-SELECT query with the FB_DBRecordSelect of the created XML file.
Download "Sample to use mit XML as Database" sample12.zip
Used database type | XML |
Compatible database type | MS SQL, MS Compact SQL, MS Access, XML |
Used function blocks | FB_DBCreate, FB_DBConnectionAdd, FB_DBTableCreate, FB_DBWrite, FB_DBRecordSelect |
Integrated libraries | "TcDatabase.lib", "TcSystem.lib","TcBase.lib", "TcStandard.lib", "TcUtilities.lib" |
Download data list | XML_DatabaseType.pro |
MAIN Program
PROGRAMMAINVAR
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
CASEnState 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=> );
IFNOT 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=> );
IFNOT 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);
IFNOT bBusy_CreateDB ANDNOT 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);
IFNOT bBusy_ConnAdd ANDNOT 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);
IFNOTbBusy_CreateTable ANDNOT 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);
IFNOT bBusy_WriteDB ANDNOT bErr THEN
FB_DBWrite1(bExecute := FALSE);
nCounter := nCounter + 1;
IFnCounter = 5 THEN
nState := 6;
END_IFEND_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);
IFNOT bBusy_SelectRecord ANDNOT bErr THEN
nState := 0;
END_IFEND_CASE
Start the sample with a rising edge at the toggle variable bSTART..
Following files will be created:
XMLTestDB.xml (XML database file)
<?xml version="1.0" encoding="UTF-8"?>
<XMLTestDB xmlns:xs="http://www.w3.org/2001/XMLSchema-instance" xs:noNamespaceSchemaLocation="XMLTestDB.xsd">
<myTable>
<row ID="1" Timestamp="2012-05-10T13:48:47" Name="rTestValue" Value="1234.56789" />
<row ID="2" Timestamp="2012-05-10T13:48:47" Name="rTestValue" Value="1234.56789" />
<row ID="3" Timestamp="2012-05-10T13:48:47" Name="rTestValue" Value="1234.56789" />
<row ID="4" Timestamp="2012-05-10T13:48:47" Name="rTestValue" Value="1234.56789" />
<row ID="5" Timestamp="2012-05-10T13:48:47" Name="rTestValue" Value="1234.56789" />
</myTable>
</XMLTestDB>
XMLTestDB.xsd (XML Schema)
<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:simpleType name="bigint">
<xsd:restriction base="xsd:long" />
</xsd:simpleType>
<xsd:simpleType name="datetime">
<xsd:restriction base="xsd:dateTime" />
</xsd:simpleType>
<xsd:simpleType name="ntext_80">
<xsd:restriction base="xsd:string">
<xsd:maxLength value="80" />
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="float">
<xsd:restriction base="xsd:double" />
</xsd:simpleType>
<xsd:complexType name="myTable_Type">
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="row">
<xsd:complexType>
<xsd:attribute name="ID" type="bigint" />
<xsd:attribute name="Timestamp" type="datetime" />
<xsd:attribute name="Name" type="ntext_80" />
<xsd:attribute name="Value" type="float" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
<xsd:element name="XMLTestDB">
<xsd:complexType>
<xsd:sequence minOccurs="1" maxOccurs="1">
<xsd:element name="myTable" type="myTable_Type" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>