ADO.NET Parameter documentation is incorrect

Neil_1

We are having issues getting parameters to work when using ADO.NET. It does not help that the documentation is incorrect.

The documentation is here: https://my.vertica.com/docs/6.1.x/HTML/index.htm#11722.htm

The last line of example code is clearly problematic, and cannot have been run.

command.Parameters["STATE"] = 'MA';

2 obvious things are incorrect here:

1. Single quotes are used to denote character literals in C# - see http://msdn.microsoft.com/en-us/library/aa691087(v=vs.71).aspx
2. The signature for the indexer method of the VerticaParameterCollection class (the type of the object exposed by command.Parameters) specifies that a VerticaParameter object is returned (public new VerticaParameter this[string name] { get; set; }). Neither a string literal, nor a character literal, may be implicitly cast to a VerticaParameter. because of this, the code provided in the example will not compile, even if the character literal is corrected to be a string literal.

Chris_C

Thanks for letting us know about this documentation problem. We'll correct this in the doc the next time we publish.

In the meantime, here is an example that shows you how you can use a parameter for inserts:

using System;  using System.Collections.Generic;  using System.Linq;  using System.Text;  using System.Data;  using Vertica.Data.VerticaClient;  namespace ConsoleApplication  {      class Program      {          static void Main(string[] args)          {              VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();              builder.Host = "MYHOST";              builder.Database = "MYDB";              builder.User = "MYDBADMIN";              builder.Password = "MYPASSWORD";              VerticaConnection _conn = new VerticaConnection(builder.ToString());              _conn.Open();              VerticaCommand command = _conn.CreateCommand();              //create table test(id INT, username VARCHAR, email VARCHAR, password VARCHAR);              command.CommandText = "INSERT INTO test VALUES(@ID, 'blah','blah','blah')";              VerticaParameter param1 = new VerticaParameter("ID", VerticaType.BigInt);              param1.Value = 100;              command.Parameters.Add(param1);              Int32 rowsAdded = command.ExecuteNonQuery();              Console.WriteLine(rowsAdded + " rows added!");              _conn.Close();          }      }  }

Neil_1

Hi Chris,

really appreciate your support with this.

Your example does work which is great.


I think that you just helped me solve the issue I had here. It seems to me that if I create the command before I open the Connection then I get the error described in this link, if I open it prior to this, as you describe then I don't get the error:

https://community.vertica.com/vertica/topics/parameterised_queries_using_ado_net_throw_exception_unl...).

1. Can you confirm that you see this behaviour also (so, move the connection.Open to immediately before the ExecuteNonQuery call on the command)?
2. Given that I don't get this behaviour when I use a non-Parameterised query can you confirm that this is only the case (needing to Open connection prior to creating a command) with parameterised queries?
3. Is this behaviour considered a bug? Idiomatic practice in .NET is to defer opening the connection to prior to the first commands execution, so as to minimise the time that the connection is acquired from the pool (see http://msdn.microsoft.com/en-us/library/dw70f090(v=vs.110).aspx for some examples). Certainly my team and I find this behaviour surprising.

Thanks

Neil_1

Further investigation would suggest that opening the connection prior to creation of the parameter is sufficient. Can you confirm?

This would fit with the behaviour I was seeing with regard to parameterless queries, as of course, I was opening the connection to execute them, and it then remained open when the parameterised queries were constructed.

Thanks

Chris_C

Hi Neil,

After some research, it turns out this was a defect in the 6.x ADO.net driver. The issue is resolved in the 7.x driver.

The workaround for 6.x, as you have discovered, is to call Open() on the connection before creating any parameters.

-Chris

Neil_1

Thanks for your support with this Chris.

Do you know if we can use the 7.x driver with 6.1.3 server?
Is there a list of known issues for the Vertica.Data.dll versions anywhere?

Thanks again, much appreciated,

Neil

Chris_C

Hi Neil,

Unfortunately, the 7.0 driver is not compatible with 6.x according to the supported platforms document:
https://my.vertica.com/docs/7.0.x/PDF/HP_Vertica_7.0.x_Supported_Platforms.pdf 

Known and fixed issues are available in the release notes. However, this particular issue was not listed as fixed in the 7.x release notes even though it is certainly fixed.

You can find the release notes here:

https://my.vertica.com/docs/6.1.x/HP_Vertica_6.1.3_Release_Notes.pdf
https://my.vertica.com/docs/7.0.x/PDF/HP_Vertica_7.0.1_Release_Notes.pdf 

Hope that helps!

Neil_1

Thanks again Chris

Neil_1

Having updated to 7 we still get this issue. I have put a gist up that reproduces the issue, details the exception, etc...

https://gist.github.com/NeilRobbins/11396515

(will cross-post to other thread also)