| View previous topic :: View next topic |
| Author |
Message |
KsE
Apprentice
Joined: 18 Mar 2003
Posts: 269
Location: St. Cloud, MN
|
 Posted: Tue Jul 13, 2004 8:23 pm Post subject: PHP MSSQL HOWTO |
 |
|
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 |
|
 |
ian!
Bodhisattva
Joined: 25 Feb 2003
Posts: 3829
Location: Essen, Germany
|
| Posted: Tue Jul 13, 2004 10:00 pm Post subject: |
|
|
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 |
|
|
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1490
|
| Posted: Tue Sep 27, 2005 12:48 am Post subject: |
|
|
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:
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 |
|
|
WhimpyPeon
Apprentice
Joined: 18 Oct 2003
Posts: 158
Location: Columbus, Nebraska
|
| Posted: Tue Sep 27, 2005 12:36 pm Post subject: |
|
|
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 |
|
|
eddieroger
n00b
Joined: 29 Mar 2005
Posts: 18
|
| Posted: Fri Feb 24, 2006 2:55 am Post subject: |
|
|
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 |
|
|
|
|
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
|
|
Documentation, Tips & Tricks
