Category Archives: Open Source

A simple PHP regular expression tester

February 22, 2012 11:27 am / Leave a Comment /

I love making my own tools for the simple and mundane tasks I face during the day. One of those tasks is the repeated need for testing regular expressions that I am faced with when programming. Yes, there are a multitude of regular expression testers on the web today, and all of them have a laundry list of cool things they do. But none of them are mine, and none of them are necessarily simple. And few of them let you see how they work. So with that, I decided to take one of the many tools I’ve built for myself and offer it here for you.

My (very) simple regular expression is just that… a simple tester that takes a regular expression and a subject string and gives you back your matches as PHP would see them in your application. There is one small bit of robustness (I had to use robustness… the word is just so cool) added to this tester that will allow you to group your matches together (PREG_SET_ORDER) and allow you to include the string position of the match in the subject (PREG_OFFSET_CAPTURE). But that is where the robustness ends the simple kicks in again.

So if you want a simple little regular expression tester, or just want to see how I’ve coded mine, feel free to use my tester for your own needs. Have fun, stay curious and GO BIG.

Posted in: Open Source, PHP, Programming / Tagged: , ,

Building the PHP MS SQL Server extension from source on Ubuntu 8.10

February 18, 2009 7:32 pm / 31 Comments /

Yesterday I wrote about how I got an enhanced Sybase CT extension for PHP built on Ubuntu 8.10 from source files and from Red Hat RPM archives. Today I wanted to write about installing the SQL Server extension from source on Ubuntu 8.10.

I have written about this once before but it was for a Red Hat environment. Seeing as we are converting all of our system at work to Ubuntu I had to do the same thing for the Ubuntu platform. And wouldn’t you know it, Debian and Red Hat are vastly different from each other.

Aside from the stupidity that is the Ubuntu package manager’s rendition of the SQL Server extension for PHP the fact remained for me that I had to be able to integrate database communication from LAMP to a SQL Server on Windows as well to a Sybase server on Unix. The Sybase bit was taken care of in yesterday’s write up. But how do we get PHP to talk to SQL Server on Ubuntu 8.10?

Throw out the notion that you can use the php-sybase package from the package manager. It munges stuff up pretty badly and at the same time does things to the Sybase extension that make it unusable for my needs. Not to mention that if their is a Sybase extension already installed then it really pukes because the php-sybase extension that installs the SQL Server functions actually installs the mssql_* functions then maps the sybase_* functions to them, which makes PHP squawk since those functions are already defined.

So the only way to make this happen is to build the SQL Server extension from source.

Building and installing the mssql extension from source

