Cannot deserialize object in variable 'test1': SPIN/JACKSON-JSON-01007 Cannot construct java type from string 'java.util.ArrayList<groovy.sql.GroovyRowResult<java.lang.Object,java.lang.Object>>'

Hi .It seems my question to be duplicate but I couldn’t infer the answer.
I had read an array of object by inline groovy script and sat my variable like this :

import groovy.sql.Sql
import java.util.logging.Logger;
import org.camunda.bpm.engine.variable.Variables;
import org.camunda.bpm.engine.variable.value.ObjectValue;
 Logger LOGGER = Logger.getLogger("personnel");

    def dbPwkaraURL="jdbc:sqlserver://192.168.1.199:1433;databaseName=CamundaPersonel"
    def dbPwkaraUser="sa"
    def dbPwkaraPass="abc405060!"
    def dbDriver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
    def db = Sql.newInstance(dbPwkaraURL,dbPwkaraUser,dbPwkaraPass,dbDriver)
    //def rows = db.rows('select family,secNo from personnel where perId='+"$perID")
    def rows = db.rows('select * from personnel');
    ObjectValue serializedList = Variables.objectValue(rows)
      .serializationDataFormat(Variables.SerializationDataFormats.JSON)
      .create(); 
    execution.setVariable("test1", serializedList);

After that I need to retrieve the “test1” variable and apply some operations . on

   		  execution.getVariable("test1")

command I face this error message :

 Caused by: org.camunda.spin.json.SpinJsonDataFormatException: SPIN/JACKSON-JSON-01007 Cannot construct java type from string 'java.util.ArrayList<groovy.sql.GroovyRowResult<java.lang.Object,java.lang.Object>>'
        at org.camunda.spin.impl.json.jackson.JacksonJsonLogger.unableToConstructJavaType(JacksonJsonLogger.java:76)
        at org.camunda.spin.impl.json.jackson.format.JacksonJsonDataFormat.constructJavaTypeFromCanonicalString(JacksonJsonDataFormat.java:167)
        at org.camunda.spin.impl.json.jackson.format.JacksonJsonDataFormatMapper.mapInternalToJava(JacksonJsonDataFormatMapper.java:83)
        at org.camunda.spin.plugin.impl.SpinObjectValueSerializer.deserializeFromByteArray(SpinObjectValueSerializer.java:101)
        at org.camunda.bpm.engine.impl.variable.serializer.AbstractObjectValueSerializer.deserializeFromByteArray(AbstractObjectValueSerializer.java:119)
        at org.camunda.bpm.engine.impl.variable.serializer.AbstractSerializableValueSerializer.readValue(AbstractSerializableValueSerializer.java:83)
        ... 228 more
Caused by: java.lang.IllegalArgumentException: Cannot create TypeBindings for class groovy.sql.GroovyRowResult with 2 type parameters: class expects 0
        at spinjar.com.fasterxml.jackson.databind.type.TypeBindings.create(TypeBindings.java:139)
        at spinjar.com.fasterxml.jackson.databind.type.TypeBindings.create(TypeBindings.java:98)
        at spinjar.com.fasterxml.jackson.databind.type.TypeBindings.create(TypeBindings.java:87)
        at spinjar.com.fasterxml.jackson.databind.type.TypeParser.parseType(TypeParser.java:54)
        at spinjar.com.fasterxml.jackson.databind.type.TypeParser.parseTypes(TypeParser.java:68)
        at spinjar.com.fasterxml.jackson.databind.type.TypeParser.parseType(TypeParser.java:53)
        at spinjar.com.fasterxml.jackson.databind.type.TypeParser.parse(TypeParser.java:33)
        at spinjar.com.fasterxml.jackson.databind.type.TypeFactory.constructFromCanonical(TypeFactory.java:582)
        at org.camunda.spin.impl.json.jackson.format.JacksonJsonDataFormat.constructJavaTypeFromCanonicalString(JacksonJsonDataFormat.java:165)
        ... 232 more

