Update of /project/pg/cvsroot/pg In directory clnet:/tmp/cvs-serv24811

Modified Files: README pg.lisp v3-protocol.lisp Log Message: Improved documentation and a basic example for the use of execution plans (prepared statements).

--- /project/pg/cvsroot/pg/README 2006/08/28 20:08:00 1.6 +++ /project/pg/cvsroot/pg/README 2006/09/15 20:49:03 1.7 @@ -1,7 +1,7 @@ pg.lisp -- socket level interface to the PostgreSQL RDBMS for Common Lisp

Author: Eric Marsden eric.marsden@free.fr - Time-stamp: <2006-08-28 emarsden> + Time-stamp: <2006-09-15 emarsden> Version: 0.23

Copyright (C) 1999,2000,2001,2002,2003,2004,2005,2006 Eric Marsden @@ -102,6 +102,64 @@ (pg-disconnect connection) -> nil Close the database connection.

+ +=== Support for prepared statements ==================================== + + (pg-supports-pbe conn) -> boolean + Returns T iff the connection to the database is able to support + prepared statements. This is only true of connections using + version 3 of the frontend/backend protocol. + + (pg-prepare conn statement-name sql &optional parameter-types) + Prepares an execution plan for a query (a prepared statement). + The prepared statement may contain arguments that are refered to + as $1, $2 etc; if arguments are present their types must be + declared via the list PARAMETER-TYPES. Each element of + PARAMETER-TYPES should be a string that defines the type of its + corresponding parameter (see PG::*TYPE-PARSERS* for examples of + type names used by PostgreSQL). + + Using execution plans is more efficient than multiple calls to + PG-EXEC, since the parsing and query optimizing phase only occurs + once, at preparation time. It also helps to protect against "SQL + injection" attacks, by ensuring that arguments to an SQL query + cannot be interpreted as a part of the SQL request. + + (pg-bind conn portal-name statement-name typed-arguments) + Binds the execution plan that was previously prepared as + STATEMENT-NAME to PORTAL-NAME, with TYPED-ARGUMENTS. + TYPED-ARGUMENTS is a list of tuples of the form '(type value), + where TYPE is one of :char, :byte, :int16, :int32, :string. + + (pg-execute conn portal-name &optional maximal-return-rows) + Executes the execution plan that was previously bound to + PORTAL-NAME. Optionally returns up to MAXIMAL-RETURN-ROWS rows + (0 means an unlimited number of rows). + + (pg-close-statement conn statement-name) + Releases the command execution plan (prepared statement) + STATEMENT-NAME. This also releases any open portals for that + prepared statement. + + (pg-close-portal conn portal-name) + Releases the portal PORTAL-NAME. + +Example using prepared statements: + + (defun delete-item (db-connection int-value string-value) + (pg-prepare db-connection "delete-statement" + "DELETE FROM items WHERE int_column = $1 AND string_column = $2" + `("int4" "varchar")) + (unwind-protect + (progn (pg-bind db-connection "delete-portal" "delete-statement" + `((:int32 ,int-value) (:string ,string-value))) + (pg-execute db-connection "delete-portal")) + ;; NB: portal is closed automatically when statement is closed + (pg-close-statement db-connection "select-statement"))) + + +=== Introspection support ============================================== + (pg-databases connection) -> list of strings Return a list of the databases available at this site (a database is a set of tables; in a virgin PostgreSQL @@ -119,6 +177,9 @@ detailed information (attribute types, for example), it can be obtained from `pg-result' on a SELECT statement for that table.

+ +=== Support for large objects (BLOBs) ================================= + (pglo-create conn . args) -> oid Create a new large object (BLOB, or binary large object in other DBMSes parlance) in the database to which we are --- /project/pg/cvsroot/pg/pg.lisp 2006/08/28 20:08:00 1.7 +++ /project/pg/cvsroot/pg/pg.lisp 2006/09/15 20:49:03 1.8 @@ -1,7 +1,7 @@ ;;; pg.lisp -- socket level interface to the PostgreSQL RDBMS for Common Lisp ;; ;; Author: Eric Marsden eric.marsden@free.fr -;; Time-stamp: <2006-08-28 emarsden> +;; Time-stamp: <2006-09-15 emarsden> ;; Version: 0.22 ;; ;; Copyright (C) 1999,2000,2001,2002,2003,2004,2005 Eric Marsden @@ -200,7 +200,8 @@

(defgeneric pg-close-statement (connection statement-name) (:documentation - "Closes a prepared statement")) + "Closes prepared statement specified by STATEMENT-NAME and closes +all portals associated with that statement (see PG-PREPARE and PG-BIND)."))

(defgeneric pg-close-portal (connection portal) (:documentation --- /project/pg/cvsroot/pg/v3-protocol.lisp 2006/09/15 20:04:37 1.20 +++ /project/pg/cvsroot/pg/v3-protocol.lisp 2006/09/15 20:49:03 1.21 @@ -902,8 +902,7 @@ (:int16 0))))) t))

-(defmethod pg-execute ((connection pgcon-v3) (portal string) &optional (maxinum-number-of-rows 0)) - +(defmethod pg-execute ((connection pgcon-v3) (portal string) &optional (maximum-number-of-rows 0)) ;; have it describe the result: (send-packet connection #\D