how to Modif column length from 255 to varchar(4000) in hybris v5.0?

[Former Member]

how to Modif column length from 255 to varchar(4000) in hybris v5.0?

I tried changing the persistence as below, but update didnt help.

                         <columntype database="oracle">
                             <value>varchar2(4000)</value>
                         </columntype>
                         <columntype database="sqlserver">
                             <value>nvarchar(max)</value>
                         </columntype>
                         <columntype database="mysql">
                             <value>text</value>
                         </columntype>
                         <columntype>
                             <value>varchar(4000)</value>
                         </columntype>
                     </persistence>

I tried with alter table command from sql server and it worked. but this code is already in production and we will not be having access to run alter queries.

Please suggest on how we can fix this issue.

Thanks in advance

[mtiwa129]

Hi,

You can use the below script to update the cloumn size of an existing table.

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 [tableName] alter column [columnName] VARCHAR(5000)");
stmt.execute();
}
catch (final SQLException e){
println("Unable to modify database column!");
println(e.getMessage());
}finally{
Utilities.tryToCloseJDBC(conn, pstmt, null, true);
}

If using MySQL as a database change the statment as

alter table [tableName] modify column [columnName] VARCHAR(500)

If face any issue try to clear the table data first then run the above script.

I hope this helps!!!!

[Former Member]

Thanks everyone for the replies, we have fixed it by directly altering it from DB using alter query only

[Former Member]

Vinay,

that is not the best practice. and somewhere i heard if we alter the tables like that the hybris warranty is not given..

[Former Member]

I do not believe this is true. The wiki evens recommends manual updates.

https://wiki.hybris.com/display/release5/items.xml#items.xml-MakeColumnTypesChangeableDuringUpdateSy...

[Former Member]

Hi Vinay
Could you mind to share me the query??

Regards,
Md hamid

[Former Member]

Update will create a table and columns if it not exists. But if the column is already created it will not modify. May be because the tables are not empty.

[indrit79]

Sorry to jump-in. Why the update didn't work? Did the DDL generated correctly?

Why altering table manually when items.xml & update system are available? Does this mean that the update system doesn't work?

Thank you.

[Former Member]

Update will create a table and columns if it not exists. But if the column is already created it will not modify. May be because the tables are not empty.

[Former Member]

According to this post, when you are changing the size only, the update system will not work. It must be manually done.

https://answers.sap.com/questions/12748769/modifying-column-type-of-existing-attribute-doesnt.html

[indrit79]

Thank you very much for your feedback, very appreciated indeed.

I checked that post but I don't accept this answer "you can just do an "alter column" on the database" as an explanation...sorry.

[Former Member]

That's a fair enough review, but as it stands hybris does not support this change. So you could raise a bug with hybris maybe?

[Former Member]

import groovy.sql.Sql sql = Sql.newInstance("jdbc:oracle:thin:localhost", "username", "password", "oracle.jdbc.driver.DatabaseDriver") sql.execute("alter table XYZTable modify p_name varchar(4000)")

[Former Member]

Hi Vinay,

You may fix it with the help of interceptors by swapping the (attributes) columns in the back-end as follows:

Create a new attribute with column length as varchar(4000) in items.xml.

Now create a new Prepare interceptor that will intercept this model before saving and save your data in the new attribute instead the attribute in which you were originally saving the data.

Now create another interceptor Load Interceptor that will intercept when this model is loaded. When this model gets loaded copy the value from your new attribute to your old attribute and return the loaded model.

This way for Hybris it would not make any difference but actually you would be swapping values using interceptor.

Regards,

[Former Member]

This seems to be giving extra pain to the system, instead we can alter table in table.