You have set the serialization format to SerializationDataFormats.JSON, but rows is not actually serialized. To serialize the rows try like this:

import groovy.json.JsonOutput

//other implementations

def rows = db.rows('select * from personnel');
String serializedRows = JsonOutput.toJson(rows);
ObjectValue serializedList = Variables.objectValue(serializedRows)
      .serializationDataFormat(Variables.SerializationDataFormats.JSON)
      .create(); 
execution.setVariable("test1", serializedList);

Hi @aravindhrs thanks for your attention.
I must say I have no problem in serialization. actually the error occurs in deserialization .
Any way I’ve added your code in setting variable but now its throwing new exception

> Unable to evaluate script while executing activity 'FillForm' in the process definition with id 'WH_Sample:9:eb480054-9b4e-11ea-bea6-003048b2ff0b': java.lang.StringIndexOutOfBoundsException: begin 1, end 2, length 1

@Ali_Titan if you are using that variable, then it should be deserialized and assign it to the variable before use.

After serialization how does the data looks like?

@aravindhrs let me describe it from the beginning
In the process start script. I fetch some data as an array and serialize the table data to JSON format like you see with no problem . then I will need this data in the next user task which I should show this data to user so I set table data (table data) as test1 variable. in the task form I have it as json object in javascript and no problem.But in the next task which I want to access this ‘test1’ variable the deserialization error occurs .
You said that I must serialize the rows first but I don’t think so or may be didn’t understand you.
Variable status in cockpit

image

my bpmn file :
WareHouseSample (1).bpmn (7.5 KB)

@Ali_Titan, you’re directly storing the db resultSet object. This is not correct approach I believe. This will lead to deserialization issues. Populate the values in pojo and serialize the pojo to persist. For deserialization make sure that same pojo exists in camunda server classpath.

@aravindhrs
Hi again
I’ve switched from groovy script to Java class . I use a POJO class to persist as you said .
but then again I have deserialization error. you’ve mentioned than to be sure same POJO must be existed in camunda server classpath. I didn’t get that to what to do.
error shows up in cockpit :
Cannot deserialize object in variable 'test1': SPIN/JACKSON-JSON-01007 Cannot construct java type from string 'java.util.LinkedList<com.camunda.demo.test.PersonnelModel>'

@Ali_Titan For deserialization, you should have the below class in camunda classpath.

com.camunda.demo.test.PersonnelModel

Serialization:

ObjectValue typedObjectValue = Variables
  .objectValue(order)
  .serializationDataFormat(Variables.SerializationDataFormats.JAVA)
  .create();

Access serialized properties:

// returns true
boolean isDeserialized = retrievedTypedObjectValue.isDeserialized();

// returns the format used by the engine to serialize the value into the database
String serializationDataFormat = retrievedTypedObjectValue.getSerializationDateFormat();

// returns the serialized representation of the variable; the actual value depends on the serialization format used
String serializedValue = retrievedTypedObjectValue.getValueSerialized();

// returns the class com.example.Order
Class<com.example.Order> valueClass = retrievedTypedObjectValue.getObjectType();

// returns the String "com.example.Order"
String valueClassName = retrievedTypedObjectValue.getObjectTypeName();

Deserialization:

String serializedOrder = "...";
ObjectValue serializedValue =
  Variables
    .serializedObjectValue(serializedOrder)
    .serializationDataFormat(Variables.SerializationDataFormats.JAVA)
    .objectTypeName("com.example.Order")
    .create();

runtimeService.setVariableLocal(execution.getId(), "order", serializedValue);

ObjectValue retrievedTypedObjectValue = runtimeService.getVariableTyped(execution.getId(), "order");
com.example.Order retrievedOrder = (com.example.Order) retrievedTypedObjectValue.getValue();

Read about Serializing Process Variables


Custom Java objects can be serialized with the value type object:
https://docs.camunda.org/manual/latest/user-guide/process-engine/variables/#object-values


Object Value Serialization:
https://docs.camunda.org/manual/latest/user-guide/process-engine/variables/#object-value-serialization