How do I find complete error messages with MS SQL 2000?

[tcarnes]

When I have

ini_set('display_errors', '1');

I will see a rather complete error message if I try to do something wrong with the database. ie:

faultCode0faultStringWarning:mssql_execute() [function.mssql-execute]: message: Cannot insert the value NULL into column 'UserName', table 'ORDER'; column does not allow nulls. INSERT fails. (severity 16) in db2000_fns.php on line 64

faultCode0faultStringWarning:mssql_execute() [function.mssql-execute]: message: Order: Cannot insert new Order (severity 16) in db2000_fns.php on line 64

faultCode0faultStringWarning:mssql_execute() [function.mssql-execute]: stored procedure execution failed in db2000_fns.php on line 64

However, when I use the following code:

	$result[] = mssql_get_last_message();
return $result;	

I just get back:

Order: Cannot insert new Order

Any idea how I can get a more complete error message? I especially want to see the reason for the error (i.e., "Cannot insert the value NULL into column 'UserName', table 'ORDER'; column does not allow nulls. INSERT fails.") I am using MS SQL Server 2000 for this application.

[premiso]

The messages coming from the first set were from the php function returning a warning to throw up a bigger error. It seems that it is how the MSSQL driver is coded. Looking at the mssql functions page: http://jm2.php.net/manual/en/function.mssql-get-last-message.php#12352 there is a code using output buffers (a hack imo) to catch the error and parse it to display prettier.

 

Here is an updated version of that you can try and see if it fits your needs:

 

function treat_mssql_error($buffer) { 
    $buffer=preg_replace("#<br>\n<b>Warning</b>:  MS SQL message:#i","<b>Error in query (SQL Server)</b>: ",$buffer); 
    $buffer=explode("(severity",$buffer); 
    return $buffer[0]; 
} 

function my_query($query,$cnx, &$sql_error="") { 
    ob_start(); 
    ini_set('display_errors', '1'); // you may want to check if this was enabled prior but yea.
    $result=mssql_query($query,$cnx); 
    if(!$result) { 
        $sql_error=treat_mssql_error(ob_get_contents()); 
        ob_end_clean(); 
        ini_set('display_errors', '0');
        return false; 
    } 

    ob_end_clean(); 
    return true; 
} 

 

You can expand this in many ways, such as using it in a class wrapper instead of a function etc, but that is the gist.

 

Alternative, you can check and see if PDO will work for your needs, as I think it would handle the errors a bit cleaner...if it has MSSQL Server support.

[PFMaBiSmAd]

If track_errors is on (edit: or you turn it on in your script), you can get the last php error in $php_errormsg

 

Also see - error_get_last

 

[tcarnes]

premiso,

 

Thanks for your help! I've got it working and am glad to see the details of the error. I could clean up the result a bit, but since I'm the only one getting the error emails, I'm just going to leave it as it is.

 

One question:

In your my_query function you set display_errors to 1 in the beginning and then reset it to 0 only if there is an error. Should I reset it to 0 even when there is not an error?

[premiso]

One question:

In your my_query function you set display_errors to 1 in the beginning and then reset it to 0 only if there is an error. Should I reset it to 0 even when there is not an error?

 

Basically, you do not want a production system to show errors to everyone. I am not sure how it was setup prior to, but you can use the ini_get to get the value of it and re-set it to that after the script runs. But it does need to be 1 for the error to be caught by the output buffer.

[PFMaBiSmAd]

Or you can use either of the two simple methods I suggested which work even if error_reporting is off or an @ error suppressor is being used on the statement.