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

Try using ":paramName" instead of "paramName".

Answer 2

ODBC parameters (marked by the symbol ?) are bound by position, so you have to make sure that you add the OdbcParameters in the correct order. Their name is then unimportant, but I would suggest paramName, without the @ which is a SQL Server (or, rather, Microsoft) specific name format.

You could also try to use the Oracle parameter format, which should be recognized by the Microsoft ODBC for Oracle driver and would allow you binding by name instead (not 100% sure about this, though) :

• Replace ? by :paramName in your query.
• Name your parameter paramName.

Answer 3

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