Difference: UsingAPPXODBCConnection (1 vs. 6)

Revision 62016-01-08 - JeanNeron

Line: 1 to 1
 
META TOPICPARENT name="APPXExternalDatabases"

Using the APPX/ODBC Connection

Changed:
<
<

>
>

 

Introduction:

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

Line: 32 to 31
  3 - DISAM (includes CISAM) 4 - Sybase (not actively supported) 5 - Oracle
Changed:
<
<
6 - ODBC (for example, SQL Server, MySQL, etc.SQL Server requires a product registration)
>
>
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

Line: 50 to 49
 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.
Changed:
<
<
  • Proxy Database ID is no longer required and can be left blank.
>
>
  • 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.
Line: 65 to 64
 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.

Changed:
<
<

>
>

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:

FMS8.png

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):

FMS9.png

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 :

Line: 75 to 88
 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.
Changed:
<
<

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:

FMS5.png

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

FMS6.png

Select the field you want to override and Press Enter:

FMS7.png

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

>
>

---++ 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:

FMS5.png

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

FMS6.png

Select the field you want to override and Press Enter:

FMS7.png

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 :

Line: 90 to 103
 
  • 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.
Changed:
<
<
  • 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).

>
>
  • 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:

Line: 98 to 111
 Read what other users have said about this page or add your own comments.
Changed:
<
<

<--/commentPlugin-->
>
>

<--/commentPlugin-->
 
META FILEATTACHMENT attachment="FMS1.png" attr="h" comment="" date="1452027257" name="FMS1.png" path="FMS1.png" size="37018" user="JeanNeron" version="1"
META FILEATTACHMENT attachment="FMS3.png" attr="h" comment="" date="1452028140" name="FMS3.png" path="FMS3.png" size="17121" user="JeanNeron" version="1"
META FILEATTACHMENT attachment="FMS4.png" attr="h" comment="" date="1452028393" name="FMS4.png" path="FMS4.png" size="13803" user="JeanNeron" version="1"
META FILEATTACHMENT attachment="FMS2.png" attr="h" comment="" date="1452031482" name="FMS2.png" path="FMS2.png" size="16924" user="JeanNeron" version="1"
Added:
>
>
META FILEATTACHMENT attachment="FMS8.png" attr="h" comment="" date="1452278432" name="FMS8.png" path="FMS8.png" size="63879" user="JeanNeron" version="1"
META FILEATTACHMENT attachment="FMS9.png" attr="h" comment="" date="1452278685" name="FMS9.png" path="FMS9.png" size="44048" user="JeanNeron" version="1"

Revision 52016-01-05 - JeanNeron

Line: 1 to 1
 
META TOPICPARENT name="APPXExternalDatabases"

Using the APPX/ODBC Connection

Changed:
<
<

>
>

 

Introduction:

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

Line: 23 to 23
  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.
Changed:
<
<
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.
>
>
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:
Line: 39 to 39
 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:

Changed:
<
<
Below is an example of a complete FMS Control entry:
>
>
Below is an example of a complete FMS group entry:
 
Changed:
<
<
     DSN=local_sql_server; UID=sa; PWD=;: 
     table_name=$lcase($appl_$file):
     proxy_db=LOK:
>
>
FMS2.png
 
Changed:
<
<
The APPX/ODBC Connection interprets the above example as follows:
  • 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'.
Every line must end in a colon (":"). Spaces are not allowed before colons.

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.

>
>
The FMS group name is 'sql'. The FMS Group Attributes are:
 
Changed:
<
<
<a name="Row Locking">
>
>
FMS1.png
 
Changed:
<
<

</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

>
>
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 no longer required and can be left blank.
  • 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 must also define an Identity for the FMS Group. These are the credentials APPX will use to connect to the RDBMS:
 
Changed:
<
<
>
>
FMS3.png
 
Changed:
<
<
<a name="Field to Column Mapping">
>
>
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:
 
Changed:
<
<

>
>
FMS4.png
 
Changed:
<
<
</a>
>
>
The Realm can be used for systems where more than one authority (domain) can be used to authenticate a user.
 
Added:
>
>
You must use the 'Change Password' button to assign the password for the RDBMS to this Identity.

 

Field to Column Mapping :

Line: 153 to 75
 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.
Changed:
<
<
<a name="Converting to and from ODBC Data Sources">


