Database

Values a factory system keeps in a database table (SQL Server, PostgreSQL, MySQL and MariaDB, SQLite, or any database through ODBC) read as tags, and written back where a tag is told to: the Database and Sign-in cards, a tag that reads a cell by its table, column and row or runs a query of its own, the two ways a tag writes, the scan that offers a tag per column, four worked examples and what the driver does not do.

The Database driver reads the values a factory system keeps in a database table: the production order the MES holds, the recipe or the setpoint the ERP keeps, the result another test system recorded. Each tag reads one value of one table, and its values land where every other tag's do: in Logic, on dashboards, in the runs of a Process test bench. Where a tag is told to, the driver also writes back: the cell the tag reads, or a command of the tag's own.

SQL Server, PostgreSQL, MySQL and MariaDB, and SQLite are reached with nothing installed on the computer. Any other database (Oracle, Access, Firebird and others) is reached through its own ODBC driver, which has to be installed on the computer in its 64-bit version.

Reading is the main use, and its path is short: a device says where its database is and how the station signs in, the scan offers one tag per column of its tables, and every tag is born read only. What belongs to writing appears on a tag only once its access lets the station write it.

A device of this driver is one database: two databases of the same server are two devices. The devices sit under the driver and may be filed in folders, Driver → Folder → Device → Tag, as under any driver without a serial line or a broker.

The driver's page

The line under the header reads "Each device reads one database. Add a device, enter where its database is and how the station signs in, then scan it to add its columns as tags." For Configure connector, the page holds the driver's own Scan scope card, with the device whose database the scan reads (see Discovery). Under it stands the Devices table of any driver (see The device table), grown by Add device at the right of its title; empty, it reads "No devices yet." The driver's bar carries Discover, greyed until the Scan scope card is complete. The driver is listed under Files and databases, beside the File driver.

Device

A device of this driver has no Connection card: the Database card stands in its place, followed by the Sign-in card on every database but SQLite, which has no sign-in. The Poll interval (ms) of the Device card, 1000 by default, says how often the tags read the database, each tag able to override it as on any driver.

A new device is a SQL Server one with no server: it reads "Server not entered yet", its page says "The station does not reach this device until it says where its database is, so nothing is read from it yet and there is no error to show. Enter it in the Database card below (the server and the database name, the file or the data source) and the station connects to it." and nothing is read until it does. The Base URI the header shows is the runtime address, which never holds the user or the password: database://sqlserver?server=mes-sql&instance=SQLEXPRESS&database=MES for a SQL Server instance, database://sqlite?file=C%3A%5CData%5Crecipes.db for a SQLite file, database://odbc?source=ERP for an ODBC data source.

The Database card

A field the type picked does not read is not drawn, and keeps its value for when it does. Picking another type empties a port left at the old database's own port, so the new database is reached on its own. The Server box reads apart what database tools write together:

While the card does not say where the database is, the device is saved as it is and waits, its dot grey, nothing read and no error shown:

A setting the card cannot take is said under the cards, and the device is not saved until it is corrected:

The save holds the same rules, and the feed says so when it refuses one: "Device 'MES' has a database setting its driver cannot use. Open it and correct the field its panel marks."

The Sign-in card

Every database but SQLite carries the Sign-in card, with the user the station signs in with.

PostgreSQL and MySQL have no Encryption field: their connection is encrypted whenever the server offers it. A SQL Server whose certificate is its own, as a factory server's often is, refuses the connection with the database's words about a certificate chain issued by an authority that is not trusted: Encrypted, any certificate accepted is the choice that server asks for.

Under its fields the card says what user to ask for: "Ask the database's administrator for a user with only the permissions the station needs: reading, and writing only to the tables its tags change." The station reads and writes another system's data with whatever its user may do, so that user is the real limit of what the station can change. Every write is written in the journal, as on every driver.

A server signed in to with a user and no user entered yet says instead "Enter the user the station signs in with. Until then the station does not connect this device." That is not a refusal: the device is saved, and its error says no database user is entered yet until the user is there.

Online, waiting and offline

Each device holds one connection of its own, opened when it connects and never shared or kept in a pool, so a connection a server dropped is never used again.

Prepare the server

The three network databases come closed to another computer in common installations. Each of these settings is made on the server, by whoever administers it; the station changes none of them.

Every one of them also needs the database's port allowed in the firewall of the server's computer. When the server answers one of these ways, the device's page says which setting it is, with these steps and the values of this device (the port, the instance, this station's address as the server saw it, the pg_hba.conf line and the MySQL account, each with a copy button), in place of the database's English error. The notice goes away by itself once the device connects, or when the server answers another way. The journal and the agent read the same steps.

Tag addressing

How a tag reads

A tag of this driver has no address box of its own: its Source card asks how it reads.

