Skip to end of metadata
Go to start of metadata



Providing access to relational databases is at the heart of the Ignition platform. Ignition can connect to any SQL database that has a JDBC driver, though depending on the database's capabilities, some features may not be available.

By connecting Ignition to one or more SQL databases, you can query existing data, update data, store historical information, and more.

Why use Databases?

Ignition can perform many tasks without the use of a database. For instance, the Vision and OPC-UA modules let you create powerful HMI status and control screens ,or Tags can be used to generate alarms that can be sent over email. However, a tightly integrated database access is a key feature that makes Ignition stand out from its competitors.

Modern relational databases offer amazing storage and querying capabilities with great performance at a price that is incomparable to older legacy historians. While it is true that historians still have a place in the industry, for most applications relational SQL databases not only suffice, but offer much more than what was previously available. Using SQL, you can store and track production information with ease. However, you can also correlate that data to who was on shift, previous runs, downtime, inventory levels and more, naturally and easily. Make the data available to more people using the Vision module's web-launch clients, or integrate the data directly into your company's internal or external website. SQL databases are at the heart of the web and modern corporate IT systems, and now thanks to Ignition, the plant floor as well.

      


On this page...





How are Databases used in Ignition?

While connecting to a database is not required for basic status and control functionality, it can dramatically increase the possibilities that the system offers. There are a few places where databases are used in Ignition, such as historical data logging, reporting, storing alarm logs, and as your tags storage.  

Historical Data Logging

Logging data for historical analysis, either through Tags Historian or with the SQL Bridge module, requires a database connection. Databases are great at handling historical data, and by using a standard relational database your data is stored in an open format that can be used in many ways.

Reports, Graphs and Charts

The Vision module makes it easy to present data stored in databases in a variety of ways. You can quickly create charts that show performance over time, locate anomalies, detect trends, and more. Furthermore, it's important to remember that it is possible to pull data from any database that Ignition is connected to, even if the data wasn't placed there by Ignition. This means you can tie in data from other sources or areas of your company, such as pulling in inventory and staff information, as well.

Storing Alarm Logs

Store alarm information historically and examine it later for patterns or trouble spots.

Database-driven Tags

You can use a SQL database as your Tags repository. Any other Ignition system with access to the database will be able to share and contribute tags, allowing you to create highly integrated distributed systems. For example, multiple plant sites can use Tags to report current status over a secure network connection to a central corporate headquarters.

Getting Started with Databases

The first step in using a database with Ignition is to identify a database server. Many companies already have database servers maintained by their IT departments. If you do not, or wish to set up your own database server for Ignition, the Supported Databases section below offers some advice on choosing a database vendor.

Once you've identified a server, all you need to do is create a connection to that server to get up and running.

Supported Databases in Ignition

Ignition has been tested with the following databases, and can connect to them directly after installation. You can connect to other databases by installing additional JDBC drivers (the Java database connection specification), which are often provided by database vendors.

Database

Version

Full Support (sorted by our current user install base)

MySQL5.0+ for full support. Ignition can connect to 4.x, but many features such as Tags are not tested.
Microsoft SQL Server

2005, 2008, full, and express editions. Ignition can connect to 2000, but has not been fully tested.

Oracle10g, 11g, full, and express.
PostgreSQL8.0+

Limited support

Other JDBC driversDue to variances in databases, some features may not work fully through other non-tested JDBC drivers. However, it is usually possible to get full functionality though the careful use of the database translator feature.

Choosing other databases

If you are new to working with SQL databases and are trying to choose a vendor, you need to consider the following three factors:

1- Existing company usage

Many companies already use SQL databases for other purposes, and thus most IT departments already have a defined standard. Going along with your company's existing standard is usually recommended, as there will already be staff available who are knowledgeable about the system. Furthermore, you may be able to tie into your company's existing database system instead of maintaining your own.

2 - Price and Features

The fully supported databases shown above vary dramatically in price. Some systems can cost thousands of dollars, but may have a free "express" edition that will work perfectly well for your requirements. Others offer advanced features such as redundancy, which are either not offered or difficult to configure in the other systems. It is therefore important to clearly define the features and capabilities that you need.

3 - Most common among Inductive Automation users

Choosing a database that is commonly used by Inductive Automation users means that you are more likely to find examples and help in the Forum, among other benefits. The supported database list above is sorted according to our current user install base.

Installing and Connecting to a Database

Once you've identified a server, all you need to do is create a connection to that server to get up and running. See the Installing Databases and Connecting to Databases sections for details about how to install and connect to different databases through Ignition.

Database Drivers and Translators

What is JDBC?

JDBC stands for the Java DataBase Connectivity API. It is a standardized way for Java-based applications to interact with a wide range of databases and data sources. A JDBC Driver enables Ignition to connect to, and use data from, a particular database system.

JDBC in Ignition

Ignition, being a Java-based application, leverages JDBC in order to connect to a variety of data sources. This enables Ignition to offer a standardized set of functionality on a wide range of different systems and databases. This includes not only commonly-used databases such as MySQL, Microsoft SQL Server, and Oracle, but additionally other lesser-known systems as well, provided the manufacturer offers a JDBC driver for the system.

JDBC vs. ODBC

