The system supports the following databases:
The system can create, read and write Microsoft SQL Server 2005 databases with full Unicode support including multi-byte Asian languages.
To set up SQL Server 2005 take the following steps:
Install SQL Server 2005 and be sure to set up the system to use SQL Server authorization.
Install the SQL Server Management Studio.
Open the SQL Server Configuration Manager.
Select SQL Server 2005 Network Configuration > Protocols for SQLEXPRESS.
Right-click the TCP/IP node, and select Enable.
Close the SQL Server Configuration Manager.
Restart the SQL Server service.
Open the SQL Server Configuration Manager.
Select SQL Server 2005 Network Configuration > Protocols for SQLEXPRESS.
Right-click the TCP/IP node, and select Properties.
Select the IP Addresses tab in the TCP/IP dialog.
In the IP ALL section, note the port value specified in TCP Dynamic Ports.
To configure the system to use SQL Server 2005, take the following steps:
Set the Your Locale field in the Regional Options of the Control Panel to the correct locale so the system loads the correct fonts.
Set the type property in the systemDataSource and/or dataSource sections in the config.xml file to SQLSERVER_JDBC.
Set the database property in the systemDataSource and/or dataSource sections in the config.xml file to the name of the SQL Server database.
Set the server property in the systemDataSource and/or dataSource sections in the config.xml file to name of the server followed by a color (:) and the TCP Dynamic Port number or localhost:portnumber if the server is running on the same machine as the application. For example, MyServer:49189 or localhost:49189.
Set the user property in the systemDataSource and/or dataSource sections in the config.xml file to the user ID used to access the database.
Set the password property in the systemDataSource and/or dataSource sections in the config.xml file to the password for the user ID used to access the database.
Download the driver files from Microsoft at: http://msdn.microsoft.com/en-us/sqlserver/aa937724
Install the file sqljdbc.jar in the “lib” folder of the installation directory.
To use the improved memory functions of the latest driver for 2005 when using a connection string, add ";responseBuffering=adaptive" to the end of the connection string.
The JDBC driver for SQL Server 2005 and later enables the user to use integrated security so that the user id and password for the database do not have to appear in the config.xml file.
Users who want to use integrated security can add a "connect" attribute to the "systemDataSource" and "dataSource" elements like this:
<systemDataSource
name = "EdmWeb"
version = "1"
type = "SQLSERVER_JDBC"
server = "localhost"
database = "EdmWeb"
connect = "jdbc:sqlserver://localhost;databaseName=EdmWeb;integratedSecurity=true" />In addition to specifying "integratedSecurity=true" in the connection string, to use Windows security instead of a user name and password from config.xml, the following files must be in the correct folders:
sqljdbc.jar must be in the lib folder of the system installation folder. The file is included in the config/common folder of the installation folder on the installation CD image.
sqljdbc_auth.dll must be on the system path such as in the Windows/system32 folder. The file is included in the config/common folder of the installation folder on the installation CD image. For 64-bit systems, put the sqljdbc_auth.dll from the config/common/sqljdbc_auth_64bit directory to the Windows/SysWOW64 directory.
If the error "java.lang.SecurityException: sealing violation: package oracle.jdbc.driver is sealed" is in the log, then deleting the Oracle JDBC driver (ojdbc6.jar) from the "lib" folder will resolve the issue.
The system can create, read and write Microsoft SQL Server 2000 databases with full Unicode support including multi-byte Asian languages. To configure the system, take the following steps:
Set the Your Locale field in the Regional Options of the Control Panel to the correct locale so the system loads the correct fonts.
Set the type property in the systemDataSource and/or dataSource sections in the config.xml file to SQLSERVER_JDBC.
Set the database property in the systemDataSource and/or dataSource sections in the config.xml file to the name of the SQL Server database.
Set the server property in the systemDataSource and/or dataSource sections in the config.xml file to name of the server or localhost if the server is running on the same machine as the application.
Set the user property in the systemDataSource and/or dataSource sections in the config.xml file to the user ID used to access the database.
Set the password property in the systemDataSource and/or dataSource sections in the config.xml file to the password for the user ID used to access the database.
Download the driver files from Microsoft at: http://msdn.microsoft.com/en-us/sqlserver/aa937724
Install the files (mssqlserver.jar, msbase.jar, and msutil.jar) in the Jre/Lib/Ext subdirectory of the installation directory.
The system can create, read and write MSDE databases with full Unicode support including multi-byte Asian languages. To configure the system, take the following steps:
Set the Your Locale field in the Regional Options of the Control Panel to the correct locale so the system loads the correct fonts.
Be sure SQL Server was installed
with the DISABLENETWORKPROTOCOLS=0 and the
SECURITYMODE=SQL so that the system can connect to the
database using SQL Server security instead of Windows security. The
installation command should be similar to the following, though you
should have a different password for the sa user and
the instancename may be any unique name for the instance:
setup sapwd="mypassword" instancename="EDM"
DISABLENETWORKPROTOCOLS=0 SECURITYMODE=SQL
Set the type property in the systemDataSource and/or dataSource sections in the config.xml file to SQLSERVER_JDBC.
Set the database property in the systemDataSource and/or dataSource sections in the config.xml file to the name of the SQL Server database.
Set the server property in the systemDataSource and/or dataSource sections in the config.xml file to name of the server followed by the MSDE instance name or localhost followed by the MSDE instance name if the server is running on the same machine as the application. For example, server = “MyServer\MyInstance” or server = “localhost\MyInstance”.
Set the user property in the systemDataSource and/or dataSource sections in the config.xml file to the user ID used to access the database.
Set the password property in the systemDataSource and/or dataSource sections in the config.xml file to the password for the user ID used to access the database.
Download the driver files from Microsoft at: http://msdn.microsoft.com/en-us/sqlserver/aa937724
Install the files (mssqlserver.jar, msbase.jar, and msutil.jar) in the Jre/Lib/Ext subdirectory of the installation directory.
The system can create, read and write Microsoft SQL Server databases. To configure the system, take the following steps:
Set the type property in the systemDataSource and/or dataSource sections in the config.xml file to SQLSERVER.
Set the database property in the systemDataSource and/or dataSource sections in the config.xml file to the name of the SQL Server database.
Set the server property in the systemDataSource and/or dataSource sections in the config.xml file to name of the server or (local) if the server is running on the same machine as the application.
The system can create, read and write Microsoft Access 97 databases. To configure the system, take the following steps:
Set the type property in the systemDataSource and/or dataSource sections in the config.xml file to ACCESS.
Set the database property in the systemDataSource and/or dataSource sections in the config.xml file to the full path of the .MDB file.
The system can create, read and write Microsoft Access 2000 databases. To support the Access 2000 database format, take the following steps:
Create the Program Files\Common Files\Microsoft Shared\DAO folder.
Copy DAO360.DLL from the Access2K directory of the installation CD to the Program Files\Common Files\Microsoft Shared\DAO folder.
Copy JNIODBC.DLL from the Access2K directory of the installation CD to the installation directory.
Set the type property in the systemDataSource and/or dataSource sections in the config.xml file to ACCESS.
Set the database property in the systemDataSource and/or dataSource sections in the config.xml file to the full path of the .MDB file.
The system can create, read and write PostgreSQL databases. To use PostgreSQL, set the type property in the systemDataSource and/or dataSource sections in the config.xml file to POSTGRESQL_JDBC.
Connection strings may also be used of the form:
jdbc:postgresql://hostname/databasename?user=myusername&password=mypassword&stringtype=unspecified"
Enterprise Data Manager supports the Oracle database for use as the central database. The user must create the database before installing Enterprise Data Manager.
To use Oracle as the central database, take the following steps:
Create an Oracle database (usually named EdmWebHQ).
Create a user on the new database with DBA privileges.
Copy the contents of the installation CD to a temp directory on the hard drive.
Use Notepad to edit
data\config\config_central_app_version.xml
in the temp directory and change the systemDataSource section
so that it looks like the following:
name = “EdmWebHQ”
type
= “ORACLE”
database = “databasename”
server
= “servername”
user = “username”
password
= “password”
then close and save the file.
Run the setup_central_app_version.bat file in the temp installation directory to start the installation.
If desired, you can create another role for normal users to select, insert, update, and delete records in the database by taking the following steps:
Log into the EdmWebHQ database using the dba user id and password.
Create a role called RMUSER.
Grant privileges to RMUSER for each table in the database.
To grant priveleges on the system tables you could use the following:
grant select, insert, update, delete on CDMAUDIT to rmuser;
grant
select, insert, update, delete on CDMLOCATION to rmuser;
grant
select, insert, update, delete on CDMLOCATIONGROUP to rmuser;
grant
select, insert, update, delete on CDMREMOTETABLES to rmuser;
grant
select, insert, update, delete on CDMREMOTEFILES to rmuser;
grant
select, insert, update, delete on CDMVALUE to rmuser;
grant
select, insert, update, delete on CDMPACKAGE to rmuser;
For application tables, you would also grant priveleges as follows:
To grant priveleges on other application tables, use a grant statement like the following:
grant select, insert, update, delete on tablename to rmuser;
Assign data entry personnel to the RMUSER role.
To use Oracle as the central database using the Oracle JDBC connection, take the following steps:
Create an Oracle database (usually named 'EdmWebHQ').
Create a user on the new database with DBA privileges. (This may be the same name of 'EdmWebHQ'.)
Copy the contents of the installation CD to a temp directory on the hard drive.
Use Notepad to edit
data\config\config_central_app_version.xml
in the temp directory and change the systemDataSource section
so that it looks like the following:
name = “EdmWebHQ”
version = “1”
type = “ORACLE_JDBC”
database = “databasename/tablespace name”
server = “servername”
user = “username”
password = “password”
sid = “SID
-- such as "orcl"”
port = “port of
the database instance -- such as "1521"”
then
close and save the file.
Run the setup_central_app_version.bat file in the temp installation directory to start the installation.
If desired, you can create another role for normal users to select, insert, update, and delete records in the database by taking the following steps:
Log into the EdmWebHQ database using the dba user id and password.
Create a role called RMUSER.
Grant privileges to RMUSER for each table in the database.
To grant priveleges on the system tables you could use the following:
grant select, insert, update, delete on CDMAUDIT to rmuser;
grant
select, insert, update, delete on CDMLOCATION to rmuser;
grant
select, insert, update, delete on CDMLOCATIONGROUP to rmuser;
grant
select, insert, update, delete on CDMREMOTETABLES to rmuser;
grant
select, insert, update, delete on CDMREMOTEFILES to rmuser;
grant
select, insert, update, delete on CDMVALUE to rmuser;
grant
select, insert, update, delete on CDMPACKAGE to rmuser;
For application tables, you would also grant priveleges as follows:
To grant priveleges on other application tables, use a grant statement like the following:
grant select, insert, update, delete on tablename to rmuser;
Assign data entry personnel to the RMUSER role.
The system can create, read and write dBASE IV (.dbf) files using the Microsoft ODBC driver. To configure the system, take the following steps:
Set the type property in the systemDataSource and/or dataSource sections in the config.xml file to DBASEIV.
Set the database property in the systemDataSource and/or dataSource sections in the config.xml file to the full path of the folder containing the .DBF files.
The system can create, read and write dBASE IV (.dbf) files using the DataDirect ODBC driver. To configure the system, take the following steps:
Set the type property in the systemDataSource and/or dataSource sections in the config.xml file to DBASEIVDD.
Set the database property in the systemDataSource and/or dataSource sections in the config.xml file to the full path of the folder containing the .DBF files.