[Haskell-cafe] Announce: HDBI-1.3 and friends

[Gauthier Segay]

no the use case is to issue a bunch of disparate select statements,
i.e. retrieving data from several tables in a single roundtrip.

select 1,2,3

select 'a', 'b'

In this case, I'd like to call the API in this manner (or something better):

-- statement is prepared
rows1 <- fetchAllRows statement
_ <- nextResults statement
rows2 <- fetchAllRows statement
-- continue

right now, with hdbc-odbc (nextResults doesn't exist though so I don't
call it) rows2 will be an empty list, I'd expect it to be[(SqlText
"a"), (SqlText "b")]

I assume in the case of odbc, the related C function is SQLMoreResults

http://msdn.microsoft.com/en-us/library/ms714673%28v=vs.85%29.aspx


On Wed, Oct 16, 2013 at 3:16 PM, Alexey Uimanov <s9gf4ult at gmail.com> wrote:
> If I understand correctly you just want to re-execute the same query
> multiple times. There is method `reset` in HDBI already. Just call `reset`
> and statement return to it's initial state just after `prepare`, then
> execute `fetchAll` to get the result. Or just create new statement with
> `prepare` using the same query string or getting it from old statement with
> `originalQuery`.
>
>
> 2013/10/16 Gauthier Segay <gauthier.segay at gmail.com>
>>
>> > use `runFetchAll` it will return constructed Seq with results. But I
>> > would use conduits and conduit `selectAll`
>> > http://hackage.haskell.org/package/hdbi-conduit-1.3.0/docs/Data-Conduit-HDBI.html.
>> > Conduits are realy simple and effective.
>>
>> I'm unsure I expressed the question properly, multiple results (rows)
>> is definitely supported but multiple resultset does not seem to be.
>>
>> There are occurrences where you issue multiple select statements in a
>> single roundtrip to the database, each select with potentially
>> different row layout.
>>
>> After reading your answer, I actually tried (hdbc) calling the
>> fetchRows function several times on Statement but it won't return
>> anything past the first resultset.
>>
>> It seems the .net NextResults() approach is good because it let's you
>> check whether or not there is a next resultset to fetch rows from, I
>> think it would be necessary to have a similar approach if this is
>> going to be supported.
>>
>>
>> On Wed, Oct 16, 2013 at 6:03 AM, Alexey Uimanov <s9gf4ult at gmail.com>
>> wrote:
>> >> ability to use named parameters
>> >
>> > Yes. I dont know when It will be done, but it is on github issues
>> > already
>> > https://github.com/s9gf4ult/hdbi/issues/3 .
>> >
>> >> fetching multiple results returned from single statement
>> >
>> > use `runFetchAll` it will return constructed Seq with results. But I
>> > would
>> > use conduits and conduit `selectAll`
>> >
>> > http://hackage.haskell.org/package/hdbi-conduit-1.3.0/docs/Data-Conduit-HDBI.html.
>> > Conduits are realy simple and effective.
>> >
>> > If you need something like nextresult just use method `fetch` of
>> > `Statement`
>> > until it return Nothing. It is just the same. Or use ResumableSink from
>> > conduits (I would).
>> >
>> >
>> >
>> > 2013/10/16 Gauthier Segay <gauthier.segay at gmail.com>
>> >>
>> >> Thanks for the announcement / library, I have started using hdbc (and
>> >> it's odbc driver) recently and had two concerns with it so far:
>> >>
>> >> * ability to use named parameters
>> >>
>> >>
>> >> (http://stackoverflow.com/questions/19137803/does-database-hdbc-support-named-parameters)
>> >> * fetching multiple results returned from single statement
>> >>
>> >>
>> >> (http://stackoverflow.com/questions/19159287/hdbc-and-multiple-resultsets-in-a-single-statement-only-first-resultset-returne)
>> >> (in .net this is done via
>> >>
>> >>
>> >> http://msdn.microsoft.com/en-us/library/system.data.idatareader.nextresult.aspx)
>> >>
>> >> Is there any plan to get this supported in HDBI?
>> >>
>> >> On Tue, Oct 15, 2013 at 7:55 PM, Alexey Uimanov <s9gf4ult at gmail.com>
>> >> wrote:
>> >> > Hello haskellers!
>> >> >
>> >> > HDBI is the fork of HDBC but reworked. It supports SQlite3 and
>> >> > Postgresql
>> >> > for now. It also supports streaming with coduits.
>> >> > There is TH deriving mechanism to map database rows to Haskell
>> >> > structures
>> >> > and back. HDBI is trying to become simple
>> >> > but still powerfull and flexible database interface. It must be
>> >> > suitable
>> >> > to
>> >> > become the common RDBMS interface  for higher level interfaces
>> >> > like persistent or haskelldb.
>> >> >
>> >> > The documentation is not very good, while I have no enough time to
>> >> > make
>> >> > some.
>> >> >
>> >> > In this version changed typeclass signatures of Connection and
>> >> > Statement.
>> >> > Now methods `run` and `execute` get any instance of `ToRow` and
>> >> > method
>> >> > `fetch` return an instance of `FromRow`.
>> >> > Note that [SqlValue] is also an instance of `FromRow` and `ToRow`
>> >> > typeclasses so you do not loose the control.
>> >> > Methods `fromRow` and `toRow` for [SqlValue] are just `id`. SQlite
>> >> > and
>> >> > Postgresql drivers are fixed as well as hdbi-conduit.
>> >> >
>> >> > There is also new helper functions, like `onei :: Integer ->
>> >> > [SqlValue]`
>> >> > which helps you to execute queries with one parameter
>> >> > or execute many queries consistinf of one parameter.
>> >> >
>> >> > Prelude Database.HDBI Database.HDBI.SQlite> :set
>> >> > -XScopedTypeVariables
>> >> > Prelude Database.HDBI Database.HDBI.SQlite> :set -XOverloadedStrings
>> >> > Prelude Database.HDBI Database.HDBI.SQlite> c <- connectSqlite3
>> >> > ":memory:"
>> >> > Prelude Database.HDBI Database.HDBI.SQlite> runRaw c "create table
>> >> > test(val
>> >> > integer)"
>> >> > Prelude Database.HDBI Database.HDBI.SQlite> withTransaction c $
>> >> > runMany
>> >> > c
>> >> > "insert into test(val) values (?)" $ map one [1..1000]
>> >> >
>> >> > <interactive>:7:76: Warning:
>> >> >     Defaulting the following constraint(s) to type `Integer'
>> >> > .........
>> >> >
>> >> > Prelude Database.HDBI Database.HDBI.SQlite> r :: (Maybe Integer) <-
>> >> > runFetchOne c "select sum(val) from test" ()
>> >> > Prelude Database.HDBI Database.HDBI.SQlite> r
>> >> > Just 500500
>> >> >
>> >> > Note here that the empty set is used as a parameter of query in
>> >> > `runFetchOne`. Empty set is an instance of `FromRow` and `ToRow` and
>> >> > return
>> >> > an empty list of [SqlValue].
>> >> > Use empty list as a parameters is bad idea, because we could
>> >> > instantiate
>> >> > some another list of things, suppose the [Integer] as `FromRow` and
>> >> > `ToRow` instance. So it would lead to ambigous type because
>> >> > [SqlValue]
>> >> > is
>> >> > also a list instantiating `FromRow` and `ToRow`.
>> >> >
>> >> > _______________________________________________
>> >> > Haskell-Cafe mailing list
>> >> > Haskell-Cafe at haskell.org
>> >> > http://www.haskell.org/mailman/listinfo/haskell-cafe
>> >> >
>> >
>> >
>> >
>> > _______________________________________________
>> > Haskell-Cafe mailing list
>> > Haskell-Cafe at haskell.org
>> > http://www.haskell.org/mailman/listinfo/haskell-cafe
>> >
>
>
>
> _______________________________________________
> Haskell-Cafe mailing list
> Haskell-Cafe at haskell.org
> http://www.haskell.org/mailman/listinfo/haskell-cafe
>