How to change default database to sql server for kie-workbench and kie-server in tomcat

[jagan reddy]

Hi Viwers,

I am using tomcat7 and kie-workbench.6.5.0.Final and kie-server.6.5.0.Final 

here are configuration what i changed

./tomcat/bin/stenev.sh file 

CATALINA_OPTS="-Xmx512M -XX:MaxPermSize=512m 

-Dbtm.root=$CATALINA_HOME 

-Dorg.jbpm.cdi.bm=java:comp/env/BeanManager 

-Dbitronix.tm.configuration=$CATALINA_HOME/conf/btm-config.properties 

-Djbpm.tsr.jndi.lookup=java:comp/env/TransactionSynchronizationRegistry 

-Djava.security.auth.login.config=$CATALINA_HOME/webapps/kie-wb/WEB-INF/classes/login.config 

-Dorg.kie.server.persistence.ds=java:comp/env/jdbc/jbpmDS 

-Dorg.kie.server.persistence.tm=org.hibernate.service.jta.platform.internal.BitronixJtaPlatform 

-Dorg.kie.server.persistence.dialect=org.hibernate.dialect.SQLServerDialect

-Dorg.kie.server.id=tomcat-kieserver 

-Dorg.kie.server.location=http://localhost:8080/kie-server/services/rest/server 

-Dorg.kie.server.controller=http://localhost:8080/kie-wb/rest/controller 

-Dorg.kie.override.deploy.enabled=true

-Dorg.uberfire.nio.git.dir=/mnt/apache-tomcat-7.0.54/temp 

-Dorg.uberfire.nio.git.ssh.enabled=true 

-Dorg.jboss.logging.provider=jdk 

-Dorg.drools.server.ext.disabled=false

-Dorg.jbpm.server.ext.disabled=false

-Dorg.jbpm.ui.server.ext.disabled=false

-Djbpm.enable.multi.con=true"

./tomcat/conf/resources.property file

resource.ds1.className=com.microsoft.sqlserver.jdbc.SQLServerXADataSource

resource.ds1.uniqueName=jdbc/jbpmDS

resource.ds1.minPoolSize=0

resource.ds1.maxPoolSize=10

resource.ds1.driverProperties.user=xxxxxxxx

resource.ds1.driverProperties.password=xxxxxxx

resource.ds1.driverProperties.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver

resource.ds1.driverProperties.URL=jdbc:sqlserver://localhost:1433;databaseName=jbpm;

resource.ds1.allowLocalTransactions=true

./tomcat/webapps/kie-wb/META-INF/context.xml

add below lines

 <Resource name="jdbc/jbpmDS" uniqueName="jdbc/jbpmDS" auth="Container"

            removeAbandoned="true" factory="bitronix.tm.resource.ResourceObjectFactory" type="javax.sql.DataSource" />

./tomcat/weapps/kie-server/META-INF/context.xml

 <Resource name="jdbc/jbpmDS" uniqueName="jdbc/jbpmDS" auth="Container"

            removeAbandoned="true" factory="bitronix.tm.resource.ResourceObjectFactory" type="javax.sql.DataSource" />

./tomcat/webapps/kie-wb/WEB-INF/classes/META-INF/persistence.xml  file

change Jta datasource

    <jta-data-source>java:comp/env/jdbc/jbpmDS</jta-data-source>

in the property changed dialect

      <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>

i am getting the following error.

Feb 20, 2018 1:12:02 PM org.apache.catalina.core.AprLifecycleListener init

INFO: The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: /usr/java/packages/lib/amd64:/usr/lib64:/lib64:/lib:/usr/lib

Feb 20, 2018 1:12:03 PM org.apache.coyote.AbstractProtocol init

INFO: Initializing ProtocolHandler ["http-bio-8080"]

Feb 20, 2018 1:12:03 PM org.apache.coyote.AbstractProtocol init

INFO: Initializing ProtocolHandler ["ajp-bio-8009"]

Feb 20, 2018 1:12:03 PM org.apache.catalina.startup.Catalina load

INFO: Initialization processed in 820 ms

Feb 20, 2018 1:12:03 PM org.apache.catalina.core.StandardService startInternal

INFO: Starting service Catalina

Feb 20, 2018 1:12:03 PM org.apache.catalina.core.StandardEngine startInternal

INFO: Starting Servlet Engine: Apache Tomcat/7.0.54

Feb 20, 2018 1:12:03 PM org.apache.catalina.startup.HostConfig deployWAR

INFO: Deploying web application archive /home/jaganmohan/apache-tomcat-7.0.54_kie-wb-distribution/webapps/kie-wb.war

Feb 20, 2018 1:12:03 PM bitronix.tm.integration.tomcat55.BTMLifecycleListener lifecycleEvent

INFO: Starting Bitronix Transaction Manager

Feb 20, 2018 1:12:03 PM bitronix.tm.BitronixTransactionManager logVersion

INFO: Bitronix Transaction Manager version 2.1.4

Feb 20, 2018 1:12:03 PM bitronix.tm.Configuration buildServerIdArray

INFO: JVM unique ID: <tomcat-btm-node0>

Feb 20, 2018 1:12:03 PM bitronix.tm.resource.ResourceLoader init

INFO: reading resources configuration from /home/jaganmohan/apache-tomcat-7.0.54_kie-wb-distribution/conf/resources.properties

Feb 20, 2018 1:12:03 PM bitronix.tm.resource.ResourceLoader initXAResourceProducers

WARNING: unable to create resource with unique name jdbc/jbpmDS

bitronix.tm.resource.ResourceConfigurationException: cannot create JDBC datasource named jdbc/jbpmDS

at bitronix.tm.resource.jdbc.PoolingDataSource.init(PoolingDataSource.java:92)

at bitronix.tm.resource.ResourceLoader.initXAResourceProducers(ResourceLoader.java:179)

at bitronix.tm.resource.ResourceLoader.init(ResourceLoader.java:152)

at bitronix.tm.resource.ResourceLoader.init(ResourceLoader.java:84)

at bitronix.tm.BitronixTransactionManager.<init>(BitronixTransactionManager.java:86)

at bitronix.tm.TransactionManagerServices.getTransactionManager(TransactionManagerServices.java:75)

at bitronix.tm.integration.tomcat55.BTMLifecycleListener.lifecycleEvent(BTMLifecycleListener.java:18)

at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:117)

at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:90)

at org.apache.catalina.util.LifecycleBase.setStateInternal(LifecycleBase.java:402)

at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:147)

at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)

at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)

at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:632)

at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:1083)

at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1880)

at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)

at java.util.concurrent.FutureTask.run(FutureTask.java:266)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

at java.lang.Thread.run(Thread.java:748)

Caused by: bitronix.tm.utils.PropertyException: no writeable property 'driverClassName' in class 'com.microsoft.sqlserver.jdbc.SQLServerXADataSource'

at bitronix.tm.utils.PropertyUtils.getSetter(PropertyUtils.java:318)

at bitronix.tm.utils.PropertyUtils.setDirectProperty(PropertyUtils.java:217)

at bitronix.tm.utils.PropertyUtils.setProperty(PropertyUtils.java:83)

at bitronix.tm.resource.common.XAPool.createXAFactory(XAPool.java:304)

at bitronix.tm.resource.common.XAPool.<init>(XAPool.java:63)

at bitronix.tm.resource.jdbc.PoolingDataSource.buildXAPool(PoolingDataSource.java:101)

at bitronix.tm.resource.jdbc.PoolingDataSource.init(PoolingDataSource.java:88)

... 20 more

can anyone tell me what i made mistake.

any help highly appreciated.

Thanks,

Jagan

[sandeep gollapudi]

Hi Jagan,

I think you can try using  resource.ds1.className=bitronix.tm.resource.jdbc.lrc.LrcXADataSource in resources.properties instead of com.microsoft.sqlserver.jdbc.SQLServerXADataSource.

Thanks,

Sandeep

[jagan reddy]

HI sandeep,

Thanks for reply,

can u please send all properties in resources.property file.

Thanks

Jagan

[sandeep gollapudi]

Hi Jagan,

I am using Mysql... Please refer the resources.properties

resource.ds1.className=bitronix.tm.resource.jdbc.lrc.LrcXADataSource

resource.ds1.uniqueName=jdbc/jbpm

resource.ds1.minPoolSize=10

resource.ds1.maxPoolSize=20

resource.ds1.driverProperties.driverClassName=com.mysql.jdbc.Driver

resource.ds1.driverProperties.url=jdbc:mysql://127.0.0.1:3306/jbpm

resource.ds1.driverProperties.user=root

resource.ds1.driverProperties.password=root

resource.ds1.allowLocalTransactions=true

resource1.ds1.driverProperties.url.security=jdbc:mysql://127.0.0.1:3306/security_jbpm6

resource1.ds1.driverProperties.dialect=org.hibernate.dialect.MySQL5InnoDBDialect

resource1.ds1.driverProperties.hbm2ddl.auto=update

resource1.ds1.driverProperties.show_sql=true

resource1.ds1.driverProperties.id.new_generator_mappings=false

resource1.ds1.driverProperties.transaction.manager_lookup_class=org.hibernate.transaction.BTMTransactionManagerLookup

Thanks,

Sandeep

On Tuesday, 20 February 2018 14:36:37 UTC+5:30, jagan reddy wrote:

[jagan reddy]

Hi sandeep,

Thanks for reply.

Can u explain why u r using two type of properties. as resource and resources1 

Thanks,

Jagan

[jagan reddy]

Hi viewers,

Finally i fixed this issue by using editing resources property file 

resource.ds1.className=bitronix.tm.resource.jdbc.lrc.LrcXADataSource

resource.ds1.uniqueName=jdbc/jbpmDS

resource.ds1.minPoolSize=0

resource.ds1.maxPoolSize=10

resource.ds1.driverProperties.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver

resource.ds1.driverProperties.url=jdbc:sqlserver://localhost:1433;databaseName=jbpm;

resource.ds1.driverProperties.user=sa

resource.ds1.driverProperties.password=qruize

resource.ds1.allowLocalTransactions=true

remaining as follow as i mention in first post message.

Thanks,

Jagan.

[sandeep gollapudi]

resource is for security db

resource1 for  jbpm database

Regards,

Sandeep 

On Tuesday, 20 February 2018 14:36:37 UTC+5:30, jagan reddy wrote:

[jagan reddy]

thanks for reply