This is pgintcl/README, describing pgintcl: A PostgreSQL interface in Tcl Last updated for pgintcl-3.5.1 on 2017-11-12 The project home page is: http://sourceforge.net/projects/pgintcl/ ----------------------------------------------------------------------------- OVERVIEW: This is a pure-Tcl interface to the PostgreSQL Database Management System. It implements almost all the commands in the original libpgtcl, the Tcl interface which was bundled with PostgreSQL until release 8.0, plus it has many extensions. But it is written entirely in Tcl, so does not require compilation for a specific platform or any additional components. I originally wrote this to be able to use Tcl/Tk database clients on platforms where the PostgreSQL client library (libpq) and the Tcl interface (libpgtcl) were not available, or were too much trouble to build. pgin.tcl uses the Tcl binary data and TCP socket features to communicate directly with a PostgreSQL database server, using the internal PostgreSQL frontend/backend protocol. Therefore, pgin.tcl is dependent on the protocol, rather than being protected from its details as are libpq-based applications. This version of pgin.tcl uses version 3 of the PostgreSQL protocol, and only communicates with PostgreSQL-7.4 and higher servers. pgin.tcl is also highly compatible with pgtcl-ng, the "Next Generation" libpq-based implementation of the pgtcl interface. pgtcl-ng can be found at http://sourceforge.net/projects/pgtclng/ The same test suite is used to verify both interfaces. Version 3 of pgin.tcl added Unicode character set encoding and decoding. It was tested with LATIN1 and UTF8 database encodings, as well as SQL_ASCII. (Note SQL_ASCII encoded databases are meant for 7-bit ASCII characters only. Do not use SQL_ASCII databases if your data includes non-ASCII characters.) It should work with any PostgreSQL database encoding, but user testing is encouraged. (The previous version 2 of pgin.tcl does not include character set encoding handling. It may only work properly with SQL_ASCII encoded databases.) REQUIREMENTS: Tcl-8.4.4 or higher, with the latest 8.6.x recommended. PostgreSQL-9.1.x or higher, with the latest 10.x or 9.6.x recommended. Recent testing used the following: Database server: PostgreSQL-9.6.6 and 10.1. Client on Linux: Tcl-8.6.5. Client on Windows XP: ActiveState Tcl-8.6.0 and Tcl-8.5.14 (Older version of PostgreSQL and Tcl might work but are no longer tested.) Pgin.tcl should be usable on all platforms with Tcl, however current testing is limited to 32-bit Linux and Windows platforms. CONTENTS: Documentation: Note: In the zip file distribution only, these documentation files have a ".txt" extension and MS-DOS line endings. README ........... This file COPYING .......... The license for pgin.tcl (BSD/Berkeley Open Source) NEWS ............. Release information and change history REFERENCE ........ Reference documentation for programmers using pgin.tcl INTERNALS ........ Some information about the innards of pgin.tcl Scripts: pgin.tcl ......... This is the complete implementation of the interface. pkgIndex.tcl ..... Package index file tkpsql.tcl ....... An example wish script for interactive database querying FEATURES: + Written completely in Tcl + Implements virtually all the standard (original, bundled) libpgtcl commands + Supports large object manipulation commands + Supports listen/notify + Supports passing a payload with NOTIFY (PostgreSQL-9.0.0 and higher) + Supports replacing the notice handler + Supports pg_execute command + Supports PostgreSQL MD5 challenge/response authentication + pg_result -cmdTuples returns the number of tuples affected by an INSERT, DELETE, or UPDATE + Supports distinguishing NULL database values from empty strings + Implements pg_result -list, and pg_result -llist + Implements pg_escape_string, pg_quote, pg_escape_literal [New: 3.5.0], and pg_escape_identifier [New: 3.5.0] for escaping strings. + Execute prepared statements with: pg_exec_prepared, including sending and receiving un-escaped binary data + Get PostgreSQL parameters with: pg_parameter_status + Get transaction status with: pg_transaction_status + Access expanded error message fields with: pg_result -errorField This was extended [at 2.2.0] to also apply to pg_result -error for compatibility with pgtcl. More fields were added at 3.5.0. + Access extended attribute information with: pg_result -lxAttributes + Get command status tag with pg_result -cmdStatus [New: 2.0.1] + Separate parse and execute with: pg_exec_params, binary safe [New: 2.1.0] + Escape/unescape bytea with: pg_escape_bytea, pg_unescape_bytea [New: 2.2.0] + Return query results as a dictionary with pg_result -dict [New: 3.3.0] + Access to process ID (PID) of backend and in notifications [New: 3.4.0] + Connect via postgresql:// URI, or keyword/value Tcl list [New: 3.5.0] + Supports 64-bit offsets in large objects [New: 3.5.0, PostgreSQL-9.3.0 and up] LIMITATIONS and DIFFERENCES: + pg_connect does not support the older method using a separate dbname plus options for host, port. + Does not support $HOME/.pgpass password file. + Only talks to v3 backend (PostgreSQL 7.4 or higher required). + Uses only TCP/IP sockets (defaults host to localhost, PostgreSQL server must be listening on TCP sockets). Does not support Unix Domain sockets. + Notification messages are only received while reading query results. + Performance isn't great, especially when retrieving large amounts of data. + The values of connection handles and result handles are of a different format than other implementations, but nobody should be relying on these. + No pg_on_connection_loss (New at PostgreSQL 7.3). + No asynchronous query commands (found in pgtcl and pgtcl-ng). + Support for COPY FROM/TO is not compatible with other versions of the interface - must use pg_copy_read and pg_copy_write, no I/O directly to connection handle. + With other pgtcl's, you can have up to 128 active result structures (so leaks can be caught). pgin.tcl has no limits and will not catch result structure leaks. + [Added at 2.1.0] Do not use "return -code N" (for N>4) in the script body for pg_select or pg_execute, because the effect is not well defined. You can safely use return, break, continue, and error (either directly or via return -code). + [Added at 2.2.0] pg_escape_bytea (and pg_unescape_bytea, to a lesser extent) is quite slow. Using it on large bytea objects is not recommended; you should use binary prepared queries instead. + [Added at 3.1.0] Whether or not you use the $conn argument to the string and bytea escape routines, pgin.tcl does not use encoding-aware escaping. This also applies to pg_escape_literal and pg_escape_identifier [at 3.5.0]. + [Added at 3.2.0] pg_escape_bytea always uses the older 'escape' encoding in the returned result, never the newer 'hex' encoding. RELEASE ISSUES: Version 3.5.0 added new commands based on more recent Libpq functions, but some of these will only work when connected to a PostgreSQL-9.3.0 server. This includes 64-bit Large Object offset commands, and new error field codes. Also starting with this release, single-character error field codes in "pg_result -error" and "pg_result -errorField" are now case sensitive. This incompatible change was necessary due to changes in PostgreSQL-9.3.0. Versions 3.4.0 and up handle notification names (also known as channel names) in pg_listen differently from previous versions. This can result in compatibility problems if you used mixed-case names in pg_listen. Starting with version 3.4.0, pgintcl folds the channel name to lower case unless it is in double quotes. This now matches the behavior of pgtcl-ng. See the REFERENCE file and pgintcl bug #2 (old #3410251) for more details. Versions 3.3.0 and up require Tcl 8.4 or higher. Previous versions checked for Tcl 8.3 or higher, but were not actually tested with Tcl 8.3. Versions 3.2.0 and up pass a payload argument to a notification listener handler procedure if a non-empty payload was provided in the SQL NOTIFY command. See the NEWS file for more information and compatibility issues. Version 3 does encoding and decoding of character data, as described in the REFERENCE file. It also sets the PostgreSQL parameter CLIENT_ENCODING to UNICODE when a connection is opened to the server. This is the same behavior as Pgtcl and pgtcl-ng. This informs PostgreSQL that UNICODE data (encoded as UTF-8) will be sent and received. Note that the client application using pgin.tcl can have any encoding which Tcl supports. Tcl converts between the client encoding and Unicode, and the PostgreSQL server converts between Unicode and the database encoding. This assumes the database encoding is other than SQL_ASCII. * * * CAUTION * * * Do not store non-ASCII characters in character or text fields in a PostgreSQL database which was created with encoding SQL_ASCII. The SQL_ASCII encoding provides no information to PostgreSQL on how to translate characters, so the server will be unable to translate. Applications using a Tcl interface, including pgin.tcl, will encode these characters using UTF-8 for storage in the database, but PostgreSQL will not know it due to the SQL_ASCII encoding setting. The result is that it may be impossible to access the data correctly from other applications. Always use the correct encoding when creating a database: for example, LATIN1 or Unicode. Pgin.tcl-2.x and older do not convert to/from Unicode and do not set client_encoding at all. These older versions may not work with non-ASCII characters in any database encoding. At this time, Pgin.tcl does not recode the connection string parameters such as Username, Database Name, or Password. Non-ASCII characters in these fields will probably not work. Older Information: There are some incompatibilities between this release and pre-2.0.0 releases: + pg_parameter_status can no longer fetch all parameters at once; + "pg_configure nulls" option is no longer available. The only way to distinguish NULL from empty string now is with pg_result -getNull. + Changes in large object call error handling. + COPY FROM/TO must use pg_copy_read/pg_copy_write; you cannot read or write copy data from the connection. You will have to change your application if it relies on behavior which changed. See the file NEWS for more information. INSTALLATION AND USAGE: There is no install script. Just copy the script "pgin.tcl" anywhere your application can access it. In your application, insert "source .../pgin.tcl" at the top level, where ... is the directory. This must be run at the top level, so if you need it inside a proc use uplevel as shown below. Optionally, you can install and use pgin.tcl as a Tcl package. You should copy pgin.tcl and pkgIndex.tcl into a sub-directory of your Tcl installation package library root directory (or you can extend auto_path: see the Tcl documentation for the 'package' and 'pkgMkIndex' commands). Then your application can load pgin.tcl with the following: package require pgintcl You can use the included "tkpsql.tcl" script to try it out. This is a simple interactive GUI program to issue database queries, vaguely like the Sybase ASA "dbisql" program. On **ix systems, type "wish tkpsql.tcl" to start it; under Windows you should be able to double click on it from Explorer. You need to press F1 or click on the Run button after each query.