Why does my ODBC SelectCommand get truncated when I add a parameter.

Question

I am using an ODBC connection to retrieve a data set in C#. I can see that the full command string is added to the connection when it is created.

OdbcDataAdapter dataAdapter = new OdbcDataAdapter(GetCommandString(), odbcConnection);

I am using the following line to add the parameter to the command string.

dataAdapter.SelectCommand.Parameters.Add("@Foo", OdbcType.Decimal).Value = foo2;

However, when using SQL Profiler I can see that only a portion of the command string actually makes it to SQL.

During testing we have seen that the full string will get passed in if we hard code a value into the string and remove the parameter line shown above.

Does using a parameter restrict the length of the string that I can pass though?

Edited - Adding info about SQL query: Here is an example of what the query looks like before the parameter is added.

DECLARE @foo decimal
SET @foo = ?
Select c1,c2,c3,c4 from table1 where id = @foo
Select b1,b2,b3,b4 from table1 where id = @foo
Select a1,a2,a3,a4 from table1 where id = @foo
Select t1,t2,t3,t4 from table1 where id = @foo

Answer 1

Is "@Foo" the right syntax for parameters using the OdbcCommand? ODBC treats parameters differently than ADO.NET. In ODBC, parameters are bound by position and all parameters use the placeholder '?' in the statement.

I'm not sure if OdbcCommand does some translation between named and positional parameters. Maybe it does, and it's failing to rebuild the command successfully.

Also, if you are only connecting to SQL Server consider just using SqlClient.

Answer 2

Are you setting scale and precision?

And what do you mean by truncate? Do you get this...

SELECT * FROM myTable WHERE decCol = 123

when you expect

SELECT * FROM myTable WHERE decCol = 123.456

or

SELECT * FROM myTable WHERE decCol = 123.456 AND OtherCol = 'bar'

Also, profiler will capture the whole query text. Which leads me to ask, if you can use SQL profiler why are you using ODBC?

Answer 3

From what you've shown it looks like you're on the right track, but "@Foo"is probably the wrong syntax, but I can't say without seeing the actual output from GetCommandString(). The ODBC syntax is:

int foo2 = 5;
OdbcDataAdapter dataAdapter = new OdbcDataAdapter("SELECT * FROM Bar WHERE Foo = ?", odbcConnection);
dataAdapter.SelectCommand.Parameters.Add("Foo", OdbcType.Decimal).Value = foo2;

http://msdn.microsoft.com/en-us/library/zxdcah9t.aspx

Answer 4

I agree with the others, that the @Foo syntax may not be correct. If your OdbcDriver understands @Foo as a parameter it won't be heeding its name and this may be leading you to write queries in a broken way.. Suppose this time youre trying to reuse a parameter (your other "working" queries do not feature this requirement)

SELECT * FROM table WHERE col = @Foo or col2 = @Foo

In Odbc will either not work or silently become equivalent to:

SELECT * FROM table WHERE col = ? or col2 = ?

You'll have to add parameter values for both params, you cannot just add one parameter called "@Foo" and have Odbc reuse the value.. Further, it matters not what your parameters are called, only that you add them in the right order:

cmd.Parameters.AddWithValue("col1Val", "foo")
cmd.Parameters.AddWithValue("column two value", "foo")

The name you give to parameters is solely a convenience for your reuse of them; the driver cares not what they are called

Switch to ? syntax and see if your issue persists