none
How can I debug a parameterized query?

    Question

  • I have a parameterized query built.  I think it is working for some, it is failing on some saying "ORA-1858: a non-numeric character was found where a numeric was expected".  I am having trouble debugging the query.  If I break on my catch section and then I cannot view my sql statement.  If I break prior to that, I get break on every query, even if they don't fail.  I am not sure how to debug?  Suggestions?

            public OracleDataReader ExecuteParamReader(string sql, string sParam1, string sParam2, string sParam3)
            {
    
                try
                {
                    OracleDataReader reader;
                    OracleCommand cmd = new OracleCommand(sql, conn);
                    cmd.Parameters.Add("sParam1", OracleDbType.Varchar2).Value = sParam1;
                    if (!sParam2.Equals(""))
                    {
                        cmd.Parameters.Add("sParam2", OracleDbType.Varchar2).Value = sParam2;
                    }
                    if (!sParam3.Equals(""))
                    {
                        cmd.Parameters.Add("sParam3", OracleDbType.Date).Value = sParam3;
                    }
                    cmd.Prepare();
                    reader = cmd.ExecuteReader();
                    return reader;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    ViewSQL(sql, "Reader Query");
                }
                return null;
            }


    Wednesday, July 24, 2013 4:54 PM

Answers

  • Hi,

    just some guessing, as there is no SQL and schema information.

    You declare sParam3 as Date type but assigning a string:

     cmd.Parameters.Add("sParam3", OracleDbType.Date).Value = sParam3;

    You should convert the sParam3 to a Datetime or use a varchar2 parameter and a Oracle function like TO_DATE to convert it in the query.

    For better breakpoint handling: Move the declaration of OracleCommand cmd outside the try catch block, then you can set a breakpoint in the catch block and the CommandText property and its Parameters collection is accessible.

    Regards, Elmar

    Wednesday, July 24, 2013 6:33 PM
  • Hi,

    If it happens again, depending on your VS edition, you could likely use Intellitrace to see what is sent to Oracle. Else use whatever tool is used by Oracle to trace SQL statements. This way you'll start by seeing what exactly runs server side which would be my starting point for debugging (as this is an Oracle side error).


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    Tuesday, July 30, 2013 5:58 PM

