multiple statements with parameters in ado .net client

i am trying to execute multiple statements via a single command having parameters. They pass when executed independently but fail when executed as single query, Any idea as to why this is happening. Sample below.

In the schema create a table as

create table test(id integer not null);

in Ado .NET with version 6.1.3  execute the following code.


 const string query = "insert into test(id) values (@anumber) ; select id from test limit 1;";

            using (var connection = new VerticaConnection(TestUtils.DatabaseConnectionString))
            using (var command = connection.CreateCommand())
            {
                command.CommandText = query;
                connection.Open();
                command.Parameters.Add(new VerticaParameter("anumber", VerticaType.BigInt, 12));
                command.ExecuteScalar();
            }


and it gives me the following error :

Vertica.Data.VerticaClient.VerticaException : [42601] ERROR: Syntax error at or near ")"
  ----> Vertica.Data.Internal.Util.ServerException : ERROR: Syntax error at or near ")"



Comments

  • I find that if I don't have a parameterised query then multiple statements like this succeeds, but as soon as I add a parameter to the command, then I get this error.

    In the 'JDBC Feature Support' page (https://my.vertica.com/docs/6.1.x/HTML/index.htm#16749.htm) it states that 
    Multiple SQL Statement Support
    The HP Vertica JDBC driver supports executing strings containing multiple statements. For example:
    stmt.executeUpdate("CREATE TABLE t(a INT);INSERT INTO t VALUES(10);");
    Only the Statement interface supports executing strings containing multiple SQL statements. You cannot use multiple statement strings with PreparedStatement.
    In the Vertica ADO.NET documentation on 'Using Parameters' (https://my.vertica.com/docs/6.1.x/HTML/index.htm#11722.htm) it states:
    Note: Although the VerticaCommand class supports a Prepare() method, you do not need to call the Prepare() method for parameterized statements because HP Vertica automatically prepares the statement for you.
    Does this explain what we are seeing?

    Is it possible to prevent Prepare() being called for a parameterised query?
    Is this fixed in 7.x?
    Is this a known issue?

Leave a Comment

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