DSN=local_sql_server; UID=sa; PWD=;: 
     table_name=$lcase($appl_$file):
     proxy_db=LOK:

• Connect to an ODBC System Data Source named "local_sql_server". Provide a UserID of "sa", and no password.
• Map file names to table names by concatenating the 3-character APPX application ID, an underscore, and the 8-character APPX file name, then converting the result into lowercase. (Thus TAR CUSTOMER file becomes tar_customer.)
• Create proxy lock files in Database 'LOK'.

The FMS Control parser is very unforgiving. Syntax errors will likely result in an APPX infinite loop. This will be improved in a future release.

System Data Source (DSN):

ODBC requires a specification identifying the System Data Source, using one of the following formats:

     DSN=<data_source_name>:
     DSN=<data_source_name>; UID=<user_id>:
     DSN=<data_source_name>; UID=<user_id>; PWD=<password>:

For example, to connect to an Access data source, you would provide ODBC with a specification such as:

     DSN=Access-Accounting;: 

To connect to an MS SQL-Server Database (which requires a user name and password) you might specify:

     DSN=sql_server; UID=bobby; PWD=ybbob;: 

To connect as the SQL-Server administrator (with a blank password):

     DSN=local_sql_server; UID=sa; PWD=;:
    

<a name="Table Names">

</a>

Table Names (table_name):

When you define a File System Groups, you must specify 1)FMS Controls. The APPX/ODBC Connection understands the following FMS controls:

     DSN=<expression>:         - specifies ODBC Data Source
     table_name=<expression>:  - specifies ODBC table name
     proxy_db=<expression>:    - specifies proxy locking Database

Note that required colons terminate each <expression>. <expression> may use the following 'variables':

     $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 ); 

... will cause us to convert APPX file names to lower-case and prepend the 3-character application ID and an underscore. (For example "TGL BALANCE1" will be converted to "tgl_balance1").

If your ODBC Data Source Name (DSN) contains multiple Databases, try opening the file from APPX. Data Sources that support multiple Databases will sometimes pop up an interactive box asking you which Database you wish to operate upon.

The current release of APPX has a very picky FMS control parser. If your FMS control specifications are not syntactically correct, APPX will hang when you try to create a data file in that File System Group. Sorry.

</a>

Row Locking (proxy_db):

Because most ODBC compliant Databases do not handle "intent" record locks, the APPX/ODBC Connection manages Row Locking external to the underlying Database.

When you read a record (row) with Hold, APPX locks (by 'intent') that record for rewrite or deletion, preventing other APPX users from locking the record.

To manage locking among multiple users, APPX uses its own 'proxy' Database. Define the following in your FMS Control specification:

     proxy_db=<3-character Database ID>:

(note the ':' is required). For example:

     proxy_db=LOK:
 

This tells APPX to use an APPX Database 'LOK' to build proxy locking files. This Database should not be the same as your operating database. It should be a name otherwise unused. 'LOK' is a good choice for a proxy database. Proxy locking files are located in directory:

     $APPXPATH/<proxy_db_id>/<application>/Data/FILE.dat

</a>

• Setting APPX_SQL_CMD={filename} 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 this variable to a log file location into which APPX has write permissions.

APPX_DBG_CODE=0xFFFFFFFF must also be set to enable APPX_SQL_CMD logging. Go to Setting Environment Variables for information on how to set APPX_SQL_CMD and APPX_DBG_CODE.

• The free <a name="WinSQL"> WinSQL utility </a>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).

Limitations:

The APPX/ODBC Connection as of APPX releases 4.0.9 and higher has been tested and is supported using the Microsoft NT Server, and the MS SQL-Server and Access ODBC drivers. The ODBC/Excel interface is not fully functional, due to restrictions within the Excel/ODBC interface. Additional ODBC drivers will be tested as market conditions dictate.

This version of the APPX/ODBC Connection will not restructure tables stored in all ODBC Databases. This version will restructure MS SQL-Server tables.

The error reporting mechanism has not been completed. Typical errors such as "End of File", "File Not Found", and "Already On File" are handled properly. Unusual conditions such as "Out of Space" or "Connection Failure" are reported in a Windows dialog box.

APPX/ODBC connection does not work in APPX 4.0.a. It works properly in 4.0.9, and the 4.1 releases and above

Issues :

• Importing a Database in SQL-Server format requires me to define the pathname in the optional path option of each file, when the Portdata directory is not at the standard place. It's not working when the pathname is defined in the File System Group.

  ==> The problem may be that your AppxIO files are stored in a non-standard path. When you export these AppxIO files to portable form, they also end up in the non-standard location.

  When you then try to import them into SQL-Server, you are using a File System Group which does not point to the actual Portdata location.

  When APPX tries to find your portable files, it is looking for AppxIO Portdata files (which have an FMS Type of '1') using the FMS Name that you provided for the import (say, SQLSERVER).

  To get around this problem, define a File System Group with type='1', and a name of 'SQLSERVER' (or whatever your relational File System Group is called). Then specify the non-standard path in that type '1' FMS type.

  For example, suppose the original AppxIO files were in the /AppxData/locn tree. Portdata exported files would be created in that tree.

  The 2nd entry below would allow the Portdata files to be read from that tree when Importing into the ODBC files defined in File System Group 6/SQLSERVER:

                                                               -Opt-
     FMS Group                      FMS  Path                   FC
     ============================== ==== ====================== =====
     Original AppxIO file location  1    /AppxData/locn
     SQLSERVER                      1    /AppxData/locn
     SQLSERVER                      6                           FC

• When redirecting to RDBMS backend connections (Oracle, Sybase, SQL Server, etc.), the override for FMS-Number and FMS-Control can occur in two places:

  1) The SysAdmin Database editor (which points to the FMS (File System) Groups), and

  2) Individual files' "File Specifications" in Physical File (Database) Management.

  It's possible to specify a default FMS-Control at the Database level. But it's not possible to specify a Database's default FMS-Number (ex: 5=Oracle, 6=SQL Server).

  This is because it's not possible in "File Specifications" to leave the FMS (Number) blank. The individual files' FMS-Number therefore always overrides the FMS-Number specified in the FMS Group's FMS-Number. (ECR #2782)

  When you are editing a database/application's "File Specifications", your edits are stored in System Administration's SYSDIR file. If you ever need to restore these edits from backup, this is the file from which to restore them.