On 2007-07-25, david48 wrote:

On 7/25/07, George Moschovitis wrote:

...

I am a Haskell newbie and I would like to hear your suggestions regarding a Database conectivity library:

...

HSQL or HDBC ?

...

which one is better / more actively supported?

HDBC Supports Mysql only through ODBC :(

This is true, unless some MySQL hacker would like to contribute a native module. I don't use MySQL myself and haven't had the time to write an interface to it. That said, HDBC supports unixODBC quite nicely... ;-) -- John

Hello,

I don't mean to hijack the thread. Does anyone have experience in using either HDBC or HSQL with Microsoft SQL server?

I use HDBC with MS SQL Server, Sybase, and Oracle. I use the ODBC bindings. I am running on both a windows XP machine and a linux machine (although I haven't been able to get Oracle connectivity on the linux machine). When I started this project, I tried both HSQL and HDBC. I managed to connect to MS SQL Server, via ODBC, with both systems without problems. I ultimately chose to go with HDBC because I like the interface better (but this was a purely subjective decision). -Jeff

Hello,

Would you go as far to say that when new programmers ask which database binding to use, we should _recommend_ HDBC then? (As we do gtk2hs, for the gui libraries).

I'm not sure about this. Although I didn't extensively compare HSQL and HDBC, I got the impression that they offered roughly the same core functionality-- though HDBC seemed to have a few more bells and whistles, and seems to be better maintained. One thing about HDBC is that it provides, and to a certain extent encourages using, a function to lazily retrieve query results. While this can be very convenient, it can also easily lead to very frustrating errors and/or resource leaks (just like any lazy IO operation); I eventually had to remove all trace of this function from my code base. Ideally something like HaskellDB (i.e. a DSL on top of HDBC or HSQL) would be the right thing for new Haskell programmers; however HaskellDB's current documentation really needs to be improved (i.e. easier to find, more comprehensive, full examples... ) before it makes sense to recommend it. Takusen also seems like a good candidate for recommending to new Haskell programmers; the only reason I haven't seriously considered switching to Takusen is the lack of ODBC bindings. I think Takusen's basic design, besides being safe, is quite in line with Haskell and functional programming; Takusen is also actively maintained. On the other hand, Takusen probably has a steeper learning curve than HDBC for a new functional programmer. At this point in time, my advice to new Haskell programmers would be: first try Takusen, as long as it can connect to the server you're using (and don't hesitate to ask the maintainers and/or haskell-cafe questions); then try HDBC, perhaps avoiding the lazy retrieval function. -Jeff

From: haskell-cafe-bounces@haskell.org [mailto:haskell-cafe-bounces@haskell.org] On Behalf Of Donald Bruce Stewart

Does anyone know why Takusen isn't on hackage yet? It appears to be cabalised, and have a tarball:

http://hackage.haskell.org/packages/archive/pkg-list.html#cat:Database http://darcs.haskell.org/takusen/

Simply because it's (yet) another thing that neither Oleg nor I have got around to doing, or figuring out how/what to do. (Looking now...) Seems pretty simple. I see there's just one option to cabal's sdist: --snapshot. Can anyone tell me if I should use this or not? Does it matter much? BTW, an ODBC implementation for Takusen is in the pipeline. The latest patches in our darcs repo include some ODBC modules, although they're far from finished. There are still quite a few issues to sort out. After ODBC I think a FreeTDS (http://www.freetds.org/) implementation would be a good idea, as this ought to give native (i.e. not via ODBC) access to both MS Sql Server and Sybase. Alistair ***************************************************************** Confidentiality Note: The information contained in this message, and any attachments, may contain confidential and/or privileged material. It is intended solely for the person(s) or entity to which it is addressed. Any review, retransmission, dissemination, or taking of any action in reliance upon this information by persons or entities other than the intended recipient(s) is prohibited. If you received this in error, please contact the sender and delete the material from any computer. *****************************************************************

Hello,

I have heard from a number of people that this behavior is not very newbie-friendly. I can see how that is true. I have an API revision coming anyway, so perhaps this is the time to referse the default laziness of HDBC calls (there would be a '-version of everything with laziness enabled, still).

Thoughts?

I think this is a good idea. In general, I think effort should be made to remind people that lazy IO operations break Haskell's type abstractions and can thus lead to unexpected/dangerous behavior. -Jeff

On Mon, 30 Jul 2007 00:56:30 +0200, John Goerzen wrote:

I have heard from a number of people that this behavior is not very newbie-friendly. I can see how that is true. I have an API revision coming anyway, so perhaps this is the time to referse the default laziness of HDBC calls (there would be a '-version of everything with laziness enabled, still).

Thoughts?

I would like the libraries as stable as possible; it seems to me that, if you make the functions strict, there should be a new set of functions for this. This prevents a lot of work on existing applications. I get the impression that the bit rot rate is very high for Haskell applications. -- Met vriendelijke groet, Henk-Jan van Tuyl -- http://functor.bamikanarie.com http://Van.Tuyl.eu/ --

Out of curiosity, can I ask what you are actually trying to do? I am asking because I am trying to make HAppS a reasonable replacement for all contexts in which you would otherwise use an external relational database except those in which an external SQL database is a specific requirement. -Alex- Isto Aho wrote:

Hi,

I was also wandering between these different db-libs and thanks for your information.

I tried several (HDBC, HSQL, HaskellDB) and made only small trials. HaskellDB has quite many examples on wiki that gave a quick start to further trials. But, I wasn't able to tell that some of the fields have default values and then it was already time to move on to the HSQL and HDBC trials.

Is it possible to use sql-array-types with HDBC with postgresql? I don't remember was this the reason why I eventually tried HSQL - anyhow, it was rather difficult to get started with HDBC but the src test cases helped here. One example in a wiki would do miracles :)

HSQL didn't have the array-types but it took only couple of hours to add "a sort of" support for those. There are some problems though... (indexed table queries returning some nulls is not yet working and ghci seems to be allergic to this) I was even wondering, should I propose a patch in some near future for this.

But if HDBC can handle those sql-arrays or if you can give a couple of hints, how to proceed in order to add them there, given your view below, I'd be willing to try to help / to try to use HDBC.

br, Isto

2007/7/30, John Goerzen mailto:jgoerzen@complete.org>:

On 2007-07-25, George Moschovitis mailto:george.moschovitis@gmail.com> wrote: > I am a Haskell newbie and I would like to hear your suggestions regarding a > Database conectivity library: > > HSQL or HDBC ? > > which one is better / more actively supported?

I am the author of HDBC, so take this for what you will.

There were several things that bugged me about HSQL, if memory serves:

1) It segfaulted periodically, at least with PostgreSQL

2) It had memory leaks

3) It couldn't read the result set incrementally. That means that if you have a 2GB result set, you better have 8GB of RAM to hold it.

4) It couldn't reference colums in the result set by position, only by name

5) It didn't support pre-compiled queries (replacable parameters)

