JDBC Protocol Handler (jdbc://)

A-Stack supports JDBC protocol Handler, that can be used to retrieve records from various relational databases using JDBC interface.

JDBC Protocol Handler Flow:

JDBC Protocol Handler Installation:

Date	Download Location	Version
1/30/2019	sff.bundle.ext.jdbc-1.0.0.zip	1.0.0

JDBC Setup Instructions

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.
1. org.osgi.service.jdbc-1.0.0.jar
2. 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.
- https://search.maven.org/search?q=org.ops4j.pax.jdbc

Change Log

Version 1.0.0

Selection queries can be used to retrieve data from Relational database using JDBC

JDBC Protocol Parameters:

The table below lists all the parameters of Mqtt protocol handler and their description

Parameter Name

Parameter Description

ConnectionParameters

The list of jdbc properties that are required to create

javax.sql.DataSource object for the given database.

Query

Select query to retrieve records from the database.

Usage of JDBC protocol handler:

JDBC Invoke Action

<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://1234" scope="Local">
					<Message type="text" xmlns="foo">
						<Value>
							<ConnectionParameters>
								<JdbcDriverClass>org.h2.Driver</JdbcDriverClass>
								<url> jdbc:h2:tcp://localhost/~/employee</url>
								<user>sa</user>
								<password></password>
							</ConnectionParameters>
							<Query>
								select * from emp
							</Query>
						</Value>
					</Message>
				</Invoke>
				<Output name="Result" as="ActionResult">
					<Value>						
						<userId>[:Event.Argument.userId.Value:]
						</userId>
					</Value>
				</Output>
			</Task>
		</Workflow>
	</Action>
</ThingModel>

Trigger JDBC Invoke Action

<Query>
	<Create>
		<User>
			<userId>userid123</userId>
			<password>pwd123</password>
		</User>
	</Create>
</Query>

JDBC Result Output Format: The output message of jdbc handler is an xml structure that has fields of database columns which are retrieved using SELECT query.

JDBC Handler successful response

<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 Failure Response

<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>