Using the APPX/ODBC Connection

Introduction:

This page is applicable to APPX version 4.1 and higher. For older versions, refer to this page

The APPX/ODBC Connection allows APPX Applications, through an ODBC Driver, to access and operate upon data stored in a non-APPX data format, such as SQL Server or MySQL. ( If you're interested in accessing AppxIO data on an APPX/Server, from a non-APPX application such as Crystal Reports or MS-Access, see APPX/ODBC Server .)

If you are connecting to Microsoft SQL Server then you will require a product registration for APPX/ODBC. If you connecting to another database (MySQL, Postgress, etc), a product registration is not required.

You must have an ODBC Data Driver installed to use the ODBC Connection. On Windows servers, the ODBC driver is usually supplied. On non Windows servers you will need to purchase a third party driver, see this page for details.

Setting up APPX/ODBC Connection:

On Windows go to the Control Panel, ODBC, 'System DSN' tab, and create or determine the Data Source Name (DSN) you want to use. Make sure you use the ODBC manager that matches the 'bitness' of your APPX installation (either 32 or 64 bit). All versions of APPX prior to and including 5.4.3 are 32 bit.

The specifics of the driver settings vary by the type of database you are connecting to (SQL Server vs MySQL, etc). If the driver offers a 'Test Connection' option, use it to confirm you can connect to the database. Alternatively, if you have an ODBC client in addition to APPX (such as WinSQL), you can use that to confirm the connection works.

On non Windows platforms, install the third party driver and any other required software as per their installation instructions. Confirm that the connection works by using 'isql' or some other software to connect to the target database.

Once you have confirmed your ODBC connection is working, you must set up an FMS group to tell APPX how to connect to the database. The FMS group tells APPX which DSN to use, what table naming convention to use, and some other details.

Entire Databases and/or individual files (tables) may be assigned to a File System Group. When you assign a FMS group to an APPX Database all files within that Database share the File System Group's specifications. You can assign an individual file to a File System Group using the Database Management's 2)File Specifications option.

As of APPX release 5.4.3 and higher, current FMS types are:

    1 - AppxIO (our proprietary ISAM).
    2 - VISION (Unix only, not actively supported)
    3 - DISAM (includes CISAM)
    4 - Sybase (not actively supported)
    5 - Oracle
    6 - ODBC (for example, SQL Server, MySQL, Postgres, etc. SQL Server requires a product registration)
    7 - APPX/NET remote AppxIO file server 
    8 - DB/2

To set up an FMS group, go to System Administration, Configuration, File System Groups, or System Admin->File Management->FMS Groups from the pull down menu on the main APPX screen.

Example:

Below is an example of a complete FMS group entry:

The FMS group name is 'sql'. The FMS Group Attributes are:

The APPX/ODBC Connection interprets the above example as follows:

• Connect to an ODBC System Data Source named "SQL 2008".
• Map file names to table names by concatenating the 3-character APPX Database ID, an underscore, 3-character APPX application ID, an underscore, and the 8-character APPX file name (Thus the DMO PROSPECT file in Database DMO becomes DMO_DMO_PROSPECT). You can use the following variables in your Table Naming Scheme: $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.
• Proxy Database ID is not required if you are connecting to SQL Server. It is used for all other ODBC connections. This is an internal DB where APPX keeps track of what rows are locked by other APPX sessions, since other RDBMS's or drivers do not support row level locking. Be aware that non APPX programs will still be able to access rows that are 'locked' by APPX.
• Log Profile is a system dependant value that designates which Log Profile to use with this FMS group. Fill this in only if you are setting up an FMS group for logging. See APPX Audit Log for details.
• Supress Null Numerics determines whether or not to disable the null numeric feature inherent in the database; check this if you want to be consistent with APPXIO behavior which does not have this capability (a zero entry cannot be distinguished from a null value in APPXIO).
• Read Uncommitted determines whether or not to read a record with an update pending which has not yet been committed. If checked, Appx will read the uncommitted changes. This more closely emulates APPXIO behaviour. If unchecked, Appx will wait for the data to be committed or rolled back.

