Difference: UsingAPPXODBCConnection (2 vs. 3)

Revision 32016-01-04 - JeanNeron

Line: 1 to 1

META TOPICPARENT	name="APPXExternalDatabases"

Using the APPX/ODBC Connection

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.

View topic | History: r6 < r5 < r4 < r3 | More topic actions...