206
edits
Changes
From SME Server
Jump to navigationJump to search
mLine 3:
Line 3: − + − This HowTo focus on building PHP support for ODBC.+ + + − + − + − + − + Line 58:
Line 60: − + Line 72:
Line 74: − + Line 96:
Line 98: − + Line 106:
Line 108: − + − + + Line 131:
Line 134: − + Line 148:
Line 151: − + Line 192:
Line 195: − + + + Line 206:
Line 211: − + − 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. Line 220:
Line 221: − + + + + + +
update
{{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.
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===
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
====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:
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
====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
====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
====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
====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
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===
Sample php code to check above could be (add standard html code):
Sample php code to check above could be (add standard html code):
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."); }