6) Its transaction handling didn't permit enough flexibility

I initially looked at fixing HSQL, but decided it would be easier to actually write my own interface from scratch.

HDBC is patterned loosely after Perl's DBI, with a few thoughts from Java's JDBC, Python's DB-API, and HSQL mixed in.

I believe it has fixed all of the above issues. The HDBC backends that I've written (Sqlite3, PostgreSQL, and ODBC) all use Haskell's C memory management tools, which *should* ensure that there is no memory leakage.

I use it for production purposes in various applications at work, connecting to both Free and proprietary databases. I also use it in my personal projects. hpodder, for instance, stores podcast information in a Sqlite3 database accessed via HDBC. I have found HDBC+Sqlite3 to be a particularly potent combination for a number of smaller projects.

http://software.complete.org/hdbc/wiki/HdbcUsers http://software.complete.org/hdbc/wiki/HdbcUsers has a list of some programs that are known to use HDBC. Feel free to add yours to it.

-- John

_______________________________________________ Haskell-Cafe mailing list Haskell-Cafe@haskell.org mailto:Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe

-- br, Isto

------------------------------------------------------------------------

_______________________________________________ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe

Will be pushing out the refactored happs repos in the next 2 weeks. The gist is: * HAppS.IxSet provides efficient query operations on haskell sets. * HAppS.State provides ACID, replicated, and soon sharded access to your application state. * HAppS.Network will provide server side HTTP functionality from which to access your replicated state. -Alex- Bulat Ziganshin wrote:

Hello Alex,

Wednesday, August 1, 2007, 8:34:23 AM, you wrote:

...

I am asking because I am trying to make HAppS a reasonable replacement for all contexts in which you would otherwise use an external relational database except those in which an external SQL database is a specific requirement.

where i can read about such usage?

Have you looked at the HAppS.DBMS.IxSet? It gives you a type safe way to query indexed collections. -Alex- Isto Aho wrote:

Hi,

I'd like to store small matrices into a db. Number of rows and columns may vary in a way not known in advance. One might use a relation (matrixId, col, row, value) or something like that but if it is possible to put a matrix in one command into db, some queries will be easier. E.g., one relation can store several matrices and it would be easy to query, how many matrices are stored currently. With that above four tuple you can find out the number of unique matrixId's, too, but it is not as easy as with matrices.

