Changes

From SME Server

ODBC

711 bytes added, 16:47, 20 June 2009
m
update
{{Level|medium}}
===Description===
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 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 ODBCdetails.
===Used packages===
'''unixODBC''' - is an API that provides a common interface to many different databasesexept Sybase and SQL Server. [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 serversusing PHP.
'''freetds''' - a re-implementation of the Tabular Data Stream protocol made by adding drivers för Sybase and Microsoftusing 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 with libraries for these languages. [http://gcc.gnu.org/ GCC official site]
===Installation===
cd freetds-0.82
./configure --sysconfdir=/etc --prefix=/usr --enable-msdblib --with-tdsver=8.0
make
====Test with tsql====
TSQL is a simple database client included in FreeTDS package for testing and troubleshooting. "man tsql" give more info.
Check compile settings:
locale is "sv_SE"
locale charset is "ISO-8859-1"
Msg 18456, Level 14, State 1, Server XP3BXP3C\SQLEXPRESS, Line 1
Login failed for user 'dbuser'.
Msg 2002, Level 9, State -1, Server OpenClient, Line -1
====freetds.conf====
This file is provided by FreeTDS and contains the settings that tell freetds about each server it will connect to. <br>Host should be a FQDN (Fully Qualified Domain Name).<br>Make directory to hold template fragments to be included in the original path: final file located in /etc/freetds.conf
mkdir -p /etc/e-smith/templates-custom/etc/freetds.conf
====odbcinst.ini====
This file is provided by unixODB and tells unixODBC about the FreeTDS driver.
mkdir -p /etc/e-smith/templates-custom/etc/odbcinst.ini
====odbc.ini====
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
====isql====
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 the command "isql -v DSN USERNAME PASSWORD " e.g.:
isql -v remoteserver dbuser dbpass
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 REMOTESERVER is not the same as xp3cremoteserver!
===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):
and
phpinfo();
Section Configure Command, should contain: --with-unixODBC=shared,/usrThere should also be a "odbc"-section further down. So far the php-functionality have been provided by unixODB andphp-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");
if (!$con) { print("There is a problem with SQL Server connection."); }
206
edits

Navigation menu