Executing Microsoft Sql Server Stored Procedure From Php On Linux

Executing Microsoft SQL Server Stored Procedure from PHP on Linux Learn how to setup, install and configure Apache and PHP on a Linux box to execute MS SQL stored procedures. This extensive article takes you through an in-depth look on how to achieve your ideal environment. Stored procedures are intensively used in MS SQL Server based development today. If we choose Apache + PHP as a web solution, we can execute Microsoft SQL Server stored procedures from PHP by calling these 3 functions: mssql_bind(), mssql_execute(), and mssql_init(). It’s true that we can use these functions when we install PHP on a Windows platform with precompiled PHP binary code. But in most cases, when installed on a Linux platform with FreeTDS, you will receive an error in your PHP script. There isn’t much information in the PHP documentation that can help you solve these problems. Some people might not think that these functions are available on a Linux/Unix platform. In this article, I will show you how to make these 3 functions work successfully on a Linux Platform. Step by step, I’ll explain how to modify the PHP source code, and compile with FreeTDS and Apache. I will also give you all the testing code and necessary screenshots, so you can repeat each step on your own machine.

Executing Microsoft SQL Server Stored Procedure from PHP on Linux - Preparation First, we’ll prepare the source code that we are going to compile in this article. I will be using all of the latest versions of Red Hat Linux , Apache, PHP and FreeTDS. • • • •

Download Red Hat Linux 9.0 from http://www.redhat.com/download/products.html Download Apache Web Server 2.0.47 from http://httpd.Apache.org/download.cgi Download PHP 4.3.3 from http://www.php.net/downloads.php Download FreeTDS 0.61.2 from http://www.ibiblio.org/pub/linux/

Install Red Hat Linux 9.0 with all the necessary development packages (there is a good reason for a development box). After the installation of Red Hat 9.0, you can check or install these packages from “Add/Remove Applications” applet. I prefer that you check all development modules (default selected packages are the numbers showing on the right/all packages). Logon as root, make a folder on your hard drive called public, which you’ll use to save all your downloaded source code. The next step, we will open a terminal window; unzip all these 3 files by issuing the following commands: tar xvfz FreeTDS-0.61.2.tar.gz tar xvfz httpd-2.0.47.tar.gz tar xvfj php-4.3.3.tar.bz2

After you unzip them you can see 3 new folders appearing under your public folder.

Executing Microsoft SQL Server Stored Procedure from PHP on Linux - Installing the Apache Web Server Change directory to httpd-2.0.47 by issuing the following command: cd httpd-2.0.47

To configure your installation tree and modules, issue the following: ./configure --prefix=/usr/local/Apache2 --enable-mods-shared=all --enable-ssl -enable-cache --enable-proxy --enable-suexec

