Adding PHP MSSQL support … is it possible ?



  • without blowing the actual PHP 5.2.17 out of the water ?

    Hi all,
    I'm using pfSense 2.0.1 - plain basic vanilla setup using WAn+LAN+OPT1(= Wifi accès for our hotel clients, using a bunch of AP's).
    All is working great- for years now.

    My question is: Our (hotel) PMS system is using a Microsoft SQL 2008 R2 database.
    This database contains tables with names, room numbers etc.

    I'm able to write some SQL scripts so I can use the info from this MSSQL database to grant access to our clients (modifying the existing captive portal PHP code).
    But : how do I integrate Microsoft SQL "PHP commands" the the actual PHP 5.2.17 setup that pfSense is using, so I can connect to the R2 2008 database ?

    I already tried to pkg_add -r ftp://…... /php5-myssql.tbz but this also upgraded the PHP version to 5.3.2 ... and this isn't NOT what pfSense likes ... the box will not survive a reboot anymore.

    Do I need to use FeeTDS + unixodbc ?
    Some one has a walk-through for me ?


  • Rebel Alliance Developer Netgate

    Use php52-mssql and not php5-mssql, and then touch /etc/php_dynamodules/mssql, then /etc/rc.restart_webgui



  • Thank for the follow up.

    Correct, installing
    http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz
    looks better.

    after the
    /etc/php_dynamodules/mssql
    and the
    /etc/rc.restart_webgui
    mssql.so was loaded (visible when I issued a php -m)

    Please note: I had to use this
    /rc.php_ini_setup
    so that the php.ini file was re-generated.
    (from what I understood, "rc.php_ini_setup" will include touched file names from 'php_dynamodules' to the php.ini file)
    Nevertheless
    The function mssql_connect() was still unknown. phpinfo() didn't show any mssql support.

    BUT: the package php52-mssql-5.2.13_3.tbz depends on PHP 5.2.13 …. (and 4 more packages like freetds, libxml,  libiconv and pkg-config - these installed without issues)
    5.2.13 was installed and the cli version of PHP became 5.2.13 - the web server still showed 5.2.17 at that moment (that might explain something), which is the version used by pfSense 2.0.1
    Also, some issues started with the lib pcre.so (it wasn't present anymore) - so function like preg_match_all() became unavailable. And that means: no good.
    Adding the package php52-pcre-5.2.13_3.tbz put that straight, but, as before, pfSense continued functioning, but all web server activity died.

    I'll put this on a hold, and use a second pfSense station to install the dev version.
    Maybe I should 'make' my own PHP …. (hoping that the special libs like pfsense.so will still be accepted ...)


  • Rebel Alliance Developer Netgate

    You might also try:

    pkg_add -rfi http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz

    That should tell it to force the install and not install dependencies, so it won't bring along the other cruft.



  • just for your information
    I find that if you you do a software installation if you reinstall some of pfsense  original software it breaks it ie:freetds, libxml,  libiconv and pkg-config  and others

    your best bet it to step through the installation  and ignore   the one that are already installed

    so do a pkg_info -r http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz

    then install manually  individual packages

    pkg_add -rfi

    skipping out ones that already installed
    which if your not certain which ones they are  can be a bit of  trial and error. and you might break pfsense a couple times before you get it right.

    the easiest way  to do this is I find
    first do this pkg_add -rfi http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz

    touch /etc/php_dynamodules/mssql

    then

    /etc/rc.php_ini_setup

    it will  then tell you what broken dependency you have  install that one
    then again  
    /etc/rc.php_ini_setup

    it list the next broken dependency

    install and repeat

    a short cut to doing it is
    pkg_info -r http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz
    then just copy and paste the  need dependency as they come up,  from this list and adding it to this line
    pkg_add -rfi http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/
    and then adding .tbz to the end
    and enter

    hope this helps you out..



  • Also thanks for your solution, sash99 !

    This is actually when I did.
    I discovered that freetds - libxml -libiconv - pkg-config are pretty standalone packages, so I installed them first.
    Then , adding pkg_info -r http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz doesn't break anything any more.
    Followed to that:
    touch /etc/php_dynamodules/mssql
    and a
    /etc/rc.php_ini_setup
    and even a
    /etc/rc.restart_webgui

    a php -m informed me that the mssql was loaded:

    
    apc
    cgi-fcgi
    ...
    mssql
    ...
    zlib
    zmq
    
    /tmp/PHP_errors.log didn't mention any module errors error.
    
    BUT : using mssql php functions still shows errors like 
    [i]Fatal error: Call to undefined function mssql_connect() in /usr/local/www/aaa.php on line 7 [/i]
    phpinfo() doesn't show any mssql support.
    
    


  • hi gertjan

    perhaps it is a typo or a brain fart as  people sometimes call it
    but  you did not install php52-mssql-5.2.13_3  when  you did this

    pkg_info -r http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz

    it only lists what the software dependencies are

    for this package this is how I would install it.
    you might have to do a clean install twice. or use a second test machine for proofing
    on the proofing machine  do a
    pkg_add -r http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz

    (which will break pfsense.  but that fine  do not worry about it  we are only using it for proofing)

    when you do this it will install all the software and dependencies.  but it will also list the ones that are already installed  make note of which ones they are then on the test machine manually install the packages that are not installed

    now on the test machine do this:  ( i installed it on my machine to proof it for you )

    pkg_add -rfi http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz
    then a ( or vice versa does not mater)
    pkg_info -r http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz
    then a
    /etc/rc.php_ini_setup
    it will say missing dependency  - libsybdb.so ( if you do a google search it tells you it is part of  freetds)
    then do a  ( the package name I grabbed from the pkg_info  used it  for reference)
    pkg_add -rfi http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/freetds-msdblib-0.64_5,1.tbz
    then do a
    /etc/rc.php_ini_setup
    no broken dependencies
    touch /etc/php_dynamodules/mssql
    /etc/rc.php_ini_setup
    exit  
    the shell displays properly  no listing of broken  files  mssql is properly installed

    when i go to my  vhost web server on my pfsense  box and look at the php informational page it  list it up and running

    basically if you follow my method  you should be able to install any php module you want with out breaking pfsense

    good luck  hopefully now it works for you



  • Thanks again for the info.

    Ofcourse, I didn't use "pkg_info -r  …." but "pkg_add -r", but a "pkg_info -r ...." is usefull because it lists this:

    Dependency: pkg-config-0.23_1
    Dependency: libiconv-1.13.1_1
    Dependency: libxml2-2.7.7
    Dependency: php52-5.2.13_3
    Dependency: freetds-msdblib-0.64_5,1

    I "pkg_add -r …" all of these first, except the one that will downgrade the installed version of PHP coming with pfSEnse, which is 5.2.17
    Then I pkg_add -rfi http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz so that the package php52-mssql comes in WITHOUT php52-5.2.13_3

    From what I understood, if I do a
    pkg_add -r http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz
    right away, pkg-config - libiconv - libxml2 - freetds-msdblib and php52-5.2.13_3 will have to get installed manually, which I did.
    No errors like "libsybdb.so is missing", because freetds-msdblib was also installed.
    …..

    I discovered something.
    Using php - the cli version, I had this
    php -i | grep mssql

    
    ## <a name="module_mssql">mssql</a>
    
    mssql.allow_persistentOnOn
    mssql.batchsize00
    mssql.charset_no value__no value_
    mssql.compatability_modeOffOff
    mssql.connect_timeout55
    mssql.datetimeconvertOnOn
    mssql.max_linksUnlimitedUnlimited
    mssql.max_persistentUnlimitedUnlimited
    mssql.max_procsUnlimitedUnlimited
    mssql.min_error_severity1010
    mssql.min_message_severity1010
    mssql.secure_connectionOffOff
    mssql.textlimitServer defaultServer default
    mssql.textsizeServer defaultServer default
    mssql.timeout6060
    
    

    So, mssql support is enabled …. when asking php on the command line (cli version).
    Not when I make a page like
    /phpinfo.php

     phpinfo()
    ?>
    
    

    Info from php  - cli version:

    PHP 5.2.17 with Suhosin-Patch 0.9.7 (cgi-fcgi) (built: Sep 20 2011 10:54:21)
    Copyright © 1997-2009 The PHP Group
    Zend Engine v2.2.0, Copyright (c) 1998-2010 Zend Technologies
       with Suhosin v0.9.27, Copyright (c) 2007, by SektionEins GmbH

    The web version:

    FreeBSD pfsense.brithotelfumel 8.1-RELEASE-p6 FreeBSD 8.1-RELEASE-p6 #0: Mon Dec 12 17:53:00 EST 2011 root@FreeBSD_8.0_pfSense_2.0-snaps.pfsense.org:/usr/obj./usr/pfSensesrc/src/sys/pfSense_SMP.8 i386
    Build Date Sep 20 2011 10:53:14

    So I figure is messed up again ….



  • Humm? I was only going by what you qouted

    @Gertjan:

    This is actually when I did.
    I discovered that freetds - libxml -libiconv - pkg-config are pretty standalone packages, so I installed them first.
    Then , adding pkg_info -r http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz doesn't break anything any more.
    Followed to that:
    touch /etc/php_dynamodules/mssql
    and a
    /etc/rc.php_ini_setup
    and even a
    /etc/rc.restart_webgui

    like I said I installed mssql on my pfsense box it seems to work
    from a clean install of  pfsense on my computer
    i installed
    vhost and bandwidthd ( plus some extra php modules as this is my test machine I pre loaded)

    then i installed your package you wanted on my box by doing this

    pkg_add -rfi http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz
    then
    /etc/rc.php_ini_setup
    it  said missing dependency  - libsybdb.so

    then
    pkg_add -rfi http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/freetds-msdblib-0.64_5,1.tbz

    then
    /etc/rc.php_ini_setup

    no broken dependencies

    touch /etc/php_dynamodules/mssql
    /etc/rc.php_ini_setup

    then once I start vhost and check to see if it working it displays this in the php info
    (but I will admit I have many extra php module install on this test pfsense computer
    gd json, dom, mcrypt, mysql, PDO , pdo mysql, pdo sqlite, and zip. but perhaps it this is why it seems works on my  computer and not yours but i doubt it..)

    mssql

    MSSQL Support enabled
    Active Persistent Links 0
    Active Links 0
    Library version FreeTDS

    Directive Local Value Master Value
    mssql.allow_persistent On On
    mssql.batchsize 0 0
    mssql.charset no value no value
    mssql.compatability_mode Off Off
    mssql.connect_timeout 5 5
    mssql.datetimeconvert On On
    mssql.max_links Unlimited Unlimited
    mssql.max_persistent Unlimited Unlimited
    mssql.max_procs Unlimited Unlimited
    mssql.min_error_severity 10 10
    mssql.min_message_severity 10 10
    mssql.secure_connection Off Off
    mssql.textlimit Server default Server default
    mssql.textsize Server default Server default
    mssql.timeout 60 60

    it seems to work for me, though I have no ms sql  machine to prove that it actually working .



  • but I think I see where are differences  are coming from, you're working within the pfsense  web root folder. and I am working from with in the  pfsense vhost web root folders.  two completely different areas.  perhaps installing them this way does not give pfsense  main web root   the added php modules.  and most likely you will have to modify that region manually.. ( i have no clue where that region lies)

    a couple of method to solve this :

    an easy method would probably be  to create  a vhost  create your webpage in there  and create sys link to the area with in the pfsense  web root folder giving your self permission to what ever you need editing by your web page.

    or deconstruct one of pfsense packages  ie: ntop  and see where they are changing  the php ini  for the pfsense  web root  or simply keep hunting untill you find the php area for pfsense's  web root

    if I was to guess the area you probably need to look at is /usr/local/php5    probably extract the mssql.so from the  tbz and copy it to the  ext folder  and then possible enable the extension  in  one of the php ini or config files in that same folder



  • Big progress ….

    Believe it or not.
    The error is now "Unable to connect to server: 192.168.1.4 in /usr/local/www/aaa.php on line 9 Erreur de connexion  MSSQL"
    which is ok for me.
    This is just a matter of calling mssql_connect() with the right params.

    The thing that made it work: I rebooted .......  ;D

    edit: test results ...
    This is the code: http://paste2.org/p/1938572
    This is the ouput: http://paste2.org/p/1938560  (my SQL server is a Windows 2008 R2 SQL setup)

    pfSense 2.0.1 connects to MSSQL .....

    Thanks you both for the support  :D



  • glad you got it working :)

    took me 6 hrs to figure out how to get php modules to load into pfsense with out breaking it.

    that's why i posted my howto's  get them to install, so other people world not have the same problem that i had..

    also for my "memory back up". because in a couple  months when I need to do it again for some reason I will probably of forgot how to do it..  :P


Log in to reply