All replies

  • Hi,

    just some guessing, as there is no SQL and schema information.

    You declare sParam3 as Date type but assigning a string:

     cmd.Parameters.Add("sParam3", OracleDbType.Date).Value = sParam3;

    You should convert the sParam3 to a Datetime or use a varchar2 parameter and a Oracle function like TO_DATE to convert it in the query.

    For better breakpoint handling: Move the declaration of OracleCommand cmd outside the try catch block, then you can set a breakpoint in the catch block and the CommandText property and its Parameters collection is accessible.

    Regards, Elmar

    Wednesday, July 24, 2013 6:33 PM
  • Yeah... I knew it was going to be a bit of a challenge.  The weird thing is that it works just fine on MOST until right at the end.  Then it fails on one.  I put a conditional breakpoint on the line and it breaks, but when I manually build the query based on the params, etc, it works fine.  I am stumped!
    Wednesday, July 24, 2013 6:48 PM
  • As "Elmar Boye" suggested you should convert the sParam3 to datetime. Probably you should write your code as below

    DateTime dt;
    if (DateTime.TryParse(sParam3, out dt) == true)
    {
        cmd.Parameters.Add("sParam3", OracleDbType.Date).Value = dt;
    }
    else
    {
        cmd.Parameters.Add("sParam3", OracleDbType.Date).Value = DBNull.Value;
    }
    


    Gaurav Khanna | Microsoft VB.NET MVP | Microsoft Community Contributor

    Wednesday, July 24, 2013 7:02 PM
  • ex.Message is not enough information. You need Message, StackTrace, InnerExceptions and possibly the other Fields of the Exception.

    And the way to get them is ex.ToString()

    Se here for more infor on proper exception handling:

    http://www.codeproject.com/Articles/9538/Exception-Handling-Best-Practices-in-NET


    Let's talk about MVVM: http://social.msdn.microsoft.com/Forums/en-US/wpf/thread/b1a8bf14-4acd-4d77-9df8-bdb95b02dbe2 Please mark post as helpfull and answers respectively.

    Wednesday, July 24, 2013 8:32 PM
  • Hi!

    try this:

     public static string _cliptext(SqlCommand objCommand)
            {
                string comand = objCommand.CommandText;
                for (int i = 0; i < objCommand.Parameters.Count; i++)
                {
                    comand = comand.Replace(objCommand.Parameters[i].ParameterName, objCommand.Parameters[i].Value.ToString());
                }
                        Clipboard.SetText(comand);
                return comand;
            }

    If usefull give de vote

    Wednesday, July 24, 2013 9:02 PM
  • Hi!

    try this:

     public static string _cliptext(SqlCommand objCommand)
            {
                string comand = objCommand.CommandText;
                for (int i = 0; i < objCommand.Parameters.Count; i++)
                {
                    comand = comand.Replace(objCommand.Parameters[i].ParameterName, objCommand.Parameters[i].Value.ToString());
                }
                        Clipboard.SetText(comand);
                return comand;
            }

    If usefull give de vote

    I am sorry but there is no other way to say it:

    This is the single worst and most stupid thing you an do with a Parametized query!

    You use Parametized query to add typesafety and avoid SQL-Injections that purley string-build queries are sp prone too. Your code takes a perfectly made Paramtized Query and makes it to an effectively strign build again:

    http://xkcd.com/327/


    Let's talk about MVVM: http://social.msdn.microsoft.com/Forums/en-US/wpf/thread/b1a8bf14-4acd-4d77-9df8-bdb95b02dbe2 Please mark post as helpfull and answers respectively.

    Wednesday, July 24, 2013 9:19 PM
  • In visual studio you can set the break point and on the left of right click on the red dot... there are options like break on a condition or break on a hit count.
    Wednesday, July 24, 2013 10:01 PM
  • In visual studio you can set the break point and on the left of right click on the red dot... there are options like break on a condition or break on a hit count.
    He knows. The problem is he calls that code from a loop and only one of the last transfers fails. He can't break on the dozen others.

    Let's talk about MVVM: http://social.msdn.microsoft.com/Forums/en-US/wpf/thread/b1a8bf14-4acd-4d77-9df8-bdb95b02dbe2 Please mark post as helpfull and answers respectively.

    Thursday, July 25, 2013 8:51 AM
  • <<<<<<<<<<<<<<<<Here is one weird way to pass information to an area you normally don't see but I've done it as a last resort and have found what I was looking for.

    public OracleDataReader ExecuteParamReader(string sql, string sParam1, string sParam2, string sParam3) { <<<<<<<<<<<<<<<<<Do some variable in this scope try {

    <<<<<<<<<<<<<<<<<<<,,In here set those out of scope variable with info... OracleDataReader reader; OracleCommand cmd = new OracleCommand(sql, conn); cmd.Parameters.Add("sParam1", OracleDbType.Varchar2).Value = sParam1;

    When in your catch area If you don't see those variables in the Locals tab type the variable names in the Watch tab.

    Thursday, July 25, 2013 11:46 PM
  • Okay... I made some adjustments to my code.  It was still acting badly.  I shut the machine off and took a night off.  The next day, everything was fine.  I am wondering if there was something weird stuck in cache in Oracle memory or something.  Who knows.  It was very strange though.  Thanks for all the thoughts.  Although parameterized queries are better for Oracle, they are quite the pain to run and debug.  

    Thanks,

    Dave

    Tuesday, July 30, 2013 5:50 PM
  • Hi,

    If it happens again, depending on your VS edition, you could likely use Intellitrace to see what is sent to Oracle. Else use whatever tool is used by Oracle to trace SQL statements. This way you'll start by seeing what exactly runs server side which would be my starting point for debugging (as this is an Oracle side error).


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    Tuesday, July 30, 2013 5:58 PM