I would like to include all Apache modules on my test environment; you may reduce these modules by using different parameters with the configure command (to understand these parameters, please refer to Apache documentation after installation, or read it online http://httpd.Apache.org/docs-2.1/). It is a good idea to compile and install all the modules; however, at run time, only load the modules your web server needs (how to configure Apache modules at run time, please refer the Apache document with this installation or read it online). Make sure there is no error after you run the above command. Most errors come from your missing some development components when you install Red Hat 9.0, you can install these missing components through the “Add/Remove Applications” applet mentioned at the beginning of the this article, then come back to rerun above command until there are no errors. To compile your module tree, issue the following command: make

This command usually takes a while to finish all the steps (first it will compile all selected modules then link them with libraries). Make sure there are no error messages. (there may be some warning message while compiling, but that’s OK). To install the Apache Web Server, issue: make install

After this step, you can see a new folder /usr/local/Apache2. This is where our Apache Web Server is installed. Testing your new installation and making it start as a service: Before we test the new installation, we need some post-installation setup. To edit the Apache configuration file, issue: gedit /usr/local/apache2/conf/httpd.conf

search for ServerAdmin and ServerName key words, you will see something like this: ServerAdmin [email protected] #ServerName new.host.name:80

Please change it to

ServerAdmin root@dellrh ServerName dellrh:80

dellrh is the name for my machine, you should change it to your machine name, which you supplied when installing Red Hat Linux , then save the configuration file. Close the editor. Start Apache by issuing the following: /usr/local/Apache2/bin/apachectl start

Open your browser, and type http://localhost into the address bar. You will see the Apache Web Server welcome screen. The Apache documentation is also installed; clicking the documentation hyperlink will lead you to the document page.

Executing Microsoft SQL Server Stored Procedure from PHP on Linux - Installing the Apache Web Server, Cont'd In a real production environment, we would like Apache running as a service, meaning that whenever our machine starts-up, these services will startup automatically. Copy and paste the following script and save it as httpd in the /etc/init.d folder. #!/bin/bash # # Startup script for the Apache Web Server # # chkconfig: - 85 15 # description: Apache is a World Wide Web server. # HTML files and CGI. # processname: httpd # pidfile: /var/run/httpd.pid # config: /usr/local/apache2/conf/httpd.conf

It is used to serve \

# Source function library. . /etc/rc.d/init.d/functions

# This will prevent initlog from swallowing up a pass-phrase prompt if # mod_ssl needs a pass-phrase from the user. INITLOG_ARGS="" # Path to the Apachectl script, server binary, and short-form for messages. apachectl=/usr/local/apache2/bin/apachectl httpd=/usr/local/apache2/bin/httpd prog=httpd RETVAL=0

# The semantics of these two functions differ from the way Apachectl does # things -- attempting to start while running is a failure, and shutdown # when not running is also a failure. So we just do it the way init scripts # are expected to behave here. start() { echo -n $"Starting $prog: " daemon $httpd $OPTIONS RETVAL=$?

echo [ $RETVAL = 0 ] && touch /var/lock/subsys/httpd return $RETVAL

} stop() { echo -n $"Stopping $prog: " killproc $httpd RETVAL=$? echo [ $RETVAL = 0 ] && rm -f /var/lock/subsys/httpd /var/run/httpd.pid } reload() { echo -n $"Reloading $prog: " check13 || exit 1 killproc $httpd -HUP RETVAL=$? echo } # See how we were called. case "$1" in start) start ;; stop) stop ;; status) status $httpd RETVAL=$? ;; restart) stop start ;; condrestart) if [ -f /var/run/httpd.pid ] ; then stop start fi ;; reload) reload ;; graceful|help|configtest|fullstatus) $Apachectl $@ RETVAL=$? ;; *) echo $"Usage: $prog {start | stop | restart | condrestart | reload | status | fullstatus | graceful | help | configtest}" exit 1 esac exit $RETVAL

Then issue the following command: chkconfig httpd --level 345 on

This will set Apache to start on Linux level 3 4 and 5 automatically – (by default, Red Hat is running on level 5 with X windows, most production machines will choose level 3, without GUI). Reboot the machine; you can test it in the browser, now Apache web server starts up automatically with the machine reboot.

Executing Microsoft SQL Server Stored Procedure from PHP on Linux - Installing FreeTDS From the /public folder, issue: cd FreeTDS-0.61.2

This will change our working directory to FreeTDS. According to the PHP documentation, if we want to use FreeTDS to connect to a MS SQL Server , we need to append --enable-msdblib to the configure command. For detailed information, you can issue the following command: ./configure --help

This is what I used for the installation of FreeTDS: ./configure --prefix=/usr/local/freetds --sysconfdir=/usr/local/ freetds --withtdsver=7.0 --enable-msdblib --enable-threadsafe --enable-sybase-compat

Since there is less dependency on the operating system , it is easy to pass this step without any error. Next, we compile the module tree, issue the following: make

And install FreeTDS by issuing: make install

After these 2 steps, you will find a new folder /usr/local/freetds, which contains the new files we’ve just installed. Let’s test our installation, change directory to this folder, and issue: cd /usr/local/freetds

Edit freetds.conf: gedit freetds.conf

I have a windows box running MS SQL Server 2000 on it. The machine name is home2k with IP address 192.168.0.212. Append the following part to FreeTDS.conf, and save it: [home2k] host = 192.168.0.212 port =1433 tds version = 7.0

Change to the bin folder under /usr/local/freetds, and issue: tsql –S ‘home2k’ –U ‘sa’ –P ‘’

If you see 1> coming out, that means the connection to MS SQL Server is established, you can test further via sending an SQL statement to the SQL Server like the following screen, then exit using the tsql command.

