Options

ADO.NET Parameter documentation is incorrect

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.

Comments

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


  • Options
    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
  • Options
    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
  • Options
    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
  • Options
    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!
  • Options
    Thanks again Chris
  • Options
    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)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file