ADO.NET Parameter documentation is incorrect

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();          }      }  }

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

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

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

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

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!

Thanks again Chris

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)