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.

Generation of a MS compact database 1:

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"