Store Results in a Database

The Elections client may be configured to use a SQL database to store election results. This is useful if your station wants to create custom applications that utilize election data.

This section will show you how to set up a computer as the designated election database workstation. It also includes a list of fields the Elections client creates in the database.

Set up the Database Workstation

To set up the machine that is connected to the database:

1. Determine which ENPS workstation will send election data to the SQL database.

   This machine should not be used for other purposes during election coverage.

2. Create a new account and password on your SQL server with rights to create and update tables for the database you will create.

   You will need to this information in order to connect the Elections client to the database.

3. Ensure that this workstation is set up as a Private Channel.

   Refer to Configuring Updates Over UDP for more information. Refer to the Private Channels section of the ENPS Operations Guide for more information on private channels.

Initialize the Database Connection

Now connect the machine to the database:

1. Open ENPS and select an existing election or right-click on your Group Folder and select New Election. Then select the Election Database button in the Production ribbon.

2. Provide the database connection information using one of the following two options:

   • Option 1 - Enter the provider, server, login, password and database information directly into the appropriate fields. In the Database field, enter the desired name for your database, and select the Test button. The Elections client will prompt you to create the new database using that name.

     

   • Option 2 - Use a Data Link or UDL file, which gives you more flexibility with the types of databases that you can select. If you use this method to connect your database to the Elections client, you will need to manually create a new catalog in your database with your desired database name and assign the appropriate permissions.

     To create a Data Link file, right-click on the Windows desktop and select New > Text Document. Name the file Stats DB Connection.UDL and click Yes when you are prompted to confirm changing the filename extension.

     Double-click the UDL file to modify the Data Link properties. Fill out the fields in the properties windows. On the Connection tab ensure that the connection information is correct by clicking Test Connection.

     

     In the Election Database Updates window in the Elections client, click beside the UDL File field and then select the UDL file you created.

3. Click Test to verify that the connection to the database is working properly.

4. If you see a "success" message in the grey display box on the right, click the Active checkbox and then click OK. All of the incoming election data will now be sent to the database.

If the active election is closed, you will need to repeat the connection test and activation process to re-enable the link to the database.

If an election has an active link to the database and you attempt to open another election with a database connection, you will be prompted to disconnect the first election before you can proceed. All old database results will be removed before the new election data is added. You will then need to re-establish the database connection for the new election.

Timeout and Troubleshooting Settings

You can use the following settings in the [ElectionDB] section of the ELECTIONS.INI to control how long ENPS will wait for a response from the database and to troubleshoot connection problems:

Setting	Description
ConnectionTimeout	Number of seconds ENPS will wait for the database to respond to a connection attempt. The default value is 5.
CommandTimeout	Number of seconds ENPS will wait for the database to respond to a Create, Delete, Update or Insert command. The default value is 20.
ShowStatusForm	Monitor the connection between the ENPS client and the database by setting this value to 1. This will cause a database monitor window to open on the client workstation whenever a database connection is established.

Using the Database

Once the connection to the SQL database is active, there will be a new icon to the left of the election date. Hover over this icon to show the timestamp of the last database update.

If you want to only send races with results to the database, select Suppress Empty Races in the Production ribbon. This option is useful for automated ticker displays which might cycle through all election races in the database.

Closing the Database Connection

If the election is closed, you will see the following dialog which includes the option to remove records from the database.

If you plan to air election results in other newscasts after the election display has been closed, you should click No to keep the results in the database.

Database Fields

The Elections client stores election data in two database tables: ENPSElectionRaceProperties and ENPSElectionRaceResults. The tables below describe the fields that are stored in the SQL database. This information is useful for stations that are writing custom applications to utilize election data.

ENPSElectionRaceProperties

Field	Description
ElectionID	(adChar) String value (DBTYPE_STR)
RaceID	(adInteger) A 4-byte signed integer (DBTYPE_I4). A unique ordinal ID for each race’s arbitrary ENPS internal reference number. This ID is NOT visible within the election display and is only used for tracking purposes within the Elections application.
BasePage	(adInteger) A 4-byte signed integer (DBTYPE_I4). Value of the initial CG page which would be used with a serial connection. This is visible within the AP Election display and may also be used as a reference for advanced graphic systems.
RaceShortName	(adChar) A String value (DBTYPE_STR). This is a unique value within the ENPS election data.
RaceCGTitle1	(adChar) A String value (DBTYPE_STR)
RaceCGTitle2	(adChar) A String value (DBTYPE_STR)
RaceCGTitle3	(adChar) A String value (DBTYPE_STR)
RacePrecinctsTotal	(adInteger) A 4-byte signed integer (DBTYPE_I4)
RacePrecinctsReporting	(adInteger) A 4-byte signed integer (DBTYPE_I4)
RacePrecinctsPercent	(adSingle) A single-precision floating point value (DBTYPE_R4)
RaceLastUpdate	(adDBTimeStamp) A date-time stamp (yyyymmddhhmmss plus a fraction in billionths) (DBTYPE_DBTIMESTAMP). Date and time of last update for the race, localized to the time of the ENPS client workstation.
Groups	(adChar) A String value (DBTYPE_STR). Groups to which this race belongs. Groups are separated within this field by comma delimiters. SQL queries for items within this field are not case-sensitive.

ENPSElectionRaceResults

Field	Description
ElectionID	(adChar) String value (DBTYPE_STR)
RaceID	(adInteger) A 4-byte signed integer (DBTYPE_I4). A unique ordinal ID for each race’s arbitrary ENPS internal reference number. This ID is NOT visible within the election display and is only used for tracking purposes within the Elections application.
BasePage	(adInteger) A 4-byte signed integer (DBTYPE_I4). Value of the initial CG page which would be used with a serial connection. This is visible within the AP Election display and may also be used as a reference for advanced graphic systems.
RaceCandidateWireName	(adChar) A String value (DBTYPE_STR)
RaceCandidateDisplayName	(adChar) A String value (DBTYPE_STR)
RaceCandidateParty	(adChar) A String value (DBTYPE_STR)
RaceCandidateIncumbent	(adBoolean) A Boolean value (DBTYPE_BOOL)
RaceCandidateLeader	(adBoolean) A Boolean value (DBTYPE_BOOL)
RaceCandidateWinner	(adBoolean) A Boolean value (DBTYPE_BOOL)
RaceCandidateImageRef	(adChar) A String value (DBTYPE_STR)
RaceCandidateVotes	(adInteger) A 4-byte signed integer (DBTYPE_I4)
RaceCandidatePercentVotes	(adSingle) A single-precision floating point value (DBTYPE_R4)