Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
PHP MSSQL HOWTO
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks
View previous topic :: View next topic  
Author Message
KsE
Apprentice
Apprentice


Joined: 18 Mar 2003
Posts: 269
Location: St. Cloud, MN

PostPosted: Tue Jul 13, 2004 8:23 pm    Post subject: PHP MSSQL HOWTO Reply with quote

This will explain how to setup php to work with mssql.

I decided to write this 'cause I have had many problems with this and haven't seen anything in the forums about it.

First you need freetds installed.

Code:

# USE="mssql" emerge freetds


Freetds is the connector between linux and mssql.

Now you need to make some config changes in freetds.conf

Code:

# nano -w /etc/freetds.conf


Now add this to the bottom.

Code:

[SQLserver]                     #the name of the sql server
    host = 192.168.1.100    #the ip or hostname of the sql server
    port = 1433                  #the port the server runs on
    tds version = 8.0          # the version of mssql you are using, 8.0 = 2000


The name you put in for the server is the host you will use in php: mssql_connect("SQLserver", "user", "pass")

Now you need to install php.

Code:

# USE="freetds" emerge php


For some reason when you have USE="mssql" it doesn't compile right and you get errors everytime you use php (even php -v). It seems to work just fine without it.

Now for a test.

Code:

# nano -w test.php

#!/usr/bin/php -q
<?

mssql_connect("SQLserver", "user", "pass");
mssql_select_db("dbName");

$sql = "select count(1) from table";
$result = mssql_query($sql);
$row = mssql_fetch_array($result);

print $row[0]."\n";

?>


This should print the number of rows in table.

Now, you need to make a change on the sql server to execute heterogenious queries (cross-server), because freetds doesn't set up the connection like it should (I hate freetds).

Open Enterprise Manager and right click on the server connection. Click the "connection" tab. Check the ANSI_NULLS and ANSI_WARNINGS boxes. Click ok.

This sets ANSI_NULLS and ANSI_WARNINGS on by default. Otherwise you need do $sql = "SET ANSI_NULLS ON; SET ANSI_WARNINGS ON"; everytime you connect. And depending on the version of php/freetds you are using, it won't allow for multiple queries in one string like that.

-KsE
Back to top
View user's profile Send private message
ian!
Bodhisattva
Bodhisattva


Joined: 25 Feb 2003
Posts: 3829
Location: Essen, Germany

PostPosted: Tue Jul 13, 2004 10:00 pm    Post subject: Reply with quote

Moved from 'Portage & Programming'.
_________________
"To have a successful open source project, you need to be at least somewhat successful at getting along with people." -- Daniel Robbins
Back to top
View user's profile Send private message
hanj
Veteran
Veteran


Joined: 19 Aug 2003
Posts: 1359

PostPosted: Tue Sep 27, 2005 12:48 am    Post subject: Reply with quote

Hello

I'm glad I came across this howto.... but I'm running into problems. I'm unable to make the connection to the SQLServer 2000 on a Windows2003 server.

Here are my packages...
Code:
[ebuild   R   ] dev-php/mod_php-4.4.0-r3  +X +apache2 -berkdb +crypt +curl -debug -doc -fdftk -firebird -flash +freetds +gd -gd-external +gdbm -gmp +hardenedphp -imap -informix -ipv6 -java +jpeg -kerberos +ldap -mcal -memlimit -mssql +mysql +nls -oci8 -odbc +pam +png -postgres +snmp -spell +ssl +tiff +truetype +xml2 -yaz 0 kB
[ebuild   R   ] dev-db/freetds-0.62.3  +mssql -odbc 0 kB


I originall tried with +mssql.. and that didn't get me any further.

Here is my /etc/freetds.conf...
Code:
[SQLserver]
        host = 192.168.0.10
        port = 1433
        tds version = 8.0


Here is my test code...
Code:

$connect        = mssql_connect("SQLserver","testuser","testpass");
echo var_dump($connect);


The mssql_connect() is always returning false. Here is output:
Code:
bool(false)


I can verify that I can connect to this SQLServer box using EnterpriseManager from another workstation. Checked firewall rules.. it's allowing incoming connection within my internal subnet. The user/pass are good, verified via Enterprise Manager.

I'm kinda stuck.. any help is greatly appreciated.

Here is my configure output via phpinfo()..might shed some light.

