November 2010 - postmodern-devel - mailman3.common-lisp.net

[postmodern-devel] Quoted lists with QUERY or SQL
by Brit Butler 30 Nov '10

30 Nov '10

Hello, I'm working on a Postmodern backend for weblocks. To conform to their Store API<http://bitbucket.org/S11001001/weblocks-dev/src/f14b59da2238/src/store/stor…> I need to implement a count-persistent-objects method. I would like to be able to write something like: (defmethod count-persistent-objects ((store list) class-name &key where &allow-other-keys) (let ((sql-expr `(:select (:count '*) :from ,class-name ,@(when where (list :where where))))) (with-connection store (query sql-expr :single)))) However a simple test of this results in a type error: The value of CL-POSTGRES::QUERY is (:SELECT (:COUNT '*) :FROM QUOTES), which is not of type STRING. Is there a reason that query doesn't support evaluated lists? If so, how would you recommend this be done? Regards, Brit Butler

2 2

[postmodern-devel] Question on returning multi-dimensional arrays
by Sabra Crolleton 28 Nov '10

28 Nov '10

Hello all, I'm having trouble trying to get a array result out of a database. Assume (query "create table test2 ( id integer, name text, stuff1 integer[] , stuff2 integer[][])") (query "insert into test2 values (5,'t5',array[21,78,93,24],array[[83,92],[16,27]])") If I'm at a psql command line, then select stuff2[1:2][1] from test2 where id=5; returns {{83},{16}} If I try to use postmodern, eg: (query "select stuff2[1:2][1] from test2 where id=5") I get an error message about junk in the result: junk in string "{83" [Condition of type SB-INT:SIMPLE-PARSE-ERROR] I'm likely missing something obvious. Everything is fine if I'm only trying to get a single item out of the array, but ideally I'd like to just pull the entire array out directly into a variable. Any suggestions or pointers would be appreciated. Sabra

2 1

[postmodern-devel] Multiple connections and connection pools
by Slobodan Milnović 25 Nov '10

25 Nov '10

Hi, I have a bit of a problem understanding how multiple connections and connection pools work. What I would like to do is to open an connection to the database, read several rows from one table with doquery, and for each read row analyze data, and if conditions are met, write a row to another table. I'd rather not use memory to store the results from the first query because I am a bit memory constrained. I have tried the following (just the simplification of the actual code), for which I get the message "Database error: This connection is still processing another query." (defvar *db-parameters* (list "databasename" "databaseuser" "databasepassword" "dbhostaddress" :pooled-p t)) (defmacro with-db (&body body) `(postmodern:with-connection *db-parameters* ,@body)) (defun process-some-stuff () (with-db (postmodern:doquery (read some stuff from a table) (several variables) (analyze row) (if some conditions (with-db (postmodern:query (insert stuff into another table))))))) Also, I have tried using two postmodern:connection and then after each with-db, but the error is the same. I understand that when I use ":pooled-p t" postmodern will use some kind of pool, but I have'nt been able to understand how to use it properly, nor did I manage to find any kind of solution on the internet. The test.lisp in postmodern source directory did test the pool, but each time the connection was imediatelly closed after openning it, and there were no examples like in my situation. Perhaps the nesting of the querries is not allowed in postmodern?

2 3

[postmodern-devel] Parameterized sql strings
by achambers＠mcna.net 17 Nov '10

17 Nov '10

Hi, Am I correct in thinking that there is currently no way to parameterize raw SQL strings to be sent through postmodern? I realize most SQL can be expressed as S-SQL but it doesn't seem to support COPY syntax yet. I added the function `sql-fmt' for this purpose and am submitting it to the list in case it's useful for others.... (defun sql-fmt (fmt &rest args) (apply 'format nil fmt (mapcar 'sql-ize args))) In my repo [1], I added this to the :export list of both s-sql and postmodern packages. Please pull the change or let me know if you need this changed in any way. [1]: https://github.com/cddr/Postmodern/commits/master Cheers, Andy

3 3

[postmodern-devel] Problems with doquery and simple-date
by Slobodan Milnović 16 Nov '10

16 Nov '10

Hi, There is probably some simple reason why these won't work, but I'm not as good in lisp to understand it. This works: CL-USER> (simple-date:decode-timestamp (caar (postmodern:query (:limit (:select (:dot 'sem 'avl 'timestamp) :from (:dot 'sem 'avl)) 1)))) 2010 1 12 8 35 50 0 This doesn't: CL-USER> (postmodern:doquery (:limit (:select (:dot 'sem 'avl 'timestamp) :from (:dot 'sem 'avl)) 1) (ivo) (simple-date:decode-timestamp ivo)) NIL I don't understand the difference, especially since for other stuff doquery works. Please advise.

2 2