Converting applications from Jet database to Oracle database

Edit online

If you have multiple applications that require Oracle database, you can convert the applications from a Microsoft Jet Database to a single Oracle database using Datacap Application copy tool.

Multiple Datacap applications within a single Oracle instance are differentiated by the user credentials used to access them. You can use the same Oracle database for multiple Datacap applications, as long as each unique Oracle "User" owns the schema for each application.

Note: You cannot combine schemas for multiple Datacap applications into a single Oracle schema.

Create a unique database user for each application. This will create a schema for that user.

Note: You can optionally specify a new tablespace for each user/schema. If the tablespace is not specified, by default the users are created in the system tablespace. The users can be in the same tablespace or different tablespaces.

Repeat the following steps for each user.

Log on to Oracle as one of the users created in step 1.
Navigate to ...\Datacap\support\DBScript\ and run the following Oracle scripts:
- Oracle_Adm_base.sql
- Oracle_Eng_base.sql
- Oracle_FP_base.sql

Converting TravelDocs and Flex to Oracle versions

This example illustrates how to convert TravelDocs and Flex applications to Oracle versions. For test purpose, the following server names are used. You must replace the server names, ports, etc. as applicable to your test environment.

MS Active Directory Server = WIN2K8R2A
Datacap Server (and the location of the applications) = WIN2K8R2B
Datacap Web Server = WIN2K8R2C
Datacap Dev station + Oracle = WIN2K8R2D

On WIN2K8R2D (Oracle)

In Oracle server, create a unique database user for each application, so that a schema for that user is created. Log on to Oracle as one of the users created in this step.
Note:
- The users created for test purpose are TravelDocsORA and FlexORA
- Optionally, you can specify a new tablespace for each user/schema. In this test, the users were created in the system tablespace by default.
- The users can be in the same tablespace or different tablespaces.
Navigate to the location ...\Datacap\support\DBScript\ and execute the following Oracle scripts against each database mentioned above:
- Oracle_Adm_base.sql
- Oracle_Eng_base.sql
- Oracle_FP_base.sql

Repeat the steps 1 and 2 for each application. Optionally, you can create a separate user for each of Oracle_Adm, Oracle_Eng and Oracle_FP. For example, FlexORAadmin, FlexORAengine, and FlexORAfingerprint.

On WIN2K8R2D (DEV Station)

Using command line, run the DAppCopy tool as described below to copy the TravelDocs app to TravelDocsORA app:
DAppCopy -from -an TravelDocs -af \\WIN2K8R2B\Datacap\TravelDocs -adb "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\WIN2K8R2B\Datacap\TravelDocs\TravelDocsAdm.mdb;" -edb "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\WIN2K8R2B\Datacap\TravelDocs\TravelDocsEng.mdb;" -fdb "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\WIN2K8R2B\Datacap\TravelDocs\TravelDocsFingerprint.mdb;" -how online -to -af \\WIN2K8R2B\Datacap\TravelDocsORA -adb "Provider=OraOLEDB.Oracle.1;Data Source=WIN2K8R2D;User ID=TravelDocsORA;Password=FileNet123;" -edb "Provider=OraOLEDB.Oracle.1;Data Source=WIN2K8R2D;User ID=TravelDocsORA;Password=FileNet123;" -fdb "Provider=OraOLEDB.Oracle.1;Data Source=WIN2K8R2D;User ID=TravelDocsORA;Password=FileNet123;" -how onlineDAppCopy -from -an TravelDocs -af \\WIN2K8R2B\Datacap\TravelDocs
Using command line, run the DAppCopy tool as described below to copy Flex app to FlexORA app:
DAppCopy -from -an Flex -af \\WIN2K8R2B\Datacap\Flex -adb "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\WIN2K8R2B\Datacap\Flex\FlexAdm.mdb;" -edb "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\WIN2K8R2B\Datacap\Flex\FlexEng.mdb;" -fdb "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\WIN2K8R2B\Datacap\Flex\FlexFingerprint.mdb;" -how online -to -af \\WIN2K8R2B\Datacap\FlexORA -adb "Provider=OraOLEDB.Oracle.1;Data Source=WIN2K8R2D;User ID=FlexORA;Password=FileNet123;" -edb "Provider=OraOLEDB.Oracle.1;Data Source=WIN2K8R2D;User ID=FlexORA;Password=FileNet123;" -fdb "Provider=OraOLEDB.Oracle.1;Data Source=WIN2K8R2D;User ID=FlexORA;Password=FileNet123;" -how onlineDAppCopy -from -an Flex -af \\WIN2K8R2B\Datacap\Flex

On WIN2K8R2B

Update the datacap.xml to add TravelDocsORA.
Navigate to ...\Datacap\TravelDocsORA folder and rename the Port.app to TravelDocsORA.app.
Create export and batches folders in the ...\Datacap\TravelDocsORA folder.
Install the Oracle 32-bit client.
Restart the Datacap Server service.

Repeat these five steps for FlexORA.

On WIN2K8R2D (Oracle)

In the TravelDocsORA.adminfo table, update the db_apptitle column from TravelDocs to TravelDocsORA. Update the db_index column to a different value. (For test purpose, the value is increased by 1).
In the FlexORA.adminfo table, update the db_apptitlecolumn from Flex to FlexORA . Update the db_index column to a different value. (For test purpose, the value is increased by 1)

On WIN2K8R2D (DEV Station)

Open Datacap Application Manager and verify/test the connection strings.
Test the basic connections to both applications in DStudio and DCDesktop.

On WIN2K8R2C (TMWEB.NET)

Edit the ...\Datacap\tmweb.net\apps.ini file to copy the TravelDocs section and copy it as described below.
...from...

[TravelDocs]

Debug=0

Oracle=0

... to...

[TravelDocsORA]

Debug=0

Oracle=1
Repeat this step for FlexORA.
Oracle= can be 0 (MSAccess or MSSQL) or 1 (either Oracle or DB2). Change it to 1.
Note: If the TravelDocs or the Flex sections do not exist, you must manually create them.
Restart the IIS web server.
Test the tmweb.net for both TravelDocsORA and FlexORA.