Executing Microsoft SQL Server Stored Procedure from PHP on Linux - Modify the PHP Source Code We have now come to the critical part in this article. Change directory to php-4.3.3, and issue the following command: cd /public/php-4.3.3

We need to modify some source code before we proceed to compile and install PHP. The reason to do this is: mssql_bind(), mssql_execute(), and mssql_init() do not work with FreeTDS without modification. I have compiled the PHP mssql module source code under the Windows system (Windows 2000/XP). Using Microsoft Visual C++ 6.0 by linking to the MS SQL Server DB library NTWDBLIB.LIB, these 3 functions works just fine under Windows (real functions that connect to MS SQL Server are in NTWDBLIB.DLL, which is installed with Windows and resides in SYSTEM32 folder). On Linux, when we use FreeTDS, libsybdb.so is equivalent to NTWDBLIB.DLL on Windows, and libsybdb.la is equivalent to NTWDBLIB.LIB. I seem to have encountered a small bug in FreeTDS to emulate same RPC API functions as in NTWDBLIB.DLL (these functions are: dbrpcparam, dbrpcexec, dbsqlok; the buggy one here is dbrpcparam in FreeTDS). From the php-4.3.3 folder, we go further down into the folder: ext/mssql, which holds PHP’s mssql module files. Issue the following: cd ext/mssql

Or you can use: cd /public/php-4.3.3/ext/mssql

from any directory on the filesystem. Open the file php_mssql.c in gedit: gedit php_mssql.c

Go to line# 1987, comment this line, as follows: /* datalen=Z_STRLEN_PP(var); */

Add these lines of code: if ((maxlen >0 ) && (maxlen <256)) datalen=maxlen; else { maxlen =255; datalen=255; }

Go to line# 1996, (original line number before adding above code), comment this line, as follows:

/* datalen = -1; */

Add this line: datalen = tds_get_size_by_type(type);

