CODEWITHSUNDEEP
c#asp.netsqlinformix
Ben
Ben

Reputation: 669

Logging parameterized SQL query with parameters included

I have a couple of functions that take a SQL query (parameters already added) and run it against the database. When the SQL query fails, I'd like to log the complete query, parameters included, in order to see exactly what caused the failure. query.ToString() returns IBM.Data.Informix.IfxCommand, so currently I'm just capturing query.CommandText, but if the parameters are what caused the issue this doesn't tell me exactly what I'm dealing with.

Here's one of the query functions I'm using:

public DataTable CallDtQuery(IfxCommand query)
{
  DataTable dt = new DataTable();
  using (IBM.Data.Informix.IfxConnection conn = new IfxConnection(sqlConnection))
  {
    try
    {
      IBM.Data.Informix.IfxDataAdapter adapter = new IfxDataAdapter();
      query.Connection = conn;
      conn.Open();
      adapter.SelectCommand = new IfxCommand("SET ISOLATION TO DIRTY READ", conn);
      adapter.SelectCommand.ExecuteNonQuery(); //Tells the program to wait in case of a lock.     
      adapter.SelectCommand = query;
      adapter.Fill(dt);
      conn.Close();
      adapter.Dispose();
      }
    catch (IBM.Data.Informix.IfxException ex)
    {
      LogError(ex, query.CommandText);
      SendErrorEmail(ex, query.CommandText);
      DisplayError();
    }
  }
  return dt;
}

Here's the logging function:

private void LogError(IfxException ex, string query)
{ //Logs the error.
  string filename = HttpContext.Current.Server.MapPath("~") + "/Logs/sqlErrors.txt";
  System.IO.FileStream fs = new System.IO.FileStream(filename, System.IO.FileMode.Append);
  System.IO.StreamWriter sw = new System.IO.StreamWriter(fs);

  sw.WriteLine("=======BEGIN ERROR LOG=======");
  sw.WriteLine(DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString());
  sw.WriteLine("Query = " + query);
  sw.WriteLine("User = " + HttpContext.Current.Session["UserID"]);
  sw.WriteLine("Error Message = " + ex.Message);
  sw.WriteLine("Message Source:");
  sw.WriteLine(ex.Source);
  sw.WriteLine("=============================");
  sw.WriteLine("Message Target:");
  sw.WriteLine(ex.TargetSite);
  sw.WriteLine("=============================");
  sw.WriteLine("Stack Trace:");
  sw.WriteLine(ex.StackTrace);
  sw.WriteLine("========END ERROR LOG========");
  sw.WriteLine("");

  sw.Close();
  fs.Close();
}

Is there a way to pass the entire string, parameters included, for logging as I have here? The only approach I've figured out that should work is to pass the query to the logging function and build a for loop to log each parameter as a separate item. As some of these queries have a number of parameters, and as I'd not be getting the complete query in one easy string, that's not really the most ideal solution.

Upvotes: 3

Views: 1935

Answers (2)

Ben
Ben

Reputation: 669

Rubens:

Since I want to pass the query both to the LogError and SendErrorEmail functions, I made a unique function for this approach. I also tweaked your version to automatically replace the parameter spots (for example, "cmt_slmno = ?" in the query) instead of making a list of parameters. Here's the result:

private string RecreateQuery(IfxCommand query)
{
  StringBuilder sb = new StringBuilder();
  sb.Append(query.CommandText);
  foreach (IfxParameter parameter in query.Parameters)
    sb.Replace(" ? ", string.Format(" {0} ", parameter.Value.ToString()));
  return sb.ToString();
}

And the correspondingly adjusted catch statement:

catch (IBM.Data.Informix.IfxException ex)
{
  string errorQuery = RecreateQuery(query);
  LogError(ex, errorQuery);
  SendErrorEmail(ex, errorQuery);
  DisplayError();
}

This approach has the potential problem of a rogue ? being replaced in the text, for example if the statement has a questionmark in a string it's trying to set. That should be very rare with the data I'm using in this application, and surrounding the question mark with spaces as above should cover all but the extremely rare, oddly-formatted string a user puts in.

Upvotes: 0

Rubens Farias
Rubens Farias

Reputation: 57996

What about:

// ...
catch (IBM.Data.Informix.IfxException ex)
{
    LogError(ex, query);  // NOTE
    SendErrorEmail(ex, query.CommandText);
    DisplayError();
}

And create an overload like this:

private void LogError(IfxException ex, IfxCommand query)
{
    StringBuilder sb = new StringBuilder();
    sb.Append(String.Format("{0}\n", query.CommandText));
    foreach (IDataParameter parameter in query.Parameters)
        sb.Append(String.Format("\t{0} = {1}\n",
            parameter.ParameterName, parameter.Value));

    LogError(ex, sb.ToString());
}

Upvotes: 4

Related Questions