Difference between revisions of "ODBC"

From SME Server
Jump to navigationJump to search
(Created ODBC page)
 
m (Mods)
Line 5: Line 5:
  
 
===Used packages===
 
===Used packages===
−
unixODBC.i386 - [http://www.unixODBC.org unixODBC official site]  
+
unixODBC.i386 - is an API that provides a common interface to many different databases. [http://www.unixODBC.org unixODBC official site]  
  
 
unixODBC-devel.i386
 
unixODBC-devel.i386
  
−
php-odbc.i386 - a module for PHP applications that use ODBC databases.  
+
php-odbc.i386 - is simply an extension for PHP that allows you to use unixODBC to connect to database servers.
  
 
freetds - a re-implementation of the Tabular Data Stream protocol made by Sybase and Microsoft. [http://www.freetds.org/ FreeTDS official site]
 
freetds - a re-implementation of the Tabular Data Stream protocol made by Sybase and Microsoft. [http://www.freetds.org/ FreeTDS official site]
  
−
Optional: gcc
+
Optional: gcc - The GNU Compiler Collection includes front ends for C, C++, Objective-C, Fortran, Java, and Ada, as well as libraries for these languages. [http://gcc.gnu.org/ GCC official site]
  
 
===Installation===
 
===Installation===
Line 24: Line 24:
 
php-odbc.i386
 
php-odbc.i386
  
−
====Alt 1: Install precompiled FreeTDS====
+
====Alt 1: Install FreeTDS precompiled====
−
Install FreeTDS from Dag repository
+
It save some work and you don't need gcc but usually contain an older version.
 +
Install FreeTDS from Dag repository:
 
  yum --enablerepo=dag install freetds freetds-devel
 
  yum --enablerepo=dag install freetds freetds-devel
  
Line 31: Line 32:
  
 
====Alt 2: Install FreeTDS from source====
 
====Alt 2: Install FreeTDS from source====
 +
This will give you full control and the latest version (or any version, modify path as you like).
 +
yum install gcc
 +
 
  wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-0.82.tar.gz
 
  wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-0.82.tar.gz
  
Line 54: Line 58:
 
  yum remove gcc}}
 
  yum remove gcc}}
  
−
====Test with tsql====
+
====Test # 1====
−
Check settings:
+
TSQL is a database client included in FreeTDS package for testing and troubleshooting.
 +
Check compile settings:
  
 
  tsql -C
 
  tsql -C
Line 70: Line 75:
 
  unixodbc: yes
 
  unixodbc: yes
  
−
Test connection to a foreign database, overriding settings in freetds.conf:
+
Test connection to a foreign database, overriding settings in freetds.conf (modify to suit your conditions):
  
−
  tsql -S 192.168.0.112 -U testuser -P testpass
+
  tsql -S 192.168.0.112 -U username -P password
  
 
===Configuration===
 
===Configuration===
 +
Since theese configuration files reside in /etc they may be overwritten by other server modifications. Therefore we use a templating system to make sure key information survives. [[http://wiki.contribs.org/SME_Server:Documentation:Developers_Manual:Section2#Configuration_file_templates More info here]]
 +
 
====freetds.conf====
 
====freetds.conf====
 +
This file contains the settings that tell freetds about each server it will connect to. Ip can (should) be replaced with FQDN.
 +
 
  mkdir -p /etc/e-smith/templates-custom/etc/freetds.conf
 
  mkdir -p /etc/e-smith/templates-custom/etc/freetds.conf
 
  pico /etc/e-smith/templates-custom/etc/freetds.conf/10xp3c
 
  pico /etc/e-smith/templates-custom/etc/freetds.conf/10xp3c
 
Add your foreign database server (modify to suit your conditions):
 
Add your foreign database server (modify to suit your conditions):
−
# SQL Server on Win XP machine
+
# Microsoft SQL Server 2005 Express on a Win XP machine
 
  [xp3c]
 
  [xp3c]
 
         host = 192.168.0.112
 
         host = 192.168.0.112
Line 89: Line 98:
  
 
====odbcinst.ini====
 
====odbcinst.ini====
 +
This file tells unixODBC about the FreeTDS driver. Its kind of like an Alias, e.g.
 +
 
  mkdir -p /etc/e-smith/templates-custom/etc/odbcinst.ini
 
  mkdir -p /etc/e-smith/templates-custom/etc/odbcinst.ini
 
  pico /etc/e-smith/templates-custom/etc/odbcinst.ini/20sqlserver
 
  pico /etc/e-smith/templates-custom/etc/odbcinst.ini/20sqlserver
 +
 
Add driver path (modify to suit your conditions):
 
Add driver path (modify to suit your conditions):
 
  [FreeTDS]
 
  [FreeTDS]
Line 99: Line 111:
  
 
====odbc.ini====
 
====odbc.ini====
 +
This file defines the DSN's (Data Source Names) for each ODBC connection. A DSN is simply a label for a given connection.
 +
 
  mkdir -p /etc/e-smith/templates-custom/etc/odbc.ini
 
  mkdir -p /etc/e-smith/templates-custom/etc/odbc.ini
 
  pico /etc/e-smith/templates-custom/etc/odbc.ini/20sqlserver
 
  pico /etc/e-smith/templates-custom/etc/odbc.ini/20sqlserver
 +
 
Add text (modify to suit your conditions):
 
Add text (modify to suit your conditions):
 
  [sqlserver]
 
  [sqlserver]
Line 117: Line 132:
 
There should also be a section "odbc".
 
There should also be a section "odbc".
  
−
PHP function odbc_connect should now work.
+
PHP function odbc_connect (and related) should now work.
  
 
=== Additional information ===
 
=== Additional information ===
−
Current forum discussion at http://forums.contribs.org/index.php?topic=44185.0
+
[http://forums.contribs.org/index.php?topic=44185.0 Current forum discussion]
  
 
[http://se2.php.net/manual/en/book.uodbc.php php.net ODBC]
 
[http://se2.php.net/manual/en/book.uodbc.php php.net ODBC]
 +
 +
Input from (and credit to) David Critten and [http://www.calio.it/docs/doku.php/sqlserver calio.it] [http://translate.google.se/translate?u=http%3A%2F%2Fwww.calio.it%2Fdocs%2Fdoku.php%2Fsqlserver&sl=it&tl=en&hl=sv&ie=UTF-8 translated]
  
 
----
 
----
 
[[Category:Howto]]
 
[[Category:Howto]]

Revision as of 12:51, 18 June 2009

Geographylogo.png Languages: 

English


Warning.png Warning:
Under construction, some content may be totally wrong and misleading.


Description

Adding support in SME Server for a universal connection to foreign datebase servers using Open Database Connectivity (ODBC).

Used packages

unixODBC.i386 - is an API that provides a common interface to many different databases. unixODBC official site

unixODBC-devel.i386

php-odbc.i386 - is simply an extension for PHP that allows you to use unixODBC to connect to database servers.

freetds - a re-implementation of the Tabular Data Stream protocol made by Sybase and Microsoft. FreeTDS official site

Optional: gcc - The GNU Compiler Collection includes front ends for C, C++, Objective-C, Fortran, Java, and Ada, as well as libraries for these languages. GCC official site

Installation

Install basic packages via server-manager:

unixODBC.i386

unixODBC-devel.i386

php-odbc.i386

Alt 1: Install FreeTDS precompiled

It save some work and you don't need gcc but usually contain an older version. Install FreeTDS from Dag repository: 

yum --enablerepo=dag install freetds freetds-devel

signal-event post-upgrade; signal-event reboot

Alt 2: Install FreeTDS from source

This will give you full control and the latest version (or any version, modify path as you like). 

yum install gcc

wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-0.82.tar.gz

gzip -d freetds-0.82.tar.gz

tar -xvf  freetds-0.82.tar

yum install gcc

cd freetds-0.82

./configure --sysconfdir=/etc --enable-msdblib --with-tdsver=8.0

make

make install

make clean

signal-event post-upgrade; signal-event reboot


Warning.png Warning:
Leaving gcc on a production server is considered a security risk, if you do not really need it you should not install it, if you need it you should seriously consider uninstalling gcc when you no longer need it. Removing gcc can be done using the following command: 
yum remove gcc


Test # 1

TSQL is a database client included in FreeTDS package for testing and troubleshooting. Check compile settings: 

tsql -C

Should output someting similar to this: 

Compile-time settings (established with the "configure" script)
Version: freetds v0.82
freetds.conf directory: /usr/local/etc
MS db-lib source compatibility: no
Sybase binary compatibility: no
Thread safety: yes
iconv library: yes
TDS version: 8.0
iODBC: no
unixodbc: yes

Test connection to a foreign database, overriding settings in freetds.conf (modify to suit your conditions): 

tsql -S 192.168.0.112 -U username -P password

Configuration

Since theese configuration files reside in /etc they may be overwritten by other server modifications. Therefore we use a templating system to make sure key information survives. [More info here]

freetds.conf

This file contains the settings that tell freetds about each server it will connect to. Ip can (should) be replaced with FQDN. 

mkdir -p /etc/e-smith/templates-custom/etc/freetds.conf
pico /etc/e-smith/templates-custom/etc/freetds.conf/10xp3c

Add your foreign database server (modify to suit your conditions):

  1. Microsoft SQL Server 2005 Express on a Win XP machine
[xp3c]
       host = 192.168.0.112
       port = 1433
       tds version = 8.0  
       instance = db_kalle

expand-template /etc/freetds.conf

odbcinst.ini

This file tells unixODBC about the FreeTDS driver. Its kind of like an Alias, e.g. 

mkdir -p /etc/e-smith/templates-custom/etc/odbcinst.ini
pico /etc/e-smith/templates-custom/etc/odbcinst.ini/20sqlserver

Add driver path (modify to suit your conditions): 

[FreeTDS]

Description = Ms SQL Server access with FreeTDS driver Driver = /usr/lib/libtdsodbc.so 

expand-template /etc/odbcinst.ini

odbc.ini

This file defines the DSN's (Data Source Names) for each ODBC connection. A DSN is simply a label for a given connection. 

mkdir -p /etc/e-smith/templates-custom/etc/odbc.ini
pico /etc/e-smith/templates-custom/etc/odbc.ini/20sqlserver

Add text (modify to suit your conditions): 

[sqlserver]

Driver = FreeTDS Description = Connection to DB_KALLE on server xp3c Trace = No Server = 192.168.0.112 Database = db_kalle Port = 1433 TDS_Version = 8.0 

expand-template /etc/odbc.ini

Check PHP configuration

phpinfo(), section Configure Command, outputs: --with-unixODBC=shared,/usr There should also be a section "odbc".

PHP function odbc_connect (and related) should now work.

Additional information

Current forum discussion

php.net ODBC

Input from (and credit to) David Critten and calio.it translated

Retrieved from "https://wiki.koozali.org/index.php?title=ODBC&oldid=13080"