DBReaderWorker to read databases
nl.planon.morpheus.pnworkers.server.dbreaderworker.DBReaderWorker
This reader worker allows you to connect to a database and run SQL commands to export data and import it into Planon ProCenter, so that it becomes easy to transfer data.
• You can connect only to external databases, not make a connection to the current Planon database.
• An extra data source needs to be created for the external database. The data source should be added to the …\Server\wildfly-*\standalone\configuration\standalone-full.xml file. This can be done with the Wildfly Command line interface.
 | For information about the Wildfy Command line interface see the Administrator’s Guide. |
• This extra datasource should be named <name>DS, for example Worker-DS.
• Make sure the database user can only read from the assigned database.
• In the default worker configuration, the XMLReaderWorker should be replaced by the DBReaderWorker.
• The databases supported for the DBReaderWorker are MSSQL and Oracle by default. Other databases are not supported since the corresponding drivers are not available as part of the install set.
Datasource file example
<datasource jndi-name="java:/jdbc/WorkerDS" pool-name="WorkerDS" enabled="true" spy="true" jta="false">
<connection-url>jdbc:sqlserver://[ServerName]\SQLEXPRESS2014</connection-url>
<driver>mssql-test</driver>
<new-connection-sql>PLN_SET_P5_SESSION</new-connection-sql>
<pool>
<min-pool-size>1</min-pool-size>
<!-- The minimum number of connections maintained in the pool. Unless <prefill> is true then the pool will remain empty until first use at which point the pool will be filled to the <minpool-size>. When the pool size drops below the <min-pool-size> due to idle timeouts the pool will be refilled to the <min-pool-size>. Default is 0.-->
<max-pool-size>5</max-pool-size>
<!-- The maximum number of connections allowed in the pool. Default is 20.-->
</pool>
<statement>
<prepared-statement-cache-size>25</prepared-statement-cache-size>
<!-- The number of prepared statements per connection to be kept open and reused in subsequent requests. They are stored in a LRU cache. The default is 0 (zero), meaning no cache.-->
</statement>
<timeout>
<blocking-timeout-millis>60000</blocking-timeout-millis>
<!-- The length of time to wait for a connection to become available when all the connections are checked out. Default is 30000 (30 seconds).-->
<idle-timeout-minutes>5</idle-timeout-minutes>
<!-- Indicates the maximum time a connection may be idle before being closed. Setting to 0 disables it. Default is 15 minutes.-->
<!-- Note: Incase, prefill is set to true then this will not close the idle connections-->
</timeout>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
<check-valid-connection-sql>select 1</check-valid-connection-sql>
<background-validation>true</background-validation>
<background-validation-millis>10000</background-validation-millis>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter"/>
</validation>
<security>
<user-name>John Doe</user-name>
<password>Plan$QL</password>
</security>
</datasource>
<drivers>
<driver name="mssql-test" module="com.mssql">
<xa-datasource-class transactionisolation="TRANSACTION_NONE">com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>
</driver>
</drivers>
<connection-url>jdbc:sqlserver://[ServerName]\SQLEXPRESS2014</connection-url>
<driver>mssql-test</driver>
<new-connection-sql>PLN_SET_P5_SESSION</new-connection-sql>
<pool>
<min-pool-size>1</min-pool-size>
<!-- The minimum number of connections maintained in the pool. Unless <prefill> is true then the pool will remain empty until first use at which point the pool will be filled to the <minpool-size>. When the pool size drops below the <min-pool-size> due to idle timeouts the pool will be refilled to the <min-pool-size>. Default is 0.-->
<max-pool-size>5</max-pool-size>
<!-- The maximum number of connections allowed in the pool. Default is 20.-->
</pool>
<statement>
<prepared-statement-cache-size>25</prepared-statement-cache-size>
<!-- The number of prepared statements per connection to be kept open and reused in subsequent requests. They are stored in a LRU cache. The default is 0 (zero), meaning no cache.-->
</statement>
<timeout>
<blocking-timeout-millis>60000</blocking-timeout-millis>
<!-- The length of time to wait for a connection to become available when all the connections are checked out. Default is 30000 (30 seconds).-->
<idle-timeout-minutes>5</idle-timeout-minutes>
<!-- Indicates the maximum time a connection may be idle before being closed. Setting to 0 disables it. Default is 15 minutes.-->
<!-- Note: Incase, prefill is set to true then this will not close the idle connections-->
</timeout>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
<check-valid-connection-sql>select 1</check-valid-connection-sql>
<background-validation>true</background-validation>
<background-validation-millis>10000</background-validation-millis>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter"/>
</validation>
<security>
<user-name>John Doe</user-name>
<password>Plan$QL</password>
</security>
</datasource>
<drivers>
<driver name="mssql-test" module="com.mssql">
<xa-datasource-class transactionisolation="TRANSACTION_NONE">com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>
</driver>
</drivers>