A new tag reads Table and column, by its key, as a Double. The types offered are Boolean, Int32, Int64, Float, Double, String and DateTime. Reading a cell, a whole-number tag refuses a value with a fraction rather than rounding it (writing is the other way, see Writing), and a text reads as a number only when it is one. Every tag is born Read only; what writing adds is under Writing.

Table and column

A name is read the way the database reads it:

The driver puts every part of a name in the quotes its database takes, so a name is never pasted into a statement as text. A tag whose fields cannot work is not saved, and its page says what it lacks:

The read-only Source address (wire) line shows what the driver receives: row?table=dbo.ProductionOrders&column=Quantity&key=OrderNo&equals=4711 for a row by its key, newest?table=recipe_setpoints&column=oven_temperature&by=updated_at for the most recent row.

Own query

The query is read the way the database reads it: what stands inside a quoted text, a quoted name or a comment is not a word of the statement. A query that does more than read is refused where it is typed:

The wire address of a query tag is query?sql= followed by the query, percent-encoded.

Reading

A failed read keeps the tag's last reading, which counts as failed once it is older than its poll interval plus 10 s, as on every driver. The database is asked at every read: a change another program makes is seen at the next read, never the instant it is made.

Writing

A tag writes only once its Access is Read and write or Write only. Its Source card then adds what a write does:

A Write only tag that runs its write command reads nothing, so its Source card holds the write alone.

A command the driver cannot run is refused on the tag's page:

@@ names, the functions of SQL Server, and the argument names of an EXEC (@speed = @value) are not values and are left as written. The save holds the same rules, and the feed says so when it refuses a tag: "Tag 'Order quantity' does not say where its value is in the database, or has a query or a write command its driver refuses. Open it and correct the field the tag panel marks."

The wire address adds the write: row?table=recipes&column=setpoint&key=code&equals=R-100&write=cell for a cell, none?write=command&command= followed by the command for a Write only tag that runs its command.

Discovery

The Scan scope card

For Configure connector, the driver's page carries its Scan scope card, under the line "Signs in as the device does and offers a tag for each column of its tables, at the most recent row or at the row of the smallest key."

A driver with no device holds only "A database is scanned through a device, which holds its sign-in. Add a device of this driver and say where its database is." What the scope still lacks is said in red under the card, and Discover stays greyed until it is complete:

When the device to scan has an edit that cannot be saved, the scan does not start: "Discovery was not started because the edit of the device to scan could not be saved."

What the scan offers

The scan signs in as the device does, reads the database's own list of its tables and views (the system ones left out) and their columns, and says which table it reads, such as "Reading the columns of recipes…". It offers one candidate, the device itself, marked Already configured: its card lists the columns no tag of the device reads yet, ticked, and Add tags, before Open, adds the ticked ones to the device, read only.

The tags are named after their table and column (recipes.oven_temperature), typed by the column's type, and every one is Read only. A scan reads at most 100 tables and 500 values; one cut there says "The scan of Recipes stopped early. A scan reads at most 100 tables and 500 values: give a table name or a pattern to see the others." The rest of the scan's behaviour is on Discovery.

