3

I'm giving my first steps into HDBC using ODBC to connect to a local SQL Server.

After a quickQuery on the connection, I can't close it. I need to perform a commit first.

Is this the way it is supposed to be? Why is the commit necessary when I'm only performing a query?

On GHCi:

m + Database.HDBC Database.HDBC.ODBC
conn <- connectODBC "Driver={SQL Server};Server=thiagon\\sqlserver2012;Database=senior;UID=framework;PWD=framework;"
vals <- quickQuery conn "SELECT TOP 5 * FROM whatever;" []
print vals
commit conn
disconnect conn

If I remove the commit conn line, I get an exception: 

*** Exception: SqlError {seState = "[\"25000\"]", seNativeError = -1, seErrorMsg = "disconnect: [\"0: [Microsoft][ODBC SQL Server Driver]Estado de transa\\65533\\65533o inv\\65533lido\"]"}

The message is in portuguese, it means "invalid transaction state".

Thiago Negri
  • 5,221
  • 2
  • 28
  • 39

1 Answers1

1

A quickQuery could modify the table. I don't think the API analyses the string itself, or checks the database, to see whether or not the table was modified. And HDBC doesn't support autocommit.

You could use withTransaction, which will automatically handle this detail for you.

EDIT: Try using quickQuery', which is the strict version of quickQuery. In an example on http://book.realworldhaskell.org/read/using-databases.html (scroll down to ch21/query.hs), they didn't need a commit after a plain SELECT statement, but they were using quickQuery'.

mhwombat
  • 8,026
  • 28
  • 53
  • So, a `quickQuery` always stars a transaction? As if you do only a raw `SELECT` into SQL Server, you won't open a transaction. – Thiago Negri Dec 19 '12 at 15:01
  • I don't know, to be honest. It might depend on the specific driver you're using. You might try using `prepare` instead, and see if that makes a difference. – mhwombat Dec 19 '12 at 16:21
  • `quickQuery'` yields the same result. :( – Thiago Negri Dec 19 '12 at 17:10
  • I'm considering your answer as correct, as no one came with other idea. – Thiago Negri Jan 11 '13 at 13:52
  • Every operation in hdbc starts a transcation unless you explicitly started it yourself. So yes, you have to use withTransaction everywhere. I usually just create a bunch of utility funcitons that run sql statements using withTransaction. – Vagif Verdi Oct 15 '13 at 19:45