|
PxPlus SQL ODBC Driver Configuration (Windows) |
The local PxPlus SQL ODBC Driver and the Client component of the Client/Server Driver are configured using the ODBC Data Source Administrator. This can be accessed via the Windows Control Panel (in the Administrative Tools sub-folder) unless it is a 32-bit PxPlus SQL ODBC Driver running on 64-bit Windows, in which case it is configured using the 32-bit ODBC Data Source Administrator accessed via C:\Windows\SysWOW64:
This is where you define each database and set up associated configuration details:
Either the data file directory or the INI file (or both) must be defined. There must be at least one source for a data dictionary. If both have been specified, then the contents of both will be merged. Additional PxPlus SQL Server settings are required for the Client/Server version of the driver:
(Catalog was added in PxPlus 2018.)
A data source defines the location of data and the connection information needed to access that data. In effect, it defines the path to the data, which may include a network, library, server, database, and other attributes.
To establish a connection to a data source, you must do the following:
If the ODBC connection information is stored in the Windows registry, it is called a machine data source. A machine data source can be either a user data source (one user has access) or a system data source (visible to all users on, or connected to, the same computer). The main advantage to having a machine data source is that it provides security within the system to limit who is logged on to view the data source and restrict the ability to copy the data source to other computers. Machine data sources can only be used on the computer where they are defined.
If the ODBC connection information is stored in a DSN file, it is called a file data source. A file data source is defined in a flat text file and, unlike machine data sources, they can be ported to any system. The main advantage to having a file data source is that it can be placed in common directories and shared between users; e.g., a file DSN can be distributed among clients as a part of an installation package.
The ODBC Data Source Administrator dialog allows you to choose between different DSN tabs, depending on the type of data source to be modified:
|
|
User DSN |
Defines machine data sources for the user currently signed on. |
|
|
System DSN |
Defines machine data sources for a particular workstation. |
|
|
File DSN |
Places and maintains data source definitions in a portable text file. |
Click on one of the tabs to list the current connections for that DSN type. From here, you can change/remove an existing DSN or add/configure a new one.
To create a new DSN for the PxPlus SQL ODBC Driver, click the Add button. The next dialog displays a list of the PxPlus SQL ODBC Drivers that are installed on your system. Select the appropriate PxPlus SQL ODBC Driver from the list and click Finish. This invokes the PxPlus SQL ODBC Driver Setup dialog, which allows you to create and configure access to a PxPlus database.
Dialogs for the local driver and the Client component of the Client/Server Driver are identical, except for the Server Name and TCP/IP fields. The dialog for the PxPlus SQL ODBC Driver Setup displays as follows:
These fields are presented on the Basic tab (pictured above) when the Driver Setup dialog is initially displayed.
|
Data Source Name |
Name (DSN) that other applications will use to access the database. Case insensitive. Maximum length is 32 characters. With regards to the PxPlus SQL ODBC Driver, the DSN can be considered the logical name of the database. The following characters are not permitted in a DSN: [ ] { } ( ) , ; ? * = ! @ \ |
|
Description |
Optional free form remark describing the Data Source Name. Maximum length is 127 characters. |
|
Data Directory |
Location of the PxPlus data dictionary file (providex.ddf), which is the relative starting point for all embedded file references. Maximum length is 127 characters. See PxPlus Data Dictionary. If providex.ddf is found in this directory, then all file/table definitions contained in it are made available to the PxPlus SQL ODBC Driver. Using the embedded data dictionary simplifies the installation and maintenance issues regarding the PxPlus SQL ODBC Driver. The providex.ddf file located in the database directory can be set up to contain only a subset of the files used by an application. This can be used to control which files/tables are presented to the end user. To provide different "views" of the database, create separate directories, each containing a different providex.ddf file. Note: |
|
INI File |
Path and name of the INI file used to define the data dictionary manually for files that cannot be handled by the PxPlus embedded data dictionary. Maximum length is 127 characters. See INI Definition. Note: |
|
Prefix |
Search paths to be inserted in front of all relative file references used in Data Dictionary or INI definitions. As of PxPlus 2018, Equals Sign and Asterisks substitution is supported. Use a comma as the separator between multiple prefixes. Maximum length is 1023 characters. |
The following entries set up the Client component of the Client/Server version of the PxPlus SQL ODBC Driver:
|
Server Name or IP |
Server network name or IP address required for connecting to the PxPlus SQL Server. Maximum length is 100 characters. |
|
TCP/IP Port |
TCP/IP Port required for connecting to the PxPlus SQL Server. Default is 20222. Maximum length is 15 characters. You can change the TCP/IP port that the server is listening on via the Control Panel Configuration program, in which case the DSN TCP/IP Port setting on the client side must be changed as well. |
|
Catalog |
Name of catalog. A catalog defines a remote data dictionary and/or INI file and optionally a prefix. A maximum number of 256 catalogs may be defined. Note:
|
Default values can be set in the Company code, User ID, Password and Session ID fields for use in the definition of data file pathnames. Whenever a data file pathname starts with an equals sign (=), the pathname will be scanned.
All occurrences of %C$ will be replaced with the value set in the default Company code, %U$ will be replaced with the default User ID, and %S$ will be replaced with the default Session ID. The search for occurrences is case insensitive, thus %c$ and %C$ will both be found and replaced with the value of the Company code field.
When using Sage 100 (or Sage 200) data files, the PxPlus SQL ODBC Driver will prompt the user to enter a valid Company and User ID when invalid data is used during a database connection. For other databases, enter a question mark (?) in any of the optional fields during the DSN setup, and the Driver will prompt for the values during a database connection. There is no validation of the values entered.
The following optional fields are found under the Logon tab of the PxPlus SQL ODBC Driver Setup dialog:
|
Company code |
Optional value to replace occurrences of %C$ in a definition pathname. Maximum length is 127 characters. |
|
Default UserID |
Optional value to replace occurrences of %U$ in a definition pathname. Maximum length is 64 characters. |
|
Password |
Optional password value. Passwords are validated in one of two ways: using either a Sage 100 or Sage 200 security DLL or using a custom security DLL where the developer has coded his/her own. See Security DLL. If a security DLL is not found, no password validation is done. Maximum length is 63 characters. |
|
Session ID |
Optional value to replace occurrences of %S$ in a definition pathname. Maximum length is 15 characters. This parameter provides the ability for applications to create temporary files that can be accessed from an ODBC application. Once the temporary file has been generated, the complete file name or a portion of the name can either be manually entered into the DSN information or sent to the driver programmatically using a connection string. |
The PxPlus SQL ODBC Driver Setup dialog provides for further optional settings under the Options tab:
|
Path to Views DLL |
Path to pvxwin32.dll (Windows) or libpvx.so (UNIX/Linux). This is required by the PxPlus SQL ODBC Driver in order to use the Views system (PxPlus Version 5.10 or later). |
|
Key Restrict |
Check box to restrict keys. This option allows the driver to be used with an application such as Lotus Approach 97, which does not support keys, or supports them with limitations on length, field segments, or use of sub-strings. |
|
Silent mode |
Check box to suppress most prompts or message boxes that the PxPlus SQL ODBC Driver generates during processing. |
|
Legacy NULL mode |
Check box to enable legacy NULL mode where columns are defined as nullable and empty strings are returned as null. (added in PxPlus 2017) |
|
Enforce DOUBLE |
Check box to set default format of "double" for numeric data. This helps avoid conflicts with MS Office 2000 and other applications that do not support the decimal data type for numeric values. |
|
NULL Date |
Check box to suppress invalid date error. The driver validates the contents of date columns at run time. If a value is invalid, the driver generates an error message and ceases processing of the table. This replaces an invalid entry with a null value and allows the driver to continue processing. |
|
Strip trailing spaces |
Check box to enable stripping trailing spaces from key values when joining multiple tables. |
|
Read Only |
Check box to suppress write access for this DSN connection. This is useful for providing certain users with read only access while allowing other users write access. |
|
Cache Size MB |
Establishes the amount of memory to use for local storage of intermediate results. If this value is zero, then intermediate information will not be cached locally on the workstation. Instead, it must be re-acquired from the server, which may lead to poorer performance on slower connections. |
The following options provide methods to reduce overhead when processing a file:
|
Dirty Read |
Check box for Dirty Read mode of operation to skip the normal file consistency checks. Dirty reads can speed file processing by reducing the number of locks issued against a file. However, this may result in inconsistent data should the file be updated while being read by the PxPlus SQL ODBC Driver. |
|
Burst mode |
Check box to enable Burst mode. This helps reduce some of the overhead created by temporary locks. With Burst mode set, the PxPlus SQL ODBC Driver locks the file header for either 50 file operations or three-tenths of a second, whichever occurs first. This decreases the number of times the file must be locked and the number of times that internal buffers may need to be reloaded. Refer to the next paragraph below for an explanation on the effect of temporary locks. |
Normally, when the PxPlus SQL ODBC Driver accesses data files, it must place a temporary lock on the file. This temporary lock guarantees that the driver reads key tables and structures that are in a consistent state and not in the process of being altered. Once the temporary lock is established, the driver checks the file header to see if it has been changed since the last time the file was accessed. If the file has not been altered, then the PxPlus SQL ODBC Driver can use any of the data still maintained in its buffers. If the file has been altered, then all data in the buffers is discarded. When the driver has completed its access to the data file, the temporary lock is released. The process is repeated for each file accessed by the driver, for each operation on the file.
The Debug option on the PxPlus SQL ODBC Driver Setup dialog traces active sessions within the PxPlus SQL ODBC Driver and generates a log file. This reports internal diagnostic information that is different from the SQL tracing provided by the Microsoft ODBC Driver Manager.
The following fields set the debug option and log file:
|
Enable Debug |
Check box to enable the PxPlus SQL ODBC Driver Debug option. |
|
Log File |
Path and name of the debug log file. If this field is left blank, then the PxPlus SQL ODBC Driver defaults to C:\PXPSQLODBC.LOG. |
|
Connection String |
Button to invoke a display of the connection string returned by the driver. The information displayed in the area above the button is the connection string representing the currently saved DSN attributes. See Connection String Keywords. |
|
Test Connection |
Button to test the connection to the configured database. If successful, then the area above the button will display the following: Connection succeeded. Datasource includes x tables. Where: |
|
Test Schema |
Button to test if the PxPlus SQL ODBC Driver can access the tables/views defined by the data dictionary. (added in PxPlus 2016) If successful, then the area above the button will display the following: Schema test succeeded. x tables accessible. Where: Note: |
The following entries allow you to view and modify your activation information:
|
Serial Number |
Serial number as provided by PVX Plus Technologies. |
|
Number of Users |
User count as provided by PVX Plus Technologies. |
|
Activation key |
Activation key as provided by PVX Plus Technologies. |