Code:
Configure Command './configure' '--prefix=/usr' '--host=i686-pc-linux-gnu' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--datadir=/usr/share''--sysconfdir=/etc' '--localstatedir=/var/lib' '--build=i686-pc-linux-gnu' '--with-apxs2=/usr/sbin/apxs2' '--disable-cli' '--without-pear' '--without-db3''--without-db4' '-'-without-db2' '--without-ndbm' '--with-mcrypt=/usr' '--with-mhash=/usr' '--without-interbase' '--without-ming' '--without-swf '--with-sybase=/usr' '--with-gdbm=/usr' '--without-fdftk' '--without-java' '--without-mcal' '--without-unixODBC' '--without-pgsql' '--with-snmp=/usr''--enable-ucd-snmp-hack' '--with-xpm-dir=/usr/X11R6' '--without-gmp' '--without-mssql' '--without-crack' '--without-pdflib' '--with-gd' '--enable-gd-native-ttf''--with-png=/usr' '--with-png-dir=/usr' '--with-jpeg=/usr' '--with-jpeg-dir=/usr' '--enable-exif' '--with-tiff=/usr' '--with-tiff-dir=/usr' '--with-mysql=/usr''--with-mysql-sock=/var/run/mysqld/mysqld.sock' '--with-freetype-dir=/usr' '--with-ttf=/usr' '--with-t1lib=/usr' '--with-gettext' '--without-pspell''--with-openssl=/usr' '--without-imap' '--with-ldap=/usr' '--with-dom=/usr' '--with-dom-xslt=/usr' '--with-dom-exslt=/usr' '--without-kerberos' '--with-pam''--disable-memory-limit' '--disable-ipv6' '--without-yaz' '--disable-debug' '--with-curlwrappers' '--with-curl=/usr' '--enable-dbx' '--with-zlib=/usr''--with-zlib-dir=/usr' '--with-sablot=/usr' '--enable-xslt' '--with-xslt-sablot' '--with-xmlrpc' '--enable-wddx' '--with-xml' '--disable-posix' '--enable-mbstring=all' '--enable-mbregex' '--with-bz2=/usr' '--with-cdb' '--enable-pcntl' '--enable-bcmath' '--enable-calendar' '--enable-dbase' '--enable-filepro' '--enable-ftp' '--with-mime-magic=/usr/share/misc/file/magic.mime' '--enable-sockets' '--enable-sysvsem' '--enable-sysvshm' '--enable-sysvmsg' '--with-iconv' '--enable-shmop' '--enable-dio' '--enable-yp' '--without-ncurses' '--without-readline' '--enable-inline-optimization' '--enable-track-vars''--enable-trans-sid' '--enable-versioning' '--with-config-file-path=/etc/php/apache2-php4' '--libdir=/usr/lib/php' '--without-pear'


Thanks in advance!
hanji
Back to top
View user's profile Send private message
WhimpyPeon
Apprentice
Apprentice


Joined: 18 Oct 2003
Posts: 158
Location: Columbus, Nebraska

PostPosted: Tue Sep 27, 2005 12:36 pm    Post subject: Reply with quote

I have had no success with freetds. I compiled php with mssql support and use pear to connect. Here is my php setup:

Quote:
dev-php/php-4.4.0 +X +berkdb +crypt +curl -debug -doc -fdftk -firebird -flash -freetds +gd -gd-external +gdbm -gmp -hardenedphp -imap -informix +ipv6 +java +jpeg -kerberos -ldap -mcal -memlimit +mssql +mysql +ncurses +nls -oci8 +odbc +pam +png +postgres +readline -snmp +spell +ssl +tiff +truetype +xml2 -yaz

dev-php/mod_php-4.4.0 +X +apache2* +berkdb +crypt +curl -debug -doc -fdftk -firebird -flash -freetds +gd -gd-external +gdbm -gmp -hardenedphp -imap -informix +ipv6 +java +jpeg -kerberos -ldap -mcal -memlimit +mssql +mysql +nls -oci8 +odbc +pam +png +postgres -snmp +spell +ssl +tiff +truetype +xml2 -yaz


I have not yet moved to the new apache/php setups yet (I plan it for next weekend). Just setting the +mssql in php worked for me. I really like the PEAR-DB class. To connect I do something like this:
msdb_connect.php Include File For Connections
Code:
<?php
//msdb_connect.php
//Provides Connection To mssql server
//require the PEAR::DB classes.
require_once 'DB.php';

$msdb_engine = 'mssql';
$msdb_user = '*YOURUSER*';
$msdb_pass = '*YOURPASSWORD';
$msdb_host = '*SQLSERVER*';
$msdb_name = '*DATABASE*';

$msdatasource = $msdb_engine.'://'.
                          $msdb_user.':'.
                          $msdb_pass.'@'.
                          $msdb_host.'/'.
                          $msdb_name;
$msdb_object = DB::connect($msdatasource, TRUE);
/* assign database object in $msdb_object,
if the connection fails $msdb_object will contain
the error message. */

// If $msdb_object contains an error:
// error and exit.

if(DB::isError($msdb_object)) {
        die($msdb_object->getMessage());
}
?>


employee.php Quick Listing of Employees
Code:

<?php
//employee.php
//provides listing of employees and their addresses

require 'msdb_connect.php';
//require our connection include file

$sql = "SELECT lastname, firstname, address, city FROM Employees ORDER BY lastname, firstname";
//our sql statement

$result = $msdb_object->query($sql);
if (DB::isError($result)) {
        echo "YOUR ERROR MESSAGE HERE";
        die ($result->getMessage());
        $result->free();
        exit;
}
//You Now Have A Good Connection
echo "<table>\n";
echo "<tr>\n";
echo "<th>Name</th><th>Address</th><th>City</th>\n";
echo "</tr>\n";

while ($row = $result->fetchRow (DB_FETCHMODE_ORDERED)) {;
echo "<tr>\n";

echo "  <td>\n";
echo $row[0] . ", " . $row[1];
echo "  </td>\n";

echo "  <td>\n";
echo $row[2];
echo "  </td>\n";

echo "  <td>\n";
echo $row[3];
echo "  </td>\n";

echo "</tr>\n";
}

echo "</table>\n";
?>


The only thing I have discovered is that DB_FETCHMODE_ORDERED is the only thing that seems to work.

Excuse my code if it is sloppy I just kind of banged it out.
Back to top
View user's profile Send private message
eddieroger
n00b
n00b


Joined: 29 Mar 2005
Posts: 18

PostPosted: Fri Feb 24, 2006 2:55 am    Post subject: Reply with quote

Hanj - did you ever get this resolved? I'm working off this howto, and was successfull in getting it to work, so if you haven't gotten it, I will try and help.

KsE - Excellent howto! I was looking all over for exactly this. Have you considered posting it on gentoo-wiki? I'd recommend doing it if you haven't, and would gladly help. Thanks for figuring it out.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum