multiple statements with parameters in ado .net client

Anishek_Agarwal

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 ")"

Neil_1

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?