External Database Connection

 

In order for DMS to connect to another database, you must define the connection string to the database you want to communicate.  Note that connection strings that have been entered into DMS for access to other databases are encrypted on the DMS table for security purposes.  To define a connection string, select Interface | Import settings | External Database Connection.  
 

A window will appear with the current list of defined connection strings in a tree view:

 

 

The following tabs and commands are available from the Manufacturing Order screen:

File

1.New  To add a new connection string, select File | New.

2.Edit  To edit an existing connection string, highlight the connection string in the grid and select File | Edit or click on the edit icon.

3.Delete  To delete an existing connection string, highlight the connection string in the grid and select File | Delete or click on the delete icon.  DMS will confirm the delete.  Click OK to delete the connection string, or click Cancel to cancel the delete.
 

The external database connection definition requires three entries:

1.Connection Name  The connection name is a unique name used to identify the connection string.  The name can be up to 45 alphanumeric characters long.

2.Database Type  The database type is the type of database to connect to and can be either SQL Server or Oracle.

3.Connection String Definition  The connection string is entered for the database you wish to connect to.

4.Click Save to save the new connection string.  Click Cancel to cancel the addition of the new connection string.

 

Example connection strings for the database you want to communicate with can be found on the internet.  The connection string required will need to be provided by your IT department.  To give you an idea of what a connection string looks like, the following is a connection string for Microsoft SQL Server to connect to a database named DMS on the server named DAVE-DEV\SQLEXPRESS:

Provider=SQLOLEDB;

Integrated Security=SSPI;

Persist Security Info=False;

Initial Catalog=DMS;

Data Source=DAVE\SQLEXPRESS;

Use Procedure for Prepare=1;

Auto Translate=True;

Packet Size=4096;

Workstation ID=GATEWAY;

Use Encryption for Data=False;

Tag with column collation when possible=False

 

Note that if you are using the MSOLEDBSQL SQL Server provider (the provider is setup by DMSSetup.EXE) the Provider= parameter above should state Provider=MSOLEDBSQL;

 

The above connection string uses Windows authentication.  SSPI means "Security Support Provider Interface".  The SSPI allows an application to use any of the available security packages on a system without changing the interface to use security services. The SSPI does not establish logon credentials because that is generally a privileged operation handled by the operating system.  SSPI is equivalent to True and should be used instead of True.

 

To use an SQL user name and password, the string would appear as:

Provider=SQLOLEDB;

User ID=My_Username;

Password=My_Password;

Initial Catalog=DMS;

Data Source=DAVE-DEV\SQLEXPRESS;

 

In the above connection string, note that connection string is much shorter, relying on defaults for security settings.

 

An example of an Oracle connection string is as follows:

Provider=OraOLEDB.Oracle;

Password=dms;

User ID=system;

Data Source=Dave-Dev;

Persist Security Info=True