XLReporter Database Manager

Database Manager - 1 -

Database Manager

Overview

The Database Manager is used to manage tables and columns in a database without needing to know

SQL programming. It can be used to create the tables required for Data Entry Forms or simply to

edit/preview the contents of a database.

The application is opened from the Tools tab of the Project Explorer by selecting Database, Manager

or from Tools, Database Manager in Template Studio.

Database Terminology

A database stores information in Tables. Each Table contains Columns and Rows (also referred to as

a Records) of data. Each Column has a name and a type, e.g., if a column called TagName is used to

hold tag names, it would be of type Text.

Columns can also be indexed to improve searches for records e.g., by indexing a column called

TagName, a search for a specific tag is greatly improved because of the efficiency added to the

database due to the indexing.

User Interface

The interface manages a Connector and each Table it contains.

Connector

At the top of the display is where either an existing connector is selected, or a new connector is

created.

Click New Connector and select Database (general database), Form (database that contains tables

used in data entry forms) or Analytic (database that contains tables for analytics.

Database Manager - 2 -

If a connector has already been defined, select it from the drop-down list to show all its tables.

The Run Database Script button is provided to run a database script file. Database script files

must reside in the Data directory of the project and have the extension .dbc.

An example script is provided called createtables.dbc which shows the CREATE TABLE syntax for the

supported database formats and data types is provided. This file can be edited in notepad or any other

text editing application.

Table

Select a Table Name. The first column shows an image of a key for any column that is indexed. A

black key indicates that the column has a unique index whereas an orange key indicates a non-unique

index. In the grid, are the Columns of the table, showing the Name and the Type.

For a new table, right click on the row to assign/unassign an index (note that this option is not enabled

for an existing table). The Database Manager supports two types of column indexes:

• Unique

Column value cannot duplicate. A search for a value in this column will return no more than

one record.

• Non-unique

Column value can be duplicated. A search for a value in this column will can potentially

return more than one record

To edit the Name, select the cell and change its content. To edit the Type, click a row and select from

the list.

Note that editing is not supported for all connectors.

The Types supported are:

• DateTime

Use for date and time stamps.

• Byte

Use for whole numbers that range from 0 to 255.

• Number

Use for whole numbers that range from -32,768 to 32,767.

• Number (long)

Use for whole numbers that range from -2,147,485,648 to 2,147,483,647.

• Text

Use for textual data. Up to 255 characters can be stored in the column.

• Text (wide)

Use for columns that store textual data in wide format. This must be used if Unicode

characters such as Chinese or Japanese are used. Up to 255 characters can be stored in the

column.

• Decimal

Use for numeric values with decimals (e.g., floating point values) with up to 7 significant

digits.

• Decimal (double)

Use for numeric values with decimals (e.g., floating point values) with up to 15 significant

digits.

• Currency

Use for currency values. It is recommended that cells linked to columns of this type are

formatted to display currency in Excel.

Database Manager - 3 -

New columns can be added to the grid by entering the information on the bottom row. Existing

columns can be deleted from the grid by right clicking the row and selecting Delete or by selecting the

row and pressing Delete on the keyboard.

With a Table Name selected, a number of table operations are provided:

• Add

Add a table to the database. When selected, the Table Name changes from a drop-down list

of tables to a text box where the new table name is entered, and the Columns grid is cleared.

Edits are not saved until Accept is pressed. Press the Refresh pushbutton to switch the

text box back to the dropdown list.

• Duplicate

Add a table to the database based on an existing table’s structure only. When selected, the

Table Name changes from a drop-down list of tables to a text box where a new table name

can be entered. The columns of the original table remain in the Columns grid as a starting

point for the new table.

• Delete

Delete the selected table in Table Name from the database.

• Preview

Open the Preview dialog to display the top 100 records stored in Table Name. If the table

has a column named DateAndTime, records are ordered by DateAndTime in descending order

(newest to oldest).

• Edit

Open the Edit dialog to display the top 100 records stored in the Table Name. Add new rows

to a table by entering the values on the edit row (last row) and clicking OK. This option

should be used with care since it will add information to the table.

• Reset

Clear all the rows from the selected table in Table Name. When selected, a confirmation of

the number of rows deleted is shown.

• Record Locking

This setting is only available for Form connectors. When this option is checked an xlrLock

column is automatically added to the table. Use this option if the table is intended for a Data

Entry Form that prevents users from editing the records once they have been locked. Once

checked this setting cannot be edited.

Table and Column Restrictions

Naming Convention

The following guidelines should be considered when dealing with databases:

• Table and column names should not begin with a number.

• Table names should not start with the letters lu.

• Table and column names cannot match the reserved words of SQL. A list of reserved words

can be found at http://msdn.microsoft.com/en-us/library/ms189822.aspx

• Table and column names should be less than 64 characters long.

• Table and column names should not contain a period (.), exclamation point (!), accent grave

(`), pipe (|), single quote (‘), double quote (“), comma (,), asterisk (*), colon (:) or square

brackets ([and]).

• Table and column names should not lead with a space.

• The column name DateAndTime is a reserved name. If you use this as a column name you

will not be able to modify or delete it once the table is created.

Database Manager - 4 -

Database Limitations

Microsoft Access

• A table should have no more than 127 columns configured as only 127 columns can be

updated at one time.

• The database is restricted to 2GB in size. Once this limit is reached no more records may be

inserted to the database until records are purged.

• Support for accdb database requires the appropriate Microsoft Access Database Engine OLE-

DB provider.

Database Manager - 5 -

Information in this document is subject to change without notice. SyTech assumes no responsibility for

any errors or omissions that may be in this document. No part of this document may be reproduced or

transmitted in any form or by any means, electronic or mechanical, for any purpose, without the prior

written permission of SyTech.

XLReporter

is a registered trademark of SyTech (dba of TheReportCompany, LLC).

Microsoft

and Microsoft Excel

are registered trademarks of Microsoft, Inc.

All registered names are the property of their respective owners.