Link to home
Start Free TrialLog in
Avatar of pbenito
pbenito

asked on

Why aren't my Bind variables being processed correctly?

Hi,
 
   I have a table in Oracle 11g that holds an XML document (as XMLType) and another column tha contains a GUID that services as the primary key.  I have method that accepts a GUID and an XML document as an input and replaces the stored XML document that is associated with the submitted GUID (this is a simple udpate function).  

When I build the query withouth using Bind variables it works fine, but when I use bind variables the GUID never gets successfully matched with a GUID in the table and does not update anything in the tables (it doesn't throw any exceptions either).  Please see the below C# code that I have that does not work.  Any suggestions as to what I'm doing wrong?  

As I mentioned before, when I replace the string sqlStatement with "UPDATE DOCUMENT SET XML_DOC = :xmlDoc_var WHERE UUID = " + "'" + UUID + "'"; it works just fine.

Thanks,
Pat
public bool UpdateDoc(string xmlDocument, string UUID)
{
 
string sqlStatement = "UPDATE DOCUMENT SET XML_DOC = :xmlDoc_var WHERE UUID = :UUID_var";
 
//This is a helper class that returns an OracleConnection object
OracleConnection conn = Helper.getConnectionObject();
 
try
{
//Set the OracleCommand object and open the connection
OracleCommand oraCommand = new OracleCommand(sqlStatement, conn);
conn.Open();
 
//Create the OracleXmlType object and set it to the xmlDocument 
//passed into the method
OracleXmlType xmlType = new OracleXmlType(conn, xmlDocument);
 
//Set the bind variables
oraCommand.Parameters.Add(":xmlDoc_var", xmlType);
oraCommand.Parameters.Add(":UUID_var", UUID);
 
//Execute the query
int result = oraCommand.ExecuteNonQuery();
conn.Close();
oraCommand.Dispose();
return true;
}
 
catch (Exception e)
{
Helper.logError(e);
if (null != conn && conn.State == ConnectionState.Open) conn.Close();
return false;
}
 
}

Open in new window

Avatar of schwertner
schwertner
Flag of Antarctica image

Of course

"UPDATE DOCUMENT SET XML_DOC = :xmlDoc_var WHERE UUID = :UUID_var";

is not acceptable.

The compiler will not accept :UUID_var as bind variable.
It will accept it as hardcoded value

I mean the C runtime system, not Oracle.

So Oracle will get (and fail of course) for execution the statement

UPDATE DOCUMENT SET XML_DOC = :xmlDoc_var WHERE UUID = :UUID_var

verbatim (as is) instead the statement

UPDATE DOCUMENT SET XML_DOC = :xmlDoc_var WHERE UUID = :UUID_var

PLUS

the concrete values of both bind variables.

the same for the second binds :xmlDoc_var
Avatar of pbenito
pbenito

ASKER

I'm a bit confused.

So Oracle won't accept bind variables when used in SQL statements genreated via ODP.NET in C#?
I think it is not an Oracle problem. You cause this problem in your c# code.

See
http://www.guru.net.vn/PermaLink,guid,1cfcd9d6-3837-493d-87bb-a28be66ce3a5.aspx

Do you see that the guy after defining the parameters also provides values for these variables.
I didn't find where you pass values in both variables.
public bool CheckExistNews(string url)
        {
            bool ret = false;
            int ok=0;
            OracleCommand myCommand = new OracleCommand("PKG_Test.CheckExistNews", connection);
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.Add(new OracleParameter("newsurl", OracleType.VarChar, 500));
            myCommand.Parameters["newsurl"].Value = url;
            myCommand.Parameters.Add(new OracleParameter("checkvalue", OracleType.Number,1));
            myCommand.Parameters["checkvalue"].Direction = ParameterDirection.ReturnValue;
            
            try
            {
                if(connection.State!= ConnectionState.Open)
                    connection.Open();
                myCommand.ExecuteScalar();
                ok=Convert.ToInt32(myCommand.Parameters["checkvalue"].Value);
                if (ok!=0)
                    ret = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message,"CheckExistNews : Error");
            }
            finally
            {
                connection.Close();
            }
            return ret;
        }

Open in new window

http://www.codeproject.com/KB/database/CSharpAndOracletransactio.aspx

You can also see that every parameter you definine has also direction - Input or Output.
Avatar of pbenito

ASKER

My bind variables are being set with the statements:

oraCommand.Parameters.Add(":xmlDoc_var", xmlType);
oraCommand.Parameters.Add(":UUID_var", UUID);


xmlType and UUID are declared and set prior to these statements being executed.

Thanks for the links, but they reference stored procedures.  I'm not using stored procedures.

Thanks,
Pat
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial