hdbc / hdbc-odbc Goto Github PK

View Code? Open in Web Editor NEW

26.0 13.0 33.0 796 KB

ODBC driver for HDBC

License: BSD 3-Clause "New" or "Revised" License

Haskell 97.61% Makefile 0.85% C 1.01% Nix 0.53%

hdbc-odbc's Introduction

HDBC-ODBC

Welcome to HDBC, Haskell Database Connectivity.

This package provides a database backend driver for ODBC. You should be able to use any ODBC front-end with it.

Please see HDBC itself for documentation on use.

This package provides one function in module Database.HDBC.ODBC:

Connect to an ODBC server. For information on the meaning of the passed string, please see: http://msdn2.microsoft.com/en-us/library/ms715433(VS.85).aspx

connectODBC :: String -> IO Connection

For example, you might use connectODBC as follows:

connectODBC "DSN=hdbctest1"

For more information about HDBC-ODBC, please visit the wiki.

Differences from HDBC standard

None known at this time.

MySQL note

Important note for MySQL users:

Unless you are going to use InnoDB tables, you are strongly encouraged to set

Option = 262144

in your odbc.ini (for Unix users), or to disable transaction support in your DSN setup for Windows users.

If you fail to do this, the MySQL ODBC driver will incorrectly state that it supports transactions. dbTransactionSupport will incorrectly return True. commit and rollback will then silently fail. This is certainly NOT what you want. It is a bug (or misfeature) in the MySQL driver, not in HDBC.

You should ignore this advice if you are using InnoDB tables.

For the error "2013: Mysql server has gone away" error message, you'll have to use withRTSSignalsBlocked from the HDBC-mysql package.

query conn stmStr binds = withRTSSignalsBlocked $ quickQuery conn stmStr binds

Getting Started

Here are some instructions to set up ODBC with a sqlite3 backend, and how to communicate with that database with HDBC-ODBC. These instructions are written to work with Ubuntu 11.10.

First, we'll need to install the appropriate libraries:

sudo apt-get install unixodbc unixodbc-dev unixodbc-bin
sudo apt-get install libsqliteodbc

Verify that the sqlite ODBC drivers have been set up correctly:

odbcinst -q -d

This should return:

[SQLite]
[SQLite3]

Next, fire up the ODBCConfig too to set up a new DSN:

ODBCConfig

If you want to run the HDBC test suite, then set your DSN to hdbctest, and set up to connect to a database of your choice, such as an empty file in the hdbc-odbc/testsrc directory:

touch hdbc-odbc/testsrc/hdbctest.db

You can check that everything is working appropriately in ghci:

ghci> :m + Database.HDBC Database.HDBC.ODBC
ghci> conn <- connectODBC "DSN=hdbctest"
ghci> hdbcDriverName conn
"odbc"
ghci> hdbcClientVer conn
"03.52"

You can then run some tests on your database:

cd testsrc
runhaskell runtests.hs

Contributing

Contributions are welcome! If you would like to contribute, please fork the the github repository, and submit a pull request.

hdbc-odbc's People

Contributors

Stargazers

Watchers

hdbc-odbc's Issues

(Unexpected SQL_NO_TOTAL) error on text fields larger than 4096 bytes

OS: linux 64 bit (archlinux), ghc-7.4.1, hdbc-odbc-2.3.1.0

Connecting to MS Sql server 2005.

Retrieving a text field larger than 4096 bytes.

With unixodbc 2.3.0 and freetds 0.82 works fine

With unixodbc 2.3.1 and freetds 0.91 gives an error "Unexpected SQL_NO_TOTAL"

connectODBC `chr` bad argument exception

I‘ve set up a simple postgres database:

nmap localhost
…
5432/tcp open  postgresql

and have the following in ~/.odbc.ini:

[postgres]
Driver = PostgreSQL
ServerName = localhost
Database = postgres

I also created a user user with password foo.

> isql postgres user foo -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

In GHCI though:

Prelude Database.HDBC Database.HDBC.ODBC> connectODBC "DSN=postgres"
*** Exception: SqlError {seState = "[\"*** Exception: Prelude.chr: bad argument: 3670064
Prelude Database.HDBC Database.HDBC.ODBC> connectODBC "DSN=postgres; USR=user; PWD=foo"
*** Exception: SqlError {seState = "[\"*** Exception: Prelude.chr: bad argument: 3670064

Version: 2.5.0.0

executeRaw fails silently

executeRaw = return ()

in hdbc-odbc / Database / HDBC / ODBC / Statement.hsc at line 116 (

hdbc-odbc/Database/HDBC/ODBC/Statement.hsc

Line 116 in 3d1702c

, executeRaw = return ()

)

Support to call procedures with table-valued parameters

Some SQL databases have stored procedures that accept table-valued parameters. (https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters). This is a request to add support for calling such procedures.

Memory Leak on Windows

When using the Haskell HDBC-ODBC library to connect to a Microsoft SQL Server I am experiencing a bad memory leak.

import           Database.HDBC        
import qualified Database.HDBC.ODBC   as ODBC
import           Control.Monad

-- | Main application.
main :: IO ()
main = dbTest

dbTest :: IO ()
dbTest = do
    let connStr = "DSN=TESTDB;Uid=sa;Pwd=password"
    conn <- ODBC.connectODBC connStr
    replicateM_ 20000 (loop conn)
    disconnect conn
  where
    loop c = do
        result <- getTables c
        commit c
        putStrLn $ show result

Running the heap profiler gives me constant memory usage but Window reports memory increasing to almost 100MB of usage.

http://i.stack.imgur.com/YkUTW.png

To me this seems like the memory leak is in the Foreign Function interface of the ODBC driver, but this is my first time profiling code so I can't be certain.

Release a new version that fixes the SQL_C_WCHAR issue

The latest version 2.4.0.0 of HDBC-odbc on hackage has an issue related to SQL_C_WCHAR.
That issue had been fixed at ab03a6b.
Would you release a new version?

SQLGetData called on bound columns regardless of SQL_GD_BOUND

For fields exceeding the buffer size, SQLGetData is used to retrieve the data with multiple fetches. At this point the columns are already bound, which generates an error with drivers without SQL_GD_BOUND.

"If the driver does not support extensions to SQLGetData, the function can return data only for unbound columns with a number greater than that of the last bound column." - http://msdn.microsoft.com/en-us/library/ms715441%28v=vs.85%29.aspx

MS SQL connection fails using GHC but not GHCi

I'm attempting to connect to a MS SQL Server database over a VPN using hdbc-odbc. Everything works fine when run in ghci, or through runhaskell. However, compiling a program in ghc and running it produces the following error upon calling connectODBC:

SqlError {seState = "[\"08S01\",\"08001\"]", seNativeError = -1, seErrorMsg = "connectODBC/sqlDriverConnect: [\"20009: [unixODBC][FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist\",\"0: [unixODBC][FreeTDS][SQL Server]Unable to connect to data source\"]"}

Looking around it appears that the error itself means the program is failing to connect at all to the SQL server, at least according to the FreeTDS user guide: http://www.freetds.org/userguide/confirminstall.htm

I tried connecting to the server using ODBC in C and it worked, and through tsql and isql as well. I wasn't able to check the tests in hdbc-odbc/testsrc as they just returned an error:

Testbasics.hs:141:8: Not in scope: `catch'

I'm not sure what's wrong, but given that the connection works in GHCi, tsql, and in hand-written C code my guess is it has something to do with FFI and the point at which connectODBC actually talks to the ODBC API.

As a side note, I've tested this against hdbc-odbc 2.3.1.1 and 2.3.1.0, and both have the same issue. Connecting to SQLite works as well.

Update: Using ghci with -fno-ghci-sandbox also causes the connection to fail. Not sure why.

Add to Stackage

Would it be possible to add this package to Stackage please?

Cf instructions, the effort should be small: https://github.com/commercialhaskell/stackage/blob/master/MAINTAINERS.md#adding-a-package

The current Stackage build environment already contains the ODBC system package but just in case, for your information, if other system dependencies are needed to build HDBC-odbc:

if present in the default Debian package registry, add it here
if it requires adding another package registry, add it there

mssql varbinary(max) fails when selecting data

Hi,
I am getting an "Invalid Descriptor Index" error (as below) when selecting from tables that have varbinary(max) fields.
This used to work about a year ago (not sure if that helps). Anyway here is a small example that fails using HDBC-odbc-2.4.0.1.

Thanks for any help you give me.
Grant

CREATE TABLE [dbo].[testblob]([id] [bigint] IDENTITY%281,1%29 NOT NULL,
[bs1] [varbinary]%28max%29 NULL,
PRIMARY KEY CLUSTERED
%28
[id] ASC
%29)

insert into testblob values(convert(varbinary(max),'xxxxx'))

conn <- H.connectODBC connectionString
stmt1 <- H.prepare conn "select * from testblob"
vals <- H.execute stmt1 []
results <- H.fetchAllRowsAL stmt1
mapM_ print results

*** Exception: SqlError {seState = "["07009"]", seNativeError = -1, seErrorMsg = "sqlGetData: ["0: [Microsoft][ODBC Driver 11 for SQL Server]Invalid Descriptor Index"]"}

oracle connect error with weired number

i don't know what happened when i try to connect to oracle for weired number message.
i install oracle odbc driver and unixodbc library already？

oracleConnInfo = "Driver={Oracle ODBC Driver};DBP=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.129.35.238)(PORT=1521))(CONNECT_DATA=(SI
D=EDWDBUAT)));UID=XXX;PWD=YYY;"

connectODBC oracleConnInfo
*** Exception: SqlError {seState = "["*** Exception: Prelude.chr: bad argument: 3211312

changelog for 2.5.x.x

Would be nice to see what caused a major version bump.

Driver randomly failed with different SqlError on MS SQL Server 2005

Greetings,

Here's my situation, I run the same query but with different parameters several times and it never fails at the same point when running my program (I print each line I read). Here a possible outcome when launching my program 3 times.

Attempt 1

A
B
C
testing-mssqlserver: user error (Data.ByteString.packCStringLen: negative length: -15471)

Attempt 2

A
B
testing-mssqlserver: SqlError {seState = "[]", seNativeError = -2, seErrorMsg = "sqlFetch: []"}

Attempt 3

A
B
C
D
E
testing-mssqlserver: SqlError {seState = "[]", seNativeError = -2, seErrorMsg = "SQLNumResultCols: []"}

My env:
OS: Archlinux 64bits
GHC: 8.0.2 64bits

λ yoeight → cat /etc/odbc.ini
[MSSQL]
Description = MS SQL Server
Driver = FreeTDS
Server = ${server_name}
UID = ${user}
PWD = ${password}
ReadOnly = No
Port = 1111
TDS_Version = 8.0

λ yoeight → cat /etc/odbcinst.ini
[FreeTDS]
Description = MS SQL Server
Driver = /usr/lib/libtdsodbc.so
UsageCount = 1
FileUsage = 1
Trace = Yes
TraceFile = /tmp/odbc.log

nb: Don't know if it's related to the driver but I never had a single line of log in /tmp/odbc.log

Thanks for your time.

executeMany optimisations

Currently, executeMany is simply a call to replicateM execute. This could be optimised.

safe vs unsafe FFI calls

vagifverdi noted:

Currently all FFI call are marked unsafe. Unsafe calls block all threads. And this is library communicating with a remote server potentially waiting many seconds / minute for results to come back. Blocking all threads is simply unacceptable for a db library, especially when used on the web server for example.

I marked all of them as safe for my own use. We could potentially go through all the functions and analyze which ones initiate a network communication and which ones are not. And mark them safe/unsafe accordingly.

But all safe works fine for me. And i do not want to spend my time on it.

Would you accept another patch that marks all FFI calls as safe ?

Considering that major overhead of db library is in communicating with sql server, i do not think making FFI calls safe will add any noticeable delay.

Proposal: Add flexible version of `getTables`, and `getTableTypes`

getTables :: Connection -> IO [String] provides results for ODBC table type TABLE. I found myself wanting the equivalent results for other ODBC table types, such as VIEW or SYSTEM TABLE.

Perhaps the library could be extended to include:

getTablesOfType :: Connection -> String -> IO [String]
getTableTypes :: Connection -> IO [String]

so that getTableTypes conn provides a list of the valid table types for conn and getTablesOfType conn "TABLE,VIEW,SYSTEM TABLE" (for example) gets the relevant table names of those types.

I have done that in a fork, by extending the constructor of data type Connection. Would it be of interest? If yes, I'll make a pull request.

Driver segfault when compiled with -threaded

Greetings,

Can't say much about it, here the trace:

*** Error in `/home/yoeight/project/.stack-work/install/x86_64-linux-ncurses6/lts-8.0/8.0.2/bin/my-prog': free(): invalid pointer: 0x00000000023f5e60 ***
======= Backtrace: =========
/usr/lib/libc.so.6(+0x70c4b)[0x7f9483a75c4b]
/usr/lib/libc.so.6(+0x76fe6)[0x7f9483a7bfe6]
/usr/lib/libc.so.6(+0x777de)[0x7f9483a7c7de]
/usr/lib/libtdsodbc.so(+0x231ce)[0x7f948156e1ce]
/usr/lib/libtdsodbc.so(SQLFetch+0x3e)[0x7f9481560ede]
/usr/lib/libodbc.so.2(SQLFetch+0xce)[0x7f948435161e]
/home/yoeight/project/.stack-work/install/x86_64-linux-ncurses6/lts-8.0/8.0.2/bin/my-prog[0xab2656]
======= Memory map: ========
00400000-00f66000 r-xp 00000000 103:04 5517926                           /home/yoeight/project/.stack-work/install/x86_64-linux-ncurses6/lts-8.0/8.0.2/bin/my-prog
01166000-011a5000 r--p 00b66000 103:04 5517926                           /home/yoeight/project/.stack-work/install/x86_64-linux-ncurses6/lts-8.0/8.0.2/bin/my-prog
011a5000-01232000 rw-p 00ba5000 103:04 5517926                           /home/yoeight/project/.stack-work/install/x86_64-linux-ncurses6/lts-8.0/8.0.2/bin/my-prog
01232000-01234000 rw-p 00000000 00:00 0 
02367000-0240b000 rw-p 00000000 00:00 0                                  [heap]
4200000000-4200100000 rw-p 00000000 00:00 0 
4200100000-14200100000 ---p 00000000 00:00 0 
7f9464000000-7f9464021000 rw-p 00000000 00:00 0 
7f9464021000-7f9468000000 ---p 00000000 00:00 0 
7f946c000000-7f946c021000 rw-p 00000000 00:00 0 
7f946c021000-7f9470000000 ---p 00000000 00:00 0 
7f94733e5000-7f94733fb000 r-xp 00000000 103:02 2756127                   /usr/lib/libgcc_s.so.1
7f94733fb000-7f94735fa000 ---p 00016000 103:02 2756127                   /usr/lib/libgcc_s.so.1
7f94735fa000-7f94735fb000 r--p 00015000 103:02 2756127                   /usr/lib/libgcc_s.so.1
7f94735fb000-7f94735fc000 rw-p 00016000 103:02 2756127                   /usr/lib/libgcc_s.so.1
7f94735fc000-7f94735fd000 ---p 00000000 00:00 0 
7f94735fd000-7f9473dfd000 rw-p 00000000 00:00 0 
7f9473dfd000-7f9473dff000 r-xp 00000000 103:02 2755839                   /usr/lib/gconv/CP1252.so
7f9473dff000-7f9473ffe000 ---p 00002000 103:02 2755839                   /usr/lib/gconv/CP1252.so
7f9473ffe000-7f9473fff000 r--p 00001000 103:02 2755839                   /usr/lib/gconv/CP1252.so
7f9473fff000-7f9474000000 rw-p 00002000 103:02 2755839                   /usr/lib/gconv/CP1252.so
7f9474000000-7f9474021000 rw-p 00000000 00:00 0 
7f9474021000-7f9478000000 ---p 00000000 00:00 0 
7f9478000000-7f9478021000 rw-p 00000000 00:00 0 
7f9478021000-7f947c000000 ---p 00000000 00:00 0 
7f947c000000-7f947c021000 rw-p 00000000 00:00 0 
7f947c021000-7f9480000000 ---p 00000000 00:00 0 
7f9480198000-7f948019b000 r-xp 00000000 103:02 2756071                   /usr/lib/gconv/UTF-16.so
7f948019b000-7f948039a000 ---p 00003000 103:02 2756071                   /usr/lib/gconv/UTF-16.so
7f948039a000-7f948039b000 r--p 00002000 103:02 2756071                   /usr/lib/gconv/UTF-16.so
7f948039b000-7f948039c000 rw-p 00003000 103:02 2756071                   /usr/lib/gconv/UTF-16.so
7f948059f000-7f94805a4000 r-xp 00000000 103:02 2755809                   /usr/lib/libnss_dns-2.24.so
7f94805a4000-7f94807a3000 ---p 00005000 103:02 2755809                   /usr/lib/libnss_dns-2.24.so
7f94807a3000-7f94807a4000 r--p 00004000 103:02 2755809                   /usr/lib/libnss_dns-2.24.so
7f94807a4000-7f94807a5000 rw-p 00005000 103:02 2755809                   /usr/lib/libnss_dns-2.24.so
7f94807a5000-7f94807e6000 r-xp 00000000 103:02 2763096                   /usr/lib/libnss_resolve.so.2
7f94807e6000-7f94807e9000 r--p 00040000 103:02 2763096                   /usr/lib/libnss_resolve.so.2
7f94807e9000-7f94807ea000 rw-p 00043000 103:02 2763096                   /usr/lib/libnss_resolve.so.2
7f94807ea000-7f94807eb000 rw-p 00000000 00:00 0 
7f94807eb000-7f94807ef000 r-xp 00000000 103:02 2759142                   /usr/lib/libcap.so.2.25
7f94807ef000-7f94809ee000 ---p 00004000 103:02 2759142                   /usr/lib/libcap.so.2.25
7f94809ee000-7f94809ef000 rw-p 00003000 103:02 2759142                   /usr/lib/libcap.so.2.25
7f94809ef000-7f9480a03000 r-xp 00000000 103:02 2755817                   /usr/lib/libresolv-2.24.so
7f9480a03000-7f9480c02000 ---p 00014000 103:02 2755817                   /usr/lib/libresolv-2.24.so
7f9480c02000-7f9480c03000 r--p 00013000 103:02 2755817                   /usr/lib/libresolv-2.24.so
7f9480c03000-7f9480c04000 rw-p 00014000 103:02 2755817                   /usr/lib/libresolv-2.24.so
7f9480c04000-7f9480c06000 rw-p 00000000 00:00 0 
7f9480c06000-7f9480c47000 r-xp 00000000 103:02 2763095                   /usr/lib/libnss_mymachines.so.2
7f9480c47000-7f9480c4a000 r--p 00040000 103:02 2763095                   /usr/lib/libnss_mymachines.so.2
7f9480c4a000-7f9480c4b000 rw-p 00043000 103:02 2763095                   /usr/lib/libnss_mymachines.so.2
7f9480c4b000-7f9480c4c000 rw-p 00000000 00:00 0 
7f9480c4c000-7f9480c5d000 r-xp 00000000 103:02 2794992                   /usr/lib/libodbcinst.so.2.0.0
7f9480c5d000-7f9480e5c000 ---p 00011000 103:02 2794992                   /usr/lib/libodbcinst.so.2.0.0
7f9480e5c000-7f9480e5d000 r--p 00010000 103:02 2794992                   /usr/lib/libodbcinst.so.2.0.0
7f9480e5d000-7f9480e5e000 rw-p 00011000 103:02 2794992                   /usr/lib/libodbcinst.so.2.0.0
7f9480e5e000-7f9480e61000 rw-p 00000000 00:00 0 
7f9480e61000-7f94810af000 r-xp 00000000 103:02 2762729                   /usr/lib/libcrypto.so.1.0.0
7f94810af000-7f94812ae000 ---p 0024e000 103:02 2762729                   /usr/lib/libcrypto.so.1.0.0
7f94812ae000-7f94812ca000 r--p 0024d000 103:02 2762729                   /usr/lib/libcrypto.so.1.0.0
7f94812ca000-7f94812d6000 rw-p 00269000 103:02 2762729                   /usr/lib/libcrypto.so.1.0.0
7f94812d6000-7f94812d9000 rw-p 00000000 00:00 0 
7f94812d9000-7f9481340000 r-xp 00000000 103:02 2762728                   /usr/lib/libssl.so.1.0.0
7f9481340000-7f9481540000 ---p 00067000 103:02 2762728                   /usr/lib/libssl.so.1.0.0
7f9481540000-7f9481544000 r--p 00067000 103:02 2762728                   /usr/lib/libssl.so.1.0.0
7f9481544000-7f948154b000 rw-p 0006b000 103:02 2762728                   /usr/lib/libssl.so.1.0.0
7f948154b000-7f94815b6000 r-xp 00000000 103:02 2795039                   /usr/lib/libtdsodbc.so.0.0.0
7f94815b6000-7f94817b6000 ---p 0006b000 103:02 2795039                   /usr/lib/libtdsodbc.so.0.0.0
7f94817b6000-7f94817b9000 r--p 0006b000 103:02 2795039                   /usr/lib/libtdsodbc.so.0.0.0
7f94817b9000-7f94817ba000 rw-p 0006e000 103:02 2795039                   /usr/lib/libtdsodbc.so.0.0.0
7f94817ba000-7f94817c4000 r-xp 00000000 103:02 2755810                   /usr/lib/libnss_files-2.24.so
7f94817c4000-7f94819c4000 ---p 0000a000 103:02 2755810                   /usr/lib/libnss_files-2.24.so
7f94819c4000-7f94819c5000 r--p 0000a000 103:02 2755810                   /usr/lib/libnss_files-2.24.so
7f94819c5000-7f94819c6000 rw-p 0000b000 103:02 2755810                   /usr/lib/libnss_files-2.24.so
7f94819c6000-7f94819cc000 rw-p 00000000 00:00 0 
7f94819cc000-7f94819d7000 r-xp 00000000 103:02 2755812                   /usr/lib/libnss_nis-2.24.so
7f94819d7000-7f9481bd6000 ---p 0000b000 103:02 2755812                   /usr/lib/libnss_nis-2.24.so
7f9481bd6000-7f9481bd7000 r--p 0000a000 103:02 2755812                   /usr/lib/libnss_nis-2.24.so
7f9481bd7000-7f9481bd8000 rw-p 0000b000 103:02 2755812                   /usr/lib/libnss_nis-2.24.so
7f9481bd8000-7f9481bec000 r-xp 00000000 103:02 2755816                   /usr/lib/libnsl-2.24.so
7f9481bec000-7f9481dec000 ---p 00014000 103:02 2755816                   /usr/lib/libnsl-2.24.so
7f9481dec000-7f9481ded000 r--p 00014000 103:02 2755816                   /usr/lib/libnsl-2.24.so
7f9481ded000-7f9481dee000 rw-p 00015000 103:02 2755816                   /usr/lib/libnsl-2.24.so
7f9481dee000-7f9481df0000 rw-p 00000000 00:00 0 
7f9481df0000-7f9481df7000 r-xp 00000000 103:02 2755807                   /usr/lib/libnss_compat-2.24.so
7f9481df7000-7f9481ff6000 ---p 00007000 103:02 2755807                   /usr/lib/libnss_compat-2.24.so
7f9481ff6000-7f9481ff7000 r--p 00006000 103:02 2755807                   /usr/lib/libnss_compat-2.24.so
7f9481ff7000-7f9481ff8000 rw-p 00007000 103:02 2755807                   /usr/lib/libnss_compat-2.24.so
7f9481ff8000-7f9481ff9000 ---p 00000000 00:00 0 
7f9481ff9000-7f94827f9000 rw-p 00000000 00:00 0 
7f94827f9000-7f94827fa000 ---p 00000000 00:00 0 
7f94827fa000-7f9482ffa000 rw-p 00000000 00:00 0 
7f9482ffa000-7f9482ffb000 ---p 00000000 00:00 0 
7f9482ffb000-7f94837fb000 rw-p 00000000 00:00 0 
7f94837fb000-7f9483804000 r-xp 00000000 103:02 2774785                   /usr/lib/libltdl.so.7.3.1
7f9483804000-7f9483a03000 ---p 00009000 103:02 2774785                   /usr/lib/libltdl.so.7.3.1
7f9483a03000-7f9483a04000 r--p 00008000 103:02 2774785                   /usr/lib/libltdl.so.7.3.1
7f9483a04000-7f9483a05000 rw-p 00009000 103:02 2774785                   /usr/lib/libltdl.so.7.3.1
7f9483a05000-7f9483b9a000 r-xp 00000000 103:02 2755757                   /usr/lib/libc-2.24.so
7f9483b9a000-7f9483d99000 ---p 00195000 103:02 2755757                   /usr/lib/libc-2.24.so
7f9483d99000-7f9483d9d000 r--p 00194000 103:02 2755757                   /usr/lib/libc-2.24.so
7f9483d9d000-7f9483d9f000 rw-p 00198000 103:02 2755757                   /usr/lib/libc-2.24.so
7f9483d9f000-7f9483da3000 rw-p 00000000 00:00 0 
7f9483da3000-7f9483ea6000 r-xp 00000000 103:02 2755815                   /usr/lib/libm-2.24.so
7f9483ea6000-7f94840a5000 ---p 00103000 103:02 2755815                   /usr/lib/libm-2.24.so
7f94840a5000-7f94840a6000 r--p 00102000 103:02 2755815                   /usr/lib/libm-2.24.so
7f94840a6000-7f94840a7000 rw-p 00103000 103:02 2755815                   /usr/lib/libm-2.24.so
7f94840a7000-7f9484139000 r-xp 00000000 103:02 2759134                   /usr/lib/libgmp.so.10.3.2
7f9484139000-7f9484338000 ---p 00092000 103:02 2759134                   /usr/lib/libgmp.so.10.3.2
7f9484338000-7f9484339000 r--p 00091000 103:02 2759134                   /usr/lib/libgmp.so.10.3.2
7f9484339000-7f948433a000 rw-p 00092000 103:02 2759134                   /usr/lib/libgmp.so.10.3.2
7f948433a000-7f948439d000 r-xp 00000000 103:02 2794993                   /usr/lib/libodbc.so.2.0.0
7f948439d000-7f948459c000 ---p 00063000 103:02 2794993                   /usr/lib/libodbc.so.2.0.0
7f948459c000-7f948459d000 r--p 00062000 103:02 2794993                   /usr/lib/libodbc.so.2.0.0
7f948459d000-7f94845a4000 rw-p 00063000 103:02 2794993                   /usr/lib/libodbc.so.2.0.0
7f94845a4000-7f94845a8000 rw-p 00000000 00:00 0 
7f94845a8000-7f94845c0000 r-xp 00000000 103:02 2755738                   /usr/lib/libpthread-2.24.so
7f94845c0000-7f94847bf000 ---p 00018000 103:02 2755738                   /usr/lib/libpthread-2.24.so
7f94847bf000-7f94847c0000 r--p 00017000 103:02 2755738                   /usr/lib/libpthread-2.24.so
7f94847c0000-7f94847c1000 rw-p 00018000 103:02 2755738                   /usr/lib/libpthread-2.24.so
7f94847c1000-7f94847c5000 rw-p 00000000 00:00 0 
7f94847c5000-7f94847c7000 r-xp 00000000 103:02 2755814                   /usr/lib/libdl-2.24.so
7f94847c7000-7f94849c7000 ---p 00002000 103:02 2755814                   /usr/lib/libdl-2.24.so
7f94849c7000-7f94849c8000 r--p 00002000 103:02 2755814                   /usr/lib/libdl-2.24.so
7f94849c8000-7f94849c9000 rw-p 00003000 103:02 2755814                   /usr/lib/libdl-2.24.so
7f94849c9000-7f94849cb000 r-xp 00000000 103:02 2755819                   /usr/lib/libutil-2.24.so
7f94849cb000-7f9484bca000 ---p 00002000 103:02 2755819                   /usr/lib/libutil-2.24.so
7f9484bca000-7f9484bcb000 r--p 00001000 103:02 2755819                   /usr/lib/libutil-2.24.so
7f9484bcb000-7f9484bcc000 rw-p 00002000 103:02 2755819                   /usr/lib/libutil-2.24.so
7f9484bcc000-7f9484bd3000 r-xp 00000000 103:02 2755818                   /usr/lib/librt-2.24.so
7f9484bd3000-7f9484dd2000 ---p 00007000 103:02 2755818                   /usr/lib/librt-2.24.so
7f9484dd2000-7f9484dd3000 r--p 00006000 103:02 2755818                   /usr/lib/librt-2.24.so
7f9484dd3000-7f9484dd4000 rw-p 00007000 103:02 2755818                   /usr/lib/librt-2.24.so
7f9484dd4000-7f9484dea000 r-xp 00000000 103:02 2763186                   /usr/lib/libz.so.1.2.11
7f9484dea000-7f9484fe9000 ---p 00016000 103:02 2763186                   /usr/lib/libz.so.1.2.11
7f9484fe9000-7f9484fea000 r--p 00015000 103:02 2763186                   /usr/lib/libz.so.1.2.11
7f9484fea000-7f9484feb000 rw-p 00016000 103:02 2763186                   /usr/lib/libz.so.1.2.11
7f9484feb000-7f948500e000 r-xp 00000000 103:02 2755756                   /usr/lib/ld-2.24.so
7f9485047000-7f94851df000 r--p 00000000 103:02 2774975                   /usr/lib/locale/locale-archive
7f94851df000-7f94851e5000 rw-p 00000000 00:00 0 
7f948520c000-7f948520d000 rw-p 00000000 00:00 0 
7f948520d000-7f948520e000 r--p 00022000 103:02 2755756                   /usr/lib/ld-2.24.so
7f948520e000-7f948520f000 rw-p 00023000 103:02 2755756                   /usr/lib/ld-2.24.so
7f948520f000-7f9485210000 rw-p 00000000 00:00 0 
7ffd02688000-7ffd026aa000 rw-p 00000000 00:00 0                          [stack]
7ffd02763000-7ffd02765000 r--p 00000000 00:00 0                          [vvar]
7ffd02765000-7ffd02767000 r-xp 00000000 00:00 0                          [vdso]
ffffffffff600000-ffffffffff601000 r-xp 00000000 00:00 0                  [vsyscall]
[1]    8343 abort (core dumped)  stack exec my-prog

Environment

OS: Archlinux 64bits
GHC: 8.0.2 64bits

λ yoeight → cat /etc/odbc.ini
[MSSQL]
Description = MS SQL Server
Driver = FreeTDS
Server = ${server_name}
UID = ${user}
PWD = ${password}
ReadOnly = No
Port = 1111
TDS_Version = 8.0

λ yoeight → cat /etc/odbcinst.ini
[FreeTDS]
Description = MS SQL Server
Driver = /usr/lib/libtdsodbc.so
UsageCount = 1
FileUsage = 1
Trace = Yes
TraceFile = /tmp/odbc.log

λ yoeight → cat /etc/freetds/freetds.conf 
#   $Id: freetds.conf,v 1.12 2007-12-25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same 
# name is found in the installation directory.  
#
# For information about the layout of this file and its settings, 
# see the freetds.conf manpage "man freetds.conf".  

# Global settings are overridden by those in a database
# server specific section
[global]
        # TDS protocol version
	tds version = auto

	# Whether to write a TDSDUMP file for diagnostic purposes
	# (setting this to /tmp is insecure on a multi-user system)
;	dump file = /tmp/freetds.log
;	debug flags = 0xffff

	# Command and connection timeouts
;	timeout = 10
;	connect timeout = 10
	
	# If you get out-of-memory errors, it may mean that your client
	# is trying to allocate a huge buffer for a TEXT field.  
	# Try setting 'text size' to a more reasonable limit 
	text size = 64512

# A typical Sybase server
[egServer50]
	host = symachine.domain.com
	port = 5000
	tds version = 5.0

# A typical Microsoft server
[egServer70]
	host = ntmachine.domain.com
	port = 1433
	tds version = 7.0

`True` and `"True"` converted to 0 MySQL (Windows)

Running this:

import Database.HDBC
import Database.HDBC.ODBC (connectODBC) 
main :: IO ()
main = do
    conn <- connectODBC "Driver={MySQL ODBC 5.3 Unicode Driver};Database=test;Server=localhost;User=***;Password=***;Option=3;"

    stmt <- prepare conn "INSERT INTO p (a) VALUES (?)"
    execute stmt [toSql True]
    execute stmt [toSql "True"]
    execute stmt [toSql "1"]
    execute stmt [toSql (1::Int)]
    commit conn

    disconnect conn

where

CREATE TABLE test.p (a BOOLEAN);

results in

mysql> select * from test.p;
+------+
| a    |
+------+
|    0 |
|    0 |
|    1 |
|    1 |

Please render package cross-compiler aware

Due to using let in hsc2hs files, the hdbc-odbc package cannot be used during cross-compilation. In order to cross-compile it for the aarch64-architecture (MariaDB/ODBC on Debian Buster on Nirogen8M) on an x86_64.architecture (GHC with stack tool on Ubuntu 20.04 LTS multiarch installation), I had to download the package and remove the let uses and with also replacing the "#{CALLCONV}" occurences by "ccall".

The package could simply be rendered cross-compiler aware by using separate function call headers for "stdcall" and "ccall" instead of using let-controlled macros. Please repair the present issue.

Exception: Prelude.chr: bad argument: 5046345

I'm consistently getting the following exception when running code talking to Hive through ODBC both from a binary and from GHCi:

*** Exception: Prelude.chr: bad argument: 5046345

On a different system, we got the same exception with a different number:

*** Exception: Prelude.chr: bad argument: 5832776

This is on GHC 8.0.1 on OS X with the latest version of HDBC and HDBC-odbc. The problem comes up at some initial part of the connection process because I get the exception even if I don't have the ODBC drivers installed or a DSN entry—it's not even trying to connect.

Here's the smallest example that causes the error. (It doesn't work with a correctly configured database either.):

main = connectODBC ""

Here's a stack trace:

ghc-iserv-prof: Prelude.chr: bad argument: 5046345
CallStack (from -prof):
  Database.HDBC.ODBC.Api.Errors.raiseError (Database/HDBC/ODBC/Api/Errors.hs:(22,1)-(33,61))
  Database.HDBC.ODBC.Api.Errors.checkError (Database/HDBC/ODBC/Api/Errors.hs:(18,1)-(19,50))
  Control.Concurrent.ReadWriteLock.withRead (Control/Concurrent/ReadWriteLock.hs:222:1-50)
  Control.Concurrent.ReadWriteVar.with (Control/Concurrent/ReadWriteVar.hs:118:1-58)
  Database.HDBC.ODBC.Wrappers.withMaybeDbc (Database/HDBC/ODBC/Wrappers.hs:120:1-36)
  Database.HDBC.ODBC.Wrappers.withDbcOrDie (Database/HDBC/ODBC/Wrappers.hs:(123,1)-(132,9))
  Database.HDBC.ODBC.Connection.connectODBC (Database/HDBC/ODBC/Connection.hsc:(77,1)-(92,17))
ghc: ghc-iserv terminated (1)
Leaving GHCi.

Hackage docs not building

There have been no hackage docs since 2.2.3.2.

non-strict version of quickQuery fails

It looks like a bug that a sequence of two calls of non-strict version of a quickQuery function leads to an user error (see https://gist.github.com/romankr/5553271).

I'm using
GHC Version: 7.4.1, stage 2 booted by GHC version 7.0.4
Windows 7 32-bit
HDBC-ODBC version: 2.3.1.0

Microsoft ODBC Driver 13: "Invalid character value for cast specification" inserting SqlBool

I seem to be having issues trying to insert a Bool value into a bit field in MSSQL. I'm receiv'ing the following error when I execute the INSERT:

SqlError {seState = "["22018"]", seNativeError = -1, seErrorMsg = "execute execute: ["0: [Microsoft][ODBC Driver 13 for SQL Server]Invalid character value for cast specification"]"}

I have no issues reading out bit values using SELECT, although it looks like these values are being pulled into a SqlChar instead of a SqlBool so not sure if that's a clue. The SqlChar is converted to Bool on my record type, so at least it's compatible.

When I convert my record type into [SqlValue], the value for the Bool is represented as a SqlBool False instead of SqlChar '\NUL' and this fails on INSERT.

Below is some sample code that illustrates the issue:

type ItemId = Integer
data TodoItem = TodoItem { itemId :: ItemId, description :: String, dueDate :: LocalTime, completed :: Bool } deriving (Show, Generic)
instance FromJSON TodoItem
instance ToJSON TodoItem

-- Maps the record type to SqlValues
fromItem :: TodoItem -> [SqlValue]
fromItem = flip (\x -> map (\f -> f x)) $ [toSql . description, toSql . dueDate, toSql . completed]

insertItem :: IConnection conn => IO conn -> TodoItem -> IO ()
insertItem ioconn item = do
    conn <- ioconn
    stmt <- prepare conn "INSERT INTO Items (Description, DueDate, Completed) VALUES (?, ?, ?)"
    execute stmt $ fromItem item
    commit conn

HDBC ODB errors on receiving 'wide character' data from MS SQL server

error message like 'Database/HDBC/ODBC/Statement.hsc:347:8-47: Irrefutable pattern failed for pattern' occurs in simple test cases where underlying data has 'extended' characters.
Simple hacking of the source showed that the unhandled case is an instance of 'BindColWString'.

I found this with both MS ODBC Client for Linux and FreeTDS drivers.

I will attempt to work around by restricting the client character set using FreeTDS.

I will consider attempting a fix if it is considered that the bug is 'genuine' and not a massive undertaking.

Upload documentation to Hackage

Would it be possible to upload the documentation to Hackage please? 🙏

Connecting with HDBC-ODBC in Windows 7

When trying to connect to data source in Windows 7 with HDBC-ODBC rising SqlError {seState = "[]", seNativeError = -1, seErrorMsg = "sqlGetInfo SQL_TXN_CAPABLE: []"}

Using Haskell Platform 2013.2.0.0 and Microsoft Access Database Engine 2010 Redistributable are installed.

Sample code which i use:

import Database.HDBC 
import Database.HDBC.ODBC

main = do
  let connectionString =  "DBQ=D:\\PROGRAMS\\TEST\\TEST.MDB;Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
  let ioconn = connectODBC connectionString
  conn <- ioconn
  vals <- quickQuery conn "SELECT TOP 10 * FROM testtab;" []
  print vals

OBDC Driver 11 for SQL Server query fails

I can create a connection without problem. I have at small test table

id: bigint
name: nvarchar(max)

And when I try

> quickQuery c "select id from test" []
[[SqlInt64 123]]
> quickQuery c "select * from test" []
*** Exception: SqlError {seState = "[\"07009\"]", seNativeError = -1, seErrorMsg = "sqlGetData: [\"0: [Microsoft][ODBC Driver 13 for SQL Server]Invalid Descriptor Index\"]"}

Same error for select name and select id,name.

StackOverflow has some ideas, but it doesn’t seem to fit this problem.

Apple Silicon Error: symbol not found in flat namespace '_SQLAllocHandle'

I get the following error in Apple Silicon only:

: dlopen(/Users/leandro/.stack/snapshots/x86_64-osx/9ed0aac300c5bf015175a50c931d043bc2219cb6f7af4b6c1e5f5753d2c76834/8.10.4/lib/x86_64-osx-ghc-8.10.4/libHSHDBC-odbc-2.6.0.0-6p9dskTqzU2DNqybDaJm5C-ghc8.10.4.dylib, 0x0005): symbol not found in flat namespace '_SQLAllocHandle'

Does anyone have any idea on how to solve it?

Support marshalling of other types than strings

The bindSqlValue function marshals all non-string values as strings, which results in type errors from the database server when e.g. passing an integer as a positional argument:

Implicit conversion from datatype 'CHAR' to 'INT' is not allowed.

A comment in the code already notes that the current marshalling is a hack.

HDBC-odbc not in LTS Haskell 10.*

no more update?