<!doctype linuxdoc system>

<!--
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Changelog:

  v3.0
     Convert to linuxdoc sgml (gferg(at)metalb.unc.edu)

  v2.0b
     A. iODBC is not required to make things work.
     B. No missing *.h files, so do not need to modify unified_odbc.h of PHP.
     C. Example code changes, discarding odbc_num_rows() function.
     D. Explicit announce support for MS SQL 7.0.
     E. FAQ section added.

 -->

<article>

<title>Connecting to MS SQL 6.x+ via Openlink/PHP/ODBC mini-HOWTO

<author>Zili Zhang, <tt/silen@silen.net/
<date>3.0, 1999-07-15

<abstract>
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.
</abstract>

<!-- Table of contents -->
<toc>

<!-- Begin the document -->

<sect>Introduction
<p>
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.

<sect1>Copyright
<p>
Copyright (c) 1999 by Zili Zhang
<P>
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:

<itemize>
<item>
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.
<item>
License the derivative work with this same license or use GPL.
Include a copyright notice and at least a pointer to the license used.
<item>
Give due credit to previous authors and major contributors.
</itemize>

<P>
If you're considering making a derived work other than a translation,
it's requested that you discuss your plans with the current maintainer.

<sect1>Disclaimer
<p>
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
<url url="http://www.silen.net/openlink-php-odbc.txt"
    name="http://www.silen.net/openlink-php-odbc.txt">.

<sect>Openlink
<p>
This step is a little bit complicated. You have to done works on both
sides of your Linux client machine and NT server.

<sect1>On the Client
<p>
<itemize>
<item>
From <url url="ftp://www.openlinksw.com/" name="ftp://www.openlinksw.com/">, 
download <tt>install.sh</tt> and <tt>likoxglc.taz</tt>
(for a libc6 system) or <tt>likoxxxx.taz</tt> (for a libc5 system).
</item>
<item><tt>mkdir /usr/local/openlink</tt></item>
<item>copy <tt>install.sh</tt> and <tt>likoxglc.taz</tt> 
into <tt>/usr/local/openlink</tt>
</item>
<item><tt>cd /usr/local/openlink</tt></item>
<item>
<tt>sh install.sh</tt>, the install script will ask you the owner and group of
the program. It will extract things to
<tt>odbcsdk</tt> directory under <tt>/usr/local/openlink</tt>
and copy a <tt>.odbc.ini</tt> into the owner's home directory.
</item>
</itemize>

<sect1>On the Server
<p>
<itemize>
<item>
From <url url="ftp://www.openlinksw.com/" name="ftp://www.openlinksw.com/">, 
download <tt>ntadm65x.zip</tt> onto your NT server.
</item>
<item><tt>unzip ntadm65x.zip</tt></item>
<item><tt>cd disk1</tt> - directory of where you unzip the package.</item>
<item>execute <tt>setup</tt> and follow the instructions to install the Openlink
middleware.</item>
<item>remember to start Openlink request broker from start menu or service
control panel.</item>
</itemize>

<sect1>Configuration Before Test
<p>
<itemize>
<item>
keep the <tt>.odbc.ini</tt> file in your home directory.
</item>
<item>copy the <tt>udbc.ini</tt> from the bin directory of Openlink middleware
install directory to the <tt>/etc</tt> directory of client.
</item>
<item>customize <tt>/etc/udbc.ini</tt>. In &lsqb;dsn_sql6&rsqb; section, change the 
host, database, username and password entries to fit your server settings.
Here is part of my <tt>/etc/udbc.ini</tt>:
<tscreen><verb>
  &lsqb;dsn_sql6&rsqb;
  Host            = 10.0.0.1
  ServerType      = sql6
  ;ServerOptions  =
  Database        = pubs
  ;FetchBufferSize = 30
  UserName        = sa
  Password        = xxxxxxx

</verb></tscreen>
</item>
<item>add to your environment <tt>LD_LIBRARY_PATH='/usr/local/openlink/odbcsdk/lib'</tt>
and export it. In csh shell, type: 
<tt>setenv LD_LIBRARY_PATH /usr/local/openlink/odbcsdk/lib</tt></item>
</itemize>

<sect1>Text with <tt>odbctest</tt>
<p>
<itemize>
<item><tt>cd /usr/local/openlink/odbcsdk/examples</tt></item>
<item><tt>./odbctest</tt></item>
<item>type: <tt>dsn=dsn_sql6</tt></item>
<item>when 'sql&gt;' appears, you can execute your sql clauses to test the
connection.</item>
</itemize>


<sect>PHP
<p>
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.
<itemize>
<item>From <url url="http://www.php.net/" name="http://www.php.net">, download
<tt>php-3.0.11.tar.gz</tt></item>
<item> Perform the following steps:
<tscreen><verb>
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

</verb></tscreen>
These will install php executable into <tt>/usr/local/bin</tt>.
Before you execute php, copy the library files under
<tt>/usr/local/openlink/odbcsdk/lib</tt> into <tt>/usr/lib</tt> to
make it easier for php to find openlink libraries (I know there are
better methods).
</item>
</itemize>


<sect>Example
<p>
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.)
<tscreen><verb>
&lt;?
/* 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>";
}
?&gt;

</verb></tscreen>

<sect>FAQ
<p>

<itemize>
<item><em>Does this exact procedure work for MSSQL 7.0?</em>
<p>
Yes, Openlink will upgrade their software to 7.0, but I don't know when.
<p>
&nbsp;
</item>

<item><em>I followed this document step by step to install
the openlink drivers but I stopped at the &quot;install ntadm65x.zip&quot;
step. After I installed ntadm65x I tried to start the OpenLink Request Broker
and got the error:</em>

<tscreen><verb>
 unable to open the service control manager &lt;5&gt;
 press RETURN to exit oplrqb

</verb></tscreen>
<em>Could you please tell me how I can fix that problem?</em>
<p>
This should be a problem with NT itself. try restart the computer
and then start openlink service from Control Panel/service manager.
</item>
</itemize>

</article>

