I noticed an issue in my app recently where an database insert that occurred in a stored procedure was failing, but no exception was getting thrown. The insert failed because a column that doesn't allow nulls was getting assigned a null value. The error message was something like
Error Code 515: Cannot insert the value NULL into column 'column_name', table 'my_table_name'; column does not allow nulls. INSERT fails.
Here is the stored procedure: https://pastebin.com/tLtjxgPL
So I have a TRY/CATCH with the error sql in the TRY portion. When it runs, it definitely gets to the CATCH portion, because I am creating a record of the error in my errors table. BUT... after that, I do a
THROW which should pass the caught error back on up to PHP... but it doesn't. I've tried using a RAISERROR instead to no avail. I've also tried adding a
SELECT in my CATCH block, but that never makes it to PHP.
Now, I can simplify that a bit, and get rid of all the transaction business, and I get this: https://pastebin.com/Faz5fUD3, which still has the issue.
But if I keep going, and get rid of the TRY/CATCH all together, it successfully passes the error up to PHP! Somehow, this specific type of error is causing MSSQL to not pass back any results or errors.
I have tested with various other types of errors (divide by zero, referencing a table that doesn't exist, explicit THROW, explicit RAISERROR) and they all behave as I would expect... catching the error and then re-throwing it so I can grab it in PHP.
Some details on my setup:
- I am running this app on Windows 10, with XAMPP.
- I am using the Slim framework, and using the Eloquent ORM. Since I'm using Eloquent... I believe I have to use the pdo_sqlsrv.dll rather than the sqlsrv.dll.
- I have the live version of this same app on an Ubuntu box running Apache, using PDO and am NOT getting this issue.
- I have a different app that is running on the same local machine, but with IIS. It is using the sqlsrv.dll, not PDO, and it doesn't have this issue.
- I have tried setting the PDO attribute ATTR_ERRMODE to ERRMODE_SILENT, ERRMODE_WARNING, and ERRMODE_EXCEPTION, and no matter what, I cannot find a way to get PHP notified of this error.
- I've tried taking that
EXEC errors_addpart out of my CATCH block, just to make sure that's not the issue, and it doesn't fix it.
- On my local machine (where I'm experiencing this issue), I'm connecting to 'Microsoft SQL Server Developer (64-bit) 14.0.2002.14'
- On my live version (where I'm NOT experiencing this issue), I'm connecting to 'Microsoft SQL Server Enterprise: Core-based Licensing (64-bit) 12.0.4213.0'
So... I feel like it has to be caused by the SQLSRV driver I'm using... or something about the settings or version of the DB I'm connecting to...?
Any help would be greatly appreciated. Thanks!