Hi, Marc Weber wrote:

Yes, I know about haskelldb.. But some more advanced optimizations can't be expressed.. so I'd like to ask if someone has done some work in the other direction not ensuring type safety by using the haskell type system to derive SQL queries but to use template haskell to derive functions from given SQL queries thereby checking them for syntax error ? It be a little bit like SQLJ (Java tool for db connectivity)

I am working on this very issue :) However, it is still in the early stages. What I am trying to do is to use Template Haskell to connect (compile-time) to the database and let the database figure out the correct type of a SQL expression. See this thread http://www.nabble.com/Using-Template-Haskell-to-make-type-safe-database-acce... for more details.

Eg $(q "SELECT '2', 4") should result in [(String, Int)] and

$(q "INSERT INTO foo (2, ?1, ?2)" ) should result in

Int -> -> -> IO ()

similar to what I had in mind. Also I have access to bound variables like: foo :: Int -> IO () foo bar = $(q "INSERT INTO foo (2, ?bar)") Note my current code supports both options - both anonymous (only question mark) and question mark followed by the name of a bound variable.

Ideally this would not only result in a query but in a prepared statement.

Do not do that just yet. Have not really thought about it. I think it would be trivial for databases which supports making prepared statements doing compile time and evaluating them doing run-time. I do not know how common this feature is. I know DB2 on z/OS supports it. Other databases could be investigated. To support compile-time prepared statements means that the prepared statement must not be deleted when the database connection is closed. Maybe, my current API could be changed to support prepared statements in made doing run-time. I will have to think a bit about it.

I have no clue how much RDBMS such as Postgresql provide some help determining type of parameters or results without acutally doing a query (but bcause Postgresql provides transactions this should not be a problem)

Using ODBC you do _not_ have to execute a statement to query its results types. However, querying types of parameter is optional for ODBC driver implementers (according to the standard). MySQL do not support it. I _think_ PostgreSQL do not support it either, but I am still investigating PostgreSQL. I have also been investigating SQLite and looking around the web for other databases. A am afraid it is common to _not_ support querying the result of parameter types :( Currently DB2 (on Linux) seems to be my best hope. Also I assume that MS SQL server supports querying parameter types using ODBC, as they invented ODBC.

I see the following benefits: You can use your existing SQL- Knowledge and don't have to dive into deep type hackery yet gettitng as much type safety as possible

Marc Weber _______________________________________________

If you are interested in contributing to the project, I would be happy to put more information on-line. I currently have some working code and sketchy unit tests. Also I have been taking notes about the various databases I have used. Greetings, Mads