The follow steps will guide you through how I got the mssql extension built and installed on Ubuntu 8.10:

  • Build the FreeTDS library
    FreeTDS is a free, open source library of clients that allow Linux machines to talk to SQL servers and Sybase servers. Download the latest stable version of the FreeTDS library (as of this writing it was 0.82) and unpack the source to a directory on your machine. Once the source is unpacked, change to the directory where the source is located and configure and make the library:
    $ ./configure --prefix=/usr/local/freetds --enable-msdblib
    $ make
    $ sudo make install

    DO NOT MAKE CLEAN AT THIS POINT.

    Before you do anything else you must make sure that you have copied over all the necessary files from FreeTDS. “Wait, didn’t the installation routine do that?” you might ask. No, it doesn’t, because of a change made on the part of the FreeTDS developers. There are two files that are needed in order to build the mssql extension. Those files are among the make files and must be copied over to the freetds install directory.

    From inside the source directory of freetds, where you built it from, enter:
    $ sudo cp include/tds.h /usr/local/freetds/include
    $ sudo cp src/tds/.libs/libtds.a /usr/local/freetds/lib

  • Get the PHP source code
    Change to a directory where you wouldn’t mind having the entirety of the PHP source stashed. From the command line type:
    $ apt-get source php5
  • Copy the mssql extension files to a directory you can build from
    My preference is to leave source alone and work from copies. So I always copy the files I need to a different location and build from the copies. To that end, I created a directory at ~/SourceCode/php-mssql and copied the following files from the original PHP source directory:
    $ cp ext/mssql/config.m4 ~/SourceCode/php-mssql
    $ cp ext/mssql/php_mssql.c ~/SourceCode/php-mssql
    $ cp ext/mssql/php_mssql.h ~/SourceCode/php-mssql
  • Make and install the extension
    Make sure you have the php5-dev package installed on your system so that you can build PHP extensions from source. From inside the directory where your mssql extension source code is, at the command line, enter:
    $ phpize
    $ ./configure --with-mssql=/usr/local/freetds
    $ make
    $ make install

    Find out where your extensions directory is on your machine and quickly check it to make sure there is a php_mssql.so file living in it. On my machine the extension directory is /usr/lib/php5/20060613+lfs/. Yours may be different.

  • Configure PHP to use the new extension we just made
    Now we need to tell PHP to use the new extension we just built. To do that we need to create an ini file for the extension and put it inside of the extensions directory where PHP can find it. On my machine PHP looks for ini files to parse in /etc/php5/conf.d/ so naturally that is where I am going to go to to tell PHP to use this extension.

    $ cd /etc/php5/conf.d/

    Now we need to create an ini file and put into a directive to load the extension. You can use whatever editor you like. I prefer to use vim:
    $ sudo vim mssql.ini

    Inside this file place the following two lines:
    ; Enable the mssql extension
    extension=mssql.so

  • Configure your environment to load a much needed environment variable whenever the machine starts
    Much like the Sybase extension we did yesterday the mssql extension needs an environment variable in order to function properly. Again, this one caused me fits for a long time in Ubuntu. To be sure you can use the SQL Server extension from both the CLI and the web server you will need to add an environment variable to both the /etc/profile startup script AND the web servers environment variable setting script.

    $ sudo vim /etc/profile

    At the end of the file add:
    export FREETDSCONF=/etc/freetds/freetds.conf

    Now add this same entry into your web server’s environment variables. I am using apache and assuming you are to. If not, consult your web server’s documentation for how to do this:
    $ sudo vim /etc/apache2/envvars

    Now add these entries to the end of the file:
    export FREETDSCONF=/etc/freetds/freetds.conf

  • Configure FreeTDS
    In order for FreeTDS to communicate properly with the SQL server a DSN of sorts needs to be created. To make a DSN you need to edit the freetds.conf file:
    $ sudo vim /etc/freetds/freetds.conf

    Go to the end of the file and add the following lines (MAKE SURE TO INDENT ALL OF THE CONFIG VALUES FOR THE SERVERNAME):
    ;--- Custom MSSQL server name ---
    ; THIS CAN BE ANY NAMED SERVER REFERENCE YOU WANT TO CREATE
    [SERVERNAME]
      ; This is the host name of the MSSQL server
      host=HOSTNAME
      ; This is the port number to use
      port=PORTNUMBER
      ; This is the TDS version to use for anything over Server 2000
      tds version=8.0

    Now we need to add the freetds library to the load library stack.
    $ sudo vi /etc/ld.so.conf

    Go to the end of the file and add the following line:
    /usr/local/freetds/lib

  • Restart your web server
    Like everything that involves a change to the PHP environment or configuration on your machine, restart the web server. I am assuming this is being built upon an apache server. If not, you will need to know how to stop and start your web server or, at the very least, know how to reboot your machine:
    $ sudo apache2ctl stop
    $ sudo apache2ctl start

    Run a PHP info page or CLI call to see if it is loaded:
    $ php -m

    You should now see mssql.

Again, I hope this was helpful to you. If it was, please leave a comment and let me know. It something went haywire for you, leave me a comment, too. Systems being what they are, it is never unheard of for two almost identical systems to have vastly different experiences with building software from source.

Posted in: Geek Stuff, Linux, Open Source, Programming, Web Development / Tagged: , , , ,

Handling multiple Sybase result sets in PHP on Ubuntu 8.10

February 17, 2009 4:50 pm / Leave a Comment /

A long time ago I was tasked with creating a PHP application that would talk to our Sybase database that drives our entire enterprise at work. This is not normally a big thing to do as one can simply enable the Sybase extension for PHP and have a database connection generally within minutes. However there was one thing that was an absolute necessity in this application that made it significantly more difficult to get it working: some parts of the application would be reliant upon stored procedures that returned multiple result sets.

First off let me say that any time you have the option to build stored procedures for your database quieries, do it. They are faster, safer, compiled and centralized and they keep your code from being littered with query builders all over the place. That said, this is neither the time nor the place to talk about the merits of stored procedures in application development. Just know that where I work there are no direct queries ever allowed to touch our database servers.

