1
;; Postgresql C library interface, example program 3, using the xedit
4
;; Test the binary cursor interface
6
;; populate a database by doing the following:
8
;; CREATE TABLE test1 (i int4, d real, p polygon);
10
;; INSERT INTO test1 values (1, 3.567, polygon '(3.0, 4.0, 1.0, 2.0)');
12
;; INSERT INTO test1 values (2, 89.05, polygon '(4.0, 3.0, 2.0, 1.0)');
14
;; the expected output is:
16
;; tuple 0: got i = (4 bytes) 1, d = (4 bytes) 3.567000, p = (4
17
;; bytes) 2 points boundbox = (hi=3.000000/4.000000, lo =
18
;; 1.000000,2.000000) tuple 1: got i = (4 bytes) 2, d = (4 bytes)
19
;; 89.050003, p = (4 bytes) 2 points boundbox =
20
;; (hi=4.000000/3.000000, lo = 2.000000,1.000000)
22
;; Output of the lisp code:
24
;; type[0] = 23, size[0] = 4
25
;; type[1] = 700, size[1] = 4
26
;; type[2] = 604, size[2] = -1
29
;; d = (4 bytes) 3.567
30
;; p = (4 bytes) 2 points boundbox = (hi=3.0/4.0, lo = 1.0/2.0)
33
;; d = (4 bytes) 89.05
34
;; p = (4 bytes) 2 points boundbox = (hi=4.0/3.0, lo = 2.0/1.0)
39
(defun exit-nicely (conn)
44
;; begin, by setting the parameters for a backend connection if the
45
;; parameters are null, then the system will try to use reasonable
46
;; defaults by looking up environment variables or, failing that,
47
;; using hardwired constants
48
(setq pghost nil) ; host name of the backend server
49
(setq pgport nil) ; port of the backend server
50
(setq pgoptions nil) ; special options to start up the backend server
51
(setq pgtty nil) ; debugging tty for the backend server
52
(setq pgdbname "test") ; change this to the name of your test database
53
;; XXX Note: getenv not yet implemented in the
56
;; make a connection to the database
57
(setq conn (pq-setdb pghost pgport pgoptions pgtty pgdbname))
59
;; check to see that the backend connection was successfully made
60
(when (= (pq-status conn) pg-connection-bad)
61
(format t "Connection to database '~A' failed.~%" pgdbname)
62
(format t "~A" (pq-error-message conn))
65
(setq res (pq-exec conn "BEGIN"))
66
(when (= (pq-status conn) pg-connection-bad)
67
(format t "BEGIN command failed~%")
71
;; Should PQclear PGresult whenever it is no longer needed to avoid memory leaks
74
(setq res (pq-exec conn "DECLARE mycursor BINARY CURSOR FOR select * from test1"))
75
(when (= (pq-status conn) pg-connection-bad)
76
(format t "DECLARE CURSOR command failed~%")
81
(setq res (pq-exec conn "FETCH ALL in mycursor"))
82
(when (or (null res) (not (= (pq-result-status res) pgres-tuples-ok)))
83
(format t "FETCH ALL command didn't return tuples properly~%")
87
(setq i-fnum (pq-fnumber res "i"))
88
(setq d-fnum (pq-fnumber res "d"))
89
(setq p-fnum (pq-fnumber res "p"))
92
(format t "type[~D] = ~D, size[~D] = ~D~%"
93
i (pq-ftype res i) i (pq-fsize res i))
96
(dotimes (i (pq-ntuples res))
97
(setq i-val (pq-getvalue res i i-fnum 'int32))
98
(setq d-val (pq-getvalue res i d-fnum 'float))
99
(setq p-val (pq-getvalue res i p-fnum 'pg-polygon))
100
(format t "tuple ~D: got~%" i)
101
(format t " i = (~D bytes) ~D~%" (pq-getlength res i i-fnum) i-val)
102
(format t " d = (~D bytes) ~D~%" (pq-getlength res i d-fnum) d-val)
103
(format t " p = (~D bytes) ~D points~,8@Tboundbox = (hi=~F/~F, lo = ~F/~F)~%"
104
(pq-getlength res i d-fnum) (pg-polygon-num-points p-val)
105
(pg-point-x (pg-box-high (pg-polygon-boundbox p-val)))
106
(pg-point-y (pg-box-high (pg-polygon-boundbox p-val)))
107
(pg-point-x (pg-box-low (pg-polygon-boundbox p-val)))
108
(pg-point-y (pg-box-low (pg-polygon-boundbox p-val))))
112
(setq res (pq-exec conn "CLOSE mycursor"))
115
(setq res (pq-exec conn "COMMIT"))
added
removed
xedit/lisp/test/psql-3.lsp
