How to Increase The Default DB Column Length With Groovy Script in Hybris

I will mention how to increase default character length without any deployment source codes in the database column (MySQL, embedded hybris database, oracle, and so on).

As an example, when we will increase approval users for any workflow, we will face an issue to start workflow because of the length of the workflow template code. As you know, a lot of database column varchar length is 255 in hybris, so we may have to change the limit of character to start processing successfully.

As a result, we solved the problem with preparing a groovy script and execute it in the hybris administration console script menu as shown below image;

In our local hybris environment, I am using MySQL as a database and so, our groovy script to change character limit like that;

import de.hybris.platform.core.Registry;
import de.hybris.platform.core.Tenant;
import de.hybris.platform.util.Utilities;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.annotation.Resource;
import javax.sql.DataSource;

Connection conn = null;
PreparedStatement pstmt = null;

try{
final Tenant t = Registry.getCurrentTenant();
conn = t.getDataSource().getConnection();

stmt = conn.prepareStatement("alter table jobs modify column p_code VARCHAR(500)");
stmt.execute();
}
catch (final SQLException e){
println("Unable to modify database column!");
println(e.getMessage());
}finally{
Utilities.tryToCloseJDBC(conn, pstmt, null, true);
}

Before executing the above groovy script, the database column of the jobs table is shown like that (“p_code” column has 255 character limit);

After executing, the “p_code” column will have 500 character limit like that;

If you are using the Oracle database for your local environment or any hybris system environment, you have to change the “alter” query string as shown below;

stmt = conn.prepareStatement(“alter table jobs modify p_code VARCHAR(500)”);

Additionally, you can check the changing the character limit, you may use the SQL query for the Oracle database;

select table_name, column_name,data_type, data_length
from user_tab_columns
where table_name = ‘JOBS’

In the end, for the “Hsqldb” that means hybris embedded database, you have to change the “alter” query string like that;

stmt = conn.prepareStatement(“alter table jobs alter column p_code VARCHAR(500)”);

If you have any questions or problems, don’t hesitate to contact me. Thanks for reading :)