Show parameter values in sql query

#1

Hi,

I run some queries against an in-memory database. See gist. https://gistlyn.com/?gist=df066417ef961487d0e710588dc1cb71

Is it possible to get the parameter values of the last sql query?

I see this:
SELECT “Id”, “Content”, “Order”, “Done” FROM “Todo” WHERE “Id” = @Id

I want to see this:
SELECT “Id”, “Content”, “Order”, “Done” FROM “Todo” WHERE “Id” = 12

#2

You wont be able to see in-line SQL because OrmLite uses parameterized queries, the output you’re seeing shows the SQL that is generated by OrmLite and executed on the RDBMS.

There’s a few ways to view the db parameter values, you can use the db.GetLastSqlAndParams() API instead, you can specify a Debug logger which will log both the SQL and parameter values, e.g:

LogManager.LogFactory = new ConsoleLogFactory();

Or you can use a CapturedSqlFilter.

#3

Hey @mythz, it looks like GetLastSqlAndParams() hangs off dbCmd not the connection. It would be cool if it could work off the connection though :smile:

#4

Right, but it can’t hang onto the IDbCommand as it can’t be accessed after it’s disposed and capturing it after every command will impact perf, maybe I can add a OrmLiteConfig.DebugMode flag to control this.

#5

Actually I’m ok with just using a custom OrmLiteExecFilter during debug.

public class CaptureLastSqlAndParamsFilter : OrmLiteExecFilter
{
    public override void DisposeCommand(IDbCommand dbCmd, IDbConnection dbConn)
    {
        var sql = dbCmd.GetLastSqlAndParams();
        base.DisposeCommand(dbCmd, dbConn);
        dbConn.SetLastCommandText(sql);
    }
}
#6

The DB params are already available in the built-in CapturedSqlFilter.

#7

Tried to use the CaptureSqlFilter in gistlyn but then I got a
“Type ‘ServiceStack.OrmLite.OrmLiteState’ in Assembly ‘ServiceStack.OrmLite, Version=4.5.0.0, Culture=neutral, PublicKeyToken=null’ is not marked as serializable.”

#8

You can just set the Console Logger at the top in Gistlyn, i.e:

LogManager.LogFactory = new ConsoleLogFactory();
#9

Thanks!
Everyday I learn something new about ServiceStack is a good day :smile:

1 Like