2

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.

Gaidin
  • 1,381
  • 3
  • 13
  • 19

3 Answers3

5

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
Vincent Robert
  • 35,564
  • 14
  • 82
  • 119
Stephan Keller
  • 1,623
  • 11
  • 13
  • Found this solution and it worked for me, with one note for future visitors that find this question: I was using single ticks around my where criteria ( where mystring = '?' ); you do not need them. (Note that the asker did not use them, either.) (Stephan, this really has nothing to do with your answer in particular) – Lynn Crumbling May 10 '12 at 15:02
3

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.
Mac
  • 8,191
  • 4
  • 40
  • 51
  • Just tried this, and it still doesn't work. I keep getting "not all variables bound". – Gaidin May 07 '09 at 15:35
  • You cannot use named parameters with Microsoft ODBC for Oracle. The parameter must be specified using "?" and parameters added in the correct order. Try using AddWidthValue("",paramValue) instead of AddRange(). – Liao Dec 16 '09 at 07:59
2

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

ajh1138
  • 566
  • 3
  • 10
  • My parameters names are usually @paramName. is :paramName an Oracle syntax? I usually work more with SQL Server so I'm new to some of the variations of Oracle syntax. – Gaidin May 01 '09 at 14:28
  • 1
    @Gaidin A full ten years later, but I can confirm that this is the case - I also usually work with sql server, so that was a surprise to me. Oracle odbc syntax apparently uses ":variablename" in the query mapped to "variablename" (without the colon) in the parameter list. Wacky. – neminem Sep 24 '20 at 20:51