Anyhow, now I'm not sure if I should stick with HSQL any more... Earlier comments on this thread made me think that maybe it would be a better idea to try to learn enough HDBC.

This would be used in a server application. Is HAppS applicable here?

e.g. after some tweaking the following works with HSQL:

addRows = do dbh <- connect server database user_id passwd intoDB dbh ([555,111, 50, 1000]::[Int]) ([21.0,22.0,23.0,24.0]::[Double]) intoDB dbh ([556,111, 50, 1000]::[Int]) ([21.0,22.0,23.0,24.0]::[Double]) intoDB dbh ([]::[Int]) ([]::[Double]) where intoDB dbh i_lst d_lst = catchSql (do let cmd = "INSERT INTO trial (intList, dList) VALUES (" ++ toSqlValue i_lst ++ "," ++ toSqlValue d_lst ++ ")" execute dbh cmd ) (\e -> putStrLn $ "Problem: " ++ show e)

Similarly, queries can handle matrices and I like that it is now possible to select those columns or rows from the stored matrix that are needed. E.g.

retrieveRecords2 :: Connection -> IO [[Double]] retrieveRecords2 c = do -- query c "select dList[1:2] from trial" >>= collectRows getRow query c "select dList from trial" >>= collectRows getRow where getRow :: Statement -> IO [Double] getRow stmt = do lst <- getFieldValue stmt "dList" return lst readTable2 = do dbh <- connect server database user_id passwd values <- retrieveRecords2 dbh putStrLn $ "dLists are : " ++ (show values)

br, Isto

2007/8/1, Alex Jacobson mailto:alex@alexjacobson.com>:

Out of curiosity, can I ask what you are actually trying to do?

I am asking because I am trying to make HAppS a reasonable replacement for all contexts in which you would otherwise use an external relational database except those in which an external SQL database is a specific requirement.

-Alex-

For the reasons described in my previous message, I plan on looking into using takusen with HAppS. 2007/8/4 Alex Jacobson :

Have you looked at the HAppS.DBMS.IxSet? It gives you a type safe way to query indexed collections.

-Alex-

Isto Aho wrote:

...

Hi,

I'd like to store small matrices into a db. Number of rows and columns may vary in a way not known in advance. One might use a relation (matrixId, col, row, value) or something like that but if it is possible to put a matrix in one command into db, some queries will be easier. E.g., one relation can store several matrices and it would be easy to query, how many matrices are stored currently. With that above four tuple you can find out the number of unique matrixId's, too, but it is not as easy as with matrices.

Anyhow, now I'm not sure if I should stick with HSQL any more... Earlier comments on this thread made me think that maybe it would be a better idea to try to learn enough HDBC.

This would be used in a server application. Is HAppS applicable here?

e.g. after some tweaking the following works with HSQL:

addRows = do dbh <- connect server database user_id passwd intoDB dbh ([555,111, 50, 1000]::[Int]) ([21.0,22.0,23.0,24.0]::[Double]) intoDB dbh ([556,111, 50, 1000]::[Int]) ([21.0,22.0,23.0,24.0]::[Double]) intoDB dbh ([]::[Int]) ([]::[Double]) where intoDB dbh i_lst d_lst = catchSql (do let cmd = "INSERT INTO trial (intList, dList) VALUES (" ++ toSqlValue i_lst ++ "," ++ toSqlValue d_lst ++ ")" execute dbh cmd ) (\e -> putStrLn $ "Problem: " ++ show e)

Similarly, queries can handle matrices and I like that it is now possible to select those columns or rows from the stored matrix that are needed. E.g.

retrieveRecords2 :: Connection -> IO [[Double]] retrieveRecords2 c = do -- query c "select dList[1:2] from trial" >>= collectRows getRow query c "select dList from trial" >>= collectRows getRow where getRow :: Statement -> IO [Double] getRow stmt = do lst <- getFieldValue stmt "dList" return lst readTable2 = do dbh <- connect server database user_id passwd values <- retrieveRecords2 dbh putStrLn $ "dLists are : " ++ (show values)

br, Isto

2007/8/1, Alex Jacobson mailto:alex@alexjacobson.com>:

Out of curiosity, can I ask what you are actually trying to do?

I am asking because I am trying to make HAppS a reasonable replacement for all contexts in which you would otherwise use an external relational database except those in which an external SQL database is a specific requirement.

-Alex-

_______________________________________________ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe