Changes

From SME Server
Jump to navigationJump to search
711 bytes added ,  16:47, 20 June 2009
m
update
Line 3: Line 3:  
{{Level|medium}}
 
{{Level|medium}}
 
===Description===
 
===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 Ms 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===
 
===Used packages===
'''unixODBC''' - is an API that provides a common interface to many different databases. [http://www.unixODBC.org unixODBC official site]  
+
'''unixODBC''' - is an API that provides a common interface to many different databases exept Sybase and SQL Server. [http://www.unixODBC.org unixODBC official site]  
    
'''unixODBC-devel'''
 
'''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 för 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===
 
===Installation===
Line 58: Line 60:  
  cd freetds-0.82
 
  cd freetds-0.82
   −
  ./configure --sysconfdir=/etc --prefix=/usr --enable-msdblib --with-tdsver=8.0
+
  ./configure --sysconfdir=/etc --prefix=/usr --with-tdsver=8.0
    
  make
 
  make
Line 72: Line 74:     
====Test with tsql====
 
====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:
 
Check compile settings:
Line 96: Line 98:  
  locale is "sv_SE"
 
  locale is "sv_SE"
 
  locale charset is "ISO-8859-1"
 
  locale charset is "ISO-8859-1"
  Msg 18456, Level 14, State 1, Server XP3B\SQLEXPRESS, Line 1
+
  Msg 18456, Level 14, State 1, Server XP3C\SQLEXPRESS, Line 1
 
  Login failed for user 'dbuser'.
 
  Login failed for user 'dbuser'.
 
  Msg 2002, Level 9, State -1, Server OpenClient, Line -1
 
  Msg 2002, Level 9, State -1, Server OpenClient, Line -1
Line 106: Line 108:     
====freetds.conf====
 
====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
 
  mkdir -p /etc/e-smith/templates-custom/etc/freetds.conf
   Line 131: Line 134:     
====odbcinst.ini====
 
====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
 
  mkdir -p /etc/e-smith/templates-custom/etc/odbcinst.ini
Line 148: Line 151:     
====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.
+
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
 
  mkdir -p /etc/e-smith/templates-custom/etc/odbc.ini
Line 192: Line 195:     
====isql====
 
====isql====
At the command line enter the command isql -v DSN USERNAME PASSWORD e.g.:
+
ISQL is a simple database client included in unixODBC package for testing and troubleshooting. The command "isql" give more info.
 +
 
 +
At the command line enter "isql -v DSN USERNAME PASSWORD" e.g.:
    
  isql -v remoteserver dbuser dbpass
 
  isql -v remoteserver dbuser dbpass
Line 206: Line 211:  
  SQL>
 
  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===
 
===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):
 
Sample php code to check above could be (add standard html code):
Line 220: Line 221:  
and
 
and
 
  phpinfo();
 
  phpinfo();
and
+
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 unixODB and php-odbc and should have worked even before we installed the FreeTDS package.
 +
 
 +
To test FreeTDS drivers you can do a connection to your remote database, "remoteserver" contain the DSN built in odbc.ini:
 
   $con = odbc_connect("remoteserver", "dbuser", "dbpass");
 
   $con = odbc_connect("remoteserver", "dbuser", "dbpass");
 
   if (!$con) { print("There is a problem with SQL Server connection."); }  
 
   if (!$con) { print("There is a problem with SQL Server connection."); }  
206

edits

Navigation menu