Difference between revisions of "ODBC"

yum --enablerepo=dag install freetds freetds-devel

wget http://dag.wieers.com/rpm/packages/freetds/freetds-0.64-1.el4.rf.i386.rpm

wget http://dag.wieers.com/rpm/packages/freetds/freetds-devel-0.64-1.el4.rf.i386.rpm

yum localinstall freetds-*.rpm

signal-event post-upgrade; signal-event reboot

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
cd freetds-0.82
./configure --sysconfdir=/etc --prefix=/usr --with-tdsver=8.0
make
make install
make clean
signal-event post-upgrade; signal-event reboot

yum remove gcc

tsql -C

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

tsql -H 192.168.0.112 -p 1433 -U dbuser -P dbpass

locale is "sv_SE"
locale charset is "ISO-8859-1"
1> 

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

cp /etc/freetds.conf /etc/e-smith/templates-custom/etc/freetds.conf/10original

pico /etc/e-smith/templates-custom/etc/freetds.conf/20xp3c

# Microsoft SQL Server 2005 Express on a Win XP machine
[remoteserver]
host = xp3c
port = 1433
tds version = 8.0

expand-template /etc/freetds.conf

pico /etc/freetds.conf

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

cp /etc/odbcinst.ini /etc/e-smith/templates-custom/etc/odbcinst.ini/10original

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

[FreeTDS]
Description = Access Microsoft SQL Server with FreeTDS driver
Driver = /usr/lib/libtdsodbc.so

expand-template /etc/odbcinst.ini

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

cp /etc/odbc.ini /etc/e-smith/templates-custom/etc/odbc.ini/10original

pico /etc/e-smith/templates-custom/etc/odbc.ini/20xp3c

[remoteserver]
Driver = FreeTDS
Description = Connection to database DB_TEST on server XP3C
Trace = No
Server = xp3c
Database = db_test
Port = 1433
TDS_Version = 8.0

expand-template /etc/odbc.ini

mkdir -p /etc/e-smith/templates-custom/etc/hosts 

pico /etc/e-smith/templates-custom/etc/hosts/20xp3c

192.168.0.112  xp3c

expand-template /etc/hosts

tsql -S remoteserver -U dbuser -P dbpass

isql -v remoteserver dbuser dbpass

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

if (function_exists('odbc_connect')) 
{ echo "Function odbc_connect exists!"; }
else { echo "Function odbc_connect does NOT exist!"; }

phpinfo();

 $con = odbc_connect("remoteserver", "dbuser", "dbpass");
 if (!$con) { print("There is a problem with SQL Server connection."); } 
 else 	{ print("The SQL Server connection object seems to work."); 
         odbc_close($con); }

cd freetds-0.82/src/apps
./osql -S xp3c -U dbuser -P dbpass

Difference between revisions of "ODBC"

Latest revision as of 08:01, 27 June 2009

Contents

Description

Used packages

Installation

Alt 1: Install FreeTDS precompiled

Alt 2: Install FreeTDS from source

Test with tsql

Configuration

freetds.conf

odbcinst.ini

odbc.ini

Hosts

Test again

tsql

isql

Check PHP

Additional information

Troubleshooting

Microsoft SQL Server

Navigation menu

Page actions

Page actions

Personal tools

Koozali SME Server

Recent activities

Koozali resources

Koozali SME Server wiki

Tools

Search

@@ Line 1: / Line 1: @@
 {{Languages}}
-{{Warning box| Under construction, some content may be totally wrong and misleading.}}
 {{Level|medium}}
 ===Description===
-Adding support in SME Server for a universal connection to foreign datebase servers using Open Database Connectivity (ODBC).
+Adding support in SME Server for a universal connection to foreign datebase servers using Open Database Connectivity (ODBC) with focus on PHP.
-This HowTo focus on building PHP support for ODBC.
+We will assume that you have an Microsoft SQL Server 2000/2005 running somewhere. The SQL Server should allow remote access and support connections using TCP/IP. As of my knowledge, connections using named pipes are unsupported. Of course; you will want a login id and password.
+It should be similar with Sybase and other versions of SQL Server, consult FreeTDS and unixODBC documentation for details.
 ===Used packages===
