Using the APPX/Oracle Connection

Introduction:

This page is for APPX version 4.x & earlier. For 5.x and higher, go to this page

APPX allows data storage in numerous storage systems, including AppxIO disk and APPX memory files, 3rd party relational systems such as the Oracle, Sybase, and SQL Server, and 3rd party DBMS systems CISAM, Vision, and VMS/RMS.

APPX files are redirected into external storage systems by means of the use of 'File System Groups', 'FMS Numbers', and 'FMS Controls'.

A File System Group defines a set of specifications unique to each storage system. For example, the CISAM storage system allows you to define key/index mapping options unique to CISAM. All storage systems allow you to override the default locations in which data is stored.

IMPORTANT: To use the APPX/Oracle Connection in APPX 3.3 and higher, you must use the "appx.prelinked" engine, in place of the usual "appx" engine.

File System Groups:

File System Groups are defined in (starting from the APPX main menu):

System Administration | Configuration | File System Groups 

Databases, Applications, and individual files can be assigned to File System Groups. When you assign a Database to a File System Group, all that Database's files by default share its specifications. File System Groups are assigned to Databases on the 2nd screen of:

System Admin | Database/Applications | Database/Applications

You can override the File System Group assigned to an individual file within:

Database Management | File Specifications 

... or ...

Appl Design | Database Mgmt | File Mgmt | File Specifications 

FMS Numbers:

File System Groups are assigned an FMS (File Management System) number. The FMS Number indicates the storage system to be used to maintain this datafiles. FMS Numbers are used in either of two places, in the 'File System Groups' editor, and in the 'File Specifications' editor.

Valid FMS Numbers are:

1 - AppxIO  (except RMS under VMS)
2 - VISION 

(from AcuCobol)

3 - CISAM 

(from Informix)

4 - Sybase
5 - Oracle
6 - ODBC 

(SQL Server, Access, etc.)

7 - AppxNET (AppxIO Database Server)

Store an individual file in Oracle:

1) Make sure you are using the 'appx.prelinked' engine. (Important!)

2) In the 'File System Groups' editor, create a File System Group with an FMS Number of '5'. Assign it any FMS Controls you wish (see below).

3) Then, either:

3.1) Go directly to the 'File Specifications' editor for each file you wish to operate in Oracle, change its FMS Number to '5', and set its FMS Group equal to the File System Group above,

... or ...

3.1) Specify via 'Database/Applications' that an entire Database must use the above File System Group, and

3.2) Go into the 'File Specifications' editor for all files in that Database and change FMS Numbers to '5'. (We will remove this requirement in a future release.)

4) Within Database Management, run 3)Create Files.

To observe APPX's interactions with Oracle when it Creates or interacts with an Oracle table, see the section on 'Debugging' below.

Move an existing AppxIO file into Oracle:

Export the file,

Change its FMS Numbers from 1(AppxIO) to 5(Oracle),

Import the file.

You can move the file back into AppxIO by changing the FMS Number to '1' and importing again.

Remember that it's necessary to change the FMS Number in both the 'File System Group' editor, and also the 'File Specifications' editor.

FMS Controls:

When you define an File System Group, or when you define a File Specification for an individual file, you can specify FMS controls. FMS Controls are interpreted by the storage system whenever you access a file associated with an FMS Group or individual file. The APPX/Oracle connection understands the following FMS controls:

table_name=<expression>;	Oracle table name
login_name=<expression>;	Oracle user name
login_pwd=<expression>;	Oracle user password

Note that a semi-colon terminates each control.
<expression> is composed from the following:

$db	maps to 3 character APPX database ID
$appl	maps to 3 character APPX application ID
$version	maps to 2 character APPX version
$file	maps to 8 character APPX file name
$uid	maps to 3 character APPX user id
$pid	maps to Unix process ID
$uname	maps to Unix user name
$ucase( <expression> )	converts <expression> to upper-case
$lcase( <expression> )	converts <expression> to lower-case

You can use these FMS control specifications to define a mapping algorithm for file names. For example:

table_name = $lcase( $appl_$file ); 

converts APPX file names to lower-case and prepend the 3-character application ID and an underscore. (For example TGL BALANCE1 converts to tgl_balance1).

The current release of APPX has a very picky FMS control parser. If your FMS control specifications are not syntactically correct, APPX hangs when you try to create a data file in that FMS group.

Controlling Oracle Login Names and Passwords:

When APPX connects to Oracle, it must provide a User Name and Password to the Oracle server. By default it provides a value of "system/manager". You must decide whether you will use a single user name to connect all users to Oracle, or you will connect to Oracle using individual user names.

