Parameter Types

Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 857299FB1C0 for ; Thu, 9 Nov 2006 11:21:16 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 51135-04 for ; Thu, 9 Nov 2006 15:21:08 +0000 (UTC) X-Greylist: delayed 00:16:06.094387 by SQLgrey- Received: from beth.coza.net.za (beth.coza.net.za [206.223.136.193]) by postgresql.org (Postfix) with ESMTP id 3AEAF9FB28D for ; Thu, 9 Nov 2006 11:21:04 -0400 (AST) Received: from theo-laptop.int.coza.net.za ([206.223.136.211]) by beth.coza.net.za (8.11.6/8.11.2) with ESMTP id kA9F4lC13539 for ; Thu, 9 Nov 2006 17:04:48 +0200 Received: from localhost.localdomain (localhost.localdomain [127.0.0.1]) by theo-laptop.int.coza.net.za (8.13.7/8.13.7) with ESMTP id kA9F4mac007336 for ; Thu, 9 Nov 2006 17:04:48 +0200 Subject: Re: Documentation update for PQexecParams From: Theo Kramer Reply-To: theo@flame.co.za To: pgsql-docs@postgresql.org In-Reply-To: <20061109144348.GA10157@fetter.org> References: <1163009239.2384.2.camel@josh> <20061109144348.GA10157@fetter.org> Content-Type: multipart/mixed; boundary="=-iowTID3ZjNd8VB4ZI0BD" Organization: Flame Computing Enterprises cc Date: Thu, 09 Nov 2006 17:04:47 +0200 Message-Id: <1163084688.2448.28.camel@theo-laptop.int.coza.net.za> Mime-Version: 1.0 X-Mailer: Evolution 2.6.3 (2.6.3-1.fc5.5) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/5 X-Sequence-Number: 3839 --=-iowTID3ZjNd8VB4ZI0BD Content-Type: text/plain Content-Transfer-Encoding: 7bit On Thu, 2006-11-09 at 06:43 -0800, David Fetter wrote: > On Wed, Nov 08, 2006 at 08:07:19PM +0200, Theo Kramer wrote: > > Hi > > > > I have been using PQprepare() and PQexecPrepared(). This has led me to > > update libpq.sgml, in particular for PQexecParams(). > > > > The changes are to the formatting of the arguments for PQexecParams() > > (as shared by PQexecPrepared()) which make it easier to read, as well as > > creating a table giving a set of possible values for the various arguments with > > the associated SQL types. > > > > I would imagine the best place to submit the patch would be to > > psql-patches, however, before I do this I am wondering if this is the right forum for > > having the changes vetted before submitting the patch. > > > > Please advise. > > Send it to both lists, and thanks for doing this :) > Thanks - attached output of 'diff -c libpq.sgml libpq.sgml.org' where libpq.sgml contains my proposed changes and libpq.sgml.org originates from the 8.2 Beta 2 distribution for comment. -- Regards Theo --=-iowTID3ZjNd8VB4ZI0BD Content-Disposition: attachment; filename=libpq.sgml.dif Content-Type: text/x-patch; name=libpq.sgml.dif; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit *** libpq.sgml Tue Nov 7 22:06:37 2006 --- libpq.sgml.org Mon Oct 23 20:10:31 2006 *************** *** 956,962 **** are connected to. The number is formed by converting the major, minor, and revision numbers into two-decimal-digit numbers and appending them together. For example, version 8.1.5 will be returned as 80105, and version ! 8.2 will be returned as 80200 (leading zeroes are not shown). Zero is returned if the connection is bad. --- 956,962 ---- are connected to. The number is formed by converting the major, minor, and revision numbers into two-decimal-digit numbers and appending them together. For example, version 8.1.5 will be returned as 80105, and version ! 8.1 will be returned as 80100 (leading zeroes are not shown). Zero is returned if the connection is bad. *************** *** 1138,1237 **** ! ! ! ! command ! ! ! The SQL command to be executed. If parameters are used, they are referred to in the command string ! as $1, $2, etc. ! ! ! ! ! ! nParams ! ! ! The number of parameters supplied; it is the length ! of the arrays paramTypes[], paramValues[], ! paramLengths[], and paramFormats[]. (The array pointers ! may be NULL when nParams is zero.) ! ! ! ! ! ! paramTypes[] ! ! ! Specifies, by OID, the data types to be assigned to ! the parameter symbols. If paramTypes is NULL, or any particular ! element in the array is zero, the server assigns a data type to the parameter ! symbol in the same way it would do for an untyped literal string. ! ! ! ! ! ! paramValues[] ! ! ! Specifies the actual values of the parameters. ! A null pointer in this array means the corresponding parameter is null; ! otherwise the pointer points to a zero-terminated text string (for text ! format) or binary data in the format expected by the server (for binary ! format). ! ! ! ! ! ! paramLengths[] ! ! ! Specifies the actual data lengths of binary-format parameters. ! It is ignored for null parameters and text-format parameters. ! The array pointer may be null when there are no binary parameters. ! ! ! ! ! ! paramFormats[] ! ! ! Specifies whether parameters are text (put a zero in the array for ! the corresponding parameter) or binary (put a one in the array for ! the corresponding parameter). If the array pointer is null then all ! parameters are presumed to be zero terminated text strings. ! ! ! ! ! ! resultFormat ! ! ! Specify zero to obtain results in text format, or one to ! obtain results in binary format. (There is not currently a provision to ! obtain different result columns in different formats, although that is ! possible in the underlying protocol.) ! ! ! ! ! - The primary advantage of PQexecParams over PQexec is that parameter values may be separated from the command string, thus avoiding the need for tedious and error-prone quoting and escaping. - - Unlike PQexec, PQexecParams allows at most one SQL command in the given string. (There can be semicolons in it, but not more than one nonempty command.) This is a limitation of the underlying protocol, --- 1138,1178 ---- ! If parameters are used, they are referred to in the command string ! as $1, $2, etc. ! nParams is the number of parameters supplied; it is the length ! of the arrays paramTypes[], paramValues[], ! paramLengths[], and paramFormats[]. (The ! array pointers may be NULL when nParams is zero.) ! paramTypes[] specifies, by OID, the data types to be assigned to ! the parameter symbols. If paramTypes is NULL, or any particular ! element in the array is zero, the server assigns a data type to the parameter ! symbol in the same way it would do for an untyped literal string. ! paramValues[] specifies the actual values of the parameters. ! A null pointer in this array means the corresponding parameter is null; ! otherwise the pointer points to a zero-terminated text string (for text ! format) or binary data in the format expected by the server (for binary ! format). ! paramLengths[] specifies the actual data lengths of ! binary-format parameters. It is ignored for null parameters and text-format ! parameters. The array pointer may be null when there are no binary ! parameters. ! paramFormats[] specifies whether parameters are text (put a zero ! in the array) or binary (put a one in the array). If the array pointer is ! null then all parameters are presumed to be text. ! resultFormat is zero to obtain results in text format, or one to ! obtain results in binary format. (There is not currently a provision to ! obtain different result columns in different formats, although that is ! possible in the underlying protocol.) + + + The primary advantage of PQexecParams over PQexec is that parameter values may be separated from the command string, thus avoiding the need for tedious and error-prone quoting and escaping. Unlike PQexec, PQexecParams allows at most one SQL command in the given string. (There can be semicolons in it, but not more than one nonempty command.) This is a limitation of the underlying protocol, *************** *** 1259,1416 **** - shows a range of typical values for - paramTypes[] in column PG Type, as determined from - pg_type.h, as well as the corresponding SQL attribute names, ODBC and C types, and lengths. - - - - Parameter Types - - - - Name - PG Type - ODBC Type - C Type - Length Text Format - Length Binary Format - Description - - - - - - character(n) - BPCHAROID - SQL_CHAR - char[n + 1] - 0 - n - Blanks are automatically padded for the length of the string. - - - - varchar(n) - VARCHAROID - SQL_VARCHAR - char[n + 1] - 0 - n - Not blank padded. - - - - text - TEXTOID - SQL_LONGVARCHAR - char * - 0 - n - String with appropriate size must be allocated. Care must be taken not to overflow allocated string. - - - - smallint - INT2OID - SQL_SHORT - short - 0 - 2 - 2 byte short integer - - - - integer - INT4OID - SQL_INTEGER - int - 0 - 4 - 4 byte integer - - - - real - FLOAT4OID - SQL_REAL - float - 0 - 4 - 4 byte floating point - - - - double precision - FLOAT8OID - SQL_DOUBLE - double - 0 - 8 - 8 byte floating point - - - - numeric[(p,s)] - NUMERICOID - SQL_NUMERIC - structure - 0 - variable - Cast to and from double if no bcd library and use 8 as binary length. Equivalent to decimal[(p,s)] - - - - timestamp - TIMESTAMPOID - SQL_TYPE_TIMESTAMP - long long - 0 - 8 - - - - - -

- - - Column Details - - - - Name The SQL attribute type. - - - PG Type The equivalent PostgreSQL type from - pg_types.h for paramTypes[]. - - - ODBC Type The equivalent ODBC type. - - - C Type The equivalent C language type. - - - Length Text Format indicates value for paramLengths[] - for text format. - - - Length Binary Format indicates value for paramLengths[] - for binary format. - - - - Refer to table for further details on data types supported. - - - - - - - - - PQpreparePQprepare --- 1200,1205 ---- --=-iowTID3ZjNd8VB4ZI0BD--