New Database Connection

Users that have the permissions to:

Connection Maintenance

will get access to maintain connections through the Databases menu in Dynamic AI.

Through Databases , connections to databases can be added, modified and deleted. Connection maintenance is a powerful Dynamic AI feature that should be given to administrators and/or developers type of users only.

During connection definition it must be decided which user or group of users, should be allowed to explore this database connection, providing that the individual user or user being member of the group have the overall right to Explore Databases .

The combination of the two types of rights combined with the user/group assignments gives a wide range of flexibility. Combined with the possibility to separate Dynamic AI administration from database user / password administration allow for further restrictions on security depending on the functionality of the background database.

The connection definition can include @aliasn parameters. This can be used to restrict the connection to a specific server depending on the user account details.

Connections can be marked READ-ONLY, which will disable Dynamic AI’s standard functionality to list-edit and insert, update and delete records through Dynamic AI forms.

It’s possible to divide the development and administration task in Dynamic AI by giving developers access to Reports created as “master Reports” from where new Reports can be developed and distributed – without the developers having a need to explore or administrate Databases . Whether this split of responsibility is efficient depends on the task, organization and sensitivity of information.

Database drivers and the connection architecture

Dynamic AI is using OLEDB or ODBC to connect to background databases. Depending on the database provider some pre-requisites such as installation of database drivers or client software might be necessary in order for Dynamic AI to function.

Some functionality might only be available with certain database providers and/or driver versions:

Connection pooling

When using ODBC drivers and connections defined through the 32-bit ODBC administration available in the control panel in Microsoft Windows, drivers can be globally set to allow connection pooling.

Connection pooling can speed-up access to background data-sources, as connections can be re-used internally (connections are only re-used if accessed with security settings similar to the existing connection).

Rollback/commit

Specifically for the Microsoft SQL Server and Oracle database connections defined in Dynamic AI, rollback/commit functionality is being utilized to give better control and error-handling to end-users using Dynamic AI list editing functionality.

Performance (Oracle client cursors)

In general OLE-DB connections perform much better than ODBC connections.

Therefore it is recommended to use the specific OLE-DB types of connections in Dynamic AI (e.g. Microsoft SQL Server) instead of ODBC definitions whenever possible.

As a general rule Dynamic AI use server-side database cursors in order to gain maximum database performance. However for data-entry tasks on Oracle database connections a client side cursor is being used for internal Dynamic AI reasons.

Security

As explained above, Dynamic AI is by default using one central database connection, having one defined user and password as the central database account for all users within Dynamic AI using Reports that uses that particular connection.

It is possible to parse username and password from the alias fields on the user account to the username and password fields on the connection ID by specifying e.g.: @alias1 in the username field and e.g.: @alias2 in the password field on the connection definition. As this however will result in eventual differences in terms of available tables and views on the connection, this combination of options must be used carefully. Another disadvantage is that database usernames and passwords will have to be separately maintained on the user account, which normally is not the preferred solution.

Licensing

It’s the responsibility of the organization using Dynamic AI to resolve and adhere to any user licensing issues to back-end database engines.

Connection Definition

From the New Database Connection in the Databases menu, the main screen for connection definition is available:

A connection definition includes the following fields depending on the type of connection:

Specific parameters for certain database providers

The fields included as part of the generic connection definition are used in different ways depending on the type of the data-source:

Microsoft Access Database

A Microsoft Access Database is a file-based database that will be located on a local or shared network drive. If the file is located on a NTFS or other type of drive that require specific user permissions, the user account that is used for the Dynamic AI IIS web must have access permissions to the file.

It is also possible to connect to Microsoft Access System databases by location the .mdw file in the “System Database (system.mdw) field.

Excel spreadsheet

An Excel spreadsheet can be viewed as a database if the sheet has been defined with range names to data tables. Obviously an Excel file is a file-based database that will be located on a local or shared network drive. If the file is located on a NTFS or other type of drive that require specific user permissions, the user account that is used for the Dynamic AI IIS web must have access permissions to the file.

ODBC Connection

ODBC for Sybase

ODBC for Sybase Adaptive Server

Oracle 8 Database

An Oracle database server is a high performance full-featured database server engine for Dynamic AI. In order for Dynamic AI to connect to the server, separate client software provided by Oracle must be installed on the Dynamic AI server. The requirement is limited to a simple Net8 type of client.

Microsoft SQL Database

A Microsoft SQL Server e.g. version 7 or later is a high performance full-featured database server engine for Dynamic AI. In order for Dynamic AI to connect to the server, the database must accept SQL Server authentication.

IBM DB2

IBM DB2 OLEDB

ODBC for Informix

MY SQL Database

Note: Connections to MySQL requires the third party Ole DB provider; “MySQLProv”

BORLAND Interbase

Note: Connecting to Interbase requires the third party component; “Easysoft IB7 ODBC”

VISUAL FOXPRO OLEDB

ODBC for Pervasive SQL

Common database tasks

The following will describe how to work with the most common tasks regarding database connectivity.

Add a database connection

To add a new database connection click on the “New Database connection” in the Databases menu.

A blank form for data-input will be presented:

Enter a unique name (ID) for the database connection; select the type of data-source from the drop-down selection list and fill the parameters as described above for the specific data-type.

Optionally the connection settings can be tested against the background database by clicking “Test Connection”.

Modifying a connection definition

If needed connection settings can be modified, e.g. when the password for the database user is being changed.

Click on the connection in the Databases menu under Explore Databases and then click on the Connection name to view the Connection Definition.

Change relevant settings and optionally test that the changes will successfully connect to the database.

As Dynamic AI references the name of the defined connection and not the specific data-type, back-end system changes can be accommodated very easily simply by changing the data-source type. An example of this is a Dynamic AI application being used with a connection ID PRODDATA connected to a Sybase database installation. A change of environment to, for instance, a Microsoft SQL Server 2000 can be done by modifying the data-source type to Microsoft SQL Server.

Even though this functionality makes the back-end transparent to end-users and proves that Dynamic AI applications are true multi-layered client/server applications, it should be noted that compatibility depends on the back-end data-sources for the following items:

Dynamic Views defined with specific features that are differently supported by the 2 back-ends.

Free condition filters, where specific back-end notation or functions is being used

Deleting a connection

Before deleting a connection, be aware that all definitions in Dynamic AI, if data-bound, will reference a connection. Deleting a connection will therefore make all related Dynamic Views, Dictionary definitions, Reports and Relationships obsolete. In order to keep integrity in the Dynamic AI application repository, deleting a connection will cascade deletion of all related definitions.

Example: 50 report Reports have been defined on Connection ID PRODDATA. Deleting the PRODDATA connection through Connection maintenance will remove the 50 Reports and they will no longer be available in Dynamic AI.

In order to get an overview of the impact of deleting a connection ID, the Reports  menu can be set to organize available Reports by DB Connection (Database Connection ID). Be aware however, that Reports might be defined on the connection ID and shared to users and groups that the administrator is not seeing in his navigation menu.

If a connection needs to be deleted, click on the connection in the Databases menu under Explore Databases and then click on the Connection name to view the Connection Definition.

In order to remove the connection, click on the Delete button at the bottom of the screen.

Impact on Report definitions when deleting connections

Be aware that all Reports using a certain connection will be removed when the connection is deleted.