[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