Transcript DB-07 OpenEdge SQL and Sonic

OpenEdge® SQL & SonicMQ®:
Using JMS with SQL
Applications
Pat Bonser
Session Goals

Learn how to use JMS techniques
– With a JDBC application
– With an ODBC application
– Using SQL java stored procedure
– Using SQL java row trigger
2 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
OpenEdge SQL and SonicMQ





JMS and SonicMQ
OpenEdge SQL
Integration
Demo
Summary and Questions
3 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Example – Supplier and Retailer
Retailer
Java™ App
Supplier Java App
(sports equipment
vendor)
Adding New
product
Changing
product’s price
JMS
Browsing
orders
Show new
product
SonicMQ
Broker
Show price
changes
JMS
JMS
JDBC driver
Placing orders
OE SQL DB
JMS
Retailer
Java App
Java trigger
4 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Messaging


A mechanism enabling autonomous
applications to communicate
Messages are units of information composed
of
– Body
– Header






Destinations
Priority
Ordering
Expiration
Time-to-Live
And more …
5 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
JMS

A specification for Message-Oriented
Middleware (MOM) provider
– Version 1.1

A Java interface and semantics for interapplication messaging
– Basic functionality of JMS
– Javax.jms.* package


Enterprise Application Integration (EAI)
http://java.sun.com/products/jms
6 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
JMS Features






Standard client API
Asynchronous or synchronous guaranteed
message delivery
Transactions
Message filtering
Reliability levels
Two messaging models with a common
interface
7 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Basic JMS Terminology
Application B
Application A
JMS - Message Oriented Middleware
Connection
Session
Producer
8 © 2005 Progress Software Corporation
Destination
Consumer
OpenEdge SQL and SonicMQ
JMS Two Messaging Models
Point to Point (1 to 1)
Sender
Queue
Potential Receiver
Potential Receiver
Publish and Subscribe (1 to Many)
Publisher
Topic
9 © 2005 Progress Software Corporation
Subscriber
Subscriber
OpenEdge SQL and SonicMQ
Development of JMS




Develop the JMS client application
Develop the JMS adapter
Develop the producers and consumers
Develop the interaction with the JMS server
10 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
SonicMQ



A JMS provider
Uses standard JMS components and terminology
Extends the JMS specification
–
–
–
–
–
Additional message types (XML, Multi-part, Large)
Distributed transactions (JTA XAResource API)
Security (authentication, authorization, SSL)
Open database connectivity
Continuous availability (Fault tolerant client
connection)
http://www.sonicsoftware.com
11 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Install SonicMQ


Download the product from
http://www.sonicsoftware.com
Typical installation components
–
–
–
–
–
A domain manager container
The Sonic management console (SMC)
The client runtime
The default PSE pro database
JRE ( Windows only )
12 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Configuring and Managing SonicMQ

Sonic Management Console (SMC)
–
–
–
–
Create a new container hosting a broker
View existing queues
Create a new queue
Monitor broker notification
13 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Start the SonicMQ Domain
Start →Programs →Sonic Software →SonicMQ →SonicMQ Container1
Domain
Container1
Broker1
Topic
Agent
Manager
14 © 2005 Progress Software Corporation
Queue
Directory
Service
OpenEdge SQL and SonicMQ
SonicMQ API and Archives

Packages
– Javax.jms (standard JMS)
– Progress.message.jclient (Sonic extensions)
– Progress.message.jclient.channel
– Progress.message.jclient.xa

Archives
– Under sonicmq-install-dir\lib\*.jar
15 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
OpenEdge SQL and SonicMQ





JMS and SonicMQ
OpenEdge SQL
Integration
Demo
Summary and Questions
16 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
OpenEdge SQL


SQL standard database open access via
JDBC and ODBC
DataDirect JDBC driver
– Core JDBC 2.0 and extensions

DataDirect ODBC driver
– Core ODBC 3.0

Server integration with Java
– Java Stored procedure and row trigger
17 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
OpenEdge SQL client-server
architecture
Java App
Windows App
JDBC driver
ODBC driver
JVM
OE SQL Server
Database Storage Manager
OpenEdge
SQL
Database
Java
sp/trigger
18 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Install OpenEdge SQL

SQL server
– Choose the database component
– _sqlsrv2
– Multithreaded and reusable

DataDirect JDBC and ODBC drivers
– Choose “SQL Client access”
– Java classes
– DLL or shared libraries
19 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
JDBC application program
initialization




Environment
– CLASSPATH
– Shared library path
Include the package
– Import java.sql.*;
Load the driver
– Class.forName(“com.progress.sql.jdbc.JdbcProgressDriver”);
Get the connection
– DriverManager.getConnection()
– jdbc:jdbcprogress:T:host-name:port #:database-name

Turn on the JDBC tracing
– DriverManager.setLogStream(PrintStream log)
20 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Basic JDBC API
Driver
Manager
<Interface>
Driver
<Interface>
Connection
<Interface>
Statement
<Interface>
ResultSet
<Interface>
PreparedStatement
<Interface>
DatabaseMetaData
<Interface>
ResultSetMetaData
<Interface>
CallableStatement
21 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
ODBC application program
initialization


Setting the Shared library path
Creating a DSN
– “Data Source (ODBC)” on Windows
– “ODBC.INI” file on Unix

Allocate An Environment Handle
– SQLAllocHandle(SQL_HANDLE_ENV,
SQL_NULL_HANDLE,&EnvHandle)

Allocate A Connection Handle
– SQLAllocHandle(SQL_HANDLE_DBC, EnvHandle,
&ConHandle)

Connect with the DSN
– SQLConnect(ConHandle,…)
22 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
ODBC APIs

The various ODBC functions can be broken
down into seven distinct groups
–
–
–
–
–
–
–
Connections
Cursors
Executing SQL statements
Receiving results
Cancelling an operation
Error handling
Transaction control
23 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Java Stored Procedures




Java snippets
– All standard JAVA features
– OpenEdge SQL-supplied JAVA classes
Integration between SQL and JAVA
Enhances application
– Flexibility, Performance, Portability and reusability
Extend SQL capabilities of databases
– Receive or send IN/OUT/INOUT parameters
– Handle exceptions
– Allow any SQL statement to access database
– Return result set
– Call other procedures
– Use predefined and external JAVA classes
24 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Java Stored Procedures
(Cont.)

Transaction
– Same as and part of the calling application
– COMMIT or ROLLBACK statement not allowed

Security
– Creation
 RESOURCE or DBA privilege
– Drop and Grant
 DBA or the owner
– Execution
 DBA, owner or user who is granted with the EXECUTE
– Only owner’s privileges checked for procedure objects
(tables, columns and etc) during execution
25 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Working with Java Stored Procedures

Configuring
– JDK, JRE and “sql_env” script

Using SQL statements
– CREATE PROCEDURE
– DROP PROCEDURE
– CALL

Viewing
– SYSPROGRESS.SYSPROCEDURES
– SYSPROGRESS.SYSPROCTEXT

Exporting
– SQLSCHEMA -P
26 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Accessing Stored Procedures from
Applications

Embed the call escape sequence
{ CALL proc_name [ ( parameter [ , ... ] ) ] }

JDBC “CallableStatement”
try
{
CallableStatement statement;
int Part_num = 318;
statement = conn.prepareCall("{call order_parts(?)}");
statement.setInt(1, Part_num);
statement.execute();
}

ODBC “SQLPrepare” or “SQLExecDirect”
SQLUINTEGER Part_num;
SQLINTEGER Part_numInd = 0;
SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT,SQL_C_SLONG, SQL_INTEGER,0, 0,
&Part_num, 0, Part_numInd);
Part_num = 318;
SQLExecDirect(hstmt, "{ call order_parts(?) } ", SQL_NTS);
27 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Java row triggers




A special type of stored procedure
To maintain database integrity
Automatically invoked (“fired”) by certain
SQL operations
OLDROW and NEWROW object
– getValue(), setValue()


CREATE/DROP TRIGGER SQL statements
SYSPROGRESS.SYSTRIGGER
28 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
OpenEdge SQL JAVA classes
setParam
SQLIStatement
makeNull
execute
rowCount
SQLPStatement
execute
Open
close
fetch
log
SQLCursor
err
wasNull
getValue
Found
getParam
DhSQLResultSet
set
makeNull
insert
DhSQLException
29 © 2005 Progress Software Corporation
getDiagnostics
OpenEdge SQL and SonicMQ
OpenEdge SQL and SonicMQ





JMS and SonicMQ
OpenEdge SQL
Integration
Demo
Summary and Questions
30 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
SonicMQ Messaging
Java/J2EE
™
Application
OpenEdge®
Application
JMS
JMS
EIS
Customer
Adapter
Message Broker ( SonicMQ )
Customer
Adapter
JMS
Legacy
Application
OpenEdge
Database
Connection
Session
Producer or
Consumer
31 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Solutions

Developing the JMS enabled client
applications
– JDBC + JMS

Developing the JMS adapter
– JAVA class
– Java stored procedure

Developing the JMS producer with the
database Java row triggers
32 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Writing a JMS Adapter


Set the CLASSPATH
Choose the messaging model
– PTP
– PUB/SUB

Identify the role
– Producer
– Consumer

Prepare message types
33 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Include SonicMQ jars

CLASSPATH needs to include:
–
–
–
–
–
–
sonic_Client.jar
mfcontext.jar
gnu-regexp-1.0.6.jar
jaxp.jar
xercesImpl.jar
xmlParserAPIs.jar
34 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
PTP - Basic Steps
1. Initialization
- Connect to a SonicMQ Broker
- Create a Session
- Create or lookup queues
2.
3.
4.
5.
Create a Message Producer/Consumer
Prepare Messages
Send/Receive Message
Close Message Producer/Consumer
35 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Pub\Sub - Basic Steps
1. Initialization
- Connect to a SonicMQ broker
- Create a Session
- Create or lookup Topics
2.
3.
4.
5.
6.
Create a message publisher
Subscribe to Topic
Publish message to Topic
Consume message from a Topic
Close Message Producer/Consumer
36 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Writing a JMS Adapter – Initialization
String broker = "localhost:2506";//default sonic broker
String username = "wang"; //default user name
String password = "password";//default user password
ConectionFactory
Creates
// Create a SonicMQ connection.
Try
{
javax.jms.ConnectionFactory factory;
factory = (new progress.message.jclient.ConnectionFactory
(broker));
Connection
Creates
Session
javax.jms.Connect connect;
connect = factory.createConnection (username, password);
javax.jms.Session session;
session =
connect.createSession(false,javax.jms.Session.AUTO_ACKNOWLEDGE);
}
catch (javax.jms.JMSException jmse)
{
jmse.printStackTrace();
System.exit(1);
}
37 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Creating a Producer
createQueue( String queueName )
Queue
createTopic( String topicName )
session
Topic
MessageProducer
createProducer( Queue queue )
createProducer( Topic topic )
javax.jms.Queue senderQueue = session.createQueue(“sQueue”);
javax.jms.MessageProducer sender = session.createProducer(senderQueue);
Sender.send
(msg,javax.jms.DeliveryMode.PERSISTENT,javax.jms.Message.DEFAULT_PRIORITY,
MESSAGE_LIFESPAN)
….
Sender.close();
38 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Creating a Consumer
createQueue( String queueName )
Queue
createTopic( String topicName )
session
Topic
MessageConsumer
createConsumer( Queue queue )
createConsumer ( Topic topic )
javax.jms.Queue receiveQueue = session.createQueue(“rQueue”);
javax.jms.MessageConsumer receiver = session.createConsumer(receiveQueue);
Receiver.setMessageListener (listener);
Connect.start();
….
receiver.close();
39 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Message Types
Message type
Message body
TextMessage
A Java String
ObjectMessage
A serializable Java object
MapMessage
Name/value pairs, where names are Java
strings and values are java primitive types
StreamMessage
A sequential stream of primitives
BytesMessage
An uninterrupted stream of bytes
XMLMessage
A Java String formatted as a tagged XML
document
MultipartMessage
Zero or more parts; each part can be a JMS
message or arbitrary data
40 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Select a Message Type

