[racket] in-query problems

On 12/11/2014 10:31 AM, George Neuner wrote:
> Hi Ryan,
>
> I re-read your message and did some more experimenting.
>
> On 12/10/2014 2:15 PM, Ryan Culpepper wrote:
>> Could you be turning entire rows into records (perhaps to deal with the issue below)?
>
> That seems to be precisely what is happening, although it was forced
> because  in-query  won't accept  return of multiple columns without
> parentheses.  I wasn't aware that the parentheses were being passed
> through to Postgresql.

The SQL strings you provide are never modified by the db library before 
being passed to the database back end.

Here are some examples of in-query with multiple columns:

   Setup:
   > (query-exec c "create temporary table numbers (n integer, t text)")
   > (query-exec c "insert into numbers values ($1, $2)" 1 "one")
   > (query-exec c "insert into numbers values ($1, $2)" 2 "two")
   > (query-exec c "insert into numbers values ($1, $2)" 3 "three")
   > (query-exec c "insert into numbers values ($1, $2)" 4 "four")
   > (query-exec c "insert into numbers values ($1, $2)" 5 "five")

   Using in-query within a for loop:
   > (for/list ([(n t) (in-query c "select n, t from numbers")])
       (list n t))
   '((1 "one") (2 "two") (3 "three") (4 "four") (5 "five"))

   Using in-query and * within a for loop:
   > (for/list ([(n t) (in-query c "select * from numbers")])
       (list n t))
   '((1 "one") (2 "two") (3 "three") (4 "four") (5 "five"))

   Using cursor in-query outside a for loop:
   > (start-transaction c)
   > (define seq (in-query c "select n, t from numbers" #:fetch 1))
   > (define-values (more? next) (sequence-generate seq))
   > (more?)
   #t
   > (next)     ;; NOTE: returns 2 values
   1
   "one"
   > (let-values ([(n t) (next)]) (list n t))
   '(2 "two")
   > (commit-transaction)   ;; closes the cursor


> However, the documentation for in-query says:
>
>     "Executes a SQL query, which must produce rows, and returns a
>     sequence. Each step in the sequence produces as many values as the
>     rows have columns."
>
> To me that says that  in-query  is supposed to return rows - perhaps as
> (values ...) instead of a vector, but rows nonetheless.  I don't know
> how else to interpret those statements.

That just means you can't use in-query with an INSERT statement, for 
example.

>  From what you said and what I am seeing, it appears that  in-query
> really is just an iterable form of  query-list - making in-query not as
> useful as it could be.   However, the documentation for query-list
> explicitly says "... must produce rows of exactly one column ...", which
> makes clear the operation.
>
>
>> One other note: you can also create use cursors through the SQL statements PREPARE, EXECUTE, etc.
>
> Yes.  However, explicit cursor use is not as clean.
>
>
>> I could add a variant of in-query (maybe ‘in-query-rows’) that for each step produces a vector representing the entire row, rather than one value per field. Would that solve the problem?
>
> That would be wonderful!   I'm still using 6.0.1 if that makes any
> difference, so please issue it as a patch.

I went looking around the documentation for sequences, and you can do 
nearly the same thing with in-values-sequence:

 > (for/list ([row (in-values-sequence
                     (in-query c "select n, t from numbers"))])
     row)
'((1 "one") (2 "two") (3 "three") (4 "four") (5 "five"))

You get a list instead of a vector for each step, though.

Ryan