|
PxPlus SQL ODBC Driver Configuration (Windows) |
The PxPlus SQL ODBC Driver is configured using the ODBC Data Sources application, which is accessed by invoking the Windows Start menu and searching for ODBC Data Sources.
You will see two applications: ODBC Data Sources (32-bit) and ODBC Data Sources (64-bit). Select the one that matches the architecture of the PxPlus SQL ODBC Driver you are trying to configure.
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.
To use the PxPlus SQL ODBC Driver in Client/Server mode, you must configure how to connect to the PxPlus SQL Server. The following Server settings are used:
If you specify a Server Name or IP, you must specify a Catalog as well.
(Catalog was added in PxPlus 2018.)
(SSL support was added in version 8.00.0000/PxPlus 2024.)
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 Sources application dialog allows you to choose among different DSN tabs, depending on the type of data source to be modified:
|
Type of Data Source |
Description |
|
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, follow these steps:
|
Step |
Description |
|
1. |
Click the Add button. The next dialog displays a list of the PxPlus SQL ODBC Drivers installed on your system. |
|
2. |
Select the appropriate PxPlus SQL ODBC Driver from the list. Click the Finish button. |
|
3. |
The PxPlus SQL ODBC Driver Setup dialog displays, which allows you to create and configure access to a PxPlus database. This dialog is divided into the following tabbed panels (explained below): Basic, Server, Logon, Options, Debug, Activation and About. |
The Basic panel (pictured above) consists of the following:
|
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 that describes the Data Source Name. Maximum length is 127 characters. |
|
Data Dictionary |
Location of the PxPlus data dictionary file, providex.ddf, which is the relative starting point for all embedded file references. See PxPlus Data Dictionary. Click the Browse button beside the input field to select a directory using the Windows Select Folder dialog. Maximum length is 127 characters. 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. See INI Definition. Click the Browse button beside the input field to select an INI file using the Windows Open File dialog. Maximum length is 127 characters. 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 Server panel is used to configure the PxPlus SQL ODBC Driver in Client/Server mode:
This panel consists of the following:
|
Server Name or IP |
Server network name or IP address required for connecting to the PxPlus SQL Server (e.g. PxPlusSQLServer or 127.34.28.15). 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. |
|
Enable SSL Secure Communication |
Check box to toggle between unencrypted TCP/IP communication and SSL encrypted TCP/IP communication. (Default is unencrypted TCP/IP communication.) (SSL support was added in version 8.00.0000/PxPlus 2024.) |
|
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:
|
On the Logon panel, default values can be set in the Company code, Default UserID, 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.
This panel consists of the following:
|
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. Maximum length is 63 characters. 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. |
|
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 Options panel is used for additional optional settings:
This panel consists of the following:
|
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. Click the Browse button beside the input field to select a directory using the Windows Select Folder dialog. |
|
Dirty Read |
See Performance Tuning. |
|
Burst mode |
See Performance Tuning. |
|
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. (The Legacy NULL mode option was 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. If a cache size is specified, then that amount of system memory will be used to store information locally. Once the specified amount of memory is utilized, the Driver will store additional information in a temporary disk file on the local workstation. Performance gains will vary with the environment. In a high bandwidth environment (LAN), caching may not be as beneficial as in a low bandwidth environment (WAN), where the impact can be significant. |
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, 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 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.
This panel consists of the following:
|
Enable Debug |
Check box to enable the PxPlus SQL ODBC Driver Debug option. |
|
Log File |
Path and name of the Debug log file. Click the Browse button beside the input field to select a log file using the Windows Open File dialog. If this field is blank, 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. |
|
Test Connection |
Button to test the connection to the configured database. If successful, the area above the button will display the following: Connection succeeded. Datasource includes x tables. Where: x is the number of tables/views reported for the database. |
|
Test Schema |
Button to test if the PxPlus SQL ODBC Driver can access the tables/views defined by the data dictionary. If successful, the area above the button will display the following: Schema test succeeded. x tables accessible. Where: x is the number of tables/views accessible by the PxPlus SQL ODBC Driver. Note:
|
The Activation panel allows you to view and modify your activation information:
This panel consists of the following:
|
Serial Number |
Serial number from your PxPlus Professional or Web license. |
|
Number of Users |
User count from your PxPlus Professional or Web license. |
|
Activation key |
ODBC Activation key from your PxPlus Professional or Web license. |
The About panel provides information about the PxPlus SQL ODBC Driver and a link to the PxPlus Help documentation.
This panel displays the following:
