320 lines
9.3 KiB
Plaintext
320 lines
9.3 KiB
Plaintext
|
Connecting to MS SQL 6.x+ via Openlink/PHP/ODBC mini-HOWTO
|
|||
|
Zili Zhang, silen@silen.net
|
|||
|
3.0, 1999-07-15
|
|||
|
|
|||
|
How to connect to MS SQL 6.x+ database server via ODBC functions of
|
|||
|
PHP3(3.0.1x or above) compiled with Openlink drivers under Linux.
|
|||
|
______________________________________________________________________
|
|||
|
|
|||
|
Table of Contents
|
|||
|
|
|||
|
|
|||
|
1. Introduction
|
|||
|
|
|||
|
1.1 Copyright
|
|||
|
1.2 Disclaimer
|
|||
|
|
|||
|
2. Openlink
|
|||
|
|
|||
|
2.1 On the Client
|
|||
|
2.2 On the Server
|
|||
|
2.3 Configuration Before Test
|
|||
|
2.4 Text with
|
|||
|
|
|||
|
3. PHP
|
|||
|
|
|||
|
4. Example
|
|||
|
|
|||
|
5. FAQ
|
|||
|
|
|||
|
|
|||
|
|
|||
|
______________________________________________________________________
|
|||
|
|
|||
|
1. Introduction
|
|||
|
|
|||
|
This document describes how to connect to MS SQL 6.x+ database server
|
|||
|
via ODBC functions of PHP3(3.0.1x or above) compiled with Openlink
|
|||
|
drivers under Linux.
|
|||
|
|
|||
|
In the Unix world, people may hate to be invoked with any M$ related
|
|||
|
software. However, in the real world, the boss may require you to
|
|||
|
store data in a MS SQL database on NT and run web application on
|
|||
|
Linux. What to do? Quit the job or sit down to read this document? If
|
|||
|
you choose latter, I will give you a detailed installation guide so
|
|||
|
that you can keep the job for a while. It is a How-to guide, not a
|
|||
|
Why-so guide. So don't ask me why abc instead of cba. I don't know
|
|||
|
either.
|
|||
|
|
|||
|
PHP is becoming more and more popular in web programmers, mainly
|
|||
|
because it can be configured to connect to various databases like
|
|||
|
Oracle, MySQL, Solid and so on. But for a MS SQL server, the problem
|
|||
|
is different. Though you can use PHP's Sybase-ct support features to
|
|||
|
directly connect to MSSQL, many people (at least me) prefer to connect
|
|||
|
via ODBC.
|
|||
|
|
|||
|
Utilizing Openlink ODBC middleware, you can use those ODBC_xxxx
|
|||
|
functions to connect to all the database Openlink has a driver for.
|
|||
|
You have to install Openlink Linux client and Server middleware and
|
|||
|
re-compile PHP to support ODBC functions. In the end, I made a script
|
|||
|
sample for reference.
|
|||
|
|
|||
|
|
|||
|
1.1. Copyright
|
|||
|
|
|||
|
Copyright (c) 1999 by Zili Zhang
|
|||
|
|
|||
|
Please freely copy and distribute (sell or give away) this document in
|
|||
|
any format. It's requested that corrections and/or comments be
|
|||
|
forwarded to the document maintainer. You may create a derivative work
|
|||
|
and distribute it provided that you:
|
|||
|
|
|||
|
|
|||
|
<20> Send your derivative work (in the most suitable format such as
|
|||
|
sgml) to the LDP (Linux Documentation Project) or the like for
|
|||
|
posting on the Internet. If not the LDP, then let the LDP know
|
|||
|
where it is available.
|
|||
|
|
|||
|
<20> License the derivative work with this same license or use GPL.
|
|||
|
Include a copyright notice and at least a pointer to the license
|
|||
|
used.
|
|||
|
|
|||
|
<20> Give due credit to previous authors and major contributors.
|
|||
|
|
|||
|
|
|||
|
If you're considering making a derived work other than a translation,
|
|||
|
it's requested that you discuss your plans with the current
|
|||
|
maintainer.
|
|||
|
|
|||
|
|
|||
|
1.2. Disclaimer
|
|||
|
|
|||
|
The following document is offered in good faith as comprising only
|
|||
|
safe configuration and procedures. No responsibility is accepted by
|
|||
|
the author for any loss or damage caused in any way to any person or
|
|||
|
equipment, as a direct or indirect consequence of following these
|
|||
|
instructions.
|
|||
|
|
|||
|
This document was derived from the plain text entry found on
|
|||
|
http://www.silen.net/openlink-php-odbc.txt
|
|||
|
<http://www.silen.net/openlink-php-odbc.txt>.
|
|||
|
|
|||
|
|
|||
|
2. Openlink
|
|||
|
|
|||
|
This step is a little bit complicated. You have to done works on both
|
|||
|
sides of your Linux client machine and NT server.
|
|||
|
|
|||
|
|
|||
|
2.1. On the Client
|
|||
|
|
|||
|
|
|||
|
<20> From ftp://www.openlinksw.com/ <ftp://www.openlinksw.com/>,
|
|||
|
download install.sh and likoxglc.taz (for a libc6 system) or
|
|||
|
likoxxxx.taz (for a libc5 system).
|
|||
|
|
|||
|
<20> mkdir /usr/local/openlink
|
|||
|
|
|||
|
<20> copy install.sh and likoxglc.taz into /usr/local/openlink
|
|||
|
|
|||
|
<20> cd /usr/local/openlink
|
|||
|
|
|||
|
<20> sh install.sh, the install script will ask you the owner and group
|
|||
|
of the program. It will extract things to odbcsdk directory under
|
|||
|
/usr/local/openlink and copy a .odbc.ini into the owner's home
|
|||
|
directory.
|
|||
|
|
|||
|
|
|||
|
2.2. On the Server
|
|||
|
|
|||
|
|
|||
|
<20> From ftp://www.openlinksw.com/ <ftp://www.openlinksw.com/>,
|
|||
|
download ntadm65x.zip onto your NT server.
|
|||
|
<20> unzip ntadm65x.zip
|
|||
|
|
|||
|
<20> cd disk1 - directory of where you unzip the package.
|
|||
|
|
|||
|
<20> execute setup and follow the instructions to install the Openlink
|
|||
|
middleware.
|
|||
|
|
|||
|
<20> remember to start Openlink request broker from start menu or
|
|||
|
service control panel.
|
|||
|
|
|||
|
|
|||
|
2.3. Configuration Before Test
|
|||
|
|
|||
|
|
|||
|
<20> keep the .odbc.ini file in your home directory.
|
|||
|
|
|||
|
<20> copy the udbc.ini from the bin directory of Openlink middleware
|
|||
|
install directory to the /etc directory of client.
|
|||
|
|
|||
|
<20> customize /etc/udbc.ini. In [dsn_sql6] section, change the host,
|
|||
|
database, username and password entries to fit your server
|
|||
|
settings. Here is part of my /etc/udbc.ini:
|
|||
|
|
|||
|
|
|||
|
[dsn_sql6]
|
|||
|
Host = 10.0.0.1
|
|||
|
ServerType = sql6
|
|||
|
;ServerOptions =
|
|||
|
Database = pubs
|
|||
|
;FetchBufferSize = 30
|
|||
|
UserName = sa
|
|||
|
Password = xxxxxxx
|
|||
|
|
|||
|
|
|||
|
|
|||
|
<20> add to your environment
|
|||
|
LD_LIBRARY_PATH='/usr/local/openlink/odbcsdk/lib' and export it. In
|
|||
|
csh shell, type: setenv LD_LIBRARY_PATH
|
|||
|
/usr/local/openlink/odbcsdk/lib
|
|||
|
|
|||
|
|
|||
|
2.4. Text with odbctest
|
|||
|
|
|||
|
|
|||
|
<20> cd /usr/local/openlink/odbcsdk/examples
|
|||
|
|
|||
|
<20> ./odbctest
|
|||
|
|
|||
|
<20> type: dsn=dsn_sql6
|
|||
|
|
|||
|
<20> when 'sql>' appears, you can execute your sql clauses to test the
|
|||
|
connection.
|
|||
|
|
|||
|
|
|||
|
|
|||
|
3. PHP
|
|||
|
|
|||
|
Now we have Openlink and can go to PHP compilation. Note for this
|
|||
|
version, PHP 3.0.10 or above is required to make things work.
|
|||
|
|
|||
|
<20> From http://www.php.net <http://www.php.net/>, download
|
|||
|
php-3.0.11.tar.gz
|
|||
|
|
|||
|
<20> Perform the following steps:
|
|||
|
|
|||
|
gzip -dc php-3.0.11.tar.gz|tar -xof -
|
|||
|
|
|||
|
cd php-3.0.11
|
|||
|
|
|||
|
./configure --with-openlink (--with-mysql --with-gd=/usr/local/gd1.3 --enable-track-vars)
|
|||
|
|
|||
|
NOTE: My configuration is to run PHP as CGI mode, support mysql,
|
|||
|
as well. Your configuration may be different.
|
|||
|
|
|||
|
make --silent
|
|||
|
|
|||
|
NOTE: Don't mind if there are warning messages.
|
|||
|
|
|||
|
make install
|
|||
|
|
|||
|
|
|||
|
|
|||
|
These will install php executable into /usr/local/bin. Before you
|
|||
|
execute php, copy the library files under /usr/local/open<65>
|
|||
|
link/odbcsdk/lib into /usr/lib to make it easier for php to find open<65>
|
|||
|
link libraries (I know there are better methods).
|
|||
|
|
|||
|
|
|||
|
|
|||
|
4. Example
|
|||
|
|
|||
|
Please refers to PHP manual for ODBC functions. Here is my example
|
|||
|
code odbc.php3: (Note that odbc_num_rows() will not return anything,
|
|||
|
so you must repeately call odbc_fetch_row() to get that number.)
|
|||
|
|
|||
|
|
|||
|
|
|||
|
<?
|
|||
|
/* some environment variables, you can test to comment them out to see
|
|||
|
* if things still work.
|
|||
|
*/
|
|||
|
putenv("LD_LIBRARY_PATH=/usr/local/openlink/odbcsdk/lib");
|
|||
|
putenv("UDBCINI=/etc/udbc.ini");
|
|||
|
putenv("ODBCINI=/root/.odbc.ini");
|
|||
|
putenv("DebugFile=/tmp/udbc.out"); // debug trace output
|
|||
|
|
|||
|
$dsn="DSN=dsn_main"; // note 'DSN=' is required
|
|||
|
$user="sa";
|
|||
|
$password="xxxxxxx";
|
|||
|
|
|||
|
$sql="SELECT * FROM titles";
|
|||
|
|
|||
|
/* directly execute mode */
|
|||
|
if ($conn_id=odbc_connect("$dsn",$user,$password)){
|
|||
|
echo "connected to DSN: $dsn<br><br>";
|
|||
|
if($result_id=odbc_do($conn_id, $sql)) {
|
|||
|
echo "executing '$sql'<br><br>";
|
|||
|
$num_fields=odbc_num_fields($result_id);
|
|||
|
if($num_fields>0){
|
|||
|
echo "Number of fields:
|
|||
|
$num_fields<br>";
|
|||
|
for($i=1;$i<=$num_fields;$i++){
|
|||
|
|
|||
|
$field_name[$i-1]=odbc_field_name($result_id,$i);
|
|||
|
}
|
|||
|
$num_rows=0;
|
|||
|
while(odbc_fetch_row($result_id)){
|
|||
|
for($i=1;$i<=$num_fields;$i++){
|
|||
|
|
|||
|
$result[$num_rows][$field_name[$i-1]]=odbc_result($result_id,$i);
|
|||
|
}
|
|||
|
$num_rows++;
|
|||
|
}
|
|||
|
echo "Number of rows: $num_rows<br>";
|
|||
|
}else{
|
|||
|
echo "not a field returned. <br><br>";
|
|||
|
}
|
|||
|
echo "Results:<br>";
|
|||
|
for($i=0;$i<sizeof($result);$i++){
|
|||
|
while(list($key,$value)=each($result[$i])){
|
|||
|
echo "$i:$key=$value<br>";
|
|||
|
}
|
|||
|
}
|
|||
|
echo "freeing result<br><br>";
|
|||
|
odbc_free_result($result_id);
|
|||
|
}else{
|
|||
|
echo "can not execute '$sql'<BR><BR>";
|
|||
|
}
|
|||
|
echo "closing connection $conn_id";
|
|||
|
odbc_close($conn_id);
|
|||
|
}else{
|
|||
|
echo "can not connect to DSN: $dsn<br><br>";
|
|||
|
}
|
|||
|
?>
|
|||
|
|
|||
|
|
|||
|
|
|||
|
5. FAQ
|
|||
|
|
|||
|
|
|||
|
|
|||
|
<20> Does this exact procedure work for MSSQL 7.0?
|
|||
|
|
|||
|
Yes, Openlink will upgrade their software to 7.0, but I don't know
|
|||
|
when.
|
|||
|
|
|||
|
|
|||
|
|
|||
|
<20> I followed this document step by step to install the openlink
|
|||
|
drivers but I stopped at the "install ntadm65x.zip" step. After I
|
|||
|
installed ntadm65x I tried to start the OpenLink Request Broker and
|
|||
|
got the error:
|
|||
|
|
|||
|
|
|||
|
|
|||
|
unable to open the service control manager <5>
|
|||
|
press RETURN to exit oplrqb
|
|||
|
|
|||
|
|
|||
|
|
|||
|
Could you please tell me how I can fix that problem?
|
|||
|
|
|||
|
This should be a problem with NT itself. try restart the computer and
|
|||
|
then start openlink service from Control Panel/service manager.
|
|||
|
|
|||
|
|
|||
|
|