-'''unixODBC''' - is an API that provides a common interface to many different databases. [http://www.unixODBC.org unixODBC official site]
+'''unixODBC''' - is a driver manager with built-in support for many different databases. [http://www.unixODBC.org unixODBC official site]
 '''unixODBC-devel'''
-'''php-odbc''' - is simply an extension for PHP that allows you to use unixODBC to connect to database servers.
+'''php-odbc''' - is simply an extension that allows you to use unixODBC to connect to database servers using PHP.
-'''freetds''' - a re-implementation of the Tabular Data Stream protocol made by Sybase and Microsoft. [http://www.freetds.org/ FreeTDS official site]
+'''FreeTDS''' - adding drivers for Sybase and Microsoft using TDS protocol. [http://www.freetds.org/ 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. [http://gcc.gnu.org/ GCC official site]
+Optional: '''GCC''' - The GNU Compiler Collection with libraries. [http://gcc.gnu.org/ GCC official site]
 ===Installation===
@@ Line 51: / Line 52: @@
   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
   cd freetds-0.82
+  ./configure --sysconfdir=/etc --prefix=/usr --with-tdsver=8.0
-  ./configure --sysconfdir=/etc --prefix=/usr --enable-msdblib --with-tdsver=8.0
   make
   make install
   make clean
   signal-event post-upgrade; signal-event reboot
 {{Warning box|msg=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 with tsql====
-TSQL is a database client included in FreeTDS package for testing and troubleshooting. "man tsql" give more info.
+TSQL is a simple database client included in FreeTDS package for testing and troubleshooting. "man tsql" give more info.
 Check compile settings:
   tsql -C
-Should output something similar to this:
+Should output something similar to this (if you compiled 0.82 version with above options):
   Compile-time settings (established with the "configure" script):
-  Version: freetds v0.64
+  Version: freetds v0.82
-  MS db-lib source compatibility: yes
+  MS db-lib source compatibility: no
-  Sybase binary compatibility: unknown
+  Sybase binary compatibility: no
   Thread safety: yes
   iconv library: yes
-  TDS version: 4.2
+  TDS version: 8.0
   iODBC: no
   unixodbc: yes
-Test connection to a foreign database, overriding settings in freetds.conf (modify to suit your conditions, ip could be replaced with FQDN):
+Test connection to the foreign database, overriding settings in freetds.conf (modify to suit your conditions). IP and not FQDN should be used. If on a NT domain, replace <dbuser> with <'DOMAINNAME\dbuser'>.
   tsql -H 192.168.0.112 -p 1433 -U dbuser -P dbpass
-A response something like this would indicate that connection was successful but login failed (wich is normal at this stage):
+This should output something like this indicate that you are connected, logged in and the server are ready for SQL commands:
   locale is "sv_SE"
   locale charset is "ISO-8859-1"
-  Msg 18456, Level 14, State 1, Server XP3B\SQLEXPRESS, Line 1
+>
- Login failed for user 'dbuser'.
+Exit tsql console with "quit". If connection was made but you didn't log in indicates that you have a problem in your remote server, check settings and permissions.
- Msg 2002, Level 9, State -1, Server OpenClient, Line -1
- Adaptive Server connection failed
- There was a problem connecting to the server
 ===Configuration===
@@ Line 106: / Line 94: @@
 ====freetds.conf====
-This file contains the settings that tell freetds about each server it will connect to. Host should be a FQDN (Fully Qualified Domain Name).
+This file is provided by FreeTDS and contains the settings that tell freetds about each server it will connect to.
-Make directory to hold template fragments to be included in the original path: /etc/freetds.conf
+<br>Host should be a FQDN (Fully Qualified Domain Name).
+<br>Make directory to hold template fragments to be included in the final file located in /etc/freetds.conf
   mkdir -p /etc/e-smith/templates-custom/etc/freetds.conf
@@ Line 121: / Line 110: @@
   host = xp3c
   port = 1433
   tds version = 8.0
- instance = db_kalle
 Exit with ctrl-x, save with y and enter. Regenerate the complete file:
@@ Line 132: / Line 120: @@
 ====odbcinst.ini====
-This file tells unixODBC about the FreeTDS driver.
+This file is provided by unixODB and tells unixODBC about the FreeTDS driver.
   mkdir -p /etc/e-smith/templates-custom/etc/odbcinst.ini
@@ Line 143: / Line 131: @@
   [FreeTDS]
-  Description = Access Ms SQL Server with FreeTDS driver
+  Description = Access Microsoft SQL Server with FreeTDS driver
   Driver = /usr/lib/libtdsodbc.so
@@ Line 149: / Line 137: @@
 ====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.
+This file is provided by unixODB and 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
   cp /etc/odbc.ini /etc/e-smith/templates-custom/etc/odbc.ini/10original
+If your original file are empty, there is no need to copy it, but it doesn't hurt either.
   pico /etc/e-smith/templates-custom/etc/odbc.ini/20xp3c
@@ Line 159: / Line 148: @@
 Add text (modify to suit your conditions):
-  [xp3c]
+  [remoteserver]
   Driver = FreeTDS
-  Description = Connection to DB_KALLE on server xp3c
+  Description = Connection to database DB_TEST on server XP3C
   Trace = No
   Server = xp3c
-  Database = db_kalle
+  Database = db_test
   Port = 1433
   TDS_Version = 8.0
@@ Line 171: / Line 160: @@
 ====Hosts====
-If the DNS does not resolve the ip of the machines running the remote SQL server (try ping the host name), add them to your hosts file (ip and change the machine name ...):
+If the DNS does not resolve the ip of the machines running the remote SQL server (try ping the host name), add them to your hosts file (change ip and the machine name ...):
   mkdir -p /etc/e-smith/templates-custom/etc/hosts
@@ Line 179: / Line 168: @@
   expand-template /etc/hosts
+The original content of the hosts file are generated elsewhere, hence not included here.
 ===Test again===
 ====tsql====
-Test connection, using input in freetds.conf:
+Test connection, using input (DSN and driver) in the configuration files we just edited, the result should be the same as the previous tsql test:
-  tsql -S xp3c -U testuser -P testpass
+  tsql -S remoteserver -U dbuser -P dbpass
-This should output something like this:
+====isql====
- locale is "sv_SE"
+ISQL is a simple database client included in unixODBC package for testing and troubleshooting. The command "isql" give more info.
- locale charset is "ISO-8859-1"
->
+Generally speaking, isql is a bit more fussy about correct configuration settings than tsql, therefore we include also this test.
-> indicate that you are connected, logged in and the server are ready for SQL commands. Exit tsql console with:
- quit
-====isql====
+At the command line enter <isql -v DSN USERNAME PASSWORD>. If on a NT domain, replace <dbuser> with <'DOMAINNAME\dbuser'>:
-At the command line enter the command isql -v DSN USERNAME PASSWORD e.g.:
-  isql -v xp3c dbuser dbpass
+  isql -v remoteserver dbuser dbpass
   +---------------------------------------+
@@ Line 207: / Line 195: @@
   SQL>
-If you do not get output similar to the above, then check your config files, pay special attention to upper/lowercase, for example XP3C is not the same as xp3c!
+If you do not get output similar to the above, then check your config files, pay special attention to upper/lowercase, for example REMOTESERVER is not the same as remoteserver.
 ===Check PHP===
-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.
 Sample php code to check above could be (add standard html code):
@@ Line 221: / Line 205: @@
 and
   phpinfo();
+Section Configure Command, should contain: --with-unixODBC=shared,/usr.
+There should also be a "odbc"-section further down.
+So far the php-functionality have been provided by unixODBC and php-odbc and should have worked even before we installed the FreeTDS package.
+To test FreeTDS drivers in PHP you can test a connection to your remote database:
+  $con = odbc_connect("remoteserver", "dbuser", "dbpass");
+  if (!$con) { print("There is a problem with SQL Server connection."); }
+  else 	{ print("The SQL Server connection object seems to work.");
+          odbc_close($con); }
 === Additional information ===
@@ Line 227: / Line 221: @@
 FreeTDS [http://www.freetds.org/userguide/ user guide]  with  [http://www.freetds.org/userguide/troubleshooting.htm troubleshooting section].
-[http://se2.php.net/manual/en/book.uodbc.php php.net ODBC]
+unixODBC [http://www.unixODBC.org official site]
+[http://php.net/manual/en/book.uodbc.php php.net ODBC]
 Input from, and credit to, Nick Critten [http://bends.se/it/smeserver/filer/HowTo_NickCritten_freetds.html unofficial copy of his HowTo] 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]
 ===Troubleshooting===
-Tab spaces in config files may cause troubles, use single space before and after =.
+There are some ifs and buts involved in theese config files, please consult the docs at FreeTDS and UnixODBC if you are running into trouble. Feel free to contribute to this HowTo with your experiences.
+Tab spaces in config files may cause troubles, use single space before and after the <=>.
+If on a NT domain, replace <dbuser> with <'DOMAINNAME\dbuser'> (with a single quotation mark before and after).
+<br>Escaping the backslash with another backslash <DOMAINNAME\\dbuser> would also work.
+The host name xp3c used in the examples above works in a workgroup environment, it could (should?) be a full FQDN like xp3c.mydomain.com in a NT domain environment.
+In the source directory freetds-0.82/src/apps is the shell script "osql" that attempts to automatically verify the ODBC setup. It checks odbc.ini, odbcinst, and, optionally, freetds.conf, then execute isql (assume it's unixODBC's isql). Syntax: osql -S server -U user -P password.
+ cd freetds-0.82/src/apps
+ ./osql -S xp3c -U dbuser -P dbpass
+On some systems (RISC) the drivers in /usr/local/lib/ may have the extension .sl rather than .so for shared libraries.
-====Ms SQL Server====
+====Microsoft SQL Server====
-If you are using instances, add the line "instance = db_kalle" in your freetds.conf template fragment (replace db_kalle with the name of your instance).
+If your SQL Server are using instances, replace <port = 1433> with <instance = MyInstanceName> in your freetds.conf template fragment. Maybe adding the instance name after host in freetds.conf like this: <Server = xp3c\MyInstanceName> would aid too.
-From SQL Server 2000 TDS version 8.0 should be used. Add the line "tds version = 8.0" in your template fragment for freetds.conf
+From SQL Server 2000 TDS version 8.0 should be used. Add the line <tds version = 8.0> in your template fragment for freetds.conf
-Ms SQL Server might not allow the db system user "sa" to connect from outside the server, create another account with proper permissions.
+Microsoft SQL Server might not allow the db system user "sa" to connect from outside the server, create another account with proper permissions.
-Express edition have remote access disabled by default. Can be changed in the menu "Surface Area Configuration". [http://support.microsoft.com/kb/914277 More info]
+Express edition have remote access disabled by default. Can be changed in the menu "Surface Area Configuration". [http://support.microsoft.com/kb/914277 More info here]
 ----
 [[Category:Howto]]

	Skill level: medium
	The instructions on this page require a basic knowledge of linux.

	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