</a>

>
>

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:

FMS5.png

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

FMS6.png

Select the field you want to override and Press Enter:

FMS7.png

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 :

Line: 169 to 86
 

Debugging :

Changed:
<
<
  • 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.

>
>
  • 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.
Changed:
<
<
  • 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.

>
>
  • 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).

 
Added:
>
>

 

Comments:

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


Changed:
<
<

<--/commentPlugin-->
>
>

<--/commentPlugin-->

META FILEATTACHMENT attachment="FMS1.png" attr="h" comment="" date="1452027257" name="FMS1.png" path="FMS1.png" size="37018" user="JeanNeron" version="1"
META FILEATTACHMENT attachment="FMS3.png" attr="h" comment="" date="1452028140" name="FMS3.png" path="FMS3.png" size="17121" user="JeanNeron" version="1"
META FILEATTACHMENT attachment="FMS4.png" attr="h" comment="" date="1452028393" name="FMS4.png" path="FMS4.png" size="13803" user="JeanNeron" version="1"
META FILEATTACHMENT attachment="FMS2.png" attr="h" comment="" date="1452031482" name="FMS2.png" path="FMS2.png" size="16924" user="JeanNeron" version="1"

Revision 42016-01-05 - JeanNeron

Line: 1 to 1
 
META TOPICPARENT name="APPXExternalDatabases"

Using the APPX/ODBC Connection

Changed:
<
<
>
>

 

Introduction:

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

Changed:
<
<
The APPX/ODBC Connection allows APPX Applications, through a Windows ODBC Server, to access and operate upon data stored in a non-APPX data format, such as SQL-Server. ( 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 .)
>
>
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 .)
 
Changed:
<
<
APPX/ODBC is licensed separately, requiring its own product registration. You must have an ODBC Data Source (such as SQL-Server) installed to use the ODBC Connection.
>
>
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.
 
Changed:
<
<

Setting up APPX/ODBC Connection:

Within Windows Control Panel, 32bit ODBC, 'System DSN' tab, create or determine the Data Source Name (DSN) you want to use.

Using the 'Configure' then 'Advanced' buttons, you may want to establish or identify a Login Name and Password. (Required for SQL-Server.)

>
>
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.
 
Changed:
<
<
If you have an ODBC client in addition to APPX (such as WinSQL), you may want to confirm you can access tables defined under this Data-Source/UserID/Password.

You may specify the System Data Source (DSN), table name mapping, and APPX record (row) level locking Database by creating an APPX File System Group. From the APPX Main Menu, this may be found in 3)System Administration, 2)Configuration, 3)File System Groups.

>
>

Setting up APPX/ODBC Connection:

 
Changed:
<
<
Entire Databases and/or individual files (tables) may be assigned to a File System Group. When you assign a Database to a File System Group, 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.
>
>
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.
 
Changed:
<
<
As of APPX release 3.4 and higher, current FMS types are:
    1 - AppxIO (our proprietary ISAM) or VAX/RMS.
    2 - VISION (from AcuCobol)
    3 - CISAM (from Informix)
    4 - Sybase
    5 - Oracle
    6 - ODBC (for example, SQL-Server, Access, etc.)
    7 - APPX/NET remote AppxIO file server 
>
>
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.
 
Changed:
<
<
To store a file in an ODBC Database, go into 5)Database File Management, 2)File Specifications, change the FMS Attribute from '1' to '6'.
>
>
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.
 
Changed:
<
<
Then, establish the file's ODBC specifications by setting its 'FMS Group' Attribute to a valid FMS Group Name. The FMS Group is SCANable.
>
>
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.
 
Changed:
<
<
You can create and edit File System (FMS) Groups from within this SCAN. (Look in the upper right hand corner of the screen.)
>
>
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.
 