JDBC differs from ODBC (Microsoft's OpenDataBase Connectivity standard) primarily in the fact that JDBC is written in Java, and thus can be used without modification in cross-platform environments. Additionally, whereas ODBC is a complex standard that is becoming technically out-dated, JDBC is a modern, clean specification for cross-vendor database access.

Can I connect using ODBC?

While it is indeed possible to connect to an ODBC data source using the JDBC-ODBC bridge, this is generally not advised. The bridge is designed to offer a minimal amount of functionality, and is considered a "transitional solution", meaning that it should only be used when JDBC is not available. In other words, if a JDBC option is available, ODBC should not be used.

Since most commercial databases offer JDBC drivers, transition is usually as simple as recreating your database connections inside of Ignition. The lack of a JDBC connection inside of Ignition does not necessarily indicate that JDBC isn't available for your particular database. Licensing restrictions sometime prevent the inclusion of drivers with 3rd-party software. Therefore, before using ODBC, due diligence should be taken to verify that no JDBC solution is available.

Adding a JDBC Driver

To add a new JDBC driver to Ignition, do the following steps:

  1. In Gateway on the Configure page, click on Databases > Drivers.
    The Database Drivers & Settings page is displayed.
       
  2. Find the orange arrow and click on the Create new JDBC Driver... link. 

  3. In the Name field, type the full name of the JDBC driver, see the manufacturer's documentation to get the name.
       
  4. In the JAR File(s) field, specify the core Java JAR file that contains the driver, as well as any other required JARs.

  5. Use the default settings for the following properties:

    Driver Defaults and Instructions

    Driver TypeIs the brand of database. This is used for optimizations in the Gateway, if in doubt, select GENERIC.
    URL FormatIs a default value for the connect URL. This provides a hint to the format of the connect URL that this driver requires while adding a datasource connection. For example, the hint for the format can be, jdbc:dbtype://host:port/database
    URL InstructionsFree form instructions that are shown to help the user to create a connection.
    Default Connection
    Properties
    Any additional properties to add by default to the connection string.
    Connection Properties
    Instructions
    Tips about which connection properties might be useful.
    Default Validation
    Query
    The default query that is used to verify that the connection is available.

    SQL Language Compatibility

    Default TranslatorThe database translator that is used by default for connections from this driver.

           

  6. Click the Create New JDBC Driver button, located at the very bottom of the page, to create the new driver.

Database Translators

Despite the presence of a SQL standard, many database system vary in how they implement or accomplish various tasks. The JDBC driver system tries to hide these differences as much as possible, but unfortunately some differences persist.

The database translator system in Ignition navigates these differences as they apply to the system. It provides a way to define certain key operations that are commonly different between database vendors, such as creating auto-incrementing index columns, and the keywords used for different data types.

Translator Management

Database translators are managed in the Gateway from the Databases > Drivers > Translators tab. Ignition comes pre-configured with translators for the major supported databases, but you can edit and remove them, as well as create new translators. It is necessary to create a new translator only when adding a new JDBC driver for a database that does not share syntax with any of the existing translators.

Creating a New Translator

To add a new database translator to Ignition, do the following steps:

  1. In Gateway on the Configure page, click on Databases > Drivers.
    The Database Drivers & Settings page is displayed.

  2. Go to the Translators tab, find the orange arrow, and click on the Create new Database Translator... link. 
    The New Database Translator page is displayed showing a list of all the translator properties. 
     

  3. Define the tokens used with the translator properties on the New Database Translator page.

    For most of the properties, you need to define special token markers to indicate places where other values are placed. For example, the default Create Table Syntax entry looks as follows:

    CREATE TABLE {tablename} ({creationdef}{primarykeydef})

    Where:
    tablename
    creationdef, and primarykeydef are all tokens that are expanded. tablename is replaced directly with the table, creationdef is a list of columns, and primarykeydef is the phrase created by the Primary Key Syntax entry in the translator.

    The possible tokens are as follows:


    Token

    Description

    tablenameThe name of the table being created.
    indexnameThe name of the index to create, when adding a column index to the table.
    primarykeydefA clause that defines a primary key for a new table.
    creationdefThe list of columns to create in the table.
    alterdefA list of columns to add/remove/modify in the table.
    columnnameThe name of a column.
    typeThe data type of a column.
    limitThe value of the limit clause.

    Other Properties

    Limit Position

    Defines where the limit clause should be placed. Back, the limit is placed at the end of the query. Front, places it directly after the SELECT keyword.

    Column Quote Character

    All columns are created and accessed with the defined quote, which tells the database to use a specific casing, as well as avoiding collisions between the column name and database keywords.

    Supports Returning Auto-generated Keys? /
    Fetch Key Query

    Indicates whether the JDBC driver supports the return of generated keys. If the driver does not support this feature, the Fetch Key Query is used to retrieve the last key.

    Date Type Mapping

    All data typesThe keywords that are used when creating columns of the given types.
  4. Click the Create New Database Translator button, located at the very bottom of the page, to create the translator.


Monitoring Connection Status

The state or status of a database can be monitored from the Status section of the Gateway, under Status>Database Connections. The status panels show the current state and a fault message, if applicable, or throughput statistics if the connection is active.

When a connection is not available, it is re-tested every 10 seconds, and the status is updated.



In this section ...


  • No labels