Value too long for column "TEXT_ CHARACTER VARYING(4000)

[SebastianAT]

Hello,

I have a spring-boot camunda application where i send messages to other running processes and inbetween i store Process Variables.
Now when trying to store a message with length over 4k it gives me the error:
Value too long for column "TEXT_ CHARACTER VARYING(4000).

Value too long for column “TEXT_ CHARACTER VARYING(4000)”: “'{”“D2"”:"“SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS… (4105)”; SQL statement:
insert into ACT_HI_VARINST (
ID_,
PROC_DEF_KEY_,
PROC_DEF_ID_,
ROOT_PROC_INST_ID_,
PROC_INST_ID_,
EXECUTION_ID_,
ACT_INST_ID_,
TENANT_ID_,
CASE_DEF_KEY_,
CASE_DEF_ID_,
CASE_INST_ID_,
CASE_EXECUTION_ID_,
TASK_ID_,
NAME_,
REV_,
VAR_TYPE_,
CREATE_TIME_,
REMOVAL_TIME_,
BYTEARRAY_ID_,
DOUBLE_,
LONG_,
TEXT_,
TEXT2_,
STATE_
)

After searching i tried to alter this column with this command:
ALTER TABLE ACT_HI_VARINST MODIFY TEXT_ VARCHAR(5000);
but also
ALTER TABLE ACT_RU_VARIABLE MODIFY TEXT_ VARCHAR(5000);
has had no effect. (File is in resource folder - its a sql file)

To generate a message i have this methods:

      private String serializeToJson(Map<String, Object> processVariables) {
        // TODO Auto-generated method stub
  StringBuilder jsonBuilder = new StringBuilder();
  jsonBuilder.append("{");
  for(Map.Entry<String, Object> entry : processVariables.entrySet()) {
  	String key = entry.getKey();
  	Object value = entry.getValue();
  	
  	jsonBuilder.append("\"")
  			.append(key)
  			.append("\":\"")
  			.append(value)
  			.append("\",");
  }
  
  jsonBuilder.deleteCharAt(jsonBuilder.length()-1);
  jsonBuilder.append("}");
  return jsonBuilder.toString();

}

private static String generateMessage(int size, boolean isKB) {
  StringBuilder message = new StringBuilder();
  int messageSize = 0;
  if(isKB) {
  	messageSize = size * 1024;
  } else {
  	messageSize = size * 1024 * 1024;
  }
  
  for(int i = 0; i < messageSize; i++) {
  	message.append("S");
  }
  
  return message.toString();

}

In a delegate execution class i call this methods and then i set result as process variable like this:

Map<String, Object> processVariables = new HashMap<>();
processVariables.put(doID, generateMessage(4, true));
String serialzeMessage = serializeToJson(processVariables);
execution.setVariable(doID, serialzeMessage);

In this case the code produces a 4kB message as String so over 4k chars.
What can i do? Change the size of the column field TEXT_ , make it a CLOB? How to?

Best regards
Sebastian

[aravindhrs]

@SebastianAT You can try like this:

Storing large text value into application variable store

@AmeyaShetti you should store the process variables as Objects You should try to use the Typed Variable API . ObjectValue typedObjectValue = Variables.objectValue(responseObject).create(); runtimeService.setVariable(execution.getId(), "response", typedObjectValue);

or

Set the log levels as:

 <logger name="org.camunda.bpm.engine.cmd" level="debug" />
  <logger name="org.camunda.bpm.engine.impl.persistence.entity" level="debug" />
  <logger name="org.camunda.bpm.engine.impl.history.event" level="debug" />
  <logger name="org.camunda.bpm.engine.impl.batch.history" level="debug" />
  <logger name="org.camunda.bpm.engine.impl.batch" level="debug" />

[SebastianAT]

ok wait i found another way but it works not for every variable name.

I have it like this:

String largeContent = generateLargeMessage(4, true);
String json = “{"data" : "”+largeContent+“",” + “}”;
System.out.println("json_ " + json);
JsonValue jsonValue = SpinValues.jsonValue(json).create();
execution.setVariable(dataObject.getName(), jsonValue);

Which will make this write:

But somehow when the name of the variable is equal to the name of the DataObject [D2]{GP} it doesnt let me store. If i have the name to “foo” it works.

This is the Process:

What can be the reason for this? Even if i only use “D2” it doesnt let me store but “D3” is ok.

If i change to .setVariable(“D3”, jsonValue) it works:

Pom:

4.0.0

com.example.workflow
P1
1.0.0-SNAPSHOT

UTF-8 14 14 org.springframework.boot spring-boot-dependencies 2.7.3 pom import

  <dependency>
    <groupId>org.camunda.bpm</groupId>
    <artifactId>camunda-bom</artifactId>
    <version>7.18.0</version>
    <scope>import</scope>
    <type>pom</type>
  </dependency>
</dependencies>

org.camunda.bpm.springboot camunda-bpm-spring-boot-starter-rest

<dependency>
  <groupId>org.camunda.bpm.springboot</groupId>
  <artifactId>camunda-bpm-spring-boot-starter-webapp</artifactId>
</dependency>

<dependency>
  <groupId>org.camunda.bpm</groupId>
  <artifactId>camunda-engine-plugin-spin</artifactId>
  <scope>provided</scope>
</dependency>

<dependency>
  <groupId>org.camunda.spin</groupId>
  <artifactId>camunda-spin-dataformat-all</artifactId>
  <scope>provided</scope>
</dependency>

<!-- <dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-web</artifactId>
</dependency>-->

<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
</dependency>

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

<dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-amqp</artifactId>
  </dependency>

org.projectlombok lombok 1.18.22 provided

  <!-- https://mvnrepository.com/artifact/org.json/json -->
  
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>8.0.28</version>
</dependency>

org.json json 20160810

  <dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-api</artifactId>
<version>2.16.0</version>

org.apache.logging.log4j log4j-core 2.16.0 org.springframework.boot spring-boot-maven-plugin 2.7.3

any advice here?

[jonathan.lukas]

Hello @SebastianAT ,

could your generated string contain character that should be escaped in a json string?

Jonathan

[SebastianAT]

Hey actually not.
but i found a solution, thanks to @aravindhrs

This is my write:

String largeContent = generateLargeMessage(4, true);
String json = “{"data" : "”+largeContent+“",” + “}”;
System.out.println("json_ " + json);
ObjectValue typedObjectValue = Variables.objectValue(json).create();
execution.setVariable(doID, typedObjectValue);

This is my read:

ObjectValue retrievedTypedObjectValue = execution.getVariableTyped(doID);
System.out.println("read content: " + retrievedTypedObjectValue.getValue().toString() + “\n”);

Now this works. But why the other solution is not working is a mystery to me.

[SebastianAT]

Uh I found a solution with JSON also:

Writing:

SpinJsonNode spinJsonNode = SpinJsonNode.JSON(json);
execution.setVariable(doID, spinJsonNode);

Reading:

JacksonJsonNode jsonNode = (JacksonJsonNode) execution.getVariableTyped(doID, true).getValue();
SpinJsonNode spinJsonNode = SpinJsonNode.JSON(jsonNode.toString());

Thanks for your help