Line: 1 to 1 | ||||||||
---|---|---|---|---|---|---|---|---|
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.
| ||||||||
Changed: | ||||||||
< < |
| |||||||
> > |
| |||||||
Limitations: | ||||||||
Line: 186 to 190 | ||||||||
Issues : | ||||||||
Changed: | ||||||||
< < |
| |||||||
> > |
| |||||||
-Opt- FMS Group FMS Path FC ============================== ==== ====================== ===== |