Difference between revisions of "MetaHDBC Install"

From HaskellWiki
Jump to: navigation, search
m (Creating a database)
m (UseServerSidePrepare)
 
(8 intermediate revisions by the same user not shown)
Line 3: Line 3:
 
= Installing MetaHDBC, HDBC and HDBC-odbc =
 
= Installing MetaHDBC, HDBC and HDBC-odbc =
   
On Unix:
 
  +
* cabal install MetaHDBC
  +
  +
Or you can download and install the bleeding edge version:
   
 
* darcs get http://code.haskell.org/MetaHDBC/
 
* darcs get http://code.haskell.org/MetaHDBC/
 
* cd MetaHDBC
 
* cd MetaHDBC
* runhaskell Setup.lhs configure --prefix=$HOME --user
+
* runhaskell Setup.lhs configure --user
 
* runhaskell Setup.lhs build
 
* runhaskell Setup.lhs build
 
* runhaskell Setup.lhs install
 
* runhaskell Setup.lhs install
Line 23: Line 25:
 
* use "make DBMS/run<some DBMS>" to run the tests for a specific DBMS.
 
* use "make DBMS/run<some DBMS>" to run the tests for a specific DBMS.
   
= Database on Linux =
+
= Databases on Linux =
 
To use MetaHDBC you will need a database. This section describes how to setup various databases for Linux.
 
To use MetaHDBC you will need a database. This section describes how to setup various databases for Linux.
   
