Database

Configuration

See the General Object Configuration guide for assistance configuring the General tab.

Database Tab

Row Format

Record per row (Default)
Point per row

Field Selection

Field Selection defines which fields in a historical record are included in the output of the publisher. Generally this includes all of the fields retrieved from the device when historical records were collected. This option and the "Field Map" option allow customization of that list to include more fields using information in 'Tokens', or to reduce the number of fields by listing only the fields that are desired.

Protocol and Publisher Mapped Fields (Default)
Publisher Mapped Fields Only. Allow for only user defined history items to output with custom column names.
Protocol Fields Only. Allow for only user defined history items to output with custom column names.

Field Map

Field Map Data is where a Collection (the mapping of which information is stored and the column names) is entered. This will publish the fields added in the Field Map in addition to the default fields.

Configure the Field Map Data by clicking Edit Collection...

Click Add to enter the appropriate number of New Record items
Click Remove to delete any unwanted New Record items
Click Tabular Display to view all New Record items at once
Select the appropriate New Record item
Click on Field Identifier. The name of the field as published by the protocol module. Select the identifier from the Selection form when the edit icon is selected.
Click on Output Field. Enter the appropriate name or leave blank.
Click on Record ID and enter the appropriate name.
Click Close to leave dialog

Cursor Position

Select the appropriate Cursor Position per the database configuration requirement.

Client-side (Default). Some database configurations require a client-side cursor. SQL Server generally works well with a client-side cursor.
Server-side. Some database configurations require a server-side cursor.

Floating point check

Floating point numbers (numbers with a fractional part, like "1.25") use a specific format in the computer's memory. There are certain values that can't be represented meaningfully e.g.-'infinity' or 'negative zero'. Occasionally ACM will receive one of those values from a field device. Since those values can't be used in any logical fashion, databases frequently can't accept them. This option tells ACM what to do when it has one of those values.

Leave as is (May cause database insert failure if value is de-normalized).
Convert to zero.
Insert null value (Default).

Flowing Time Unit

Select the desired units for the Flowing Time value.

Native Units (Default)
Seconds
Minutes
Hours

Record Span

This setting determines whether timestamps are left as provided by the flow computer or all records are converted to leading time stamps.

Use Native Timestamp (Default)
Convert to Leading Timestamp

Provider String

Used for the connection to the database. Entering this information is currently a manual process in ACM, but the Database Provider String Builder can be used. This can be found in: Start > Programs > AutoSol Communication Manager > Database Provider String Builder. Reference the Maintenance section of the User Guide for more information.

Create Database

Default = Selected. Attempts to create a database if the named one does not exist.

Timestamp Adjust

Enter Timestamp Adjustment if needed.

Time Stamp Column Name

Default = RecordTimestamp. User-defined column name for the timestamp.

SQL Tab

Table Name

Name for the table published.

Create Table

Default = Checked. Tries to create a table if the named one does not exist.

Table Creation

The ACM database publisher will create tables if they don't already exist and if the option to create tables is enabled. When the database publisher needs to create a table, it generates a 'create table' script based on the database it is publishing to and the number and types of columns in the records to publish. The database publisher can create scripts compatible with Microsoft SQL Server, Microsoft Access, Oracle, MySQL, and Sybase.

When generating the create table scripts, the Database publisher uses the publishing record's column names and data types to create the column descriptors for the script. Once created, the $$Columns$$ token is replaced with the actual column descriptors. (Note that you can prevent the Database publisher from generating a script by using your own create table script without the $$Columns$$ token.)

Allow Table Definition Updates

Default = Checked. Tries to create a database if the named one does not exist.

Max String Column Size

Default = 50.

Create Script

Default = CREATE TABLE [$$Table$$] ($$Columns$$) . See Database Create Script Column Syntax Reference.

Insert Command

Default = insert into [$$Table$$] ($$Fields$$) values ($$Values$$)

Alter Table Command

ALTER TABLE [$$Table$$] ADD $$Columns$$ (Default)

Tokens

To add additional tokens, click on the field to bring up Property Specific option in the tool bar, click on Property Specific and select the appropriate Token under Insert Token Item.

The listing of available tokens and how they can be used is covered in the article called Substitution Tokens

Prefix Field Names

Default = Selected.

Suffix Field Names

Default = Selected.

Use Predefined Column Names

Default = Not selected. Select to use predefined column names if available, otherwise use names provided by the protocol driver.

Use Translated Values

Default = Not selected. Select to use EFM Translated values for fields instead of the raw value from the protocol. Example: Turbine instead of 0.

Ignore Database Constraint Errors

Default = Not selected. Ignore database constraint codes including 0x80040E2F, ORA-00001, ORA-02290, ORA-02291, ORA-21614, and ORA-28117.

Log Failed Inserts To File

Default = Not selected. Select to write the insert statement to a file if the database insert fails. If selected, choose the appropriate location and file name to store the logs in Failed Inserts File Name.

Contract Tab

Contract Time

Do not use contract time.
Use the configured contract time. Enter the appropriate Contract Hour and Minute.
Use the meter’s contract time (Default). Uses defined contract time per the Protocol or Meter Configuration object.

Records Tab

History Period Time

Select the time period of the record types to be written to the file.

All (Default)
Hourly
Daily
Other

Period Time

Default = 60 (minutes). If "Other" is selected above, the publisher will use this value to publish only those records that have a matching period value.

ACM User Guide