[TriLUG] LAmP (but with Microsoft SQL 2k) - suggestions? - solution!

[Turnpike Man]

Ok folks, as promised, my solution to this problem!

Here is my basic HOWTO to get FC1, Apache, PHP, FreeTDS to work using Microsoft
SQL Server database:

http://www.turnpike420.net/linux2/Apache_PHP_FreeTDS_MSSQL2000.txt

Blasphemy you might say?  No.  I could run IIS6 and ASPX (gag), but I think
introducing linux into my corporate environment is much more worthwhile.  That
said, I'm willing to stick with MSSQL Server, not to mention the other 3rd
party apps that we have which require it anyway.

On a side note, those of you who are stuck (like me) using Windows 2000 Server
for your centralized single sign-on, you can also authenticate your LAMP
solution against your ADS:

http://www.turnpike420.net/linux2/Apache_ADS_AuthLDAP.txt

Happy playing!
laters,
David M.


--- Bob Shepherd <rwshep2000 at yahoo.com> wrote:

> David,
> 
> Well, it turns out I don't have any HOWTO, but here's some help that should 
> suffice:
> 
> 1.  Installing FreeTDS & making it available to PHP:
> 
> http://www.peceny.de/misc/freetds.html
> 
> 2.  PHP native extension for MSSQL:
> 
> http://www.phpbuilder.com/manual/ref.mssql.php
> 
> Some sample code:
> 
> ## Include file with connection method
> 
> <?php
> //conX-PHP.inc
> 
> $SQL_Error = '';
> 
> function db_connect() {
> 
> 	$dbhost = "yourhost.yournet.net";
> 	$dbusername = "sa";
> 	$dbuserpassword = "";
> 
> 	$cnxDB = mssql_connect($dbhost, $dbusername, $dbuserpassword);
> 	if(!$cnxDB) {
> 		$SQL_Error = "Connection failed to the host $dbhost.";
> 		return 0;
> 	}
> 	else {
> 	return $cnxDB;
> 	}
> }
> 
> function sql_error() {
> 	return "db connection failed";
> }
> 
> ?>
> 
> 
> ## Read from db:
> 
> include "./conX-PHP.inc";
> 
> $cnxDB;	//Connection Object
> $intQ;	//boolean for confirming query success
> //our experience is without waiting for the intQ assignment, PHP/FreeTDS may 
> silently continue without throwing an error
> $ss = "";	 //TSQL String
> $intQ = 0;
> $intCt = 0; 	 //Query # records affected
> $colResult = "";	 //Select Query Return (datareader)
> $colRow = "";	//key-value pair array
> $x = 0;
> 
> //Construct TSQL Querystring
> $ss = "select  * from Products Order by ProductNo;";
> 
> //Connect to SQL Server
> $cnxDB = db_connect(); //Connect to Sql Server
> if(!$cnxDB) die (sql_error());
> 
> //Choose database 
> $intQ = mssql_select_db("yourdb", $cnxDB);
> if(!$intQ) die (sql_error());
> 
> //Send the query
> $colResult = mssql_query($ss, $cnxDB);
> 
> //Get # of rows returned
> $intCt = mssql_rows_affected($cnxDB);
> 
> for ($x = 0; ($x <= ($intCt - 1)); $x++) {
> 	//Fetch one row into a key-value array
> 	$colRow = mssql_fetch_array($colResult);
> 	$strProductNo[$x] = $colRow['ProductNo'];
> 	$strDescription1[$x] = $colRow['Description1'];
> 	//..etc.
>         }
> }
> $intQ = mssql_close($cnxDB);
> 
> ## Delete a record
> 	$ss = "Delete from test1 where text3 like 'BOB%'; ";
> 	$intQ = mssql_query($ss, $cnxDB);
> 	$intCt = mssql_rows_affected($cnxDB);
> 
> ## Insert a record
> 	$ss = "insert into test1 (id, text1, text2, text3) values (";
> 	$ss .= "1, 'str1', 'str2', 'str3');";
> 	$intQ = mssql_query($ss, $cnxDB);
> 
> Hope this helps!
> 
> Bob Shepherd
> 
> On Wednesday 28 July 2004 1:19 pm, Turnpike Man wrote:
> > In the past, I have managed with SuSE 7.1 to MSSQL 7.0 on PPC, but that was
> > more than 2 years ago.  I keep seeing this unixODBC come up and now, so in
> > a new environment in different job, I'm trying to do as the subject
> > suggests. This is how I did it previously:
> >
> > http://www.turnpike420.net/linux2/Apache_PHP_FreeTDS_MSSQL7.txt
> > Which I honestly can't stand this method and had to do it on SuSE at the
> > time b/c standard installs were configured for it, and I don't think SuSE
> > had a package thing like RPM, but if it did, doesn't matter b/c I didn't
> > know.
> >
> > Anyway, I'm using FC1, I have php and php-odbc packages installed, unixODBC
> > is installed, as well as Apache 2.x.  PHP works great with Apache right
> > now, this is out of the box install, nothing config'd away from default.  I
> > would like to think with this combination I can talk to the MSSQL 8.0
> > (2000) database from a PHP web application.
> >
> > An alternate method I'm trying is using the FreeTDS tool that I used back
> > in the first example.  FreeTDS now comes with 'tsql' tool which I can use
> > and successfully connect to the MSSQL 2K db.  I don't know how to use tsql,
> > so other than successful connect, I don't know how far I can go.  At least
> > it proves I'm not firewalled off form the db.  Any tips or experience
> > making this happen would be great.
> >
> > David M.



		
__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail