Logging generated SQL - can't get it to work, what is the current method of doing this?

Hello -

Where can I find a clear example and instructions on how to set up logging so we capture SQL queries generated by LightSpeed?

We have looked through the previous posts concerning logging SQL queries and gotten nowhere but confused. All posts related to this seem to be from 2009 or earlier and we have tried what we thought should work (in fact, we tried what used to work) with no success. We get errors and trace messages but no SQL.

I am running Visual Studio 2010 SP1 and LightSpeed 4.0 from the 6/20 nightly build (I just downloaded the latest and will install that soon).

Thanks, Dave

Hi Dave,

Check out the documentation on logging here: http://www.mindscapehq.com/documentation/lightspeed/Testing-and-Debugging/Logging which describes how to wire things up. Are you trying to use the TraceLogger? What type of application are you dealing with and where are you trying to log things to?

Jeremy

Thanks for the reply, but that section is in the User Guide verbatim and it didn't help much. The "it just works" approach is great if it just works. When it doesn't "just work" it gets to be frustrating rather quickly.

Our application is a WCF service that connects to 2 databases - one SQL Server and one Oracle. We wanted to see the Oracle SQL generated, as it was throwing an arithmetic overflow error when using one particular set of input data. We did find and correct the issue mostly by good guesswork but in the process we wanted to see the generated SQL and could not.

We tried using both TraceLogger and ConsoleLogger with similar (non)results.

I believe the problem lies in how I am handling the contexts for each database. Logging is set up in the Main() function for one context (SQL) and not the other, and the context is a local variable (see below). From a re-read of the docs it appears I need to set up logging for each context, is that correct?

At one point I remember being advised to use the ServiceBehavior(InstanceContextMode = InstanceContextMode.PerCall) decoration at the top of each Service class. I am also deriving all service classes from EntityServiceBase and then using the (global?) UnitOfWork to do work. All throughout the User Guide, however, you encourage the use of one persistent static context declaration. Could you please clarify what is the best approach when building a WCF service and what the functional difference is between them?

1 - declare all contexts as public static variables and reference them as they are needed

2 - use the InstanceContextMode.PerCall decoration

If the preferred method is #2 how do I set up logging for the context? Do I need to declare it in each service class? Does that even apply to a WCF service?

So many questions, and I thought I had this pretty well figured out... It would be great if you all had a sample WCF app or two in the mix - am I just missing it?

Thanks, Dave

OK, so I found the WCF samples but they seem geared to the DistributedUnitOfWork model which I am not using. I also wanted to add I am using the following empty constructor for each service class:

    public TestingService()
        : base("default")
    {
    }

I assume this sets up the "global" UnitOfWork for each class and holds the static Context? Please shed some light on what this actually does and you have probably answered most of my questions...

Sorry, one more question. If I configure the static context to say "Logger = new TraceLogger()" where do the logs end up? Your docs say "TraceLogger writes logs to all application trace listeners." but that doesn't seem to happen. Is there a particular way to set up the trace listeners so the output ends up there?

I did get ConsoleLogger to show SQL for the one non-service class I have (interface to the Oracle database) but the SQL scrolled off the top of the buffer...

Hi Dave,

If you say Logger = new TraceLogger() then the log messages will be passed to any attached trace listeners, so by default they will be going nowhere at all because you need to explicitly attach a listener in code or via configuration, here is some examples on how this works: http://www.codeproject.com/Articles/149251/Debugging-Tracing-and-Instrumentation-in-NET-and-A

Our logger class is just making a Trace.WriteLine() call so it then falls to the underlying tracing framework in .NET as to where the messages will end up.

The Logger property is assigned on a per context basis, so if you have multiple contexts then you will need to make sure you are assigning this for each context. The easiest way to manage this is via configuration if that is the way you are configuring your contexts by assigning the loggerClass property in the context configuration block.

e.g.

<add name="MyContextName" loggerClass="Mindscape.LightSpeed.Logging.TraceLogger, Mindscape.LightSpeed" />

How are you managing the other logging in your system? Using the TraceLogger makes sense if you are already using application tracing to manage your logging information but if you are using another approach it would be more sensible to implement your own Logger (by implementing ILogger) to make sure you are logging those messages consistently using the other approach.

Jeremy

Hi Dave,

On the WCF front you are deriving from EntityServiceBase, so this will set up its own internal context using the ContextName you have specified (e.g. default from your post above). You will need to ensure that you specify any concerns such as logging for this context via configuration.

We do recommend the use of a static LightSpeedContext in general as you only need a single instance of a LightSpeedContext per configuration from which to create UnitOfWork instances so it makes things a little clearer if you have a static or singleton instance.

This is only needed where you are needing access to the context in code yourself for creating the UnitOfWork instances. If you are only going to be using EntityServiceBase and the UnitOfWork property it exposes for you then you will not need to define a static context separately to this. You should decide if you want to manage the context yourself and then create the UnitOfWork as required in your service calls, the convenience offered by EntityServiceBase is that it is managing this process for you - if you are not sure I would recommend sticking with the EntityServiceBase approach as this requires less work on your part to manage the UnitOfWork, in which case make sure you define the loggerClass in configuration to set up logging.

The reason we recommend PerCall session as a default to avoid issues where the UnitOfWork will be persisted across requests leading to unexpected behavior such as loading entities from the identity map when you expected them to be loaded from the database on subsequent calls, if you know that your application actually needs to implement session behavior then you should certainly go ahead and change this as needed :)

Hope that helps!

Jeremy

Thanks for the feedback. I'll get back to this as soon as the recent fires get put out...