Tutorial: Use Tables

Text tables

Configure table

1. Go to the submenu item from tables in the menu.

1. Go to the Tables submenu item and open the Tables Editor.

A button appears in the upper left corner that says "New". Click on it and create a new table (2).

1. Choose a name for your template
2. Choose a title for the table
3. Choose database
4. Set permissions
5. Set type
6. Check filter if needed
7. Check Paginator if pagination is needed
8. Set page pagination options

1. Edit Table Template Content

1. Set name of data-column
2. Set title
3. Set datatype
4. Set 'Primary key' if needed
5. Check 'Autoincrement' if needed
6. Check 'Unique key' if needed
7. Check 'not null' if needed
8. Check 'Visible', if column should be visible
9. Define sort order of columns
10. Check 'Editable', if data-cell should be editable
11. Define alignment of cell-content

Insert table in page

Now you have created a template, to use it go to the page editor and open either a new page or an already created page.

1. Choose a suitable container for your application and place it
2. Then go to the editing area

1. For example, you can choose a background image
2. Place a text box and write your headline

Generate IEC Code

To manage SQLite databases in CoDeSys IEC, one can directly use the elrest CoDeSys library SQLite. To simplify programming, a Python script can be generated in eDesign-HMI to execute in CoDeSys. CoDeSys examples for accessing a specified table are created.

In the customer/cs3/ directory, relative to the installation path of eDesign, the Python script file named TableName.py is created. There should be 2 files in this folder: SQLite.compiled-library and sqlite.xml. These are installed with eDesign and are dependencies of the Python script.

Please run this script in your CoDeSys application where you need to access the SQLITE table. The CoDeSys application must contain an Application object. Otherwise the error message "No Application!" will be displayed.

CoDeSys IDE: Menu Tools->Scripting->Execute script file ...

Script steps

• Adds the 3S libraries System, CmpErrors and SysMem to the library manager.
• Installs the elrest SQLite CoDeSys library
• Creates the SQLite folder in the Application object, if it does not exist, and imports the sqlite.xml file, which contains the default structure and types, regardless of the table.
• The enum type TableNameColumnsEnum is created. If it already exists, it is deleted first. It contains an enumeration of all columns defined in the table. Each element in the enum is called COL_ColumnName and its value is the column index in the table (the order of the columns).
  
• The structure type tableNameStruct is created. If it already exists, it is deleted first. It represents the data structure of a row in the table. The order of the structure elements is the same as in the enum type. Each element is called col_ColumnName. The element type depends on the SQLite type.
  
• The TabellenameFb function block is generated. If it already exists, it will be deleted first.
  Fb-Members:
  Fields:
  a constant containing the table definition. The array length is the number of columns in the table. The array type is tableInfoStruct.
  Example:
  Fields: ARRAY [0..6] OF tableInfoStruct :=.[(cid:=0,name:="id",d_type:="INTEGER",not_null:=FALSE,dflt_value:="NULL",pk:=TRUE), (cid:=1,name:="name",d_type:="TEXT",not_null:=TRUE,dflt_value:="NULL",pk:=FALSE), ...];

  Fields: ARRAY [0..6] OF tableInfoStruct :=.[(cid:=0,name:="id",d_type:="INTEGER",not_null:=FALSE,dflt_value:="NULL",pk:=TRUE), (cid:=1,name:="name",d_type:="TEXT",not_null:=TRUE,dflt_value:="NULL",pk:=FALSE), ...];

  Properties:

  • DB (Get/Set) : database connection as reference to the SQLite.FB_SQLite
  • LastErrorCode (Get) / LastErrorCodeExt (Get) : Details about the last error that occurred; see SysError / CmpErrors CoDeSys libraries
  • Rows (Get): Pointer to an array of rows of type TableNameStruct, where the methods getFirst/getNext return the results
    
• Methods:
  • init: By default the FB dynamically allocates memory for 20 lines read via getFirst/getNext, but it is recommended to change this via the init method. Normally it is sufficient to process each line and not load all or many lines into memory at once, see the examples.
    If successful, true is returned, otherwise see LastErrorCode & LastErrorCodeExt.
  • connect: Opens the database if it has not already been opened. It does not have to be called explicitly, because it is called in every method. If successful it returns true, otherwise see LastErrorCode & LastErrorCodeExt.
  • getFirst: Reads the first rows (default value 20 or the number of lines defined via the init method). You can specify a filter and the order as input parameters.
    Example: getFirst('Name like "%abc"', "Name ASC")
    If successful, the number of lines read is returned. Otherwise -1 and see LastErrorCode & LastErrorCodeExt.
  • getNext: It reads the next lines and returns the number of lines read or -1 if an error occurs, see LastErrorCode & LastErrorCodeExt. If getFirst is not called, the first call to getNext has the same effect as getFirst without input parameters.
  • restart: Resets the select request. The next call to getNext has the same effect as the call to getFirst.
    If successful, true is returned, otherwise see LastErrorCode & LastErrorCodeExt.
  • insert: Inserts a row into the table. The fields can be entered using the input parameters. The autoincrement field should not be entered, as well as the fields with default values.
    If successful, true is returned, otherwise see LastErrorCode & LastErrorCodeExt.
  • delete: Deletes all lines defined at the input parameter, which must not be empty!
    On success it returns true, otherwise see LastErrorCode & LastErrorCodeExt.
  • update: Updates all entered fields in all rows defined via inFilter. Please enter only the fields that need to be updated.
    Returns true if successful, otherwise see LastErrorCode & LastErrorCodeExt.
  • isValueNULL: Returns true if the field value defined at the inRowIndex and inColIndex input parameters is NULL, otherwise check the inoutResult output parameter. In case of an error the value is not 0, see SysError/CmpErrors.
• The examples subdirectory in the SQLite directory is generated if it does not already exist.
• The subdirectory delete in the Examples directory is generated if it does not already exist. The ExampleOfDeleteAllTablename program (PRG) is generated if it does not exist yet.
• insert is created if it does not exist yet.
• select is automatically generated if it does not exist yet.
• update is generated if it does not exist yet.

Note

To manage SQLite columns without type in CoDeSys IEC, the previous column must be of type INTEGER and defines the CoDeSys IEC type (see IBaseLibrary.TypeClass).
The connection to the SQLite database is based on the $eDesignDB$ placeholder. The path to the SQLite file is: $eDesignDB$/etable/elatable. Currently the placeholder must be in the CoDeSys configuration file: CODESYSCONTROL.cfg to be defined.

Example

[SysFile]
PlaceholderFilePath.1=C:workspaceeDesigncustomerdb,$eDesignDB$
Installation path of eDesign customer db

See the examples (Examples directory).