1;; Postgresql C library interface, example program 1, using the xedit
2;; lisp interface
3
4;; Test the C version of libpq, the PostgreSQL frontend library.
5(require "psql")
6
7(defun exit-nicely (conn)
8    (pq-finish conn)
9    (quit 1)
10)
11
12;; begin, by setting the parameters for a backend connection if the
13;; parameters are null, then the system will try to use reasonable
14;; defaults by looking up environment variables or, failing that,
15;; using hardwired constants
16(setq pghost nil)		; host name of the backend server
17(setq pgport nil)		; port of the backend server
18(setq pgoptions nil)		; special options to start up the backend server
19(setq pgtty nil)		; debugging tty for the backend server
20(setq pgdbname "template1")
21
22;; make a connection to the database
23(setq conn (pq-setdb pghost pgport pgoptions pgtty pgdbname))
24
25;; check to see that the backend connection was successfully made
26(when (= (pq-status conn) pg-connection-bad)
27    (format t "Connection to database '~A' failed.~%" pgdbname)
28    (format t "~A" (pq-error-message conn))
29    (exit-nicely conn))
30
31;; start a transaction block
32(setq res (pq-exec conn "BEGIN"))
33(when (or (null res) (not (= (pq-result-status res) pgres-command-ok)))
34    (format t "BEGIN command failed~%")
35    (pq-clear res)
36    (exit-nicely conn))
37
38;; Should PQclear PGresult whenever it is no longer needed to avoid memory leaks
39(pq-clear res)
40
41;; fetch rows from the pg_database, the system catalog of databases
42(setq res (pq-exec conn "DECLARE mycursor CURSOR FOR select * from pg_database"))
43(when (or (null res) (not (= (pq-result-status res) pgres-command-ok)))
44    (format t "DECLARE CURSOR command failed~%")
45    (pq-clear res)
46    (exit-nicely conn))
47(pq-clear res)
48(setq res (pq-exec conn "FETCH ALL in mycursor"))
49(when (or (null res) (not (= (pq-result-status res) pgres-tuples-ok)))
50    (format t "FETCH ALL command didn't return tuples properly~%")
51    (pq-clear res)
52    (exit-nicely conn))
53
54;; first, print out the attribute names
55(setq nfields (pq-nfields res))
56(dotimes (i nfields)
57    (format t "~15@<~A~>" (pq-fname res i))
58)
59(format t "~%")
60
61;; next, print out the rows
62(setq ntuples (pq-ntuples res))
63(dotimes (i ntuples)
64    (dotimes (j nfields)
65	(format t "~15@<~A~>" (pq-getvalue res i j))
66    )
67    (format t "~%")
68)
69(pq-clear res)
70
71;; close the cursor
72(setq res (pq-exec conn "CLOSE mycursor"))
73(pq-clear res)
74
75;; commit the transaction
76(setq res (pq-exec conn "COMMIT"))
77(pq-clear res)
78
79;; close the connection to the database and cleanup
80(pq-finish conn)
81