A device's own page does the same without its driver's page: Discover tags, on the head of its Tags card, signs in as the device does, scans its database as it is saved and offers only the columns it has no tag for yet (see Discovering a device's tags).

Example: a production order from SQL Server

The MES keeps its production orders in SQL Server, in the table dbo.ProductionOrders of the database MES, one row per order number:

1. On the driver's page, Add device; name it MES. On the Database card keep SQL Server, enter Server mes-sql (or mes-sql\SQLEXPRESS for a named instance), leave Port empty and enter Database name MES.
2. On the Sign-in card enter Username station_reader and its Password, or turn on Sign in with the station's Windows account. For a factory server whose certificate is its own, pick Encrypted, any certificate accepted.
3. Add tag Order quantity, Int32: Table dbo.ProductionOrders, Value column Quantity, Row to read By its key, Key column OrderNo, Key value 4711. Its wire address is row?table=dbo.ProductionOrders&column=Quantity&key=OrderNo&equals=4711.
4. Add tag Product, String, on the same table and row, Value column ProductCode: both tags are read in one query.

With the tools:

device_upsert name=MES driver=Database databaseKind=SqlServer host="mes-sql\SQLEXPRESS" databaseName=MES username=station_reader password=<password> databaseEncryption=TrustServerCertificate
tag_upsert device=MES name="Order quantity" opcUaType=Int32 databaseTable=dbo.ProductionOrders address=Quantity databaseRow=Key databaseKeyColumn=OrderNo databaseKeyValue=4711
tag_upsert device=MES name=Product opcUaType=String databaseTable=dbo.ProductionOrders address=ProductCode databaseRow=Key databaseKeyColumn=OrderNo databaseKeyValue=4711

Example: a recipe setpoint from PostgreSQL

The recipe system adds a row to recipe_setpoints every time a setpoint changes, dated in its updated_at column:

1. Add device; name it Recipes, Database type PostgreSQL, Server recipes-db, Port empty (PostgreSQL's 5432), Database name recipes, then Username and Password on the Sign-in card.
2. Add tag Oven setpoint, Double: Table recipe_setpoints, Value column oven_temperature, Row to read Most recent, Date column updated_at.

Each new row is read at the tag's next read. PostgreSQL reads a plain name in lower case, so a column created with capitals, such as OvenTemp, is written in quotes: "OvenTemp". With the tools:

device_upsert name=Recipes driver=Database databaseKind=PostgreSql host=recipes-db databaseName=recipes username=station password=<password>
tag_upsert device=Recipes name="Oven setpoint" opcUaType=Double databaseTable=recipe_setpoints address=oven_temperature databaseRow=Newest databaseDateColumn=updated_at

Example: a result line written as an INSERT

The quality system wants one row per measured part in its table test_results:

1. On the device of that database, Add tag Result, Double, Access Write only.
2. A write Runs its write command, Write command INSERT INTO test_results (result, recorded_at) VALUES (@value, @now).

Each value the Logic writes to Result becomes a row of its own, with the station's date and time beside it; writes that follow each other quickly are all kept, in order, none dropped for a newer one. A Read and write tag reading a cell would instead change its one row with every write. With the tools:

tag_upsert device=Quality name=Result opcUaType=Double accessType=WriteOnly databaseWriteMode=Command databaseCommand="INSERT INTO test_results (result, recorded_at) VALUES (@value, @now)"

Example: Access and Oracle through ODBC

When the data source or the driver a device names exists only for 32-bit programs (an Access of a 32-bit Office, a data source made in ODBC Data Sources (32-bit)), or not on this computer at all, the device's panel says which of the two it is, with the name the device gave, and gives the whole instruction while the device is on and not connecting. The device's error says the same in English, and so does driver_capabilities, under the Database driver's prerequisite (perDevice true):

The notice ends with the 64-bit ODBC drivers this computer has, by the name Driver={...} takes. A data source or driver the 64-bit side has, and that still fails, shows the driver manager's own error.

Through ODBC a plain name reads as the database reads it (in capitals on Oracle) and a name in quotes exactly; dates and times are sent to the whole second, the finest every ODBC driver takes. With the tools:

device_upsert name="Plant book" driver=Database databaseKind=Odbc databaseOdbc="Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\Data\plant.accdb"
device_upsert name=ERP driver=Database databaseKind=Odbc databaseOdbc=ERP username=STATION password=<password>

Agent tools

driver_capabilities describes this driver under database: the simple path, each database with its fields and its own port, the encryption choices, the two ways a tag reads and the two ways it writes, the rule that a value enters a statement only as @value or @now, the types, the timeouts, the advice on the database user, discovery and four worked examples. device_upsert driver=Database takes databaseKind (SqlServer, the default, PostgreSql, MySql, Sqlite or Odbc), and for a server host (a SQL Server server\INSTANCE or server,port is read apart), port, databaseName, and on SQL Server databaseInstance, databaseWindowsSignIn and databaseEncryption (Verified, TrustServerCertificate or Off); databaseFile for SQLite, databaseOdbc for ODBC; username and password, which is never answered back. A device it returns carries database, with the address and whether the device still waits for its user. tag_upsert takes the value column as address, databaseSource (Fields or Query), databaseTable, databaseRow (Key or Newest), databaseKeyColumn, databaseKeyValue, databaseDateColumn, databaseQuery, and once accessType writes, databaseWriteMode (Cell or Command) and databaseCommand; a new tag is read only and a Double unless opcUaType says otherwise. device_discover driver=Database takes databaseDevice, the name of a configured device of the driver, and optionally databaseTables, a table name or a pattern with *; the tags the device lacks are added with tag_upsert. connector_browse returns each device's databaseKind. The other tools are on Agent tools.

Limits worth knowing

• The driver reads and writes the values of its tags, one value per tag. It does not record the station's runs in a database: a row per run with its serial number, verdict and measures is not something this driver writes.
• No ODBC driver ships with the product. Oracle, Access, Firebird and every database but the four reached directly need their own 64-bit ODBC driver installed on the computer; a 32-bit one is not seen.
• The device stores no Windows password: the Windows sign-in to SQL Server, and a SQLite file on a network share, use the account the station runs under, so the database or the share has to let that account in.
• A query only reads. Whatever changes the database is a write, and a write happens only on a tag whose access writes.
• A value never enters the text of a statement: the value written is always the @value parameter, the station's date and time @now, and a key value a parameter as well. A statement that names any other value is refused.
• A cell write changes exactly one row; an UPDATE command needs its WHERE.
• The database is asked at each read: a change another program makes is seen at the next read, and a database that holds many tags of one row is asked once per pass for all of them.
• A binary column is not read.