Major factors
–
–
–
–

Company message format requirements
Message volume and size
Proportion of keys to data
Consumer’s need
Suggestions
–
–
–
–
–
XML for inter-application communications
Text for simple test with no interpretation
Map for random access to data via keys
Stream for sequential access to data
Object for producer and consumer have access to the
same java class representing message
– Byte for binary format message contents
41 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Working on Messages

Producing messages
– javax.jms.Session.create[message type]()
– javax.jms.MessageProducer.send()
javax.jms.MapMessage mmsg = session.createMapMessage();
Progress.message.jclient.XMLMessage xmsg =
((progress.message.jclient.Session) session).createXMLMessage();

Consuming messages
– Javax.jms.MessageListener.onMessage()
– Javax.jms.MessageConsumer.receive()
public void onMessage( javax.jms.Message aMessage)
{
…
javax.jms.TextMessage textMessage = (javax.jms.TextMessage)
aMessage;
String string = textMessage.getText(); }
…
}
42 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Use Case #1 - Producer
ODBC
App
JDBC App
JMS adapter
Receiving
App
SonicMQ
Broker
JMS adapter
(java sp)
43 © 2005 Progress Software Corporation
Receiving
App
OpenEdge SQL and SonicMQ
Example – JMS producer adapter
javax.jms.MapMessage msg=session.createMapMessage();
msg.setInt( "ID", iid.intValue());
progress.message.jclient.XMLMessage
msg =
msg.setString(
"INAME", iname);
((progress.message.jclient.Session)session).createXMLMessage();
"IPRICE", iprice);
if ( producer != null )
public classmsg.setString(
JMSProducerAdapter
msg.setText(xmlString);
{
producer.send(
msg,javax.jms.DeliveryMode.PERSISTENT,
producer.close();
producer.send(
msg,javax.jms.DeliveryMode.PERSISTENT,
private javax.jms.Connection
connect
= null;
javax.jms.Message.DEFAULT_PRIORITY,1800000);
if ( session != null )
private javax.jms.Session
session
=
null;
javax.jms.Message.DEFAULT_PRIORITY,1800000);
session.close();
javax.jms.Queue queue = null;
javax.jms.Topic topic = null;
if ( connect != null )
javax.jms.MessageProducer producer = null;
connect.close();
public
public
public
public
public
public
}
JMSProducerAdapter(String broker, String username, String password){…}
void createPTPProducer(String queueName) {…}
void createPSProducer(String topicName) {…}
void sendMapMsg (Integer iid, String iname,String iprice) {..}
void sendXMLMsg ( String xmlString ) {…}
void close () {…}
44 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Example – JDBC Producer application
jmsProducer = new
JMSProducerAdapter(DEFAULT_BROKER_NAME,DEFAULT
StringBuffer xmlmsg = new StringBuffer(); xmlmsg.append
_USERNAME,DEFAULT_PASSWORD);
("<?xml version=\"1.0\"?>\n");
jmsProducer.createPTPProducer(DEFAULT_QUEUE);
xmlmsg.append
while
(rs.next()){ ("<message>\n");
idxmlmsg.append
= rs.getInt(1); (" <ItemID>" + iid + "</ItemID>\n");
public class jdbctest_producer {
xmlmsg.append
(" <ItemName>" + iname +
…
private static Connection
connection=null;
"</ItemName>\n");
private static Statement
stmt=null;
generateXMLText(id,
iname, iprice);
private static CallableStatement
callStmt=null;
xmlmsg.append (" <ItemPrice>" + iprice +
jmsProducer.sendXMLMsg
( xmlmsg.toString() );
private static JMSProducerAdapter
jmsProducer=null;
"</ItemPrice>\n");
}
xmlmsg.append ("</message>\n");
public jdbctest_producer(String
rs.close(); dbconnectstring,String username,String password){…}
private void connect(String dbconnectstring,String username,String password){…}
public void closeConnection(){…}
public ResultSet retrieveData(String query) throws Exception{…}
public void send_message (ResultSet rs) throws Exception{…}
public void generateXMLText (int iid, String iname, java.math.BigDecimal iprice){…}
public static void main(String [] args){…}
}
45 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Example–ODBC Producer application
SQLRETURN ODBC_Class::SelectRecords(SQLCHAR * SQLStmt,char *Tname)
{
…
st = SQLExecDirect (StmtHandle, SQLStmt, SQL_NTS);
st = SQLBindCol (StmtHandle, 1, SQL_C_LONG, (SQLPOINTER) &ID, sizeof(long) , &cbID);
st = SQLBindCol (StmtHandle, 2, SQL_C_CHAR, (SQLPOINTER) Name, sizeof(Name), NULL);
st = SQLBindCol (StmtHandle, 3, SQL_C_CHAR, (SQLPOINTER) Price, sizeof(Price), &cbID);
while (st != SQL_NO_DATA)
{
st = SQLFetch(StmtHandle);
if (st != SQL_NO_DATA) SendMessage(ID,Name,Price,Tname);
}
…
}
SQLRETURN ODBC_Class::SendMessage(long iid, SQLCHAR* iname, SQLCHAR* iprice,char *Tname)
{
…
st=SQLBindParameter (CallStmtHandle, 1, SQL_PARAM_INPUT,SQL_C_LONG, SQL_INTEGER,0, 0, &iid, 4, &cbID);
st=SQLBindParameter (CallStmtHandle, 2, SQL_PARAM_INPUT,SQL_C_CHAR, SQL_CHAR,30, 0, iname, 30, NULL);
st=SQLBindParameter (CallStmtHandle, 3, SQL_PARAM_INPUT,SQL_C_CHAR, SQL_CHAR,10, 0, iprice, 10, NULL);
st=SQLExecDirect(CallStmtHandle, (SQLCHAR*)"{ call JMSProducerAdapter(?,?,?) } ", SQL_NTS);
…
}
st=SQLExecDirect(CallStmtHandle, (SQLCHAR*)"{
call JMSProducerAdapter(?,?,?) } ", SQL_NTS);
46 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Example – Java Stored Procedure
JMS producer adapter
CREATE PROCEDURE JMSProducerAdapter( IN id INTEGER, IN iname VARCHAR(30),IN
iprice VARCHAR(10) )
IMPORT
import java.sql.* ;
BEGIN
JMSProducerAdapter jmsProducer=null;
String DEFAULT_BROKER_NAME = "localhost:2506";
String DEFAULT_USERNAME = "wang";
String DEFAULT_PASSWORD
= "password";
jmsProducer
= new JMSProducerAdapter (
String DEFAULT_QUEUE = "SampleQ1";DEFAULT_BROKER_NAME,
DEFAULT_USERNAME,
DEFAULT_PASSWORD);
// Invoking the JMS
adapter Java object
jmsProducer = new JMSProducerAdapter (DEFAULT_BROKER_NAME,
jmsProducer.createPTPProducer(DEFAULT_QUEUE);
DEFAULT_USERNAME,DEFAULT_PASSWORD);
jmsProducer.createPTPProducer(DEFAULT_QUEUE);
jmsProducer.sendMapMsg (id, iname, iprice);
jmsProducer.sendMapMsg (id, iname, iprice);
jmsProducer.close();
jmsProducer.close();
END
47 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Use Case #2 - Consumer
ODBC
App
JDBC App
JMS Adapter
Sending
App
SonicMQ
Broker
JMS Adapter
(java sp)
48 © 2005 Progress Software Corporation
Sending
App
OpenEdge SQL and SonicMQ
Example – JMS consumer adapter
if (aMessage instanceof javax.jms.MapMessage)
public class JMSConsumerAdapter implements javax.jms.MessageListener
{
if (aMessage
instanceof progress.message.jclient.XMLMessage) {
{
javax.jms.MapMessage
mapMessagexmlMessage
=
progress.message.jclient.XMLMessage
=
private javax.jms.Connection
connect = null;
private javax.jms.Session
session(javax.jms.MapMessage)
= null;
(progress.message.jclient.XMLMessage)
aMessage;
aMessage;
private javax.jms.Queue queue = null;
org.w3c.dom.Document
doc = xmlMessage.getDocument();
int id = mapMessage.getInt("ID");
private javax.jms.Topic topic = null;
org.w3c.dom.NodeList
nodes
String nameconsumer
= mapMessage.getString("INAME");
private javax.jms.MessageConsumer
= null;= null;
jdbctest_consumer nodes
jdbcobj
null;
The JDBC application object
==doc.getElementsByTagName("ItemID");
String
price//
= (String)mapMessage.getObject("IPRICE");
public Integer[] ids = new Integer[20];
String
id = (nodes.getLength() > 0) ?
jdbcobj.newItem(id,name,price);
public String[] names = new String[20];
: "unknown";
} nodes.item(0).getFirstChild().getNodeValue()
public String[] prices
= new String[20];
public int msgCnt = 0;…
}
public JMSConsumerAdapter(String broker, String username, String password){…}
public void createPTPConsumer(String queueName){…}
public void createPSConsumer(String topicName){…}
private void printDocNodes( org.w3c.dom.Node
node, int indentSpaces (queueName);
){…}
queue = session.createQueue
public void onMessage( javax.jms.Message aMessage) {…}
consumer = session.createConsumer(queue);
public void close () {…}
consumer.setMessageListener(this);
public static void main(String [] args){…}
}
connect.start();
49 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Example – JDBC consumer
application
public class Jdbctest_Consumer implements javax.jms.MessageListener {
…
public void onMessage( javax.jms.Message aMessage)
{
// Cast the message as a text message.
javax.jms.TextMessage textMessage = (javax.jms.TextMessage)
aMessage;
// Passing the message
receive_message = textMessage.getText();
insertRecord(receive_message);
updateRecord(receive_message);
}
public void insertRecord(String msgText){…}
Public void updateRecord(String msgText){…}
…
}
50 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Example – Java Stored Procedure
JMS consumer adapter
CREATE PROCEDURE JMSConsumerAdapter(IN sleeptime Integer)
RESULT ( id INTEGER, name VARCHAR(30), price VARCHAR(10))
IMPORT
import java.sql.* ;
jmsConsumer = new JMSConsumerAdapter (
BEGIN
DEFAULT_BROKER_NAME,
// Create the JMS adapter java object
DEFAULT_USERNAME,
JMSConsumerAdapter jmsConsumer = new
DEFAULT_PASSWORD,true);
JMSConsumerAdapter(DEFAULT_BROKER_NAME,DEFAULT_USERNAME,DEFAULT_PASSWORD,true);
// PTP model with queue
jmsConsumer.createPTPConsumer(DEFAULT_QUEUE);
jmsConsumer.createPTPConsumer(DEFAULT_QUEUE);
// Sleep to get more messages
java.lang.Thread.sleep(sleeptime.longValue());
// Translate the JMS message into the result set
for (int i=0; i< jmsConsumer.msgCnt; i++) {
SQLResultSet.set(1, jmsConsumer.ids[i]);
SQLResultSet.set(2, jmsConsumer.names[i]);
SQLResultSet.set(3, jmsConsumer.prices[i]);
SQLResultSet.insert();
}
// Close
jmsConsumer.close();
END
51 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Example – ODBC Consumer
SQLRETURN ODBC_Class::ProcessMessage()
{
st=SQLBindParameter (CallStmtHandle, 1,
SQL_PARAM_INPUT,SQL_C_LONG, SQL_INTEGER,0, 0,
SQLRETURN
st;
&sleeptime, 4, &cbID);
SQLINTEGER cbID = 0;
long sleeptime = 600;
st=SQLExecDirect(CallStmtHandle, (SQLCHAR*)"{ call
long ID = 0;
JMSConsumerAdapter(?) } ", SQL_NTS);
SQLCHAR Name [30];
st = SQLBindCol (CallStmtHandle, 1, SQL_C_LONG,
SQLCHAR Price[10];
(SQLPOINTER) &ID, sizeof(long) , &cbID);
st=SQLBindParameter (CallStmtHandle,1,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER,0,0,&sleeptime,4,&cbID);
st=SQLExecDirect(CallStmtHandle, (SQLCHAR*)"{ call JMSConsumerAdapter(?) } ", SQL_NTS);
if (st == SQL_SUCCESS) {
…
st = SQLBindCol (CallStmtHandle, 1, SQL_C_LONG, (SQLPOINTER) &ID, sizeof(long) , &cbID);
st = SQLFetch(CallStmtHandle);
st = SQLBindCol (CallStmtHandle, 2, SQL_C_CHAR, (SQLPOINTER) Name, sizeof(Name), NULL);
st = SQLBindCol (CallStmtHandle, 3, SQL_C_CHAR, (SQLPOINTER) Price, sizeof(Price), &cbID);
while (st != SQL_NO_DATA){
st = SQLFetch(CallStmtHandle);
if (st != SQL_NO_DATA){…}}
}
return PASS;
}
52 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Use Case #3 – Database Trigger
Receiving
App
JMS adapter
(java sp)
SonicMQ
Broker
JMS
Java trigger
adapter
53 © 2005 Progress Software Corporation
Receiving
App
OpenEdge SQL and SonicMQ
Example – Insert Trigger
create trigger tb1_insert_trig1
JMSProducerAdapter
jmsProducer = new JMSProducerAdapter (
after
insert on tb1
DEFAULT_BROKER_NAME, DEFAULT_USERNAME,
REFERENCING NEWROW
for each row DEFAULT_PASSWORD);
IMPORT
jmsProducer.createPTPProducer(DEFAULT_QUEUE);
import java.sql.* ;
jmsProducer.sendMapMsg (id, iname, iprice.toString());
BEGIN
// Get the
new record from the NEWROW object
jmsProducer.close();
Integer id = (Integer)NEWROW.getValue (1,INTEGER);
String iname = (String)NEWROW.getValue (2,VARCHAR);
java.math.BigDecimal iprice = (java.math.BigDecimal)NEWROW.getValue (3,NUMERIC);
// Create the JMS adapter java object
JMSProducerAdapter jmsProducer = new
JMSProducerAdapter(DEFAULT_BROKER_NAME,DEFAULT_USERNAME,DEFAULT_PASSWORD);
// PTP model with queue
jmsProducer.createPTPProducer(DEFAULT_QUEUE);
// Send the data
jmsProducer.sendMapMsg (id, iname, iprice.toString());
// Close
jmsProducer.close();
END
54 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
OpenEdge SQL and SonicMQ





JMS and SonicMQ
OpenEdge SQL
Integration
Demo
Summary and Questions
55 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Demo – Supplier and Retailer
Retailer
Java App
Supplier Java App
(sports equipment
vendor)
Adding New
product
Changing
product’s price
JMS
Browsing
orders
Show new
product
SonicMQ
Broker
Show price
changes
JMS
JMS
JDBC driver
Placing orders
OE SQL DB
JMS
Retailer
Java App
Java trigger
56 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
Demo – Supplier and Retailer

Supplier database update for
– Product’s price changes
– New arrival products
– Receiving orders from Retailer

Supplier database’s Java trigger
– Fired up and calling the JMS Adapter to send the
updated information

Retailer’s application
– Receiving Supplier’s products updates
– Sending orders to Supplier
57 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
OpenEdge SQL and SonicMQ





JMS and SonicMQ
OpenEdge SQL
Integration
Demo
Summary and Questions
58 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ
In Summary – You can do it now !
OpenEdge



SQL programmers are
able to use JMS/SonicMQ
to communicate their
JDBC or ODBC
applications
Java stored procedures
are a great OpenEdge
SQL feature to take
advantage of JMS
It is easy to develop
59 © 2005 Progress Software Corporation
SQL DB
J2EE
JDBC
.NET
SonicMQ
Broker
Any
ODBC
Any
OpenEdge
SQL DB
OpenEdge SQL and SonicMQ
Questions?





JMS and SonicMQ
OpenEdge SQL
Integration
Demo
Summary and Questions
60 © 2005 Progress Software Corporation
OpenEdge SQL and SonicMQ