vertica6.1.x the processing of super long strings

In Vertica5, columns of very long string types automatically jieduan the value of the string to the defined length in the database when inserting/updating, and then write the truncated string to the database, but perform the same operation after upgrading the Vertica and Vertica JDBC drivers to 6. You'll get an exception, look at the code below.

This creates a table with a string column length of 32 bytes

CREATE TABLE T1 (
	ID Integer not NULL,
	col1 Varchar (), col2 Varchar (),
	col3 Varchar
	()
	, CONSTRAINT c_primary PRIMARY KEY (ID)
);

Then look at the Java code, which uses the batch mode insert, which uses the batch mode primarily for better write performance

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 {//VERTICA5 driver//private static final String Driver = "Com.vertica.Driver";
	VERTICA6 driver 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 {test ();
		private static void Test () 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, "=========================================================");
			
			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;
 }
}

This inserts two records into the database table in batch mode, where the value of the col3 of the first record is less than the length defined in the schema, and the col3 value of the second record is greater than the schema.

First of all, using VERTICA5 JDBC Driver test on VERTICA5, the following results are obtained:

No exception and results: [1, 1]

The result is [1, 1] that two records are written successfully, the database is viewed, and the value of the Col3 column of the second record is truncated.

Then we tested it on the Vertica6 with a Vertica6 JDBC driver, and the results were as follows:

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.test (verticatest.java:45) at
	Com.googlecode.garbagecan.dbtest.vertica.VerticaTest.main (verticatest.java:22)

The code executes with an exception, and then we use the following code in the captured exception code block to get the results of the execution

			if (ex instanceof batchupdateexception) {
				System.out.println ("SQLException and Results:" + arrays.tostring (( Batchupdateexception) ex). Getupdatecounts ());
			

The result is [1,-3], indicating that the first record was executed successfully and the second failed. There should be no value in the database because the rollback operation was performed at the time of exception.

View the vertica6.1.x document Https://my.vertica.com/docs/6.1.x/HTML/index.htm#16701.htm, in the new version, you can only trancate strings that exceed the database definition. So there's no way to do it on your own. Here is the updated code:

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 {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 {test ();
		private static void Test () 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");
			Parametermetadata PMD = Ps.getparametermetadata ();
			String col3 = "=========================================================";
				if (Pmd.getparametertypename (4). Equalsignorecase ("VARCHAR")) {int maxLength = pmd.getprecision (4);
				if (col3.tostring (). Length () > maxLength) {col3 = col3.substring (0, maxLength);
			} ps.setobject (4, col3);
			
			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 Parametermetadata class is used to get the length of the column defined in the database, and then the length of the data is truncate according to the defined length and then added to the batch for execution. The following is the result of the modified code execution:

No exception and results: [1, 1]

The result is [1, 1] that two records are written successfully, the database is viewed, and the value of the Col3 column of the second record is truncated.

So, Vertica's 6.1.x version and previous version still have a lot of behavior inconsistency problem, this time in the process of upgrading also found a lot, the next opportunity to tidy up.