mssql_fetch_row() issues

Anon

I am getting what seems like strange results to me. When I query a MS SQL 7 server for three fields (account,phone,fax) and then attempt to echo them to the screen if the values of the phone or fax field are null the value is in populated with the value from the previous row. This behavior continues until it reaches a field that has a real value. The behavior then continues with the new value.

To date this is what I have tried.
(1) Copying the SELCECT statement into another application, both MS Query Analyzer and sqsh. I know the query is good.

(2) Setting up the exact same data on a MySQL sever and using the same syntax. This yields proper results.

(3) I have tried clearing the variable at the end of my while() loops. All this to no avail.

In case your curious my hardware config is a Linux server with FreeTDS 0.53, PHP 4.0.6 compiled with Sybase support. The box also is running MySQL for comparitive testing.

Sorry this post is so long, my apologies. :-)

Sincerely,
Timothy Robnett
mailto:tim@robnett.net

Anon

Try mssql_fetch_array() and see if that fixes it.

And if you're doing a lot of "comparitive testing" you might find the PEAR DB class or ADOdb class database abstraction useful.

trrobnett

Thanks for the reply. I should have mentioned this in my post but both mssql_fetch_row() and mssql_fetch_array() do the exact same thing.

I am brand new to the whole PHP scene so could you point me in the right direction on the PEAR DB class. I literally have no idea what you are talking about, at least not yet. :-)

Also I am using MS SQL currently on the back-end but Linux/Apache/PHP for the middle tier. I say all of that because I am curious does the ADOdb class still apply to my situation since I am making a TDS connection to the SQL server?

Anon

That's cool... I remembered something else that is more apt...

At the end of your while loop try using unset() to empty the array before going to the next row.

while($data=mssql_fetch_array($result))
{
...print results...
unset($data);
}

trrobnett

Tried that as well, no avail. I even played around with some if/else routines to verify that unset was working and of course it was.

I am about 96% certian this is a bug in PHP's mssql code since it works fine under MySQL. Also another tid bit, since mssql is really just Sybase hence the reason TDS works I swapped out all my calls for sybase calls and the same thing happens.

Anon

The only other thing that comes to mind, since the problem is present in both sybase and mssql is maybe its the freetds libs?

I am still using 0.50 on my boxes...

If you're using the php dso you might try setting asside your freetds-0.53 libs, install the 0.50 libs. move your php so to a backup and recompile?

trrobnett

