[freetds] Getting error messages to the (PHP) user

[Mark A. Hershberger]

Yesterday, I thought that the problem of getting error messages back to
PHP was something that I had to solve on the PHP side. After a little
more digging into the FreeTDS code, I'm not so sure.

The messages from SQL Server are shown in the debug log, but all that I
see on the PHP side is "General SQL Server error: Check messages from
the SQL Server".

I found that _dblib_handle_info_message() in dbutil.c handles both
actions: logging the actual SQL Server error message and returning the
hard-coded error message ("General SQL Server error...").

This hard-coded error message seems pretty useless to me -- especially
when the actual error message is right there. Is there a reason it was
done this way?

[James K. Lowden]

On Tue, 05 Mar 2013 09:18:31 -0500

Post by Mark A. Hershberger
I found that _dblib_handle_info_message() in dbutil.c handles both
actions: logging the actual SQL Server error message and returning the
hard-coded error message ("General SQL Server error...").
This hard-coded error message seems pretty useless to me -- especially
when the actual error message is right there. Is there a reason it
was done this way?

I think you'll find, if you look again, that the hard-coded message is
in response to a severity > 10. db-lib distinguishes between *message*
arriving from the server, and *errors* returned because of improper use
of the API. Errors happen without the server; messages come from the
server.

Server messages arrive with a severity code. By convention, severity
codes >= 10 are "errors" in the sense that the user can't fix them.

select count(*), severity from sys.sysmessages group by severity order
by severity;

severity
----------- --------
1130 0
13260 10
1460 11
10 12
10 13
700 14
2290 15
55490 16
530 17
90 18
50 19
890 20
430 21
70 22
140 23
90 24

The message handler treats such messages as errors. Instead of
repeating the text of the message, it says to check for messages.
Which makes a certain amount of sense if you can keep messages and
errors straight in your mind.


--jkl

[Mark A. Hershberger]

Post by James K. Lowden
Server messages arrive with a severity code. By convention, severity
codes >= 10 are "errors" in the sense that the user can't fix them.

But I'm seeing the hard-coded message for errors in my SQL that *I*
should fix.

For example, I was getting the hard-coded message till I patched the
freetds library. Now I see the following:

$ php import-sql.php
Error: [HY000] Incorrect syntax near 'AUTO_INCREMENT'. [102]
(severity 15) [CREATE TABLE ...]

Yes, I know that this is MySQL code and I need MSSQL code. The point
here is that the error message and the severity and the severity (15 >
10) is the direct result of something I can fix.

Mark.

[James K. Lowden]

On Tue, 05 Mar 2013 11:31:27 -0500

Post by Mark A. Hershberger

Post by James K. Lowden
Server messages arrive with a severity code. By convention,
severity codes >= 10 are "errors" in the sense that the user can't
fix them.

But I'm seeing the hard-coded message for errors in my SQL that *I*
should fix.
For example, I was getting the hard-coded message till I patched the
$ php import-sql.php
Error: [HY000] Incorrect syntax near 'AUTO_INCREMENT'. [102]
(severity 15) [CREATE TABLE ...]
Yes, I know that this is MySQL code and I need MSSQL code. The point
here is that the error message and the severity and the severity (15 >
10) is the direct result of something I can fix.

Right. The "user" they meant isn't you the programmer, but a user of
your application. If the data entered fail a contraint, the user can
change the data and continue. Broken SQL is a severe problem in a
production application, as I'm sure you agree! :-)

It's not intended that you should have to change the FreeTDS code to
alter the error-handling characteristics. db-lib itself never prints a
anything in response to a server message or a library error. (This can
be frustrating to someone just learning, but as you see it's helpful.)
Instead, db-lib provides for the application -- in this case the PHP
library -- to install a callback function. Errors and messages are
passed to the installed function, which does what it does.

If you look at the bsqldb application (src/apps/bsqldb.c) you'll see
calls to dberrhandle() and dbmsghandle(). The function definitions are
at the end of the source file.

I don't know PHP. To control the error reporting behavior, you need
either to change the callback function PHP installs, or somehow affect
what it does. In applications I've written, for example, there was
sometimes a list of message numbers that were suppressed. In your
case, it sounds like you probably want to log the details and report a
more na?ve message. (I'm not sure I support that idea, but that's
another question.)

HTH.

--jkl