Knowing that I had to work under the requirement of handling multiple Sybase result sets I set out to find out how to do it and what I found was alarming. Few database extensions in PHP handle multiple result sets. In fact, as I was looking around it seemed that only the MySQLi extension and the SQL Server extension handled multiple result sets. But after a little searching around I found something called the “PHP Sybase CT driver enhancements” project on Sourceforge that essentially provided prepared statement handling and multiple result set handling for the PHP Sybase Extension. However, after trying to install it it became evident that the extension, as it was written, was still not usable in the state it was offered up for download in.

Not to worry, I have modified the package for compiling within a PHP 5 environment and have made a few changes to it that should allow it to be used as is. The source code archive file can be downloaded (in tar.gz format) by clicking here.

Please read the notes on this source code before installing it!

Before installation

Before we get started we need to cover a few assumptions:

  • You have a licensed copy of Sybase Adaptive Server
    I have not tried this against any Sybase database server other than Adaptive server.
  • You have the Sybase client installed or at the very least have the installer available
    For this exercise we are using a Red Hat RPM of the Sybase 12.5 client. Yes, we are installing on Ubuntu, which is essentially Debian and therefore does not use RPM files, but we will burn that bridge when we come to it.
  • You are comfortable building PHP extensions from source
    There is no way around this. In order to get this to work you will need to be able to build from source.
  • You have the php-devel package installed on the machine you building this extension for
    If you don’t have it, a quick sudo apt-get install php5-dev should do the trick. This will be necessary to build the Sybase extension from source.

Installation instructions

Ok, these are the steps I took in order to build the enhanced Sybase extension for PHP on my Ubuntu 8.10 desktop machine starting with Sybase ASE client and common packages in Red Hat RPM format.

  • Convert the Sybase 12.5 OpenClient and Sybase 12.5 Common RPM archives to DEB packages and install them
    To do this I used the alien package converter tool and, while inside the directory where the RPMs were living, issued the following command:
    $ alien -k sybase-common-12.5.0.1de-1.i386.rpm
    $ alien -k sybase-openclient-12.5.0.1esd-1.i386.rpm

    This created two new archives, in the directory I was in, in debian format named

    1. sybase-common-12.5.0.1DE-1_i386.deb
    2. sybase-openclient-12.5.0.1ESD-1_i386.deb

    I then issued the following commands to install them:
    $ sudo dpkg --install sybase-common-12.5.0.1DE-1_i386.deb
    $ sudo dpkg --install sybase-openclient-12.5.0.1ESD-1_i386.deb

    This made a directory named sybase-12.5 in the /opt directory.

  • Prepare your environment to build the extension
    To make things easier (and a little more compatible with the Red Hat way of doing things) I created a link inside of /top called sybase and pointed it to the sybase-12.5 directory.
    $ cd /opt
    $ ln -s -T /opt/sybase-12.5 sybase

    I then had to export some environment vars that are needed by the extension and the web server in order to handle communication with the Sybase server:
    $ export SYBASE=/opt/sybase
    $ export SYBASE_OCS=OCS-12_5
    $ export PATH=/opt/sybase/OCS-12_5/bin:$PATH
    $ export LD_LIBRARY_PATH=/opt/sybase/OCS-12_5/lib:/opt/sybase/OCS-12_5/lib3p:$LD_LIBRARY_PATH
    $ export INCLUDE=/opt/sybase/OCS-12_5/include:$INCLUDE
    $ export LIB=/opt/sybase/OCS-12_5/lib:$LIB

    Yes, I know that each one of these commands could have been placed in a file and sourced, but for some reason source is not available to my installation of Ubuntu so it was in fact easier and faster for me to do it this way. Do this how you will, but remember the values because you will need these later.

  • Build and install the php-sybase-ct extension from source against the Sybase client you just installed
    Remember that package I told you about earlier? The one that I said you could download? If not, get it now and unpack it to a directory somewhere where you have permission to unpack stuff on your system. For me, it was ~/Temp.

    Change to the directory you unpacked the source code to and configure it for make and installation using the php-devel package:
    $ cd ~/Temp
    $ phpize
    $ ./configure --with-sybase-ct=$SYBASE/$SYBASE_OCS
    $ make
    $ sudo make install

    Find out where your extensions directory is on your machine and quickly check it to make sure there is a php_sybase_ct.so file living in it. On my machine the extension directory is /usr/lib/php5/20060613+lfs/. Yours may be different.

    At this point you can safely do a sudo make clean but you might want to hold off on that until it is all working in case you need to rebuild at all. It does happen from time to time. Just sayin’.

  • Configure PHP to use the new extension
    Now we need to tell PHP to use the new extension we just built. To do that we need to create an ini file for the extension and put it inside of the extensions directory where PHP can find it. On my machine PHP looks for ini files to parse in /etc/php5/conf.d/ so naturally that is where I am going to go to to tell PHP to use this extension.

    $ cd /etc/php5/conf.d/

    Now we need to create an ini file and put into a directive to load the extension. You can use whatever editor you like. I prefer to use vim:
    $ sudo vim sybase_ct.ini

    Inside this file place the following two lines:
    ; Enable the sybase extension
    extension=sybase_ct.so

  • Configure your environment to load the correct environment variables whenever the machine starts
    This one caused me fits for a long time in Ubuntu. In order to ensure that you can use the Sybase extension from both the CLI and the web server you will need to take all of the environment variables that exported prior to building the extension and place them in both the /etc/profile startup script AND the web servers environment variable setting script. This caused countless hours of frustration and anger for me and I hope I can save you some angst with this little snippet.

    Add the environment vars to /etc/profile. You can use any editor you like. I like vim:
    $ sudo vim /etc/profile

    At the end of the file add the environment vars:
    export SYBASE=/opt/sybase
    export SYBASE_OCS=OCS-12_5
    export PATH=/opt/sybase/OCS-12_5/bin:$PATH
    export LD_LIBRARY_PATH=/opt/sybase/OCS-12_5/lib:/opt/sybase/OCS-12_5/lib3p:$LD_LIBRARY_PATH
    export INCLUDE=/opt/sybase/OCS-12_5/include:$INCLUDE
    export LIB=/opt/sybase/OCS-12_5/lib:$LIB

    Now add these same entries into your web server’s environment variables. I am using apache and assuming you are to. If not, consult your web server’s documentation for how to do this:
    $ sudo vim /etc/apache2/envvars

    Now add these entries to the end of the file:
    export SYBASE=/opt/sybase
    export SYBASE_OCS=OCS-12_5
    export PATH=/opt/sybase/OCS-12_5/bin:$PATH
    export LD_LIBRARY_PATH=/opt/sybase/OCS-12_5/lib:/opt/sybase/OCS-12_5/lib3p:$LD_LIBRARY_PATH
    export INCLUDE=/opt/sybase/OCS-12_5/include:$INCLUDE
    export LIB=/opt/sybase/OCS-12_5/lib:$LIB

  • Restart your web server
    Like everything that involves a change to the PHP environment or configuration on your machine, restart the web server. I am assuming this is being built upon an apache server. If not, you will need to know how to stop and start your web server or, at the very least, know how to reboot your machine:
    $ sudo apache2ctl stop
    $ sudo apache2ctl start

    Run a PHP info page or CLI call to see if it is loaded:
    $ php -m

    You should see sybase_ct. If not, something went wrong. If so, you are now golden.