Changed:
<
<
Within the File Sstem Group's "1)FMS Controls" must be defined:
    1) ODBC  [[#System Data Source][Data Source Information]] (DSN), 
    2)  [[#Table Names][Table Name mapping]] (table_name), and 
    3)  [[#Row Locking][Row Locking]] (proxy_db)

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

>
>
    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, etc.SQL Server requires a product registration)
    7 - APPX/NET remote AppxIO file server 
    8 - DB/2

 
Added:
>
>
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 Control entry:

Line: 70 to 64
  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:

Changed:
<
<
     DSN=Access-Accounting;: 
>
>
     DSN=Access-Accounting;: 
  To connect to an MS SQL-Server Database (which requires a user name and password) you might specify:
Added:
>
>
 
     DSN=sql_server; UID=bobby; PWD=ybbob;: 

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

Line: 79 to 76
 
     DSN=local_sql_server; UID=sa; PWD=;:
    
Changed:
<
<
>
>
<a name="Table Names">
 
Changed:
<
<
>
>
</a>
 

Table Names (table_name):

Line: 111 to 112
  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.
Changed:
<
<
>
>
<a name="Row Locking">
 
Changed:
<
<
>
>
</a>
 

Row Locking (proxy_db):

Line: 136 to 138
 
Changed:
<
<
>
>
<a name="Field to Column Mapping">
 
Changed:
<
<
>
>
</a>
 

Field to Column Mapping :

Line: 150 to 153
 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.
Changed:
<
<
>
>
<a name="Converting to and from ODBC Data Sources">
 
Changed:
<
<
>
>
</a>
 

Converting to and from ODBC Data Sources :

Line: 170 to 174
 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.

  • 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.
Changed:
<
<
  • 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).

>
>
  • 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:

Line: 186 to 190
 

Issues :

Changed:
<
<
  • 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:

>
>
  • 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
     ============================== ==== ====================== =====

Revision 32016-01-04 - JeanNeron

Line: 1 to 1
 
META TOPICPARENT name="APPXExternalDatabases"

Using the APPX/ODBC Connection

Introduction:

Changed:
<
<
The APPX/ODBC Connection allows APPX Applications, through a Windows ODBC Server, to access and operate upon data stored in a non-APPX data format, such as SQL-Server or MS-Access' Jet database. ( 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 .)
>
>
This page is applicable to APPX version 4.1 and higher. For older versions, refer to this page
 
Changed:
<
<
APPX/ODBC is licensed separately, requiring its own product registration. You must have an ODBC Data Source (such as SQL-Server or Access) installed to use the ODBC Connection.
>
>
The APPX/ODBC Connection allows APPX Applications, through a Windows ODBC Server, to access and operate upon data stored in a non-APPX data format, such as SQL-Server. ( 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 .)

APPX/ODBC is licensed separately, requiring its own product registration. You must have an ODBC Data Source (such as SQL-Server) installed to use the ODBC Connection.

 

Setting up APPX/ODBC Connection:

Line: 41 to 43
  2) Table Name mapping (table_name), and 3) Row Locking (proxy_db)
Changed:
<
<


>
>

 

Example:

Line: 60 to 60
  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.
Changed:
<
<

>
>

 

System Data Source (DSN):

Line: 70 to 70
  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:

Changed:
<
<
     DSN=Access-Accounting;: 
>
>
     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;: 
Line: 80 to 80
 

Changed:
<
<

>
>

 
Line: 112 to 112
 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.

Changed:
<
<

>
>

 
Line: 136 to 137
 

Changed:
<
<

>
>

 
Line: 149 to 151
 

Changed:
<
<

>
>

 
Line: 159 to 162
 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.

Changed:
<
<


>
>

 

Debugging :

  • 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.
Deleted:
<
<
  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.
Deleted:
<
<
 
  • 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.
Changed:
<
<
  • 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).

>
>
  • 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).

 

Limitations:

Added:
>
>
 

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.

Revision 22012-03-08 - ChrisBrower

Line: 1 to 1
 
META TOPICPARENT name="APPXExternalDatabases"

Using the APPX/ODBC Connection

Deleted:
<
<

Introduction:

 
Changed:
<
<
The APPX/ODBC Connection allows APPX Applications, through a Windows ODBC Server, to access and operate upon data stored in a non-APPX data format, such as SQL-Server or MS-Access' Jet database. ( 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 .)
>
>

Introduction:

 
Changed:
<
<
APPX/ODBC is licensed separately, requiring its own product registration. You must have an ODBC Data Source (such as SQL-Server or Access) installed to use the ODBC Connection.


>
>
The APPX/ODBC Connection allows APPX Applications, through a Windows ODBC Server, to access and operate upon data stored in a non-APPX data format, such as SQL-Server or MS-Access' Jet database. ( 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 .)
 
