[freetds] FreeTDS 0.62.4 problems with COALESCE in Stored Procedures
dclark at rastardm.com
Tue Nov 16 14:59:28 EST 2004
My company has successfully used FreeTDS with Apache/PHP to interact with
our SQL Server 2000 database for several years now.
Just today I noticed a problem with a particular stored procedure of ours
not returning the correct results. The proper number of records was being
returned by the routine, but certain fields' values were not correct. This
problem only occurs on the newest of our web servers, which is running PHP
4.3.9 (on Apache 2) with FreeTDS 0.62.4 installed.
Upon investigation, it turned out that there was a statement in the stored
procedure that was calling the COALESCE() function on two database fields,
both of which could be NULL at the same time (and for a small fraction of
the records this was exactly the case). When I called just this SELECT
query through PHP I received no results - when I modified the COALESCE()
call to include a third parameter that was guaranteed to be non-NULL, the
results appeared correctly.
I should mention that when I run the stored procedure myself directly
through Query Analyzer, it works fine without modification, i.e. the
COALESCE() function returns NULL where both parameters are NULL (as the SQL
Server documentation says it will). Which brings me to my real point of
confusion - how can the statements WITHIN the stored procedure be affected
by the client making the call? Isn't the stored procedure processed on the
database server itself?
I've managed to work around the problem for now by modifying the query, but
I have to say that it baffles me. Any input would be great.
More information about the FreeTDS