At this point you should be able to run queries against your Sybase server AND handle multiple result sets using the sybase_next_result() function. There is no documentation for this function, but a quick read through of the mssql_next_result() function will tell pretty well how to use it.

Enjoy! And if this was at all helpful please leave a comment to let me know.

Notes on the PHP Sybase CT Enhanced extension

Some things to keep in mind when using this extension:

  • This extension is coded in PHP 4 source.
  • When I got my hands on it the code was still much in development phase. Not much has changed about that other than I removed debug output from it.
  • It does not handle connection failure well when the server does not respond. So unwell in fact that internally it causes segmentation faults and results in blank web pages upon failure. I do not know how to fix that.
  • If you know how to program in c or want to make it better, please feel free to do so. I have contacted the original authors of this extension and of the three only one has done any real work on it and none have worked on it in the last five years and really do not intend to. I was given permission to distribute it and modify it. But I really do not know what I am doing in c. Yet. ;)
Posted in: Geek Stuff, LAMP, Open Source, PHP, Web Development / Tagged: , , , , ,

I hate open source

February 11, 2009 5:05 pm / 4 Comments /

Actually, I should say “I hate open source as only someone that has used open source software could hate it.”

Today I spent the better part of the day trying to get my newest installation of Ubuntu 8.10 to talk to our Sybase and MS SQL Servers from PHP. Not only that, but I need to be able to handle multiple result sets from Sybase and I cannot use PDO since it has never, ever worked correctly when trying to connect to Sybase using the native drivers. And there is no way in hell I am using ODBC to connect to our Sybase servers (why should I have to when I can use native Sybase connectivity?).

