...
- Unzip the downloaded zip file
- Copy the following jar files from this location to the sff.auto.launch folder which is present in your A-Stack Prime folder.
- org.osgi.service.jdbc-1.0.0.jar
- sff.bundle.ext.jdbc-1.0.0.jar
- In addition to the above bundles, we need to copy the JDBC driver jar file (OSGI bundle) for the selected database. You can refer the below repository to get OSGi version of database server.
Change Log
- Selection queries All CRUD operations can be used to retrieve data from executed on Relational database using JDBC handler
- Connection pooling support using HikariCP connection pool library.
JDBC Protocol Parameters:
...
Parameter Name | Parameter Description |
QueryString
| Select query to retrieve records from the database. |
Usage of JDBC protocol handler:
Invoke JDBC handler with selection query
Code Block |
---|
language | xml |
---|
title | Trigger JDBC Invoke Action |
---|
linenumbers | true |
---|
|
<ThingModel Name="User">
<Sid name="sysId" />
<String Name="userId" KnownBy="JDBCInvokeAction" />
<String Name="password" />
<Action Name="JDBCInvokeAction" Documentation="Invooke JDBC Protocol Handler">
<Workflow Limit="1" Live="1" Timeout="-1">
<Task name="Main" while="true">
<Event name="Argument" as="ActionArgument" />
<Log Level="INFO" Message="***** Before Invoke *****" waitFor="Argument" />
<Invoke name="InvokeJDBC" waitFor="Argument" Post="jdbc://?JdbcDriverClass=org.h2.Driver;url=jdbc:h2:tcp:///1234localhost/~/employee;user=sa;password=;" scope="Local">
<Message type="text" xmlns="foo">
<Value>
<ConnectionParameters>
<QueryString operation="Select" limit="2">
<JdbcDriverClass>org.h2.Driver</JdbcDriverClass>
<url> jdbc:h2:tcp://localhost/~/employee</url>
select * from emp
<user>sa</user>
</QueryString>
<password></password>
</ConnectionParameters>Value>
<Query> </Message>
select * from emp
</Invoke> </Query>
</Value>
</Message> <Log Level="INFO" Message="Total [:Invoke.InvokeJDBC.Message.Value:]" />
</Invoke>
<Output name="Result" as="ActionResult">
<Value>
<userId>[:Event.Argument.userId.Value:]
</userId>
</Value>
</Output>
</Task>
</Workflow>
</Action>
</ThingModel> |
Code Block |
---|
language | xml |
---|
title | Trigger JDBC Invoke Action |
---|
linenumbers | true |
---|
|
<Query>
<Create>
<User>
<userId>userid123</userId>
<password>pwd123</password>
</User>
</Create>
</Query> |
JDBC Result Output FormatSuccessful output format - Select Operation: The output message of jdbc handler is an xml structure that has fields of database columns which are retrieved using SELECT querySelect operation.
Code Block |
---|
language | xml |
---|
title | JDBC Handler successful response |
---|
linenumbers | true |
---|
|
<Result StatusCode="200" Description="Successful.">
<Records>
<Record>
<EMPNO>7839</EMPNO>
<ENAME>King</ENAME>
<JOB>President</JOB>
<MGR/>
<HIREDATE>1981-11-17</HIREDATE>
<SAL>7698</SAL>
<COMM/>
<DEPTNO>10</DEPTNO>
</Record>
<Record>
<EMPNO>7698</EMPNO>
<ENAME>Blake</ENAME>
<JOB>Manager</JOB>
<MGR>7839</MGR>
<HIREDATE>1981-05-01</HIREDATE>
<SAL>7782</SAL>
<COMM/>
<DEPTNO>20</DEPTNO>
</Record>
<Record>
<EMPNO>7782</EMPNO>
<ENAME>Clerk</ENAME>
<JOB>Manager</JOB>
<MGR>7839</MGR>
<HIREDATE>1981-06-09</HIREDATE>
<SAL>7566</SAL>
<COMM/>
<DEPTNO>30</DEPTNO>
</Record>
<Record>
<EMPNO>7566</EMPNO>
<ENAME>Jones</ENAME>
<JOB>Manager</JOB>
<MGR>7839</MGR>
<HIREDATE>1981-04-02</HIREDATE>
<SAL>7839</SAL>
<COMM/>
<DEPTNO>40</DEPTNO>
</Record>
</Records>
</Result></Records>
</Result> |
JDBC Successful response format - Inserrt, Update,Delete operations: The output message of jdbc handler is an xml structure that has fields of database columns which are retrieved using Insert,Update,Delete query operations.
Code Block |
---|
language | xml |
---|
title | Response for update operaton |
---|
linenumbers | true |
---|
|
<Result StatusCode="200" Description="Successful.">
<Records>
<RowsAffected>1</RowsAffected>
</Records>
</Result>
|
JDBC handler failure/error response
Code Block |
---|
language | xml |
---|
title | JDBC Failure Response |
---|
linenumbers | true |
---|
|
<Result StatusCode="502" Description="Exception occured during JDBC Invocation.">
<Error>Error while executing the jdbc query org.h2.jdbc.JdbcSQLException: Connection is broken: "java.net.ConnectException: Connection refused: connect: localhost" [90067-197]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
at org.h2.message.DbException.get(DbException.java:168)
at org.h2.engine.SessionRemote.connectServer(SessionRemote.java:451)
at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:332)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:124)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:103)
at org.h2.Driver.connect(Driver.java:69)
at org.h2.jdbcx.JdbcDataSource.getJdbcConnection(JdbcDataSource.java:189)
at org.h2.jdbcx.JdbcDataSource.getConnection(JdbcDataSource.java:178)
at com.atomiton.sff.imp.jdbc.QueryExecutor.executeSelectStatment(QueryExecutor.java:80)
at com.atomiton.sff.imp.netty.jdbc.DatabaseInvocationHandler.messageReceived(DatabaseInvocationHandler.java:59)
at org.jboss.netty.channel.SimpleChannelHandler.handleUpstream(SimpleChannelHandler.java:88)
at org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultChannelPipeline.java:564)
at org.jboss.netty.channel.DefaultChannelPipeline$DefaultChannelHandlerContext.sendUpstream(DefaultChannelPipeline.java:791)
at org.jboss.netty.handler.execution.ChannelUpstreamEventRunnable.doRun(ChannelUpstreamEventRunnable.java:43)
at org.jboss.netty.handler.execution.ChannelEventRunnable.run(ChannelEventRunnable.java:67)
at org.jboss.netty.handler.execution.OrderedMemoryAwareThreadPoolExecutor$ChildExecutor.run(OrderedMemoryAwareThreadPoolExecutor.java:314)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: java.net.ConnectException: Connection refused: connect
at java.net.DualStackPlainSocketImpl.waitForConnect(Native Method)
at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
at java.net.AbstractPlainSocketImpl.connect(Unknown Source)
at java.net.PlainSocketImpl.connect(Unknown Source)
at java.net.SocksSocketImpl.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at org.h2.util.NetUtils.createSocket(NetUtils.java:103)
at org.h2.util.NetUtils.createSocket(NetUtils.java:83)
at org.h2.engine.SessionRemote.initTransfer(SessionRemote.java:114)
at org.h2.engine.SessionRemote.connectServer(SessionRemote.java:447)
... 17 more
</Error>
</Result>
|
Code Block |
---|
language | xml |
---|
title | Response for update operaton |
---|
linenumbers | true |
---|
|
<Result StatusCode="200" Description="Successful.">
<Records>
<RowsAffected>1</RowsAffected>
</Records>
</Result>
|
...