Migration of user role from derby database to another database in Helical Insight

Hi Team Helical,

I have my own on premise software. We would like to integrate and use Helical Insight in embedded mode. I understand that Helical Insight is using Derby to store user role management. Can we switch the internal Database from derby to another database? Trying Helical Insight 3.0.

Thank You.

1 Like

Hi Anu,

Yes, it is possible to switch from the derby database to another database to store user role management information.
Let us say you want to migrate from Derby to MySQL. Install MySQL separately. Create a db by the name of hiee in this database.

Then open application-context.xml file. This file is present on the below path "C:\Program Files\Helical Insight\hi\apache-tomcat-7\webapps\hi-ee\WEB-INF\classes"

Make changes on the below code, change it from derby to DB of your choice (change driver, class etc) (line 8 to line 13)

<bean class="com.zaxxer.hikari.HikariDataSource" destroy-method="close" id="dataSource">
<property name="username" value="admin"/>
<property name="password" value=""/>
<property name="driverClassName" value="org.apache.derby.jdbc.EmbeddedDriver"/>
<property name="jdbcUrl" value="jdbc:derby:C:\Program Files\Helical Insight\hi\db\hiee;create=true"/>

also make change in the below (line 28)
<prop key="hibernate.dialect">org.hibernate.dialect.DerbyDialect</prop>

One more step is, to whichever DB you are migrating please make sure that you are also storing that specific DB JDBC driver in the LIB folder as well. Path of LIB folder is "C:\Program Files\Helical Insight\hi\apache-tomcat-7\webapps\hi-ee\WEB-INF\lib"

NOTE: In case if you are shifting to SQLServer, the SQLServer DB is having FILE as reserved keyword and hence it will not create one table which is “Cache_Datasource” table. For that you can contact us support team for more information. You might also face this issue in case if you are shifting to all together a very different datasource as well. We have tested it with various RDBMS like MySQL, PostGress, SQLServer, Oracle etc.

Once the above changes are done restart the server. Then automatically the user role information will get created inside the other DB.

Thank You
Helical Insight Team.