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

Wed Oct 16 16:21:28 UTC 2013

All this is true. But HDBI is flexible enough to let implement specific
database features without breaking common interface. My previous letter
explains.

2013/10/16 Vagif Verdi <vagif.verdi at gmail.com>

> When discussing databases it is hard to understand each other without
> knowing which exact database we are talking about.
>
> I assume you are using MS Sql server (just like me) since you keep
> mentioning dotnet.
>
> There are few things you should know about specifics of haskell community
> regarding windows and especially MS Sql server.
>
> Both windows and MS Sql server are not first class citizens here.
>
> Most haskellers use open source databases like Postgres and Mysql and they
> use those from linux/mac, not from windows.
>
> I had to fix a few bugs in hdbc-odbc driver that were specific to ms sql
> server and freetds linux driver myself. Since maintainers of hdbc-odbc do
> not even have a ms sql server to reproduce the problem and test it.
>
> If you are looking for a specific Ms Sql Server feature, most likely you
> will have to implement it yourself.
>
> I'm trying to keep db access patterns really simple and do not use any
> specific to Ms Sql features. And even in this case every time something
> updates, like say freetds driver, everything breaks.
>
> Just yesterday i fixed the problem with new freetds driver not fetching
> scope_identity() anymore. I had to add set nocount on everywhere i use
> scope_identity. Even though on previous version of either freetds or
> unixodbc it was working fine.
>
> So using Ms Sql server from haskell is still a shaky experience.
>
>
> On Wednesday, October 16, 2013 8:49:59 AM UTC-7, Gauthier Segay wrote:
>
>> 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<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<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... 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<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<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<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<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<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<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<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<http://www.haskell.org/mailman/listinfo/haskell-cafe>
>> >> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > ______________________________**_________________
>> >> >> > Haskell-Cafe mailing list
>> >> >> > Haskel... at haskell.org
>> >> >> > http://www.haskell.org/**mailman/listinfo/haskell-cafe<http://www.haskell.org/mailman/listinfo/haskell-cafe>
>> >> >> >
>> >> >
>> >> >
>> >> >
>> >> > ______________________________**_________________
>> >> > Haskell-Cafe mailing list
>> >> > Haskel... at haskell.org
>> >> > http://www.haskell.org/**mailman/listinfo/haskell-cafe<http://www.haskell.org/mailman/listinfo/haskell-cafe>
>> >> >
>> >> ______________________________**_________________
>> >> Haskell-Cafe mailing list
>> >> Haskel... at haskell.org
>> >> http://www.haskell.org/**mailman/listinfo/haskell-cafe<http://www.haskell.org/mailman/listinfo/haskell-cafe>
>> >
>> >
>> > ______________________________**_________________
>> > Haskell-Cafe mailing list
>> > Haskel... at haskell.org
>> > http://www.haskell.org/**mailman/listinfo/haskell-cafe<http://www.haskell.org/mailman/listinfo/haskell-cafe>
>> >
>> ______________________________**_________________
>> Haskell-Cafe mailing list
>> Haskel... at haskell.org
>> http://www.haskell.org/**mailman/listinfo/haskell-cafe<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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.haskell.org/pipermail/haskell-cafe/attachments/20131016/0e879ce0/attachment.html>