Awesome
persistent-odbc
Uses the Persistent package and HDBC-odbc to access databases via ODBC.
Thanks to Dmitry Olshansky at https://github.com/odr/persistent-odbc/ for doing the initial hookup to hdbc-odbc.
Supports Postgres, MySql, MS Sql Server, Oracle, DB2, and SQLite.
see TestODBC.hs for usage and tests.
How to get started using cabal >= 1.18
git clone https://github.com/gbwey/persistent-odbc
cd persistent-odbc
stack ghci --test --flag persistent-odbc:tester
:load TestODBC.hs (also need to define the appropriate odbc system dsn below and then run the command)
e.g. for postgresql
1. create a system odbc dsn "pg_test"
2. :main p
| DBMS | ODBC System Dsn | Command |
|---|---|---|
| Postgresql | dsn=pg_test | :main p |
| MySQL | dsn=mysql_test | :main m |
| DB2 | dsn=db2_test | :main d |
| Ms Sql Server >= 2012 | dsn=mssql_test | :main s |
| Ms Sql Server pre 2012 | dsn=mssql_test | :main so |
| Oracle pre 12c | dsn=oracle_test | :main o |
| Oracle >= 12c | dsn=oracle_test | :main on |
| Sqlite | dsn=sqlite_test | :main q |
Limit and Offset in Ms Sql Server and Oracle
MSSQL True for MS Sql Server 2012 which has limit and offset support (esqueleto as well). MSSQL False for MS Sql Server pre 2012 only supports limit using Persistent's select* operations. (select top n ...).
Oracle True for Oracle >=12c which has limit and offset support (esqueleto as well). Oracle False for Oracle <12c where there is no limit and offset support.
Ms Sql Server and blobs
Blobs are problematic in hdbc-odbc.
Ms Sql Server and deleteCascadeWhere
Can cause segfault in Ms Sql Server.
Oracle and nulls
Treats empty string as a null (oracle issue).
Oracle and sorting blobs
Cannot sort on a blob field (oracle issue).
DB2 and blobs
Blobs don't support nulls (both insert and select) in this version of persistent-odbc (also doesn't work in hdbc-odbc). Select returns the blob values as unpacked strings at the moment (if there is interest in getting this fixed let me know).