6.4.Configuring a Database Table

After you have set up a DSN you can create a table or select an existing one, and then assign points and properties to the columns of the table.

Table Selection or Creation

You need to either select an existing table, or create a new one.

Add New Rows or Modify the Same Row

For any given table, whether existing or newly-created, you will need to decide whether you are going to add new rows, or modify the same row with new data each time it changes.

Assigning a Key

You have the option to make one column, often the first column in a table, a key column.

[Note]

Due to a Windows bug, in the first column the interface won't respond unless you click directly below the text of the column name. We are working to resolve this.

Assigning points and properties

When you have selected a table, or you have at least one column in a table you are creating, you can assign points and their properties to the various columns.

Item

First, choose a point from the point-picker list on the right. Then click in the Item row and select <point> from the drop-down list. Optionally, you can type in the name of the point. Leaving the Item blank allows you to choose the Property of clock to display the system time, or clockms to display the number of milliseconds after the second of the system time.

Property

Select which property of the point you want written to the database in this column:

name

The name of a the point shown in the Item field.

value

The value of the point shown in the Item field.

quality

The quality of the point shown in the Item field.

timestamp

The time stamp of the point shown in the Item field. This will include the milliseconds, but many databases, such as MS Access, ignore the milliseconds and store only the seconds. Other databases such as MySQL and MS SQL Server include the milliseconds in a time stamp. For example:

    Databases like MySQL and MS SQL Server:

    Column A
    Enter for Propertytimestamp
    Enter for Data Typedatetime or timestamp or date

    Databases like MS Access:

    Column AColumn B
    Enter for Propertytimestamptimems
    Enter for Data Typedatetimenumber or integer
timems

The millisecond component of the timestamp, generally used in conjunction with timestamp. You only need this if your database cannot store the millisecond component of timestamp. timestamp.

timestampUTC

The same as the timestamp, but in UTC time. You can use timems in conjunction with this as well.

clock

The current system time. This will include the milliseconds, but like timestamp (above) many databases ignore the milliseconds and store only the seconds.

clockms

The millisecond component of clock, generally used in conjunction with clock. Like timems (above), you only need this if your database cannot store the millisecond component of clock.

[Note]

Using clock and clockms - some tips:

    You must leave the Item field blank to select either of these options.

    Example:If the time is 12:34:56.789, clock will be written as 12:34:56.789 in databases that accept milliseconds, and as 12:34:56 in databases that do not. A clockms property will be written as 789 in all databases.

    The clock and clockms properties allow you to log the system time as a column in the table, so that your record can contain the system time along with a number of different point values, for example:

    System Time      Point1      Point2      Point3
    08:12:56.000     43.883       3.727     213.905
Data Type

The data type that the database should associate with the property.

Data Size

In some cases this is entered automatically, in other cases it is not used, but sometimes it is possible or necessary to enter a size, such as the number of characters in a text string, or the number of bytes.

Transform(x)

This allows you to modify the entry or to insert a text string. For example:

    (x * 100) + 25 could be used to multiply a point value by 100 and add 25 to the result.

    "Tank Level" would insert the string Tank Level instead of, say, the point name.