APPX and Oracle
Release 4.1 of APPX contains a number of enhancements when using APPX with RDBMS systems, such as Oracle. They include partial record I/O, table caching and better control over when transactions are committed to the database. This page discusses how to make the best use of the enhancements. Note that RDBMS support is an extra cost feature of APPX, and you will require a new registration that activates this feature.
Configuring APPX and Oracle
The first step is to configure APPX to work with your RDBMS. In this example, we'll use Oracle. As of release 4.1.3, Oracle is the RDBMS that works with the new changes, but other RDBMS's are planned.
First, you have to install Oracle. The installation is generally simple, if you know what you're doing. If you haven't installed Oracle before, you'll need help. One book we recommend is 'Oracle 8i for Linux Starter Kit', ISBN 0-07-212442-3. This book also includes Oracle for Linux.
Once you have Oracle installed, you can begin configuring APPX to use Oracle as it's RDBMS. APPX 4.1.3 is compatible with Oracle 8. The engine will run against an Oracle 7 database, but the Oracle 8 client must be used. You must also set the environment variable APPX_ORACLE7_SERVER=1 so that the APPX engine will know how to properly access the database.
There are two environment variables that are required to run Oracle. By the time you have installed Oracle, you will know what these variables should contain.
ORACLE_HOME = <path name where oracle is installed> ORACLE_SID=<database name>
You can set these in the $APPXPATH/appx.env file.
Next, you must use the proper APPX engine. In your /linkable directory, you should see a file called 'appx.oracle'. Replace your existing 'appx' engine with this engine instead. Remember that for Unix systems, the engine must be owned by user 'appx' and have the sticky bit set. If you don't have a linkable directory, or an appx.oracle engine, then you can download it from the downloads section. Download the 'linkable' for your platform and install it using the 'install.sh' script. It is not necessary to reinstall the main APPX environment if you are just installing the linkable.
Troubleshooting note: If APPX complains about a missing library when you try to run it, make a note of the library name and search your system for that library. Chances are it is there, just not in the location APPX expects. Once you find it, try copying it to the /lib directory (or wherever libraries are kept on your system).
Once in APPX the first step is to create a File Management System group for the Oracle database. From the startup menu, select System Administration, then Configuration and File System Groups. Add a new group. You can call the group anything you like. The FMS type for Oracle is 5. You can press help on this field to see the type for the other supported databases. You can leave the path blank. Once you press enter, you will be prompted for the following:
Click for larger image
The only thing that must be filled in is the table naming scheme. Table Naming Scheme is the field used to set the way the tables will be named in Oracle. Since each table must have a unique name in Oracle and APPX allows you to have the same file name in multiple companies, you must have a way to identify the files in each company. Oracle does not support multiple databases and all file names are stored in a single Oracle table.
For example, if your APPX installation has two companies, like a production and test database, and each has a CLIENT file pointing to an Oracle table, Oracle would not recognize one from the other. To Oracle, CLIENT is CLIENT. On the APPX side, something must be done to make the two unique to Oracle. The Naming Scheme is one way to do that. You can type an identifying scheme, or select the Hint option to the right side of the screen. The Hint option will display a few common naming conventions from which you can choose.
The remaining fields would be of more interest for an Oracle Administrator. We can leave them blank. Once you press Enter, the FMS group is saved.
Next, call up the FMS group in change mode. We have to set the identity for this FMS group. Identities are used to access external databases like Oracle, Sybase, or APPX/NET. If no Identities are entered at all, APPX will attempt to make the connection using the system id of the current user. The password will be taken from the user's Auxiliary Password entry.
An Identity can be entered for each user in APPX. Enter the APPX user id and, if you would like, an expiration date. There is also a flag to indicate if the Identity is active or not. When enter is pressed a screen will pop up that will allow you to enter the login id and password to be used for the connection. An Identity with no user id can be created as a kind of global identity. If APPX cannot find an entry matching the user id then the blank user Identity information will be used.
The FMS Group is now set up. When files are defined in APPX, the FMS group can be specified so that the file will be created as an Oracle table.
Go to the Database Management Menu and select the database and application you want to use. If you have data you want to import into Oracle, then first export everything from the APPXIO tables.
Then choose File Specifications and begin entering the FMS type and name you defined earlier. Note that APPX does not support consecutive or one-record files in the Oracle interface. Those types of files must be left as APPXIO files.
You can also override other attributes on a file by file basis via the File Attributes popup, and you can override APPX's default selections for column names and field types via the Field Overrides popup. Once you have set the FMS groups on all desired files, run 'Create Files'. This will create the empty tables in Oracle.
Once that is done, you can run 'Import' to load the data you exported previously.
Do you have a tip you want to pass on? Contact Us.