Vertica6.1.x对超长字符串的处理

在Vertica5中，对于超长的字符串类型的列在插入/更新的时候会自动将字符串的值揭短成数据库中定义长度，然后将截短后的字符串写入数据库，但是在升级了Vertica和Vertica JDBC驱动到6以后，再执行同样的操作，就会出异常，看下面代码

这里创建了一个表，其中字符串列的长度为32字节

CREATE TABLE t1 (
	id Integer NOT NULL,
	col1 Varchar(32),
	col2 Varchar(32),
	col3 Varchar(32),
	CONSTRAINT C_PRIMARY PRIMARY KEY (id)
);

然后看Java代码，这里使用的是Batch模式插入，使用Batch模式主要是为了有更好的写入性能

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;
	}
}

这里向数据库表中用Batch模式插入了两条记录，其中第一条记录的col3的值是小于schema中定义的长度的，而第二条记录的col3的值大于schema。

首先在Vertica5上用Vertica5的JDBC驱动测试，得出如下结果：

No exception and results: [1, 1]

返回结果是[1, 1]说明两条记录都写入成功，查看数据库，第二条记录的col3列的值被截断了。

然后我们在Vertica6上用Vertica6的JDBC驱动再测试一下，得出结果如下：

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)

代码执行的时候出了异常，然后我们在捕获的异常代码块中用下面的代码来获取执行的结果

			if (ex instanceof BatchUpdateException) {
				System.out.println("SQLException and results: " + Arrays.toString(((BatchUpdateException) ex).getUpdateCounts()));
			}

结果是[1, -3]，说明第一条记录执行成功，第二条执行失败。这里由于在出异常的时候执行了rollback操作，所以数据库里应该没有值。

查看Vertica6.1.x的文档https://my.vertica.com/docs/6.1.x/HTML/index.htm#16701.htm，在新版本中只能自己来trancate超过数据库定义的字符串，所以没办法只能自己干了。下面是更新后的代码：

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;
	}
}

其中的使用了ParameterMetaData类来获取数据库中定义的列的长度，然后根据定义的长度来truncate数据长度，然后再加入到Batch里执行。下面是修改后代码执行的结果：

No exception and results: [1, 1]

返回结果是[1, 1]说明两条记录都写入成功，查看数据库，第二条记录的col3列的值被截断了。

所以，Vertica的6.1.x的版本和之前版本还是有很多行为不一致的问题，这次在升级的过程中还发现了很多，下次有机会再整理一下。