Using Parameters with an Oracle ODBC Connection

Question

I'm connecting succesfully to an Oracle 10g DB with an the Microsoft ODBC for Oracle driver.

Regular queries without parameters work fine, but parameterized queries act as if the parameters aren't getting passed in.

ex.

--this works fine
Select * from tbl1 where column1 = 'test'

--this doesn't
select * from tbl1 where column1 = ?

--odbc string parameter 'test'

Here's what my connection string looks like:

"Driver={Microsoft ODBC for Oracle}; " & _
 "CONNECTSTRING=(DESCRIPTION=" & _
 "(ADDRESS=(PROTOCOL=TCP)" & _
 "(HOST=" & pstrServer & ")(PORT=" & pintPort.ToString & "))" & _
 "(CONNECT_DATA=(SERVICE_NAME=" & pstrPhysicalName & "))); " & _
 "uid=" & pstrUserName & ";pwd=" & pstrPassword & ";"

And I'm adding parameters to my ODBC command like this:

arrOdbcParam(index) = New OdbcParameter("@paramName", paramValue)

...

cmd.Parameters.AddRange(arrOdbcParam)

Forgive the partialy copied, somewhat pseuedo code.

Answer 1

Bit of necromancing here, but since I just struggled with a similar Problem, here is how it worked with the ODBC-driver for Centura SQLBase:

OdbcCommand com = con.CreateCommand();
com.CommandText = @"
  SELECT  thing
  FROM    table
  WHERE   searchInt = ? AND searchDat = ?";
com.Parameters.Add(new OdbcParameter("", OdbcType.Int)).Value = 12345;
com.Parameters.Add(new OdbcParameter("", OdbcType.DateTime)).Value = DateTime.Now;
OdbcDataReader reader = com.ExecuteReader();

This searches in "table" for records with the value 12345 in "searchInt" and todays date in "serachDat".
Things to note:

• Parameters are marked as ? in the SQL command
• Parameters need no name, but position (and the correct type) are important