Changed:
<
<
>
>
APPX/ODBC is licensed separately, requiring its own product registration. You must have an ODBC Data Source (such as SQL-Server or Access) installed to use the ODBC Connection.
 
Changed:
<
<

Setting up APPX/ODBC Connection:

>
>

Setting up APPX/ODBC Connection:

  Within Windows Control Panel, 32bit ODBC, 'System DSN' tab, create or determine the Data Source Name (DSN) you want to use.

Using the 'Configure' then 'Advanced' buttons, you may want to establish or identify a Login Name and Password. (Required for SQL-Server.)

Changed:
<
<
If you have an ODBC client in addition to APPX (such as WinSQL), you may want to confirm you can access tables defined under this Data-Source/UserID/Password.
>
>
If you have an ODBC client in addition to APPX (such as WinSQL), you may want to confirm you can access tables defined under this Data-Source/UserID/Password.
  You may specify the System Data Source (DSN), table name mapping, and APPX record (row) level locking Database by creating an APPX File System Group. From the APPX Main Menu, this may be found in 3)System Administration, 2)Configuration, 3)File System Groups.

Entire Databases and/or individual files (tables) may be assigned to a File System Group. When you assign a Database to a File System Group, 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 3.4 and higher, current FMS types are:

Changed:
<
<
    1 - AppxIO (our proprietary ISAM) or VAX/RMS.

>
>
    1 - AppxIO (our proprietary ISAM) or VAX/RMS.

  2 - VISION (from AcuCobol) 3 - CISAM (from Informix) 4 - Sybase 5 - Oracle 6 - ODBC (for example, SQL-Server, Access, etc.)
Changed:
<
<
7 - APPX/NET remote AppxIO file server
>
>
7 - APPX/NET remote AppxIO file server
  To store a file in an ODBC Database, go into 5)Database File Management, 2)File Specifications, change the FMS Attribute from '1' to '6'.
Line: 40 to 36
  You can create and edit File System (FMS) Groups from within this SCAN. (Look in the upper right hand corner of the screen.)
Changed:
<
<
Within the File System Group's "1)FMS Controls" must be defined:
    1) ODBC  [[#System Data Source][Data Source Information]] (DSN), 
    2)  [[#Table Names][Table Name mapping]] (table_name), and 
    3)  [[#Row Locking][Row Locking]] (proxy_db)

>
>
Within the File Sstem Group's "1)FMS Controls" must be defined:
    1) ODBC  [[#System Data Source][Data Source Information]] (DSN), 
    2)  [[#Table Names][Table Name mapping]] (table_name), and 
    3)  [[#Row Locking][Row Locking]] (proxy_db)

 
Changed:
<
<
>
>
 
Deleted:
<
<
 
Changed:
<
<

Example:

>
>

Example:

  Below is an example of a complete FMS Control entry:
     DSN=local_sql_server; UID=sa; PWD=;: 

Line: 60 to 53
  proxy_db=LOK:

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

Changed:
<
<
  • 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'.
>
>
  • 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'.
 Every line must end in a colon (":"). Spaces are not allowed before colons.

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.

Deleted:
<
<
 
Changed:
<
<

System Data Source (DSN):

>
>

System Data Source (DSN):

  ODBC requires a specification identifying the System Data Source, using one of the following formats:
Changed:
<
<
     DSN=<data_source_name>:

>
>
     DSN=<data_source_name>:

  DSN=<data_source_name>; UID=<user_id>:
Changed:
<
<
DSN=<data_source_name>; UID=<user_id>; PWD=<password>:
>
>
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:
Changed:
<
<
     DSN=Access-Accounting;: 
>
>
     DSN=Access-Accounting;: 
  To connect to an MS SQL-Server Database (which requires a user name and password) you might specify:
Changed:
<
<
     DSN=sql_server; UID=bobby; PWD=ybbob;: 
>
>
     DSN=sql_server; UID=bobby; PWD=ybbob;: 
  To connect as the SQL-Server administrator (with a blank password):
Changed:
<
<
     DSN=local_sql_server; UID=sa; PWD=;:
    
>
>
     DSN=local_sql_server; UID=sa; PWD=;:
    
 

Changed:
<
<

Table Names (table_name):

>
>

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:
Changed:
<
<
     DSN=<expression>:         - specifies ODBC Data Source

>
>
     DSN=<expression>:         - specifies ODBC Data Source

  table_name=<expression>: - specifies ODBC table name
Changed:
<
<
proxy_db=<expression>: - specifies proxy locking Database
>
>
proxy_db=<expression>: - specifies proxy locking Database
  Note that required colons terminate each <expression>. <expression> may use the following 'variables':
Changed:
<
<
     $db      - maps to 3-character APPX Database ID

>
>
     $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
Line: 110 to 100
  $pid - maps to Unix process ID $uname - maps to Unix user name $ucase( <expression> ) - converts <expression> to upper-case
Changed:
<
<
$lcase( <expression> ) - converts <expression> to lower-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 ); 
Line: 126 to 116
 
Changed:
<
<

Row Locking (proxy_db):

>
>

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.

Line: 141 to 131
 

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:

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

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

 
Changed:
<
<
>
>
 

Changed:
<
<

Field to Column Mapping:

>
>

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.

Line: 163 to 153
 
Changed:
<
<

Converting to and from ODBC Data Sources:

>
>

Converting to and from ODBC Data Sources :

 
Changed:
<
<
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 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.
 
Changed:
<
<
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.
>
>
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.
 
Deleted:
<
<
 
Changed:
<
<

Debugging:

>
>

Debugging :

 
  • 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.
Changed:
<
<
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.
>
>
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.
 

  • 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.
Changed:
<
<
  • 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).


>
>
  • 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).

 
Changed:
<
<

Limitations:

>
>

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.

Line: 202 to 185
 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
Changed:
<
<

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-

>
>

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
Changed:
<
<
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.

>
>
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.

Comments:

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



<--/commentPlugin-->

Revision 12012-03-06 - ChrisBrower

Line: 1 to 1
Added:
>
>
META TOPICPARENT name="APPXExternalDatabases"

Using the APPX/ODBC Connection

Introduction:

The APPX/ODBC Connection allows APPX Applications, through a Windows ODBC Server, to access and operate upon data stored in a non-APPX data format, such as SQL-Server or MS-Access' Jet database. ( 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 .)

APPX/ODBC is licensed separately, requiring its own product registration. You must have an ODBC Data Source (such as SQL-Server or Access) installed to use the ODBC Connection.


Setting up APPX/ODBC Connection:

Within Windows Control Panel, 32bit ODBC, 'System DSN' tab, create or determine the Data Source Name (DSN) you want to use.

Using the 'Configure' then 'Advanced' buttons, you may want to establish or identify a Login Name and Password. (Required for SQL-Server.)

If you have an ODBC client in addition to APPX (such as WinSQL), you may want to confirm you can access tables defined under this Data-Source/UserID/Password.

You may specify the System Data Source (DSN), table name mapping, and APPX record (row) level locking Database by creating an APPX File System Group. From the APPX Main Menu, this may be found in 3)System Administration, 2)Configuration, 3)File System Groups.

Entire Databases and/or individual files (tables) may be assigned to a File System Group. When you assign a Database to a File System Group, 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 3.4 and higher, current FMS types are:

    1 - AppxIO (our proprietary ISAM) or VAX/RMS.
    2 - VISION (from AcuCobol)
    3 - CISAM (from Informix)
    4 - Sybase
    5 - Oracle
    6 - ODBC (for example, SQL-Server, Access, etc.)
    7 - APPX/NET remote AppxIO file server 

To store a file in an ODBC Database, go into 5)Database File Management, 2)File Specifications, change the FMS Attribute from '1' to '6'.

Then, establish the file's ODBC specifications by setting its 'FMS Group' Attribute to a valid FMS Group Name. The FMS Group is SCANable.

You can create and edit File System (FMS) Groups from within this SCAN. (Look in the upper right hand corner of the screen.)

Within the File System Group's "1)FMS Controls" must be defined:

    1) ODBC  [[#System Data Source][Data Source Information]] (DSN), 
    2)  [[#Table Names][Table Name mapping]] (table_name), and 
    3)  [[#Row Locking][Row Locking]] (proxy_db)

 


Example:

Below is an example of a complete FMS Control entry:

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

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

  • 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'.
Every line must end in a colon (":"). Spaces are not allowed before colons.

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=;:
    


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.


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
 


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.


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:

  • 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.

  • 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).


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.

 
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 2008-2024 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback