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(a)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(a)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
