Vertica client : running Multiple SQL Statements

markdane22

Hi, I am using Java Vertica jdbc client 7.1.2. When I execute any multi-statement query it fails with exception :
java.sql.SQLDataException: [Vertica]JDBC Exactly one result expected, but 2 results created from query "semicolon separated multiple queries". Query not executed.

I ran the same query mentioned here , under section 'Multiple SQL Statement Support'.

I tried adding 'allowMultiQueries=true' in jdbc URL, but it didn't work.

Code :
String url = "jdbc:vertica://myIP:5433/db?allowMultiQueries=true";
String sql = "CREATE TABLE t(a INT);INSERT INTO t VALUES(10);";
Properties myProp = new Properties();
myProp.put("user", "user");
myProp.put("password", "pass");
Connection dbConnection = DriverManager.getConnection(url,myProp);
Statement statement = dbConnection.createStatement();
statement.executeUpdate(sql);

Any help would be appreciated.

s_crossman

Mark,

I think what you are looking for is the MultipleActiveResultSets property which was introduced in 7.2.0. This is covered in the 7.2 docs under the Connecting to Vertica > Client Libraries > Managing Query Execution between Client Drivers and Vertica section. The synopsis is "MARS allows the execution of multiple queries on a single connection. While ResultBufferSize sends the results of a query directly to the client, MARS stores the results first on the server. Once query execution has finished and all of the results have been stored, you can make a retrieval request to the server to have rows returned to the client."

A couple notes:
The release of Vertica 9.0 in fall 2017 marks the end of support for version 7.2.3. Please plan your upgrades accordingly.

I couldn't find any references to the property allowMultipleQueries, so not sure where that originated from but it doesn't seem to be a Vertica created property.

I hope it helps,