How to debug parameterized SQL query

Question

I use C# to make connection to a db and then a Ad hoc SQL to get data. This simple SQL query is very convenient to debug since I can log the SQL query string. If I use parametrized SQL query command, is there any way to log sql query string for debug purpose?

Answer 1

You can log it with the parameters on the application side or turn on query logging on your database server to see what queries it is receiving.

Answer 2

Using the "debug" flag of your library is often the simplest solution. But you're dependent on the library which can lie to you or at least conceal a few things it will do (for instance, psycopg silently changes the default isolation level).

On the DBMS side, you can always activate logging. The good thing with it is that you will get the exact SQL request, whatever your client library does. In that respect, it is better than the "debug" flag of the library. But, on some DBMS, logging statements can be very slow (this is the case with PostgreSQL).

Another solution is to use a sniffer on the network, if you have sufficient privilege. Wireshark can decode the protocols of many DBMS and present the actual SQL request.

On PostgreSQL, activating logging is done in postgresql.conf by:

log_statement = 'all'                   # none, ddl, mod, all

I typically use also:

log_connections = on
log_disconnections = on
log_duration = on
log_hostname = on

Answer 3

I have asked the same question here. Short answer: there is no nice way.