This is the section after the modifications: if ( (type==SQLVARCHAR) || (type==SQLCHAR) || (type==SQLTEXT) ) { /* variablelength type */ if (is_null) { maxlen=0; datalen=0; } else { convert_to_string_ex(var); /*datalen=Z_STRLEN_PP(var); */ if ((maxlen >0 ) && (maxlen <256)) datalen=maxlen; else { maxlen =255; datalen=255; } value=(LPBYTE)Z_STRVAL_PP(var); } } else { /* fixed-length type */ if (is_null) { datalen=0; } else { /*datalen=-1; */ datalen = tds_get_size_by_type(type); } maxlen=-1;

Save the file. Now we can configure the module tree, compile and install PHP.

Executing Microsoft SQL Server Stored Procedure from PHP on Linux - Modify the PHP Source Code, Cont'd According to the PHP documentation, we need to append these options to the configure command: --with-mssql=/usr/local/FreeTDS

--with-apxs2filter =/usr/local/Apache2/bin/apxs

The following is my actual command to configure PHP 4.3.3: ./configure --host=i686-pc-linux-gnu --build=i686-pc-linux-gnu --target=i686redhat-linux-gnu --prefix=/usr/local/php --with-config-file-path=/usr/local/php --enable-force-cgi-redirect --disable-debug --enable-pic --disable-rpath --enableinline-optimization --with-db4 --with-dom=/usr --with-exec-dir=/usr/bin --withfreetype-dir=/usr --with-png-dir=/usr --with-gd --enable-gd-native-ttf --with-ttf --with-gdbm --with-gettext --with-ncurses --with-gmp --with-iconv --with-jpegdir=/usr --with-png --with-regex=system --with-xml --with-expat-dir=/usr --withzlib --with-layout=GNU --enable-bcmath --enable-exif --enable-ftp --enable-magicquotes --enable-safe-mode --enable-sockets --enable-sysvsem --enable-sysvshm --

enable-discard-path --enable-track-vars --enable-trans-sid --enable-wddx --withmssql=/usr/local/FreeTDS --with-pcre-regex --with-mysql=shared --enablememory-limit --enable-bcmath --enable-shmop --enable-versioning --enable-calendar --enable-dbx --enable-dio --enable-mcal --disable-cli --withapxs2filter=/usr/local/Apache2/bin/apxs

If there is no error, we can compile PHP4.3.3 by issuing: make

If there is no error, install PHP 4.3.3 as follows: make install

Check the /usr/local/ folder, you can see the newly installed php folder there. Use an editor to open the Apache configuration file as we did in the beginning of this article: gedit /usr/local/apache2/conf/httpd.conf

Search the following line: LoadModule php4_module modules/libphp4.so After the above line, add the following code: AddType application/x-httpd-php .php

Then save the file. Now issue the following command if you’ve setup Apache as a service: service httpd restart

If you haven’t set it up as a service, you need to restart Apache by issuing the following command: /usr/local/apache2/bin/apachectl restart

Now PHP is ready to work! Let’s test it. Open an editor window by issuing the following command: gedit

Input the following contents:

And save as a new file, called test.php in the /usr/local/apache2/htdocs folder. Now load up your browser and input http://localhost/test.php in the address bar. You will see the PHP information page. Scroll down the screen, you will see a section on Microsoft SQL Server. If you have ever worked with Apache + PHP on Windows, you can compare it with the precompiled PHP that’s installed on Windows. The difference between PHP on Windows and our compiled PHP on Linux is:

On windows, the MS SQL Server support module is running as a DLL file. In order to enable the extension, must uncomment the line from the php.ini file (also set the correct path for extensions): extension=php_mssql.dll

On Linux, the MS SQL Server support module is compiled into libphp4.so. There is no need to load it from extensions, but I still highly recommend you to copy php.ini-recommended from the PHP source code folder into /usr/local/php, and rename it as php.ini. Using php.ini, you can easily control all the parameters for the module as needed, and load third party extension modules in the future.

Executing Microsoft SQL Server Stored Procedure from PHP on Linux - Executing MS SQL Server Procedures from PHP On my Windows box (home2k), I use Microsoft SQL Server query analyzer to create the following stored procedure on pubs database (I will use this stored procedure on Sybase SQL Server with pubs2 database in my next article): CREATE PROC sp_GetBooksByPrice @minPrice money, @maxPrice money, @lowestPricedBook varchar(100) OUTPUT, @highestPricedBook varchar(100) OUTPUT AS DECLARE @realminPrice money, @realmaxPrice money, @totalBooks int SELECT @realminPrice = min(price) FROM titles WHERE price >=@minPrice SELECT @realmaxPrice = max(price) FROM titles WHERE price <=@maxPrice SELECT @lowestPricedBook =title FROM titles WHERE price = @realminPrice SELECT @highestPricedBook =title FROM titles WHERE price = @realmaxPrice SELECT @totalBooks = COUNT(title) FROM titles WHERE price >= @minPrice AND price <= @maxPrice RETURN @totalBooks GO

On the Red Hat side, use an editor to create the following file called sp_test.php:
mssql_bind($proc, mssql_bind($proc, mssql_bind($proc, FALSE,100); mssql_bind($proc, FALSE,100);

"@minPrice", $minPrice, SQLFLT8); "@maxPrice", $maxPrice, SQLFLT8); "@lowestPricedBook", $lowestPricedBook, SQLVARCHAR, TRUE, "@highestPricedBook", $highestPricedBook, SQLVARCHAR, TRUE,

// Bind the return value mssql_bind($proc, "RETVAL", $numBooks, SQLINT2); mssql_execute($proc); mssql_free_statement ($proc); mssql_close($s); echo "

There were $numBooks Books returned.

"; echo "The lowest price book was: $lowestPricedBook.
"; echo "The highest price book was: $highestPricedBook."; ?>

Save the file in /usr/local/Apache2/htdocs, open your browser, and input http://localhost/sp_test.php in address bar. It is useful when you debug your PHP code with MS SQL Server.

Executing Microsoft SQL Server Stored Procedure from PHP on Linux - Conclusion In this article, we discuss how to make Apache, PHP and FreeTDS work together, executing Microsoft SQL Server stored procedures from Red Hat Linux 9.0. Due to a bug in the FreeTDS original source code, mssql_bind(), mssql_execute(), and mssql_init() do not work with the default installation. We give out a workaround to modify the PHP source code so that these functions work successfully. This is also tested for PHP 4.3.2 with Apache 2.0.46, and FreeTDS 0.61.2 on Red Hat 9.0. I would suggest you to try this on Red Hat 8.0 and 7.3. The following is the testing environment I used for this article: The Machines for Red Hat 9.0 Are: Dell Dimension 4500 (2.4 GHz P4, 1 GB RAM) with Apache 2.0.47 + FreeTDS 0.61.2 + PHP 4.3.3 installed IBM ThinkPad T30 Laptop (1.8 GHz P4, 512 MB RAM), with Apache 2.0.46 + FreeTDS 0.61.2 + PHP 4.3.2 installed The Machine for Windows Apache Is: Same laptop as above (dual boot) with Windows XP + Apache 2.0.46 (precompiled binary) + PHP 4.3.2 (precompiled binary) The Machine for MS SQL Server 2000: Compatible PIII 1 GHz, 512 MB RAM with Windows 2000 Server installed. It is really worthy to be mention that you cannot compile Sybase or Sybase_CT module with Microsoft SQL Server Module together without modification. If you decide to use Sybase or Sybase_CT module, mssql_xxx functions are still functional except these 4 functions: mssql_bind(), mssql_execute(), mssql_init() and mssql_free_statment(), because mssql_xxx functions are treated as aliases of corresponding Sybase_xxx.

A workaround to keep these 4 functions is to remove the mssql alias definition in Sybase and Sybase_CT module from the function_entry part in php_sybase_ct.c or php_sybase_db.c files. Recompile PHP with sybase_ct and mssql options. I’ve provided a real sample here (I have Sybase Adaptive Server Enterprise 12.5 developer edition and Oracle Enterprise Server 8.1.7 installed on Red Hat 9.0): ./configure --host=i686-pc-linux-gnu --build=i686-pc-linux-gnu --target=i686redhat-linux-gnu --prefix=/usr/local/php2 --with-config-file-path=/usr/local/php2 --enable-force-cgi-redirect --disable-debug --enable-pic --disable-rpath --enableinline-optimization --with-db4 --with-dom=/usr --with-exec-dir=/usr/bin --withfreetype-dir=/usr --with-png-dir=/usr --with-gd --enable-gd-native-ttf --with-ttf --with-gdbm --with-gettext --with-ncurses --with-gmp --with-iconv --with-jpegdir=/usr --with-openssl --with-png --with-ming --with-regex=system --with-xml -with-expat-dir=/usr --with-zlib --with-layout=GNU --enable-bcmath --enable-exif -enable-ftp --enable-magic-quotes --enable-safe-mode --enable-sockets --enablesysvsem --enable-sysvshm --enable-discard-path --enable-track-vars --enable-transsid --enable-wddx --with-oci8=/home/oracle/product/8.1.7 --enable-sigchild -with-mssql=/usr/local/FreeTDS --with-sybase-ct=/opt/sybase-12.5/OCS-12_5 --withpcre-regex --with-mysql=/usr/local/mysql --with-pgsql=/usr/local/pgsql --withimap=shared --with-imap-ssl --with-kerberos=/usr/kerberos --enable-memory-limit -enable-bcmath --enable-shmop --enable-versioning --enable-calendar --enable-dbx -enable-dio --enable-mcal --disable-cli --withapxs2filter=/usr/local/Apache2/bin/apxs

Repeat the steps in this article to install PHP, and remember to restart Apache. You will see sybase_ct and mssql modules co-exist in PHP from the test.php page on Linux. On Windows, after removing the mssql alias definition in Sybase_CT module, use Visual C++ 6.0 to recompile and make a new php_sybase_ct.dll (you must at least have the Sybase Open Client installed on your Windows machine to access the header files and library files). Then copy php_sybase_ct.dll to your php extensions directory, uncomment the following 2 lines in your php.ini file: extension=php_sybase_ct.dll extension=php_mssql.dll

Restart and you will see sybase_ct and mssql modules co-existing in PHP from test.php on Windows. In my next article, Executing Sybase SQL Server Stored Procedure from PHP, I will discuss how to expand PHP Sybase DB and Sybase CT functions by yourself, adding the exact same functions like: sybase_bind(), sybase_execute(), sybase_init() and sybase_free_statment() into the PHP Sybase and Sybase_CT modules.

Reference Sites, Books and Articles: • • • •

PHP Manual PHP bug report page -- Found helpful information to modify PHP. FreeTDS user guide Apache online documentation