PxPlus SQL ODBC

PxPlus SQL Command Line Client

The PxPlus SQL Command Line Client connects to local or remote PxPlus data and executes SQL commands. This tool is built on top of the PxPlus SQL ODBC Driver; therefore, it supports the same SQL Syntax and Scalar Functions as the PxPlus SQL ODBC Driver. It is based off the PxPlus SQL ODBC Driver and therefore can connect using a DSN defined for the PxPlus SQL ODBC Driver (see Configuration Procedures), or it can connect using a connection string used by the PxPlus SQL ODBC Driver (see Connection String Keywords).

By providing a command line client that can access PxPlus data, users now have a fast and simple way to query, update and write PxPlus data. This also makes it a great tool to test PxPlus data, the PxPlus SQL ODBC Driver, and connection settings. In addition, the SQL Command Line Client can be executed from Web pages, scripts and applications, providing new ways to use your PxPlus data.

A Windows version and a UNIX/Linux version of the PxPlus SQL Command Line Client are available. The SQL Client gets installed with the PxPlus SQL ODBC Driver (version 10 or higher).

For installation procedures, see:

ODBC Product Installation and Activation (Windows)
ODBC Product Installation and Activation (UNIX/Linux)

Running the SQL Command Line Client

To run the SQL Command Line Client, simply run the pxpsql program:

dir / pxpsql [Connection Info] [Options] COMMAND

Where:

dir is the directory path (e.g. /usr/pxpodbc).

Connection Info

One of the following Connection Info parameters must be specified:

-d path
--directory path

Path of the data dictionary file (DDF), which is the relative starting point for all embedded file references. See PxPlus Data Dictionary.

This should only be set if not connecting to PxPlus SQL Server; i.e. -s address is not set (see Options).

The path should not include the data dictionary file name.

Example:

   If the DDF is located at C:\myapp\data\providex.ddf, then set -d C:\myapp\data.

-i path
--ini-file path

Path and file 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.

This should only be set if not connecting to PxPlus SQL Server; i.e. -s address is not set (see Options).

The path must include the INI file name.

Example:

   If the INI is located at C:\myapp\data\mydict.ini, then set -i C:/myapp/data/mydict.ini.

--catalog name

Name of catalog. A catalog defines a remote data dictionary and/or INI file and optionally a prefix.

This must be set if connecting to PxPlus SQL Server; i.e. -s address is set (see Options).

(added in PxPlus 2018)

-f path
--config-file path

Path and file name of the config file.

Note:
If other options are specified, they override configuration in the config file. The config file is an INI style file, which accepts parameters that are the same as the long form pxpsql parameters.

Example:

   This is an example config file:

  

-c conn_str
--connection-string conn_str

Specify the connection string directly. This ignores other Connection Info parameters. See Connection String Keywords.

Options

This table lists optional Connection Info parameters:

-v path
--view-dll path

Path and file name of the PxPlus DLL/SO file. This is required to use the Views System.

-x path
--prefix path

Search paths to be inserted in front of all relative file references. Use , (comma) to separate multiple prefixes.

-s address
--server address

Network name or IP address required for connecting to the PxPlus SQL Server. See PxPlus SQL Server.

-p port
--port port

TCP/IP Port required for connecting to the PxPlus SQL Server. See PxPlus SQL Server.

--ssl

Enable SSL encrypted TCP/IP communication.

(SSL support was added in version 8.00.0000/PxPlus 2024.)

-z 
--compression

Enable ZLib compression to minimize network traffic between the client and the server.

-j name
--company name

Value to replace occurrences of %%C$ in a definition pathname.

-u name
--user name

Value to replace occurrences of %%U$ in a definition pathname.

-w password
--password password

Password value. Used in conjunction with a Sage MAS 90 or Sage MAS 200 system only.

-o ID
--session-id ID

Value to replace occurrences of %%S$ in a definition pathname.

-r 
--dirty-read

Enable dirty read mode, which causes normal file consistency checks to be skipped.

-e 
--enforce-double

Enable treating numeric data as doubles.

-b 
--burst-mode

Enable burst mode, which reduces the number of times a file is locked by holding the lock for either 50 operations or 3/10 of a second, whichever comes first.

-n 
--null-date

Enable suppression of invalid date error.

-k 
--key-restrict

Enable key restrict mode. This option allows the Driver to be used with applications that do not support keys or supports keys with limitations.

-t 
--strip-trailing-spaces

Enable strip trailing spaces from key values when joining multiple tables.

-q 
--silent

Enable suppression of most messages.

-a size
--cache-size size

Amount of memory in MB used for local storage of intermediate results.

-l path
--log-file path

Output debugging information to a specified log file.

-y 
--read-only

Disable write access for this SQL command.

--legacy-null

Enable legacy NULL mode where empty strings are treated as nulls.

(Added in PxPlus 2017)

-g separator
--separator separator

Set separator string used to update query results. Default value is "|".

-m 
--html

HTML output mode. In this mode, query results are outputted as HTML table data.

--column-names

Include a header row with column names in the output.

(Added in PxPlus 2016)

--version

Output the version number.

(Added in PxPlus 2016)

Command

One of the following must be specified:

SQL Command

SQL query (e.g. "SELECT * FROM CUSTOMER" used to read or modify data).

LIST

Command used to display list of table names.

LIST table

Command used to display list of column names, column types and column lengths for given table.

(Added in PxPlus 2016)