Line 29: Line 31:
   
 
For full type inference you can use [http://www.ibm.com/db2 DB2] or [http://www.microsoft.com/SQL/ MS SQL Server].
 
For full type inference you can use [http://www.ibm.com/db2 DB2] or [http://www.microsoft.com/SQL/ MS SQL Server].
  +
  +
The setup instructions below, have to be adapted to your particular system
   
 
== DB2 ==
 
== DB2 ==
   
Using [http://www.ibm.com/db2 DB2] with MetaHDBC is attractive as DB2 supports the ODBC functions necessary for doing full type inference. The [http://www-306.ibm.com/software/data/db2/express/ Express-C edition] is also free of charge and [http://www.ubuntu.com/partners/ibm/db2 Ubuntu makes DB2 Express-C available though their partner program].
+
[http://www.ibm.com/db2 DB2] is a pain to install, but using DB2 with MetaHDBC is attractive as DB2 supports the ODBC functions necessary for doing full type inference. The [http://www-306.ibm.com/software/data/db2/express/ Express-C edition] is also free of charge and [http://www.ubuntu.com/partners/ibm/db2 Ubuntu makes DB2 Express-C available though their partner program].
   
 
[http://www.tldp.org/HOWTO/DB2-HOWTO/ DB2 Howto] will help you install DB2. There are explanations for various flavors of Linux.
 
[http://www.tldp.org/HOWTO/DB2-HOWTO/ DB2 Howto] will help you install DB2. There are explanations for various flavors of Linux.
Line 52: Line 56:
 
=== UnixODBC setup ===
 
=== UnixODBC setup ===
   
If you install the .deb package from above your /etc/odbcinst.ini should have added the following lines:
 
  +
You need to add the DB2 driver to /etc/odbcinst.ini, and it is something like (but it varies from version to version):
   
 
[DB2]
 
[DB2]
Line 60: Line 64:
 
DontDLClose = 1
 
DontDLClose = 1
   
and your /etc/odbc.ini should have this added:
+
and your /etc/odbc.ini should look something like:
   
 
[MHdbcDB2]
 
[MHdbcDB2]
Line 72: Line 76:
 
The UnixODBC.org site also has [http://www.unixodbc.org/doc/db2.html documentation for setting up ODBC for DB2].
 
The UnixODBC.org site also has [http://www.unixodbc.org/doc/db2.html documentation for setting up ODBC for DB2].
   
== PostgreSQL (Debian) ==
+
== PostgreSQL, MySQL, SQLite, ... ==
 
I have added the following section to /etc/odbcinst.ini:
 
 
[postgreodbc]
 
Description = PostgreSQL ODBC driver
 
Driver = /usr/lib/odbc/psqlodbcw.so
 
Setup = /usr/lib/odbc/libodbcpsqlS.so
 
Debug = 0
 
CommLog = 1
 
UsageCount = 2
 
 
and I have added this to /etc/odbc.ini:
 
 
[MetaHDBC_PQ_DSN]
 
Description = PostgreSQL ODBC
 
Driver = postgreodbc
 
Trace = No
 
TraceFile =
 
Database = MetaHDBC_DB
 
Servername = localhost
 
UserName = MetaHDBC
 
Password = <password of MetaHDBC user>
 
Port = 5433
 
Protocol = 8.3
 
ReadOnly = No
 
RowVersioning = No
 
ShowSystemTables = No
 
ShowOidColumn = No
 
FakeOidIndex = No
 
ConnSettings =
 
 
 
== MySQL (Debian) ==
 
 
I have added the following section to /etc/odbcinst.ini:
 
 
[MySQL]
 
DESCRIPTION = MySQL Database connect
 
DRIVER = /usr/lib/odbc/libmyodbc.so
 
Setup = /usr/lib/odbc/libodbcmyS.so
 
FileUsage = 1
 
 
and I have added this to /etc/odbc.ini:
 
 
[MetaHDBC_DSN]
 
DESCRIPTION = mysql driver 3.51
 
DRIVER = MySQL
 
SERVER = localhost
 
PORT = 3306
 
USER = MetaHDBC
 
Password = <password of MetaHDBC user>
 
Database = MetaHDBC
 
SOCKET = /var/run/mysqld/mysqld.sock
 
 
== SQLite (Debian) ==
 
   
The SQLite database may be the easiest to use as it do not require a server. The database is just stored in a ordinary file. No need for passwords.
 
  +
[http://www.unixodbc.org/odbcinst.html See here for instruction for setup of UnixODBC on Linux].
   
I have added the following section to /etc/odbcinst.ini:
 
  +
At least for MySQL and PostgreSQL you need to use "UseServerSidePrepare = 0" in your odbc.ini file.
   
[SQLite]
 
  +
To run the tests you need to setup a database. Database name and login information can be found in test/DSN.hs.
Description = SQLite ODBC Driver
 
Driver = /usr/lib/odbc/libsqliteodbc.so
 
Setup = /usr/lib/odbc/libsqliteodbc.so
 
UsageCount = 1
 
   
and I have added this to /etc/odbc.ini:
 
  +
= Databases on Windows =
   
[MetaHDBC_SQLite]
 
  +
MetaHDBC has been reported to work with Microsoft SQL Server. However, Microsoft's ODBC driver do not support UTF-8. [https://patch-tag.com/r/GalyshAndrey/MetaHDBC_MSSQL/wiki/ Andrey Galysh has made a workaround for the UTF-8 problem here].
Description=My SQLite test database
 
Driver=SQLite
 
Database=<path to database>
 
# optional lock timeout in milliseconds
 
Timeout=20000
 

Latest revision as of 19:50, 24 September 2012

This page is about installing MetaHDBC.

Installing MetaHDBC, HDBC and HDBC-odbc

  • cabal install MetaHDBC

Or you can download and install the bleeding edge version:

Running Test

After installation you can run some tests. They are located in the test directory. But beware, the tests drops tables, creates tables, and other data manipulation both at compile and run -time. So be sure that you do not accidentally destroy valuable data.

Before running the test you will need to install one or more databases management system (DBMSs). You can see the following section for some guidance.

After installing databases you need to:

  • Setup your data source names in test/DSN.hs
  • "runhaskell CreateTables.hs" to create database tables. You will need specify which DBMSs you want to create tables for. Use " runhaskell CreateTables.hs help" for guidance.
  • use "make DBMS/run<some DBMS>" to run the tests for a specific DBMS.

Databases on Linux

To use MetaHDBC you will need a database. This section describes how to setup various databases for Linux.

You can use the popular open source databases PostgreSQL, MySQL and SQLite with MetaHDBC. They are all likely to be part of your Linux distribution. These databases in conjunction with MetaHDBC will provide you with SQL syntax check and it will give type inference for returned values. However, they will 'not' infer the types of positional parameters, as these databases do not support the ODBC function SQLDescribeParam properly.

For full type inference you can use DB2 or MS SQL Server.

The setup instructions below, have to be adapted to your particular system

DB2

DB2 is a pain to install, but using DB2 with MetaHDBC is attractive as DB2 supports the ODBC functions necessary for doing full type inference. The Express-C edition is also free of charge and Ubuntu makes DB2 Express-C available though their partner program.

DB2 Howto will help you install DB2. There are explanations for various flavors of Linux.

Before installing DB2 you should be given this fair warning. When I installed this .deb package, pointed to from the howto, the install script accidentally overwrote a file in /usr/include. This may or may not be true for the other types of packages pointed to from the howto.

The error is explained in more detail here.

Creating a database

To create a test database that MetaHDBC can use, do:

> su - db2inst1
> db2 create database <database name>
DB20000I  The CREATE DATABASE command completed successfully.

The database name must maximally be eight characters. Make a database called MHdbcDB2, if you want it to be usable from the MetaHDBC test programs.

UnixODBC setup

You need to add the DB2 driver to /etc/odbcinst.ini, and it is something like (but it varies from version to version):

[DB2]
Description     = DB2 Driver
Driver          = /opt/ibm/db2exc/V9.5/lib32/libdb2.so
FileUsage       = 1
DontDLClose     = 1

and your /etc/odbc.ini should look something like:

[MHdbcDB2]
Description     = Test DB2
Driver          = DB2
USER            = db2inst1
PASSWD          = <password of db2inst1 user>

The password is ibmdb2 if you have not changed it after installing DB2.

The UnixODBC.org site also has documentation for setting up ODBC for DB2.

PostgreSQL, MySQL, SQLite, ...

See here for instruction for setup of UnixODBC on Linux.

At least for MySQL and PostgreSQL you need to use "UseServerSidePrepare = 0" in your odbc.ini file.

To run the tests you need to setup a database. Database name and login information can be found in test/DSN.hs.

Databases on Windows

MetaHDBC has been reported to work with Microsoft SQL Server. However, Microsoft's ODBC driver do not support UTF-8. Andrey Galysh has made a workaround for the UTF-8 problem here.