...
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://localhost//1234~/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> |
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>
|
...