search

tds-fdw / tds_fdw

A PostgreSQL foreign data wrapper to connect to TDS databases (Sybase and Microsoft SQL Server)
Other
377 stars 101 forks source link

Any way to get sql server error message? #243

Open merlinm opened 4 years ago

merlinm commented 4 years ago

Currently, when executing queries that fail, the error message returned is a generic one, e.g.

ERROR: DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 16

Is there any way to get the specific context of the error? this would signficantly aid in debugging and development.

GeoffMontee commented 4 years ago

Yeah, set msg_handler to notice. See here:

https://github.com/tds-fdw/tds_fdw/blob/master/ForeignServerCreation.md

merlinm commented 4 years ago

ok, I see that. Well that is helpful. e.g. the output is: NOTICE: DB-Library notice: Msg #: 207, Msg state: 1, Msg: Invalid column name 'idd'., Server: RCDYLSMSTSQL001, Process: , Line: 1, Level: 16 ERROR: DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 16

Having said that, for SQL level errors, I would humbly suggest that this behavior is sub-optimal; ideally we'd want to have the SQL error message be embedded in the error TEXT. There's a lot of reasons for that. For example, when trapping errors in pl/pgsql having the error present in SQLERRM allows for better diagnostics of whatever issue. The library is great btw, I'm jumping in heavily (hence the feedback).

merlinm commented 4 years ago

I don't know if my suggestion is practical. I did take a look a the code and It's not so complex. It may be possible to save off the last 'notice' generated error and try and save it off so that it can be rendered with the raised error.

GeoffMontee commented 4 years ago

Yeah, it might be possible to save notices, and then print them whenever an error is thrown. The problem is that FreeTDS generates a lot of messages, and it is not always easy to determine which ones are relevant, and which ones are noise.

I generally just set msg_handler to notice when I'm debugging, and then set it back to default when I want it to be quiet.