Line: 1 to 1 | ||||||||
---|---|---|---|---|---|---|---|---|
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: | ||||||||
< < |
| |||||||
> > |
| |||||||
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 : | |||||||
| ||||||||
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. | |||||||
| ||||||||
Changed: | ||||||||
< < |
| |||||||
> > |
| |||||||
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:
-Opt- | |||||||
> > | Issues :
-Opt- | |||||||
FMS Group FMS Path FC
========================== == ==================== =
Original AppxIO file location 1 /AppxData/locn
SQLSERVER 1 /AppxData/locn | ||||||||
Changed: | ||||||||
< < | SQLSERVER 6 FC
| |||||||
> > | SQLSERVER 6 FC
Comments:Read what other users have said about this page or add your own comments. |
Line: 1 to 1 | ||||||||
---|---|---|---|---|---|---|---|---|
Added: | ||||||||
> > |
Using the APPX/ODBC ConnectionIntroduction: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:
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:
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.
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:
-Opt- FMS Group FMS Path FC ============================== ==== ====================== ===== Original AppxIO file location 1 /AppxData/locn SQLSERVER 1 /AppxData/locn SQLSERVER 6 FC
|