1 / 60

OpenEdge® SQL & SonicMQ®: Using JMS with SQL Applications

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. OpenEdge SQL and SonicMQ. JMS and SonicMQ OpenEdge SQL

baldwin
Download Presentation

OpenEdge® SQL & SonicMQ®: Using JMS with SQL Applications

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. OpenEdge® SQL & SonicMQ®:Using JMS with SQL Applications Pat Bonser

  2. 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 OpenEdge SQL and SonicMQ

  3. OpenEdge SQL and SonicMQ • JMS and SonicMQ • OpenEdge SQL • Integration • Demo • Summary and Questions OpenEdge SQL and SonicMQ

  4. Example – Supplier and Retailer Retailer Java™ App Supplier Java App (sports equipment vendor) JMS Show new product Show price changes SonicMQ Broker Adding New product Changing product’s price Browsing orders JMS JMS JDBC driver Placing orders OE SQL DB Retailer Java App JMS Java trigger OpenEdge SQL and SonicMQ

  5. 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 … OpenEdge SQL and SonicMQ

  6. JMS • A specification for Message-Oriented Middleware (MOM) provider • Version 1.1 • A Java interface and semantics for inter-application messaging • Basic functionality of JMS • Javax.jms.* package • Enterprise Application Integration (EAI) • http://java.sun.com/products/jms OpenEdge SQL and SonicMQ

  7. JMS Features • Standard client API • Asynchronous or synchronous guaranteed message delivery • Transactions • Message filtering • Reliability levels • Two messaging models with a common interface OpenEdge SQL and SonicMQ

  8. Basic JMS Terminology Application B Application A JMS - Message Oriented Middleware Connection Session Destination Producer Consumer OpenEdge SQL and SonicMQ

  9. JMS Two Messaging Models Point to Point (1 to 1) Potential Receiver Queue Sender Potential Receiver Publish and Subscribe (1 to Many) Subscriber Publisher Topic Subscriber OpenEdge SQL and SonicMQ

  10. Development of JMS • Develop the JMS client application • Develop the JMS adapter • Develop the producers and consumers • Develop the interaction with the JMS server OpenEdge SQL and SonicMQ

  11. 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 OpenEdge SQL and SonicMQ

  12. 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 ) OpenEdge SQL and SonicMQ

  13. 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 OpenEdge SQL and SonicMQ

  14. Start the SonicMQ Domain Start →Programs →Sonic Software →SonicMQ →SonicMQ Container1 Domain Container1 Broker1 Topic Queue Directory Service Agent Manager OpenEdge SQL and SonicMQ

  15. 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 OpenEdge SQL and SonicMQ

  16. OpenEdge SQL and SonicMQ • JMS and SonicMQ • OpenEdge SQL • Integration • Demo • Summary and Questions OpenEdge SQL and SonicMQ

  17. 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 OpenEdge SQL and SonicMQ

  18. OpenEdge SQL client-server architecture Java App Windows App JDBC driver ODBC driver OE SQL Server JVM Database Storage Manager OpenEdge SQL Database Java sp/trigger OpenEdge SQL and SonicMQ

  19. 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 OpenEdge SQL and SonicMQ

  20. 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) OpenEdge SQL and SonicMQ

  21. Basic JDBC API Driver Manager <Interface> Driver <Interface> Connection <Interface> Statement <Interface> ResultSet <Interface> PreparedStatement <Interface> DatabaseMetaData <Interface> ResultSetMetaData <Interface> CallableStatement OpenEdge SQL and SonicMQ

  22. 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,…) OpenEdge SQL and SonicMQ

  23. 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 OpenEdge SQL and SonicMQ

  24. 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 OpenEdge SQL and SonicMQ

  25. 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 OpenEdge SQL and SonicMQ

  26. 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 OpenEdge SQL and SonicMQ

  27. Accessing Stored Procedures from Applications • Embed the call escape sequence { CALL proc_name [ ( parameter [ , ... ] ) ] } • JDBC “CallableStatement” • ODBC “SQLPrepare” or “SQLExecDirect” try { CallableStatement statement; int Part_num = 318; statement = conn.prepareCall("{call order_parts(?)}"); statement.setInt(1, Part_num); statement.execute(); } 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); OpenEdge SQL and SonicMQ

  28. 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 OpenEdge SQL and SonicMQ

  29. OpenEdge SQL JAVA classes SQLIStatement SQLPStatement SQLCursor DhSQLResultSet DhSQLException OpenEdge SQL and SonicMQ

  30. OpenEdge SQL and SonicMQ • JMS and SonicMQ • OpenEdge SQL • Integration • Demo • Summary and Questions OpenEdge SQL and SonicMQ

  31. SonicMQ Messaging OpenEdge®Application Java/J2EE™ Application EIS JMS Customer Adapter JMS Message Broker ( SonicMQ ) Customer Adapter JMS Connection Legacy Application OpenEdge Database Session Producer or Consumer OpenEdge SQL and SonicMQ

  32. 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 OpenEdge SQL and SonicMQ

  33. Writing a JMS Adapter • Set the CLASSPATH • Choose the messaging model • PTP • PUB/SUB • Identify the role • Producer • Consumer • Prepare message types OpenEdge SQL and SonicMQ

  34. Include SonicMQ jars • CLASSPATH needs to include: • sonic_Client.jar • mfcontext.jar • gnu-regexp-1.0.6.jar • jaxp.jar • xercesImpl.jar • xmlParserAPIs.jar OpenEdge SQL and SonicMQ

  35. PTP - Basic Steps • Initialization - Connect to a SonicMQ Broker - Create a Session - Create or lookup queues • Create a Message Producer/Consumer • Prepare Messages • Send/Receive Message • Close Message Producer/Consumer OpenEdge SQL and SonicMQ

  36. Pub\Sub - Basic Steps • Initialization - Connect to a SonicMQ broker - Create a Session - Create or lookup Topics • Create a message publisher • Subscribe to Topic • Publish message to Topic • Consume message from a Topic • Close Message Producer/Consumer OpenEdge SQL and SonicMQ

  37. Writing a JMS Adapter – Initialization String broker = "localhost:2506";//default sonic broker String username = "wang"; //default user name String password = "password";//default user password // Create a SonicMQ connection. Try { javax.jms.ConnectionFactory factory; factory = (new progress.message.jclient.ConnectionFactory (broker)); 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); } ConectionFactory Creates Connection Creates Session OpenEdge SQL and SonicMQ

  38. Creating a Producer createQueue( String queueName ) Queue createTopic( String topicName ) Topic session 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(); OpenEdge SQL and SonicMQ

  39. Creating a Consumer createQueue( String queueName ) Queue createTopic( String topicName ) Topic session 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(); OpenEdge SQL and SonicMQ

  40. Message Types OpenEdge SQL and SonicMQ

  41. 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 OpenEdge SQL and SonicMQ

  42. Working on Messages • Producing messages • javax.jms.Session.create[message type]() • javax.jms.MessageProducer.send() • Consuming messages • Javax.jms.MessageListener.onMessage() • Javax.jms.MessageConsumer.receive() javax.jms.MapMessage mmsg = session.createMapMessage(); Progress.message.jclient.XMLMessage xmsg = ((progress.message.jclient.Session) session).createXMLMessage(); public void onMessage( javax.jms.Message aMessage) { … javax.jms.TextMessage textMessage = (javax.jms.TextMessage) aMessage; String string = textMessage.getText(); } … } OpenEdge SQL and SonicMQ

  43. Use Case #1 - Producer JDBC App ODBC App JMS adapter Receiving App SonicMQ Broker Receiving App JMS adapter (java sp) OpenEdge SQL and SonicMQ

  44. Example – JMS producer adapter javax.jms.MapMessage msg=session.createMapMessage(); msg.setInt( "ID", iid.intValue()); msg.setString( "INAME", iname); msg.setString( "IPRICE", iprice); producer.send( msg,javax.jms.DeliveryMode.PERSISTENT, javax.jms.Message.DEFAULT_PRIORITY,1800000); progress.message.jclient.XMLMessage msg = ((progress.message.jclient.Session)session).createXMLMessage(); msg.setText(xmlString); producer.send( msg,javax.jms.DeliveryMode.PERSISTENT, javax.jms.Message.DEFAULT_PRIORITY,1800000); if ( producer != null ) producer.close(); if ( session != null ) session.close(); if ( connect != null ) connect.close(); public class JMSProducerAdapter { private javax.jms.Connection connect = null; private javax.jms.Session session = null; javax.jms.Queue queue = null; javax.jms.Topic topic = null; javax.jms.MessageProducer producer = null; public JMSProducerAdapter(String broker, String username, String password){…} public void createPTPProducer(String queueName) {…} public void createPSProducer(String topicName) {…} public void sendMapMsg (Integer iid, String iname,String iprice) {..} public void sendXMLMsg ( String xmlString ) {…} public void close () {…} } OpenEdge SQL and SonicMQ

  45. Example – JDBC Producer application jmsProducer = new JMSProducerAdapter(DEFAULT_BROKER_NAME,DEFAULT_USERNAME,DEFAULT_PASSWORD); jmsProducer.createPTPProducer(DEFAULT_QUEUE); while (rs.next()){ id = rs.getInt(1); … generateXMLText(id, iname, iprice); jmsProducer.sendXMLMsg ( xmlmsg.toString() ); } rs.close(); StringBuffer xmlmsg = new StringBuffer(); xmlmsg.append ("<?xml version=\"1.0\"?>\n"); xmlmsg.append ("<message>\n"); xmlmsg.append (" <ItemID>" + iid + "</ItemID>\n"); xmlmsg.append (" <ItemName>" + iname + "</ItemName>\n"); xmlmsg.append (" <ItemPrice>" + iprice + "</ItemPrice>\n"); xmlmsg.append ("</message>\n"); public class jdbctest_producer { private static Connection connection=null; private static Statement stmt=null; private static CallableStatement callStmt=null; private static JMSProducerAdapter jmsProducer=null; public jdbctest_producer(String 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){…} } OpenEdge SQL and SonicMQ

  46. Example–ODBC Producer application st=SQLExecDirect(CallStmtHandle, (SQLCHAR*)"{ call JMSProducerAdapter(?,?,?) } ", SQL_NTS); 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); … } OpenEdge SQL and SonicMQ

  47. 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"; String DEFAULT_QUEUE = "SampleQ1"; // Invoking the JMS adapter Java object jmsProducer = new JMSProducerAdapter (DEFAULT_BROKER_NAME, DEFAULT_USERNAME,DEFAULT_PASSWORD); jmsProducer.createPTPProducer(DEFAULT_QUEUE); jmsProducer.sendMapMsg (id, iname, iprice); jmsProducer.close(); END jmsProducer = new JMSProducerAdapter ( DEFAULT_BROKER_NAME, DEFAULT_USERNAME, DEFAULT_PASSWORD); jmsProducer.createPTPProducer(DEFAULT_QUEUE); jmsProducer.sendMapMsg (id, iname, iprice); jmsProducer.close(); OpenEdge SQL and SonicMQ

  48. Use Case #2 - Consumer JDBC App ODBC App JMS Adapter Sending App SonicMQ Broker JMS Adapter (java sp) Sending App OpenEdge SQL and SonicMQ

  49. Example – JMS consumer adapter if (aMessage instanceof javax.jms.MapMessage) { javax.jms.MapMessage mapMessage = (javax.jms.MapMessage) aMessage; int id = mapMessage.getInt("ID"); String name = mapMessage.getString("INAME"); String price = (String)mapMessage.getObject("IPRICE"); jdbcobj.newItem(id,name,price); } public class JMSConsumerAdapter implements javax.jms.MessageListener { private javax.jms.Connection connect = null; private javax.jms.Session session = null; private javax.jms.Queue queue = null; private javax.jms.Topic topic = null; private javax.jms.MessageConsumer consumer = null; jdbctest_consumer jdbcobj = null; // The JDBC application object public Integer[] ids = new Integer[20]; public String[] names = new String[20]; 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 ){…} public void onMessage( javax.jms.Message aMessage) {…} public void close () {…} public static void main(String [] args){…} } if (aMessage instanceof progress.message.jclient.XMLMessage) { progress.message.jclient.XMLMessage xmlMessage = (progress.message.jclient.XMLMessage) aMessage; org.w3c.dom.Document doc = xmlMessage.getDocument(); org.w3c.dom.NodeList nodes = null; nodes = doc.getElementsByTagName("ItemID"); String id = (nodes.getLength() > 0) ? nodes.item(0).getFirstChild().getNodeValue() : "unknown"; … } queue = session.createQueue (queueName); consumer = session.createConsumer(queue); consumer.setMessageListener(this); connect.start(); OpenEdge SQL and SonicMQ

  50. 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){…} … } OpenEdge SQL and SonicMQ

More Related