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
| Property | Values | Description |
|---|---|---|
| name | string | Provide a name for the component. This can be helpful when using the "Used Components" tool in the tag editor. |
| description | string | A space to provide a description of the current component, useful for documenting intent. |
| geometry | x, y, width, height (ints) | Sets the visible geometry of the component: x and y from top left corner, and width/height. Editing these values can be useful when positioning/sizing finer than grid snap is needed. |
| triggerTag | tag | Component will watch this tag to determine whether to trigger an action. See triggerOnEvent property. |
| triggerOnEvent | ZeroToNonZero, AnyChange | Determines when a change in the triggerTag will be used to trigger an action. ZeroToNonzero will trigger only when the tag value goes from zero to a non-zero value, and anyChange will trigger on any change in value. |
| statusTag | tag | Tag specifies where the component will write its status to. See details for each component. Database components write 1 for error, 0 otherwise. |
| tagList | list of tags | List of tags used to write values. See component details for more information. |
| DB Source databaseSource | DB Source | Sets which of the sources to use from the Database Sources dialog. |
| findRecordTag | tag | When set to non-zero, will select the nth entry from the results. For writes, this will overwrite the nth entry from the datasource. Effectively applies an offset to the selection query. |
| printToPrinterOnPort | Disabled, LPT*, COM*, USB*, USBToSerial* | Specifies which serial port to send print commands to. |
| printColumnSeparator | string | Defines the separator between columns. |
| printCharsPerLine | int | Specifies how many characters will be printed on a single line. |
| printHeader | true, false | When true, a header containing the names of the tags will be written. |
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
| Property | Values | Description |
|---|---|---|
| name | string | Provide a name for the component. This can be helpful when using the "Used Components" tool in the tag editor. |
| description | string | A space to provide a description of the current component, useful for documenting intent. |
| geometry | x, y, width, height (ints) | Sets the visible geometry of the component: x and y from top left corner, and width/height. Editing these values can be useful when positioning/sizing finer than grid snap is needed. |
| refreshTag | tag | Component will watch this tag to determine when to refresh data. See refreshOnEvent property. |
| refreshOnEvent | ZeroToNonZero, AnyChange | Determines when a change in the triggerTag will be used to trigger an action. ZeroToNonzero will trigger only when the tag value goes from zero to a non-zero value, and anyChange will trigger on any change in value. |
| refreshDoneTag | tag | When a database refresh is completed, this tag will be set to 1. During a refresh it will be set to 0. |
| DB Source databaseSource | DB Source | Sets which of the sources to use from the Database Sources dialog. |
| findRecordTag | tag | When set to non-zero, will select the nth entry from the results. For writes, this will overwrite the nth entry from the datasource. Effectively applies an offset to the selection query. |
| currentRecordTag | tag | Sets the value of the tag to the index of the record shown, usually the findRecordsTag value. If findRecordsTag is greater than total number of records, this will be the index of the record retrieved. So, if a table has 10 rows, and findRecordsTag is set to 20, the currentRecordsTag will read 11. 1 |
| totalRecordsTag | tag | Sets the value of the tag to the total number of rows available. |
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
| Property | Values | Description |
|---|---|---|
| name | string | Provide a name for the component. This can be helpful when using the "Used Components" tool in the tag editor. |
| description | string | A space to provide a description of the current component, useful for documenting intent. |
| geometry | x, y, width, height (ints) | Sets the visible geometry of the component: x and y from top left corner, and width/height. Editing these values can be useful when positioning/sizing finer than grid snap is needed. |
| refreshTag | tag | Component will watch this tag to determine when to refresh data. See refreshOnEvent property. |
| refreshOnEvent | ZeroToNonZero, AnyChange | Determines when a change in the triggerTag will be used to trigger an action. ZeroToNonzero will trigger only when the tag value goes from zero to a non-zero value, and anyChange will trigger on any change in value. |
| refreshDoneTag | tag | When a database refresh is completed, this tag will be set to 1. During a refresh it will be set to 0. |
| refreshTime | int | Time in seconds that the contents of this component will be refreshed. |
| DB Source databaseSource | DB Source | Sets which of the sources to use from the Database Sources dialog. |
| findRecordTag | tag | When set to non-zero, will select the nth entry from the results. For writes, this will overwrite the nth entry from the datasource. Effectively applies an offset to the selection query. |
| currentRecordTag | tag | Sets the value of the tag to the index of the record shown, usually the findRecordsTag value. If findRecordsTag is greater than total number of records, this will be the index of the record retrieved. So, if a table has 10 rows, and findRecordsTag is set to 20, the currentRecordsTag will read 11. 1 |
| totalRecordsTag | tag | Sets the value of the tag to the total number of rows available. |
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
| Property | Values | Description |
|---|---|---|
| name | string | Provide a name for the component. This can be helpful when using the "Used Components" tool in the tag editor. |
| description | string | A space to provide a description of the current component, useful for documenting intent. |
| geometry | x, y, width, height (ints) | Sets the visible geometry of the component: x and y from top left corner, and width/height. Editing these values can be useful when positioning/sizing finer than grid snap is needed. |
| hideTag | tag | When tag value is an integer other than zero, the component is not displayed on the screen. |
| refreshTag | tag | Component will watch this tag to determine when to refresh data. See refreshOnEvent property. |
| refreshOnEvent | ZeroToNonZero, AnyChange | Determines when a change in the triggerTag will be used to trigger an action. ZeroToNonzero will trigger only when the tag value goes from zero to a non-zero value, and anyChange will trigger on any change in value. |
| refreshDoneTag | tag | When a database refresh is completed, this tag will be set to 1. During a refresh it will be set to 0. |
| foreColor | color | Sets foreground color (usually text). |
| backcolor | color | Sets background color. Images are allowed to set this to transparent to support images with alpha masks. |
| headerForeColor | color | Sets the color of text for the row/column headings. |
| headerBackColor | color | Sets background color for the row/column headings. |
| text | text | Sets the text that is displayed on the component. |
| textSize | int (points) | Sets the text size in points. |
| textBold | true, false | When true, the text will be displayed in heavier type. |
| DB Connection driver | DB Driver | Sets the database driver to use. |
| DB Connection databaseName | string | Sets the name of the database to connect to. |
| dataSourceSQL | string | SQL query providing data for this component. DB Read can accept a table name. DB Edit accepts only tablenames. |
| indexColumn | When readOnly is false, this will sets the index column of the table. Defaults to __id__ (OI automatically creates this column is using DB Write to create entries). | |
| filter | When readOnly is false, this will filter the results. Valid entries are SQL expressions. | |
| orderBy | When readOnly is false, this will set the ordering of rows. Valid entries are names of the column. | |
| readOnly | true, false | When false, allows individual fields to be edited by clicking on them. This only works when datasource is a tablename (does not work with datasource that is an SQL Query). |
| numOfColumns | int | Sets the number of columns to display. Leave at 0 to auto-detect. When non-zero, properties are added to this component to specify the format of each column. |
| column:colHeading | string | Static text for the header of this column. |
| column:dbColName | string | This sets the column name from the database table that should be displayed. |
| column:width | int | Width in pixels for display. |
| column:readOnly | true, false | When true, this will overwrite the readOnly setting from the DB Table element for this column. |
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
| Property | Values | Description |
|---|---|---|
| name | string | Provide a name for the component. This can be helpful when using the "Used Components" tool in the tag editor. |
| description | string | A space to provide a description of the current component, useful for documenting intent. |
| geometry | x, y, width, height (ints) | Sets the visible geometry of the component: x and y from top left corner, and width/height. Editing these values can be useful when positioning/sizing finer than grid snap is needed. |
| triggerTag | tag | Component will watch this tag to determine whether to trigger an action. See triggerOnEvent property. |
| statusTag | tag | Tag specifies where the component will write its status to. See details for each component. Database components write 1 for error, 0 otherwise. |
| featureIDTag | tag | Tag specifies where the featureID data will be read from. This is stored with the image in the database. |
| partIDTag | tag | Tag specifies where the partID data will be read from. This is stored with the image in the database. |
| DB Source databaseSource | DB Source | Sets which of the sources to use from the Database Sources dialog. |
| camID | string | String that will be stored with the image in the database to designate which Camera was written. Can be used later to filter. |
| imageSource | string | URL for image, or local path to image device (such as /dev/video0 ) |
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
| Property | Values | Description |
|---|---|---|
| name | string | Provide a name for the component. This can be helpful when using the "Used Components" tool in the tag editor. |
| description | string | A space to provide a description of the current component, useful for documenting intent. |
| geometry | x, y, width, height (ints) | Sets the visible geometry of the component: x and y from top left corner, and width/height. Editing these values can be useful when positioning/sizing finer than grid snap is needed. |
| hideTag | tag | When tag value is an integer other than zero, the component is not displayed on the screen. |
| refreshTag | tag | Component will watch this tag to determine when to refresh data. See refreshOnEvent property. |
| refreshOnEvent | ZeroToNonZero, AnyChange | Determines when a change in the triggerTag will be used to trigger an action. ZeroToNonzero will trigger only when the tag value goes from zero to a non-zero value, and anyChange will trigger on any change in value. |
| scaledImage | true, false | When true, scales the image to the size of the component. When false, the image is painted 1:1 scale, and any pixels outside the component area are not painted. |
| DB Source databaseSource | DB Source | Sets which of the sources to use from the Database Sources dialog. |
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 $dbIndexTagThis 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.