I had the same thoughts initially so I tried the query using sqsh (http://www.sqsh.org/) and the results are perfect there.

I take it you're environment is similar to mine and the only obvious difference is the FreeTDS libs huh?

Anon

I think they're pretty close, though a little variation in my own servers:

One is RedHat 6.2 with Apache, php 4.04pl1, freetds 0.50.

Second is Mandrake 8.0, Apache, php 4.05 freetds 0.51.

Third is Mandake 8.1, Apache, php 4.06, freetds 0.51

All are access a pair of Win2k Adv Servers running MSSQL 7.02

trrobnett

Thanks for the info. I'll recompile with FreeTDS 0.50 or 0.51 and see if it helps.

At this point I am quite confused since you have a variety of PHP and FreeTDS versions and don't have this or similar problems.

I also had this problem on PHP 4.03p? so I upgraded to 4.06 before posting to this list. I am on Kernel 2.2.19 but I don't think that is my issue, at least I don't think it is.

My backend is Win2k SP1 running MSSQL 7.0 w/SP3 (I think, I am not at work right now so I would have to double check).

Thanks for all your feedback. If I get it licked I'll let you know.

trrobnett

I talked with a co-worker and I am curious if you have the time to peek at a brief code snipit and see if something is off.

<?php

putenv("TDSDUMP=/tmp/tds.log");
putenv("SYBASE=/usr/local/freetds");
putenv("TDSVER=70");

$hostname = "oms";
$username = "actual_username";
$password = "actual_password";
$dbName = "trr";
$db = mssql_connect($hostname,$username,$password);

mssql_connect($hostname,$username,$password) or DIE("SERVER FAILED TO RESPOND.");
mssql_select_db($dbName,$db ) or DIE("Database unavailable");

$result = mssql_query("SELECT TOP 10 account,mainphone,fax FROM account where account like '$acctLookup%'",$db) or DIE("Query Failed");

echo "<table border=0 cellpadding=2 cellspacing=2>\n";
echo "<tr bgcolor=\"lightblue\"><th>Account Name</th><th>Main Phone</th><th>Fax</th></tr>\n";

while ($myrow = mssql_fetch_array($result)) {
printf("<tr> <td>%s</td> <td>%s</td> <td>%s</td> </tr>\n",
$myrow[account],$myrow[mainphone],$myrow[fax]);
echo("<p>");
}
?>

And here is the output I get from some bogus data I threw into a development table. Note the repeating fax number. Only two companies actually have fax numbers. The others are null values.

Account Name, Main Phone, Fax
Acme Shoes, 636-940-1002,

Wal-Mart, 647-323-1800, 647-323-1900
GAP, 212-455-1333, 212-455-1300
PJ Milligan, 805-690-4800, 212-455-1300
A-Z Used Cars, 314-723-0500, 212-455-1300
Best Locksmith, 958-272-0110, 212-455-1300

Anon

Hm.. ok some quick observations.

I've never had to set the evironment as on lines 3-5. FreeTDS should be able to find that information in the interfaces file. But shouldn't otherwise cause this problem.

The code is making the db connection twice, once on line 11, again on line 13. The one on 11 correctly grabs a link identifier so I'd drop line 13.

Unset($myrow) after printf..

Ok, give this a shot:

<?php
$hostname = "oms";
$username = "actual_username";
$password = "actual_password";
$dbName = "trr";

$db = mssql_connect($hostname,$username,$password) || die("Unable to connect to database");
mssql_select_db($dbName,$db ) or DIE("Database Error");
$result = mssql_query("SELECT TOP 10 account,mainphone,fax FROM account where account like '$acctLookup%'",$db) || DIE("Query Failed" );

echo "<table border=0 cellpadding=2 cellspacing=2>\n";
echo "<tr bgcolor=\"lightblue\"><th>Account Name</th><th>Main Phone</th><th>Fax</th></tr>\n";

while ($myrow = mssql_fetch_array($result))
{
printf("<tr> <td>%s</td> <td>%s</td> <td>%s</td> </tr>\n", $myrow["account"],$myrow["mainphone"],$myrow["fax"]);
unset($myrow);
}
echo "</table>";
?>

trrobnett

Thanks for your follow through on this issue. When you get sick of me pestering you just say so. :-)

I tried your sample and here is what I found.
I did have to have the putenv("SYBASE=/usr/local/freetds"); I think this is a Linux issue with exporting system variables, not a PHP issue.

I got rid of the multiple mssql_connect. I also got rid of the mssql_db_select since the user has this DB has its default.

As for the unset() we had tried that but I tried again anyhow, no luck. I placed it above the printf() to verify that it was working properly and it is, as expected no results. I also played with placing a $myrow[2] = 'nothing'; above the printf(). That does what it should fills that column with a value of nothing. However setting that after the printf() does nothing to improve the situation, I still get the old repeat data.
Just for fun I also set the value of $myrow[2] to nothing then did an unset() still no good.

Lets just say I am perplexed. I am brand-new to the coding scene so I can't be very dogmatic but I feel something is very wrong here. Obviously if the field in question wasn't a null field I would never have had this issue. On the other hand I can't believe I am the first one to come across this either. Am I safe in saying that since I don't see a duplication of the problem under MySQL the problem is in the php mssql_fetch_row() and mssql_fetch_array() calls? Did I mention that I tried using sybase_fetch as well with identical results?

Thanks for your previous insights. If anything else occurs to you let me know.

Anon

I'm stumped...

trrobnett

If it is not too much to ask (and that is a big if at this point) would you mind e-mailing me your configure options for PHP perhaps your php.ini as well. =)

I looked at the logs that are generated by each script and I can see the variable get nulled out after each iteration so I am fairly confident that FreeTDS is working as advertised plus as stated before sqsh works fine through FreeTDS.

I got all the environment stuff taken care of in php.ini so it is no longer there in the PHP code now.

If you are tired of this situation I'll understand. I appreciate all your help thus far.