Forum: Building VoltDB Applications

Post: voltDB JDBC insert byte array error?

voltDB JDBC insert byte array error?
wzj
Aug 24, 2015
hi,

Thanks for your voltDB!

I am using the voltDB jdbc client to insert data to db, I use the spring framework to batch insert, but it report java.sql.SQLException: Invalid argument value.

I am import data from MySQL to voltDB, these source code work fine in MySQL for Blob data type.

Please help me , thanks!

The exception as follow.
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO rec_obj_tag_info(memberId,workcity,sex,age,height,salary,education,marriage,t952,flag_tag,score_tag,add_tag,time_tag) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)]; SQL state [s1009]; error code [0]; Invalid argument value.; nested exception is java.sql.SQLException: Invalid argument value.
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO rec_obj_tag_info(memberId,workcity,sex,age,height,salary,education,marriage,t952,flag_tag,score_tag,add_tag,time_tag) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)]; SQL state [s1009]; error code [0]; Invalid argument value.; nested exception is java.sql.SQLException: Invalid argument value.
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:657)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:670)
        at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:986)
        at org.springframework.jdbc.core.BatchUpdateUtils.executeBatchUpdate(BatchUpdateUtils.java:32)
        at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1036)
        at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1031)
        at com.zhenai.recommend.modules.arithmetic.dao.impl.VoltDBTagInfoByteDaoImpl.replaceMemObjTagInfoByte(VoltDBTagInfoByteDaoImpl.java:76)
        at com.zhenai.recommend.commons.ExportESDataToMemsqlService.exportMySqlData(ExportESDataToMemsqlService.java:201)
        at com.zhenai.recommend.commons.ExportESDataToMemsqlService.access$400(ExportESDataToMemsqlService.java:24)
        at com.zhenai.recommend.commons.ExportESDataToMemsqlService$2$1.call(ExportESDataToMemsqlService.java:150)
        at com.zhenai.recommend.commons.ExportESDataToMemsqlService$2$1.call(ExportESDataToMemsqlService.java:147)
        at java.util.concurrent.FutureTask.run(FutureTask.java:262)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        at java.lang.Thread.run(Thread.java:744)
Caused by: java.sql.SQLException: Invalid argument value.
        at org.voltdb.jdbc.SQLError.get(SQLError.java:40)
        at org.voltdb.jdbc.JDBC4PreparedStatement.setNull(JDBC4PreparedStatement.java:447)
        at sun.reflect.GeneratedMethodAccessor85.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)
        at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)
        at $java.sql.Wrapper$$EnhancerByProxool$$45b6af5b.setNull(<generated>)
        at org.springframework.jdbc.core.StatementCreatorUtils.setNull(StatementCreatorUtils.java:303)
        at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:232)
        at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:166)
        at org.springframework.jdbc.core.BatchUpdateUtils.setStatementParameters(BatchUpdateUtils.java:65)
        at org.springframework.jdbc.core.BatchUpdateUtils$1.setValues(BatchUpdateUtils.java:39)
        at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:996)
        at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:986)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:641)




The insert source code is:
voltDBJdbcTemplate.batchUpdate(getReplaceSql(tableName), pra);
Object[] op = new Object[]{memberId,
                                           workCity,
                                           memObjTagInfoByte.getSex(),
                                           memObjTagInfoByte.getAge(),
                                           memObjTagInfoByte.getHeight(),
                                           memObjTagInfoByte.getSalary(),
                                           memObjTagInfoByte.getEducation(),
                                           memObjTagInfoByte.getMarriage(),
                                           lastLoginTime,
                                           /*ByteUtil.toBytes(10),
                                           ByteUtil.toBytes(10),
                                           ByteUtil.toBytes(10),
                                           ByteUtil.toBytes(10),*/
                                           memObjTagInfoByte.getFlagTag(),
                                           memObjTagInfoByte.getScoreTag(),
                                           memObjTagInfoByte.getAddTag(),
                                           memObjTagInfoByte.getTimeTag()
                };



where par is a list of Object[] op array.
memObjTagInfoByte.getFlagTag()

return byte array
public byte[] getFlagTag() {
		return flagTag;
	}
public byte[] getScoreTag() {
		return scoreTag;
	}


The create table ddl:

CREATE TABLE rec_obj_tag_info (
  memberId INTEGER  NOT NULL,
  workCity INTEGER  NOT NULL,
  sex SMALLINT  ,
  age SMALLINT  ,
  height SMALLINT  ,
  salary SMALLINT ,
  education SMALLINT ,
  marriage SMALLINT ,
  t952 BIGINT ,
  flag_tag VARBINARY(256),
  score_tag VARBINARY(256),
  add_tag VARBINARY(256),
  time_tag VARBINARY(256)
 );
 PARTITION TABLE rec_obj_tag_info on COLUMN workCity;
pzhao
Aug 24, 2015
wzj,

From looking at the stack trace, it seems like you are trying to assign a null value to a field with an unsupported data type, blob data type? If blob data type being used, try changing them to VARBINARY type and see if that works for you. Here's some documentation on supported VoltDB datatype.

Peter Zhao
wzj
Aug 25, 2015
thanks. From the create table ddl, you can see I am using varbinary for the binary field.
wzj,

From looking at the stack trace, it seems like you are trying to assign a null value to a field with an unsupported data type, blob data type? If blob data type being used, try changing them to VARBINARY type and see if that works for you. Here's some documentation on supported VoltDB datatype.

Peter Zhao
pzhao
Aug 26, 2015
wzj,

The definition of the ddl table is fine, but the problem occurs when your passing your java arguments into VoltDB. Check that you have the compatiable Java datatypes prior to your batch insert into VoltDB.

Peter Zhao
hnoor0033
Feb 6, 2016
I am import data from MySQL to voltDB, these source code work fine in MySQL for Blob data type.