So with this in mind I set out to tap into the ease of use of Ubuntu as an operating system and installed php-sybase using the Synaptic Package Manager. That was my first, and probably biggest mistake in the whole of this ordeal. Apparently the package maintainers for Ubuntu decided that when installing the php-sybase extension they would actually install the php-mssql extension and map all the sybase function names to the mssql function names. That is just stupid. Here is why:

The common functions between php-mssql and php-sybase in the Ubuntu package
Function purpose sybase_* mssql_*
Based upon the PHP manual as of February 11, 2009
connect sybase_connect mssql_connect
persistent connect sybase_pconnect mssql_pconnect
select database sybase_select_db mssql_select_db
query sybase_query mssql_query
result (cell) sybase_result mssql_result
result (row – numeric index) sybase_fetch_row mssql_fetch_row
result (row – associative index) sybase_fetch_assoc mssql_fetch_assoc
result (row – both index) sybase_fetch_array mssql_fetch_array
result (row – object) sybase_fetch_object mssql_fetch_object
result (row count – data sets) sybase_num_rows mssql_num_rows
result (row count – affected rows) sybase_affected_rows mssql_rows_affected
result (row pointer mover) sybase_data_seek mssql_data_seek
result (field count) sybase_num_fields mssql_num_fields
result (field info) sybase_fetch_field mssql_fetch_field
result (field pointer mover) sybase_field_seek mssql_field_seek
result (remove from memory) sybase_free_result mssql_free_result
errors sybase_get_last_message mssql_get_last_message
close sybase_close mssql_close
setting (message severity) sybase_min_message_severity mssql_min_message_severity
setting (error severity) sybase_min_error_severity mssql_min_error_severity

You can see in this common table that almost all of the mssql_* functions that share a common purpose with the sybase_* functions are the same, save for one glaring exception (rows_affected? Seriously?). The problem lies in the fact that there are a ton of mssql_* functions that do not have a matching function in the sybase_* family of functions according to the manual. Those functions are:

  • mssql_init
  • mssql_bind
  • mssql_execute
  • mssql_next_result
  • mssql_field_length
  • mssql_field_name
  • mssql_field_type
  • mssql_free_statement
  • mssql_fetch_batch
  • mssql_guid_string

Throw into this mix that there are a few sybase_* functions that do not have a corresponding mssql_* function, such as:

  • sybase_deadlock_retry_count
  • sybase_min_client_severity
  • sybase_min_server_severity
  • sybase_set_message_handler
  • sybase_unbuffered_query

And you can see that building the Sybase extension from the SQL Server extension will not work under normal circumstances if you have your code built around the functions in the manual. So… it was back to the drawing board. And this drawing board is way different than the last one I remember (think back to building the sybase and mssql extension for PHP in Fedora).

What this ended up boiling down to was that I had to compile a PHP 4 hacked version of the Sybase extension source code that offered handling of multiple result sets. Not that it was that entirely stupid to do, except for the fact that the client package I needed to compile against was in Red Hat RPM format. But thanks to the Alien package converter tool I was able to install the Sybase client on Ubuntu with little headache.

Building the Sybase extension was not that big of a deal either once the client was installed. But getting the SQL Server extension built has been a pain in the rump still yet.

The first thing I had to do was build the FreeTDS package from source since the Ubuntu pacakge installer installs the freetds libraries somewhere but they are really nowhere to be found. And since you need to build them in a way that allows for dblib use I had to build from source with build flags. So I got the freetds package installed, after a few tries, and then went to build the sql server extension from source.

And it looked like it worked. But when I restarted Apache I realized it hadn’t worked. You know why? Because the stupid source code for the mssql extension aliases all the sybase extensions WITHOUT checking to see if Sybase is already compiled. Whiskey Tango Foxtrot?

So I had to comment out the aliasing part of the extension source code that rewrites the sybase extension and compile again. And this time it worked. Except for some reason, even though I am editing the freetds.conf file the server I am trying to connect to is not resolving properly. And I have no idea why.

So here I am, pissed off at open source, Linux, Ubuntu, PHP, penguins and all things cute and cuddly. I know I will get it to work. And I know when I do I will be making sounds in my cubicle that will make my coworkers hand me tissues because they will think I need to clean up after myself. But for now, I hate open source. But only as once who spends a lot of time in it.

Posted in: Linux, Open Source, Personal Messages, Rants / Tagged: , , ,