Talk about Vertica5 and vertica6.1.x handling of write errors and exceptions

Vertica6 It is true that the error or exception handling and previous versions are quite different when writing to the database, but it is undeniable that the support for the new version is more reasonable and slowly closer to the JDBC specification. Let's see what the difference is between VERTICA5 and 6.

The following is a schema for database tables

CREATE TABLE public.t1 (
	ID int8 not NULL,
	col1 varchar (a) NOT NULL,
	col2 varchar (a) NOT NULL,
	col3 VARC Har not NULL,
	CONSTRAINT "C_primary" PRIMARY KEY (ID)
);

Here is the Java class used for testing, which defines the JDBC configuration information used in VERTICA5 and 6 situations, and requires a manual switch when switching databases.

Package Com.googlecode.garbagecan.dbtest.vertica;
Import java.sql.BatchUpdateException;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import Java.sql.ParameterMetaData;
Import java.sql.PreparedStatement;
Import java.sql.SQLException;

Import Java.util.Arrays;
	public class Verticatest {//Vertica 5X private static final String Driver = "Com.vertica.Driver";
	Private static final String URL = "Jdbc:vertica://perf4:5552/fkong2";
	Private static final String username = "PA80";
	
	Private static final String password = "Fkong2";
Vertica 6X//private static final String Driver = "Com.vertica.jdbc.Driver";
Private static final String URL = "JDBC:VERTICA://PADEV3:5433/PADB";
Private static final String username = "Pauser";

	Private static final String password = "Papassword";
		public static void Main (string[] args) throws Exception {Connection conn = getconnection ();
		
		PreparedStatement PS = null; try {PS = conn.preparestatement (INSERT into T1 (ID, COL1, col2, col3) VALUES (?,?,?,?) ");
			Ps.setint (1, 1);
			Ps.setobject (2, "value1");
			Ps.setobject (3, "value2");
			Ps.setobject (4, "value3");
			
			Ps.addbatch ();
			Ps.setint (1, 2);
			Ps.setobject (2, "value1");
			Ps.setobject (3, "value2");       Ps.setobject (4, NULL);   Duplicate primary key//Ps.setnull (4, types.null);
			
			Duplicate primary key ps.addbatch ();
			Int[] results = Ps.executebatch ();
			
			System.out.println ("No Exception and Results:" + arrays.tostring (results));
		Conn.commit (); } catch (SQLException ex) {if (ex instanceof batchupdateexception) {System.out.println ("SQLException and results:
			"+ arrays.tostring ((batchupdateexception) ex). Getupdatecounts ()));
			} conn.rollback ();
		Throw ex;
		finally {conn.close ();
		} private static Connection getconnection () throws Exception {Class.forName (driver);
		Connection conn = drivermanager.getconnection (URL, username, password);
		Conn.setautocommit (FALSE); Return conn;
	}
} 

The code above needs to insert a two-day record into the database table, where the first one should be written correctly, and the second we artificially write the col3 column to null, which is not NULL in the schema, so the second data should be written to fail. And the behavior we expect is that there should be exceptions thrown. Let's take a look at what is going on in concrete execution.

First run on the VERTICA5, the results are as follows

No exception and results: [1,-3]

This result indicates that the first record was written successfully, the second write failed, and no exception occurred, so the program finally performed the Conn.commit () operation, checking the database, the first record in the database was written successfully, and the second data was not written successfully.

So for VERTICA5, we can't just rely on whether or not to throw an exception to determine whether there is an error, but to examine the exception based on the results of the check Ps.executebatch () to see if there are errors.

Let's switch to VERTICA6 to see the following results

SQLException and results: [1,-3]
Exception in thread "main" java.sql.BatchUpdateException: [Vertica][vjdbc] (100172 One or more rows were rejected by the server.
	At Com.vertica.jdbc.SStatement.processBatchResults (Unknown Source) at
	Com.vertica.jdbc.SPreparedStatement.executeBatch (Unknown Source) at
	Com.googlecode.garbagecan.dbtest.vertica.VerticaTest.main (verticatest.java:44)

First out of the ordinary, this is the behavior we expect, and then look at the result of the write from the exception, found that the first write succeeded, the second write failed, so the program executed the Conn.rollback () operation to roll back all the records. This behavior is consistent with what we normally do with JDBC programming on other databases.

So while the VERTICA6 is written with a great deal of error or exception handling and previous versions, it is true that these updates are more reasonable and more in line with the JDBC specification.