Previous: SXEmacs PostgreSQL libpq API, Up: PostgreSQL Support [Contents][Index]
This is an example of one method of establishing an asynchronous connection.
(defun database-poller (P) (message "%S before poll" (pq-pgconn P 'pq::status)) (pq-connect-poll P) (message "%S after poll" (pq-pgconn P 'pq::status)) (if (eq (pq-pgconn P 'pq::status) 'pg::connection-ok) (message "Done!") (add-timeout .1 'database-poller P))) ⇒ database-poller (progn (setq P (pq-connect-start "")) (add-timeout .1 'database-poller P)) ⇒ pg::connection-started before poll ⇒ pg::connection-made after poll ⇒ pg::connection-made before poll ⇒ pg::connection-awaiting-response after poll ⇒ pg::connection-awaiting-response before poll ⇒ pg::connection-auth-ok after poll ⇒ pg::connection-auth-ok before poll ⇒ pg::connection-setenv after poll ⇒ pg::connection-setenv before poll ⇒ pg::connection-ok after poll ⇒ Done! P ⇒ #<PGconn kantdb:5432 freundt/freundt>
Here is an example of one method of doing an asynchronous reset.
(defun database-poller (P) (let (PS) (message "%S before poll" (pq-pgconn P 'pq::status)) (setq PS (pq-reset-poll P)) (message "%S after poll [%S]" (pq-pgconn P 'pq::status) PS) (if (eq (pq-pgconn P 'pq::status) 'pg::connection-ok) (message "Done!") (add-timeout .1 'database-poller P)))) ⇒ database-poller (progn (pq-reset-start P) (add-timeout .1 'database-poller P)) ⇒ pg::connection-started before poll ⇒ pg::connection-made after poll [pgres::polling-writing] ⇒ pg::connection-made before poll ⇒ pg::connection-awaiting-response after poll [pgres::polling-reading] ⇒ pg::connection-awaiting-response before poll ⇒ pg::connection-setenv after poll [pgres::polling-reading] ⇒ pg::connection-setenv before poll ⇒ pg::connection-ok after poll [pgres::polling-ok] ⇒ Done! P ⇒ #<PGconn kantdb:5432 freundt/freundt>
And finally, an asynchronous query.
(defun database-poller (P) (let (R) (pq-consume-input P) (if (pq-is-busy P) (add-timeout .1 'database-poller P) (setq R (pq-get-result P)) (if R (progn (push R result-list) (add-timeout .1 'database-poller P)))))) ⇒ database-poller (when (pq-send-query P "SELECT * FROM sxemacs_codenames;") (setq result-list nil) (add-timeout .1 'database-poller P)) ⇒ 1910971 ;; wait a moment result-list ⇒ (#<PGresult PGRES_TUPLES_OK[7] - SELECT>)
Here is an example showing how multiple SQL statements in a single query can have all their results collected.
;; Using the same database-poller
function from the previous example
(when (pq-send-query P "SELECT * FROM sxemacs_codenames;
SELECT * FROM pg_database;
SELECT * FROM pg_user;")
(setq result-list nil)
(add-timeout .1 'database-poller P))
⇒ 1911150
;; wait a moment
result-list
⇒ (#<PGresult PGRES_TUPLES_OK[8] - SELECT> #<PGresult PGRES_TUPLES_OK[10] - SELECT> #<PGresult PGRES_TUPLES_OK[7] - SELECT>)
Here is an example which illustrates collecting all data from a query, including the field names.
(defun pg-util-query-results (results) "Retrieve results of last SQL query into a list structure." (let ((i (1- (pq-ntuples R))) j l1 l2) (while (>= i 0) (setq j (1- (pq-nfields R))) (setq l2 nil) (while (>= j 0) (push (pq-get-value R i j) l2) (decf j)) (push l2 l1) (decf i)) (setq j (1- (pq-nfields R))) (setq l2 nil) (while (>= j 0) (push (pq-fname R j) l2) (decf j)) (push l2 l1) l1)) ⇒ pg-util-query-results (setq R (pq-exec P "SELECT * FROM sxemacs_codenames ORDER BY codename DESC;")) ⇒ #<PGresult PGRES_TUPLES_OK[7] - SELECT> (pg-util-query-results R) ⇒ (("id" "version" "codename") ("8" "22.1.7" "Celica") ("7" "22.1.6" "Cadillac") ("6" "22.1.5" "Bugatti") ("4" "22.1.3" "BMW") ("3" "22.1.2" "Audi") ("2" "22.1.1" "Aston Martin") ("1" "22.1.0" "Alfa Romeo"))
Here is an example of a query that uses a database cursor.
(let (data R) (setq R (pq-exec P "BEGIN;")) (setq R (pq-exec P "DECLARE k_cursor CURSOR FOR SELECT * FROM sxemacs_codenames ORDER BY version DESC;")) (setq R (pq-exec P "FETCH k_cursor;")) (while (eq (pq-ntuples R) 1) (push (list (pq-get-value R 0 0) (pq-get-value R 0 1)) data) (setq R (pq-exec P "FETCH k_cursor;"))) (setq R (pq-exec P "END;")) data) ⇒ (("1" "22.1.0") ("2" "22.1.1") ("3" "22.1.2") ("4" "22.1.3") ("6" "22.1.5") ("7" "22.1.6") ("8" "22.1.7"))
Here’s another example of cursors, this time with a Lisp macro to implement a mapping function over a table.
(defmacro map-db (P table condition callout) `(let (R) (pq-exec ,P "BEGIN;") (pq-exec ,P (concat "DECLARE k_cursor CURSOR FOR SELECT * FROM " ,table " " ,condition " ORDER BY codename DESC;")) (setq R (pq-exec P "FETCH k_cursor;")) (while (eq (pq-ntuples R) 1) (,callout (pq-get-value R 0 0) (pq-get-value R 0 1)) (setq R (pq-exec P "FETCH k_cursor;"))) (pq-exec P "END;"))) ⇒ map-db (defun callback (arg1 arg2) (message "arg1 = %s, arg2 = %s" arg1 arg2)) ⇒ callback (map-db P "sxemacs_codenames" "WHERE version > '22.1.0'" callback) ⇒ arg1 = 8, arg2 = 22.1.7 ⇒ arg1 = 7, arg2 = 22.1.6 ⇒ arg1 = 6, arg2 = 22.1.5 ⇒ arg1 = 4, arg2 = 22.1.3 ⇒ arg1 = 3, arg2 = 22.1.2 ⇒ arg1 = 2, arg2 = 22.1.1 ⇒ #<PGresult PGRES_COMMAND_OK - COMMIT>
Previous: SXEmacs PostgreSQL libpq API, Up: PostgreSQL Support [Contents][Index]