You can enter specific identities for specific APPX users, or leave the APPX User blank. This will become the default credentials APPX will use if it cannot find an Identity record for the current user. On the next screen you specify the user id to use when connecting to the RDBMS:

The Realm can be used for systems where more than one authority (domain) can be used to authenticate a user.

You must use the 'Change Password' button to assign the password for the RDBMS to this Identity.

Assigning the FMS Group

There are 2 places you can assign the FMS group, at the APPX Database Level and in the File Specifications for each individual file.

The APPX Database Level is System Administration -> Runtime Configuration -> Database Definitions:

If you assign it at the Database level, it becomes the default FMS group for all files in the database, however, you must still go to each file you want stored in the RDBMS and set the FMS Type in File Specifications (System Administration ->File Management->Database Files):

In this example, we are specifying the FMS group at the individual file level. If we had specified 'sql' as the FMS group at the Database level, then we would only need to set FMS type of 6 here.

NOTE: If you specify an FMS group at the Database level it is not necessary to define an FMS Group with that name for type 1 files. If APPX cannot find the FMS group for a type 1 (APPX/IO) file, it will simply use the normal defaults.

Field to Column Mapping :

The APPX/ODBC Connection will pick an ODBC data type for each field. APPX queries the Data Source for a list of supported data types and picks the most closely matched data type.

Some APPX data types (such as partial time/date fields) are not supported by all ODBC data sources. In this case, APPX stores those fields in a character column.

---++ Field Overrides

If you do not want to use the APPX assigned data types you can override them using Field Overrides. For example, if you are mapping an APPX Data Dictionary over an existing RDBMS table, you will have to use the existing data types.

Field Overrides are accessed from Data File Management, File Specifications:

When you click the button, you will get a list of the fields in the selected file:

Select the field you want to override and Press Enter:

Here you can override the APPX assigned column name, the data type or whether nulls are allowed or not.

IMPORTANT: If you are changing an existing file, you should export the data first, make your changes, them import the data

Converting to and from ODBC Data Sources :

To convert an existing AppxIO file into an ODBC Data Source, Export the file, change the file's FMS type from 1 to 6, and re-Import the file.

To convert data stored in an ODBC Data Source back into AppxIO, Export it, change its FMS type from 6 back to 1 (AppxIO), and re-Import it.

Debugging :

• If you get the error '*Can't connect to server (or driver error)', the first thing to check is Identities. This is the most common mistake, either forgetting to set up the Identity for the FMS group, or entering an invalid user id/password.
• Note that APPX caches the login credentials and other data. If you need to change your FMS settings, Identities, Field Specifications, etc, start a new session to test them.
• Setting APPX_SQL_CMD={filename} and APPX_DBG_CODE=0xFFFFFFFF produces an SQL Log of all SQL traffic from APPX to the RDBMS backend. It records all commands APPX sends to the RDBMS, and all data the RDBMS sends back to APPX. Set APPX_SQL_CMD to a log file location into which APPX has write permissions. Go to Setting Environment Variables for information on how to set APPX_SQL_CMD and APPX_DBG_CODE.
• Under Windows, you can also bring up the ODBC Administrator utility used originally to set up the data source. In the "Tracing" tab, click on "Start Tracing" to start and "Stop Tracing" to stop. A trace has to be started before your APPX session has ever tried to access the data file. I.E., if APPX tries to get to the data and fails, you must completely exit APPX, and go back in, to get a trace.
• The free WinSQL utility www.imranweb.com/freesoft.htm is a great tool for debugging connections to ODBC data sources. You can attach WinSQL to any ODBC data source and browse and run SQL commands on it. It is good at passing through error messages that our APPX/ODBC connection might filter out. Thus, it's a good debugging tool if you're unable to get APPX for Windows to talk successfully to an ODBC / SQL-Server backend to APPX.

  WinSQL has an option to execute a text file containing SQL commands. So you can point it at an edited SQL_CMD log, and see how the SQL commands APPX sends to the Data Source behave under a 'thin' SQL client. Further, you can use it to massage table creation to manually set up the SQL table, if necessary (such as to modify APPX fieldnames that the ODBC Data Source might not like).

Comments:

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