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

Wed Oct 16 15:49:59 UTC 2013

Hello Vagif,

It's definitely possible to work around this feature not being here;
though I won't call it premature optimization as it's really
convenient and logical to batch statements in a single roundtrip when
you can do so, when the statements have coherence; I'm sure there are
a lot of stored procedures in the wild that gives back multiple
resultsets too.

I'm sorry if it sounded like a plea to get this feature implemented,
but I thought it might be worth considering in API design, it's there
in jdbc  (http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getMoreResults%28%29)
and ado.net (http://msdn.microsoft.com/en-us/library/system.data.idatareader.nextresult.aspx).

I appreciate the work being done on HDBI, I'll give it a go whenever
I'll work with postgresql, and check-out new drivers coming, I'll keep
an eye on it's evolution.

Well at least I got the thing I was discussing understood as it wasn't
clear in the initial exchanges due to my poor explanation :)

On Wed, Oct 16, 2013 at 5:22 PM, Vagif Verdi <vagif.verdi at gmail.com> wrote:
> Over my more than 20 years career working with many different databases and
> languages you are the first person i meet who requests such a feature.
>
> Premature optimization is harmful.
>
> You are looking for a solution to a problem that practically never manifests
> itself.
>
> Especially in a server side application that are usually hosted close to sql
> server and on a very fat bandwidth.
>
> In terms of the performance much more important question is connection
> pooling.
>
> You are basically asking a single programmer who just started a quite big
> project to spend his time on a feature practically no one is interested with
> while he has hundreds of much more pressing issues to implement.
>
> I'd say at this point your only course of action is to implement it
> yourself, if it is really that important to you.
>
>
>
> On Wednesday, October 16, 2013 6:42:56 AM UTC-7, Gauthier Segay wrote:
>>
>> 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 <s9gf... 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 <gauthie... 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 <s9gf... 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 <gauthie... 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 <s9gf... 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
>> >> >> > Haskel... at haskell.org
>> >> >> > http://www.haskell.org/mailman/listinfo/haskell-cafe
>> >> >> >
>> >> >
>> >> >
>> >> >
>> >> > _______________________________________________
>> >> > Haskell-Cafe mailing list
>> >> > Haskel... at haskell.org
>> >> > http://www.haskell.org/mailman/listinfo/haskell-cafe
>> >> >
>> >
>> >
>> >
>> > _______________________________________________
>> > Haskell-Cafe mailing list
>> > Haskel... at haskell.org
>> > http://www.haskell.org/mailman/listinfo/haskell-cafe
>> >
>> _______________________________________________
>> Haskell-Cafe mailing list
>> Haskel... 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
>