Example 1 (the default):

Allow Default of Login Name = SYSTEM, Password = MANAGER

Example 2 (be explicit):

login_name=SYSTEM; login_pwd=MANAGER 

Example 3 (use a 'Connect String'. Same results as 1 and 2):

login_name=SYSTEM/MANAGER 

Example 4 (Hard coded Environment Variable):

APPX_ORACLE_CONNECT can be set to a desired Connect String.

Example 5 (everyone uses same login_name, encrypted password):

login_name=SYSTEM; login_pwd=$PWD(SYSTEM) 

Everyone uses the SYSTEM (or some other) login name.

SYSTEM must be defined as a "System ID" in: System Admin | Security | Users.

The Password is stored in: Users | User's System-ID=SYSTEM | Change Aux Password

If you lose the Password, you must delete and re-add the SYSTEM (or whatever) User ID.

Example 6 (everyone uses their own login.name and password):

login_name=$UNAME 

login_pwd=$PWD($UNAME) 

$UNAME is the user's OS User Login Identifier. $UNAME is equivalent to the "System ID" in Security/Users maintenance.

$PWD($UNAME) extracts the encrypted password stored in 6)Aux Password.

At some point in the future, we expect to be able to use the contents of environment variables (also setable thru $APPXPATH/appx.env), to allow variable information assignment into login_name and login_pwd.

Defaults:

If you do not override the following specifications (either at the FMS or individual file level), APPX uses these defaults:

Table Names:

table_name = $ucase( $file ); 

Connect String (login_name/login_pwd):

system/manager  

Debugging:

Exporting the following two environment variables (or setting them in your appx.env file) logs all SQL traffic between APPX and Oracle:

    export APPX_SQL_CMD=/tmp/sql.log
    export APPX_DBG_CODE=0xFFFFFFFF

This log provides valuable information on the interaction of APPX and Oracle.

To diagnose Oracle error messages (those starting with an '*'), it is necessary to look up the error. You can do this through http://technet.oracle.com, a free resource for which you must register. Once registered, you can full-text-search Oracle docs and technical notes. If you're doing much work with Oracle, this is highly recommended. You'll have direct access to the same tools we use to figure out ORA- errors, which are from Oracle, rather than from APPX.

See also How to Debug an APPX/Oracle Connection.

Field/Column Mapping:

The APPX/Oracle connection currently creates Oracle tables according to the following rules:

APPX Data Type	Oracle Data Type
Alpha > 5 bytes	VARCHAR(n)
Alpha < 6 bytes	CHAR(n)
Format	VARCHAR(n)
User Added, User Changed	VARCHAR(3)
Date Added, Date Changed	see Date below
Text	VARCHAR(n)
Token	NUMBER(n,0)
Numeric, Packed Decimal	NUMBER(n,m)
Numeric, Binary	NUMBER(n,0)
Logic	VARCHAR(1)
Date (CC:ss)	DATE
All other Dates	VARCHAR(n)

Limitations:

The maximum Alpha field length is 2000 characters.

This corresponds to the Oracle maximum for VARCHAR columns. In a later release we may add support for LONG columns.

FAQs

Q: How can I connect to different ORACLE_SIDs (i.e. different Oracle Servers) without having to change the ORACLE_SID environment variable?

A: Use the "login_name" clause in your FMS Control string. You can include the server id after an "@" as follows:

login_name=system/manager@MySID; 

Currently, you can only log into one Oracle server per APPX session. This means that if you want to connect to a different server, you must exit APPX and restart.

........................................................................

Q: How can I connect to an Oracle Server residing on a different platform than my APPX server?

A: APPX must be able to talk to Oracle as a local database server. To see if Oracle is running in a manner that APPX can access it, try the following:

1. From the Unix prompt, enter the Oracle SQL interface by typing:

sqlplus 

If it says 'sqlplus not found', try searching for with ...

cd /

ls -log `find . -name sqlplus -print2>/dev/null`

2. Enter the UserID and Password that you have coded into your FMS Controls for Oracle.

3. If Oracle is running, you should get an SQL prompt.

4. Type:

desc {tablename} 

... where {tablename} is the tablename mask defined in your Oracle FMS Controls. This is frequently ...

{DatabaseID}_{ApplID}_{Filename}

... all in upper case.

........................................................................

Q: What about older versions of Oracle? Can APPX connect to them?

A: APPX recommends that you update Oracle to the currently supported version. However, customers running Oracle 7.3 can find connection information here. Please note that this information is considered obsolete, and is not warranted in any way.

Comments:

Read what other users have said about this page or add your own comments.