OLEDB Query Problems

.NET
1

I’m using the OLEDB class to query an Oracle database and I’m seeing something strange. I’m query the database using a string with an escape character, which seems to work fine if I perform a query without a variable:


string oracleCommand = "SELECT * FROM Access_List WHERE NT_ID='DOMAIN\\\\JOHNSONBL.ADM' AND Application_Text='MyApp'";

The above query properly returns my row, but if I introduce a variable, for the NT_ID column, the query no longer returns my row


string oracleCommand = "SELECT * FROM Access_List WHERE NT_ID='@NT_ID' AND Application_Text='MyApp'";

I’m absolutely certain that I’m loading my variable with the exact string that I search for in the first query above (“'DOMAIN\\JOHNSONBL.ADM”), yet no results are returned. Here is the full code:


public static AccessList GetUser(string nt_id)
    {
      OleDbConnection oracleConn = new OleDbConnection(WebConfigurationManager.ConnectionStrings["OracleServer"].ConnectionString);

      // returns a row
      string oracleCommand = "SELECT * FROM Access_List WHERE NT_ID='DOMAIN\\\\JOHNSONBL AND Application_Text='MyApp'";
      // doesn't return a row
      string oracleCommand = "SELECT * FROM Access_List WHERE NT_ID='@NT_ID' AND Application_Text='MyApp'";

      OleDbCommand cmd = new OleDbCommand(oracleCommand, oracleConn);
      // add the necessary parameter
      cmd.Parameters.AddWithValue("@NT_ID", nt_id);

      AccessList targetAccessList = new AccessList();
      try
      {
        oracleConn.Open();
        OleDbDataReader reader = cmd.ExecuteReader();
        if (reader.Read())
        {
          targetAccessList.NT_ID = reader["NT_ID"].ToString();
          targetAccessList.EDIPI = reader["EDIPI"].ToString();
          targetAccessList.Billet = reader["Billet_Description_Text"].ToString();
          targetAccessList.Application = reader["Application_Text"].ToString();
        }
        else
        {
          targetAccessList = null;
        }

        // always call Close when done reading.
        reader.Close();
      }
      catch (Exception ex)
      {
        // log exception
      }
      finally
      {
        // always call Close when done reading.
        oracleConn.Close();
      }
      return targetAccessList;
    }

Thanks

2

No particular reason, just wanted to try to make it work with the native providers. I was able to solve the problem by using a ‘:’ instead of a ‘@’:

string oracleCommand = “SELECT * FROM Access_List WHERE NT_ID=‘:NT_ID’ AND Application_Text=‘MyApp’”;

Another strange finding… if you have a query with multiple parameters, you must make sure you load the parameters in the correct order when building your parameter list (you must add them to the parameter list in the same order that they appear in the query).

3

Why aren’t you using the dedicated Oracle data provider?

Anyways, you can u can use something like this:


OracleCommand objCmd = new OracleCommand();
objCmd.Connection = "oracleConn";
objCmd.CommandText = "Your Query";
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Parameters.Add("@NT_ID", OracleType.Number).Value = nt_id;

oracleConn.Open();
OracleDataReader reader = objCmd.ExecuteReader();