FreeTDS Development Group

["Michael Peceny" <peceny AT web.de>]

Hello,

I have been trying to connect a webserver (Linux) to an MS SQL Server 7 
(NT),
so far with only partial success. I slowly get closer to my goal, but the
whole thing is still buggy - maybe someone here can help?

Setup:
  SuSE Linux 6.3
  Apache 1.3.9
  PHP 4.0.0 + Zend Optimizer Beta 4
  FreeTDS 0.50 and CVS version (last one tried: June 24)



A)
FreeTDS: configure --with-tdsver=7.0
PHP:     configure --with-mysql
                   --with-apxs
                   --disable-debug
                   --with-sybase-ct=/usr/local/freetds

This resulted in all kinds of problems with mssql_query(), only
a few queries worked, most gave an entry of "FATAL:  emalloc():  Unable to 
allocate -2147483648 bytes)" in the httpd.error_log and subsequently
returned an empty web page. And one of my test queries resulted in an
endless loop filling the log with those "Unknown marker: 0!!" messages
(an error that others have experiences here, too).

NOTE on --with-sybase-ct: The linker complains about some libraries 
missing
(-lsybtcl, -lintl, -lcomn, -lcs), on removing the references in 
config_vars.mk
and Zend/Makefile PHP compiles fine, without any unresolved symbol errors.
This doesn't happen when using --with-sybase.


So, on to using TDSVER 42 (which some have reported to work better, even
though the docs mention TDSVER 70 being needed for connecting to an
MS SQL Server 7).



B)
FreeTDS: configure --with-tdsver=4.2
PHP:     configure --with-mysql
                   --with-apxs
                   --disable-debug
                   --with-sybase-ct=/usr/local/freetds

This helped a lot, most queries work that way, only a few lead to a
segmentation fault (-> notice in httpd.error_log), and some seemed to
"hang" (might return after some time with timeout, since I was testing
on an - in Germany expensive - modem line, I didn't wait more than about
20 secs), especially of the kind "SELECT * FROM ...". None of those was
one of the queries I require for my website.

However, there is a problem that renders above solution unusable:
The data from text fields misses the last character (there aren't many
of those we use, but there are two or some such...).


Since some people seem to be using --with-sybase instead of
--with-sybase-ct, the next attempt will use this:



C)
FreeTDS: configure --with-tdsver=4.2
PHP:     configure --with-mysql
                   --with-apxs
                   --disable-debug
                   --with-sybase=/usr/local/freetds

Mixed results: The missing last char from text fields is gone, all queries
seem to work - when commited from a test page. I get weird stuff from an
actual example page (let's call it index.php) for the website, though:

--- FILE query.php ---
<?php
// For queries with a single result row
// Returns the resulting column entries in global variables named
// after the column names
function mssqlSingleRowQueryVars( $query, $conn )
{
  $query_result = mssql_query( $query, $conn )
    or die( "<strong>Query failed</strong>");

  $row = mssql_fetch_array( $query_result ); // get the single result row
  reset( $row );
  while ( list($key,$val) = each( $row ) ) // is_int($key) == true
  {
    list($key,$val) = each ( $row );       // is_string($key) == true
    unset($GLOBALS[$key]);   // destroy possibly existing result var
    $GLOBALS[$key] = $val;
    
    // Debug
    echo "Setting: ", $key, " => ", $GLOBALS[$key], " [=", $val, 
"]<br>\n";
  }
  $test = "Test"; // Debug
  mssql_free_result( $query_result );
}
?>
----------------------

--- FILE index.php ---
<?php
require("query.php");

// connect to MS SQL Server 7
$conn = mssql_connect(<name>,<user>,<password>)
  or die( "<strong>DB connection failed</strong>" );

$query = "SELECT 
Nummer,Vorname,Nachname,Anschrift1,Anschrift2,Straße,Raum,<...>";
mssqlSingleRowQueryVars( $query, $conn );

// Debug
echo "<hr>\n";
echo "Nummer =>", $Nummer, " [=", $GLOBALS["Nummer"], "]<br>\n";
echo "Raum =>", $Raum, "<br>\n";
echo "Vorname =>", $Vorname, "<br>\n";
echo "Nachname =>", $Nachname, "<br>\n";
echo "test =>$test<br>\n";
echo "<p>&nbsp;</p>\n<hr>\n";
...
----------------------

The debug line in query.php reports the correct settings of the
global variables with the column names ($Nummer, $Vorname, ...),
but upon returning from mssqlSingleRowQueryVars(), they contain
garbage (not always, but in most cases). Example output of above
debug lines, with the <xx> being non-printable chars:

Setting: Nummer => 533 [=533]
Setting: Vorname => Michael [=Michael]
Setting: Nachname => Peceny [=Peceny]
...
Setting: Raum => 225 [=225]
...
Nummer =>?8<us> [=?8<us>]
Raum =>T³<us>
Vorname => Nachname =>ô²<us><bs><bel>
test =>Test

The global variable $test that I directly set in query.php
always holds the correct value.

Interestingly enough, I can get around this by creating a new
string for the value of the global variables in query.php:

    $GLOBALS[$key] = "".$val; // create _new_ string

(this is just one way to do it, I have tested others like
= substr( $val,0,strlen($val) ), and they all seem to work).


Now, while this would appear to fix queries, I am not happy
with such a solution - seems there are some memory issues,
and I doubt our webserver would take kindly to suffering from
memory leaks or such.

Any ideas / solutions?

Thanks a lot
Michael