0

I'm looking for a way for HDBC to support multiple resultsets in a single statement

testMultipleResultsetSingleStatement = do
  let sql = "select 1,2,3 union all select 2,3,4 select 'a', 'b'"
  c <- connectODBC connectionString
  rs <- quickQuery c sql []
  return rs

this will only return [[SqlInt32 1,SqlInt32 2,SqlInt32 3],[SqlInt32 2,SqlInt32 3,SqlInt32 4]]

We see here that the results from second resultset are discarded

I'm wondering if there is another function than quickQuery that would support this?

Ideally, the return type would be [[[SqlValue]]] instead of [[SqlValue]] so the first outermost list would correspond to each result set returned by the query.

If HDBC doesn't provide a way for it, what other package would handle statements which returns multiple resultsets?

edit: Actually, a solution without API change would be to make it work this way:

testMultipleResultsetSingleStatement = do
  let
    sql = "select 1,2,3 union all select 2,3,4 select 'a', 'b'"
  c <- connectODBC connectionString
  statement <- prepare c sql
  _ <- execute statement []
  rows1 <- fetchAllRows statement
  rows2 <- fetchAllRows statement
  return (rows1, rows2)

I checked and in the case of sqlserver it did return an empty list for rows2

smoothdeveloper
  • 1,972
  • 18
  • 19

1 Answers1

1

No, it is not supported currently in hdbc.

Though i see no purpose for this kind of feature.

How would it be better than

let sql1 = "select 1,2,3 union all select 2,3,4"
    sql2 = " select 'a', 'b'"

c <- connectODBC connectionString

rs1 <- quickQuery c sql1 []
rs2 <- quickQuery c sql2 []

return (rs1,rs2)

or if you really insist on having dirrerent records with different number of fileds and types of fields in one list (hmm weird but ok) you could do this:

return $ rs1 ++ rs2
Vagif Verdi
  • 4,816
  • 1
  • 26
  • 31
  • the pros of single statement with multiple resultset is mostly in reducing the number of roundtrips to the database, I think it's common practice with networking related code. Not thinking about support in quickQuery, maybe the Statement record could contain a nextResults function returning a Bool, and subsequently issuing fetchRow would bring rows from the current resultset? – smoothdeveloper Oct 15 '13 at 21:41