activiti stop working when refresh datasource with new properties

[shivam-ver]

Hi,

I am facing problem when we refresh the datasource in activiti. 

Initially I set my datasource from properties file on H2 and after server up I show one simple page where I ask for new datasource properties  then refresh our application datasource with new new properties bbut activiti stop working and throwing error as below.

getting exception on  java code as below.

repositoryService.createDeployementQuery().list().

ERROR-Stack

SQLSERVEREXCEPTION: 

Error may exist in org/activiti/db/mapping/entity/deployment.xml

Error involved in  org.activiti.engine.impl.persistence.entity.deploymentEntity.SelectDeploymentByQueryCriteria-inline

setting parameters * from ACT_re_deployment RES order by RES.ID asc LIMT? OFFSET ?

Cause: SQLSERVEREXCEPTION : Incorrect syntax near 'LIMIT'

PLEASE I spend lot of days on this issue.

[jearles]

In your error:

Error involved in  org.activiti.engine.impl.persistence.entity.deploymentEntity.SelectDeploymentByQueryCriteria-inline setting parameters * from ACT_re_deployment RES order by RES.ID asc LIMT? OFFSET ?

The word 'LIMIT' is spelled incorrectly. Could that be a part of the issue? Have you potentially misspelled the word?
Also in that same error, your spelling of 'LIMIT' is follow immediately by a question mark, which I believe is incorrect syntax, as there should be a space between them.

If these aren't the problem, could you please include the entire stacktrace?

-JEarles

[shivam-ver]

Thanks for your reply,

Please find my problem details as attached

[shivam-ver]

thanks for reply.

Please find my problem details as attached snapshot

[jearles]

Shivam,

It appears as thought you're using Microsoft SQL Server - is that correct? Which version of it are you using? I'm not sure if the newer versions support 'LIMIT' - but that hasn't previously been a support clause in the Microsoft SQL server dialect.

If this is custom SQL that you've written, you'll want to change it yourself.
If this is autogenerated SQL by the application, you'll want to take a look at your database configuration and verify that you have your dialect configured properly.

-JEarles

[shivam-ver]

can anyone help me regarding this issue.

[sunilkumartk]

Have you installed the Activiti Tables in your new database ? 

You are expected to do that correctly

There are plenty of resources available online that helps you understand Activiti installation and configuration or try my Installation tutor heree

[shivam-ver]

Hi ,

Let me explain my scenario as below and please help me out.

Objective  :I want to switch activiti database at runtime .Initially run on H2 database and when server up I show the simple html page to user where he/she can put new database information for switch their database.Supposed user need to switch from H2 to  MSSQL database  then he/she will fill all the required database information in the html form example:

databaseType=MSSQL ,databaseName ="newDatabase",UserName="username",password="password"

As per  information I get from HTML form I create a DATASOURCE and set that datasource in processEngineConfiguration and refresh my activitiConfig file [this file is a  

@configuration file in spring which run when i refresh the spring context.

]

and when I set updated datasource in processEngine and refresh activitiConfig.java file then all the activiti table get generated in new database successfully .

BUT my problem is -after refreshing the datasouce with new database then I am trying to deploy my .bpmn file in activiti but it is giving me some error as below.And I also attached one png which show the information after refresh the datasource with new value.

getting error when I execute java code :  repositoryService.createDeploymentQuery.List()

‌ : I also checked MSSQL DIALECT but it is correct. Please see my snapshot for more information.

PLEASE see attached snapshot for more details as attached.

error:

Error during job acquisition

Error querying database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect Syntax 'LIMIT'.

The error may exist in org/activiti/db/mapping/entity/Job.xml

[shivam-ver]

Hi ,

Let me explain my scenario as below and please help me out.

Objective  :I want to switch activiti database at runtime .Initially run on H2 database and when server up I show the simple html page to user where he/she can put new database information for switch their database.Supposed user need to switch from H2 to  MSSQL database  then he/she will fill all the required database information in the html form example:

databaseType=MSSQL ,databaseName ="newDatabase",UserName="username",password="password"

As per  information I get from HTML form I create a DATASOURCE and set that datasource in processEngineConfiguration and refresh my activitiConfig file [this file is a  

@configuration file in spring which run when i refresh the spring context.

]

and when I set updated datasource in processEngine and refresh activitiConfig.java file then all the activiti table get generated in new database successfully .

BUT my problem is -after refreshing the datasouce with new database then I am trying to deploy my .bpmn file in activiti but it is giving me some error as below.And I also attached one png which show the information after refresh the datasource with new value.

getting error when I execute java code :  repositoryService.createDeploymentQuery.List()

Jonathan Earles : I also checked MSSQL DIALECT but it is correct. Please see my snapshot for more information.

 

PLEASE see attached snapshot for more details as attached.

error:

Error during job acquisition

Error querying database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect Syntax 'LIMIT'.

The error may exist in org/activiti/db/mapping/entity/Job.xml

[gdharley]

"I want to switch activiti database at runtime .Initially run on H2 database and when server up I show the simple html page to user where he/she can put new database information for switch their database"

There is no easy way to do this.
The dbSqlSession is stored as a session object within the ProcessEngineConfiguration.

This effectively makes it a singleton.

As far as I am aware, there is no easy way to override the sqlSession at runtime.

Obviously, this is just software and if you wanted to implement such behavior, I would look at re-initializing the process engine with the new DB properties.

Now, I'm not sure why you want to do this, but there is a tenant aware process engine configuration that is documented here:

Multi-Tenancy with separate database schemas in Activiti | Small steps with big feet 

If this doesnt satisfy your need, then it may be a good start on how to implement your desied behavior.

Greg