Database

This page describes database components. For more information about using the database view the Database Manual page, or some of the Tutorials.

Database Write Component

The database write component writes a series of tags to a database table.

When data is stored in a database using the Database Write component, three columns in the database are automatically generated:

Column Name	Description
__id__	An automatically generated unique id for the entry.
__db_store_time__	Timestamp of when the record was created using the database now() function.
__csv_diff__	Used internally to detect change against initial csv file. The value stored is the number of record changes since the record was equal with the csv file. (Internally there is a trigger which increments the value of __csv_diff__ on each record update)

Columns for the tags in the tagList are created and named using the tag name.

Database Read

The database read component executes a query and populates tags with the values of the results. When a database read component is create, a communication adapter is added for the read component (you may need to save and re-open the file).

The database read component can execute a custom query, or query an entire table by providing just the table name in dataSource.

Read Tags

Results from the query are passed to the tags in the communication adapter for the Database Read component. Values from the query are returned in the following format:

• DB:{result number}.{column name}

{result number} is indexed starting at zero.

To set the value of a tag to the 3rd ID column result, we would use "DB:2.__id__".

If you try to reference rows that do not exist, null values will be passed (they will display as "---" in indicators).

Database Edit

The Database Edit component allows editing fields in the database. The component works similar to the readTag - a database edit component also creates a communication adapter which maps tags to database values.

When a tag value is changed, it will trigger a write to the database automatically.

WARNING

Any tag value change will trigger a write.

Database Table

The Database Table component can be used to display values from the database. Custom queries are supported and can be used to manage what information is pulled and how it is formatted. Columns can be automatically determined or manually specified.

Editing

The Database Table component can allow editing for the table (or columns specifically) by setting the readOnly property to false. When readOnly is set to False, two other properties become available for the component: filter, orderBy. These allow some control of the displayed components.

Note

Tag injection does not currently work for the filter or orderBy columns.

Editing of columns can be disabled individually. When readOnly is set to False, then setting readOnly to true for the column will disable editing for that column.

Note

When readOnly for the component is set to true, columns cannot be set to be editable. The columns can only limit the component's readOnly setting.

Database Image Write

The Database Image Write component supports saving images to a database.

Supported Devices

IP Cameras which supports remote image capture via a URL

USB Cameras supported by Linux kernel

Database Structure

Column Name	Description
__id__	An automatically generated unique id for the entry.
__db_store_time__	Timestamp of when the record was created using the database now() function.
camID (charA)	Static string identifier specified in the component property to be saved along with captured image.
partID (charA)	The value of the tag specified here will be saved in the partID column. Empty string if not specified.
featureID (uint)	The value of the tag specified here will be saved in the featureID column. Zero if not specified.
image (binary)	Image data to be saved.
status (charA)	Automatically generated column showing Success and number of bytes loaded, or ERROR and the error message.

Database Image Viewer

The Database Image Viewer component allows viewing images stored using the Database Image Write component.

  SELECT * FROM images_from_image_capture ORDER BY __id__ DESC LIMIT 1 OFFSET $dbIndexTag

This query will load one image (LIMIT 1) from the table images_from_image_capture (use the table name specified in the DB Image Write component).

We use the $dbIndexTag to pick which of the results we want (zero would be the most recent image, 1 is the previous).

$dbIndexTag injects the value of the dbIndexTag tag into the query.