FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

> From: Mark Gruetzner [mailto:mgruetzner AT rw3.com]
> Sent: May 17, 2002 12:11 PM

Mark, 

Nice analysis.  Always good to improve the fit of FreeTDS and PHP.  I don't
think it's a question of "negative message numbers" per se, see below, but
maybe I'm overlooking something.  

> mssql_query ("mystoredproc");
> $msg = mssql_get_last_message ();
> print $msg;
> 
> where "mystoredproc" is a stored procedure that returns N rows of data and
> calls the SQL function below to send a warning message:
> 
> raiserror ('QueryLimit',9,1)
> 
> you would expect the value of $msg to be "QueryLimit" after the call to
> mssql_query.  In fact, this is how it works on Windows but not on Linux
> using freetds.  I have debugged this issue on both the freetds and php
> software and found the following.  On Freetds, the message number that
gets
> returned from SQL is 50000 which when stored as a 16-bit signed integer is
a
> negative value in the freetds code, so the freetds code ignores the
message.

raiserror is so weird.  It passes a string and a message number, and can
optionally set @@error to value of the message id ("with seterror").  

To get raiserror to pass a user-defined message number, you need to install
the message in sysmessages with sp_addmessage.  The syntax you're using is
an "ad hoc message", to which raiserror assigns a message number of 50,000.
That's where the 50,000 is coming from.  

The maximum message number is 2^31 - 1.  We need 31 bits to represent such
numbers, not the 15 we're apparently providing.  Trying to stuff 31 bits of
data into a signed two-byte variable is unlikely to be a Good Thing.  In
this case, the 50,000 only *coincidentally* needs just 16 bits, so you see
it as a negative value (high bit set).  But someone could add, say, message
number 131,196 (2^17 + 124) to sysmessages; we'd trim off the high bytes and
represent it as #124, "CREATE PROCEDURE contains no statements".  A bug, I'd
say.  

The real error is treating the message id as a 16-bit variable, no?  Where
does that happen?  Why not just widen the variable?  

What this means for Sybase, TDS 4.2, and/or big endian machines, I don't
know.  

> I modified the freetds code to NOT ignore negative message numbers but to
go
> ahead and pass those messages on to the appropriate callback function in
> PHP.  That helped matters, but there was still an issue in the php code in
> the "sybase_query" function in php_sybase_db.c.  Basically, what is
> happening is that in the case described above, you receive 2 sets of
> database results as a result of the query, but the sybase_query function
is
> only reading the first set of results.

How does it become "2 sets of database results"?  There is a result set
(output from your query), and an out-of-band message (from raiserror).
Those are two different things.  

mssql_get_last_message doesn't process result sets; it looks to me like the
PHP equivilant of dbmsghandle.  

Obviously, I don't know all the ramifications, either, Mark.  I'm just
trying to make sure we understand the spec first.  

Regards, 
--jkl