Where are the messages in OpenJMS queue stored?

Question

With reference to the title, I do know that the messages dropped into a queue are stored in a database and the default is Embedded Derby. I am trying to figure out if the messages dropped into a queue could be accessed directly using a DB tool such as DB Visualizer. I did try and connect to the database from DB Visualizer based on the database properties configured in openjms.xml but none of the tables present here seem to contain a message that was dropped into one of the server's queue using a Java Application. Any pointers will be very helpful. Thank you.

More information:

1) The database that I had connected to using DB Visualizer was based on the DB url configured in OpenJMS config file; 2) I had looked up all the tables of the connected database and nothing seem to contain any reference to the data that was transmitted by my application; 3) The queue's counter still stood at "1", so I am inclined to believe that the message still exists in the queue;

Trace:

2015-01-16 10:55:11.134 GMT Thread[ORB-Worker-1,5,ORB] (XID = 263), (SESSIONID = 1), (DATABASE = openjmsdb), (DRDAID = null), Executing prepared statement: delete from message_handles where consumerId=? and expiryTime != 0 and expiryTime<? :End prepared statement with 2 parameters begin parameter #1: 5 :end parameter begin parameter #2: 1421405711134 :end parameter 
2015-01-16 10:55:11.134 GMT Thread[ORB-Worker-1,5,ORB] (XID = 263), (SESSIONID = 1), (DATABASE = openjmsdb), (DRDAID = null), Executing prepared statement: select messageid, destinationid, consumerid, priority, acceptedtime, sequencenumber, expirytime, delivered from message_handles where consumerId=? order by acceptedTime asc :End prepared statement with 1 parameters begin parameter #1: 5 :end parameter 
2015-01-16 10:55:11.135 GMT Thread[ORB-Worker-1,5,ORB] (XID = 263), (SESSIONID = 1), (DATABASE = openjmsdb), (DRDAID = null), Committing
2015-01-16 10:55:11.135 GMT Thread[ORB-Worker-1,5,ORB] (XID = 263), (SESSIONID = 1), (DATABASE = openjmsdb), (DATABASE = openjmsdb), (DRDAID = null), Rolling back
2015-01-16 10:55:11.135 GMT Thread[ORB-Worker-1,5,ORB] (XID = 263), (SESSIONID = 1), (DATABASE = openjmsdb), (DRDAID = null), Committing
2015-01-16 10:56:47.365 GMT Thread[ORB-Worker-2,5,ORB] (XID = 263), (SESSIONID = 1), (DATABASE = openjmsdb), (DRDAID = null), Committing
2015-01-16 10:56:47.365 GMT Thread[ORB-Worker-2,5,ORB] (XID = 264), (SESSIONID = 1), (DATABASE = openjmsdb), (DRDAID = null), Begin compiling prepared statement: insert into messages (messageid, destinationid, priority, createtime, expirytime, processed, messageblob) values (?,?,?,?,?,?,?) :End prepared statement
2015-01-16 10:56:47.377 GMT Thread[ORB-Worker-2,5,ORB] (XID = 264), (SESSIONID = 1), (DATABASE = openjmsdb), (DRDAID = null), End compiling prepared statement: insert into messages (messageid, destinationid, priority, createtime, expirytime, processed, messageblob) values (?,?,?,?,?,?,?) :End prepared statement
2015-01-16 10:56:47.388 GMT Thread[ORB-Worker-2,5,ORB] (XID = 264), (SESSIONID = 1), (DATABASE = openjmsdb), (DRDAID = null), Executing prepared statement: insert into messages (messageid, destinationid, priority, createtime, expirytime, processed, messageblob) values (?,?,?,?,?,?,?) :End prepared statement with 7 parameters begin parameter #1: ID:437f9d34-d89d-1004-8a2f-64db473eadbd :end parameter begin parameter #2: 5 :end parameter begin parameter #3: 3 :end parameter begin parameter #4: 1421405807364 :end parameter begin parameter #5: 0 :end parameter begin parameter #6: 0 :end parameter begin parameter #7: aced0005737200266f72672e65786f6c61622e6a6d732e6d6573736167652e546578744d657373616765496d706c00000000000000010c0000787200226f72672e65786f6c61622e6a6d732e6d6573736167652e4d657373616765496d706c00000000000000010c00007870771a000000000000000101000000014af26363040000000000000001737200246f72672e65786f6c61622e6a6d732e6d6573736167652e4d65737361676548656164657200000000000000010c000078707708000000000000000170737200206f72672e65786f6c61622e6a6d732e6d6573736167652e54696d657374616d7000000000000000010c00007872000e6a6176612e7574696c2e44617465686a81014b5974190300007870771000000000000000010000014af26362e7787077090000000000000000007372001f6f72672e65786f6c61622e6a6d732e6d6573736167652e5072696f7269747900000000000000010c00007870770c000000000000000100000003787372001b6f72672e65786f6c61622e6a6d732e6d6573736167652e5479706500000000000000010c00007870772200000000000000010000000b0054006500780074004d006500730073006100670065787372001e6f72672e65786f6c61622e6a6d732e636c69656e742e4a6d73517565756500000000000000010c0000787200246f72672e65786f6c61622e6a6d732e636c69656e742e4a6d7344657374696e6174696f6e00000000000000010c0000787200266f72672e65786f6c61622e6a6d732e6d6573736167652e44657374696e6174696f6e496d706c00000000000000010c000078707737000000000000000100000000000000010100000000000000010000000d007200650063006500690076006500720051007500650075006578737200276f72672e65786f6c61622e6a6d732e6d6573736167652e44656c69766572794d6f6465496d706c00000000000000010c00007870770c00000000000000010000000278737200206f72672e65786f6c61622e6a6d732e6d6573736167652e4d657373616765496400000000000000020c0000787077310000000000000002002749443a34333766396433342d643839642d313030342d386132662d3634646234373365616462647870707708000000000000000078737200286f72672e65786f6c61622e6a6d732e6d6573736167652e4d65737361676550726f7065727469657300000000000000010c0000787077080000000000000001737200116a6176612e7574696c2e486173684d61700507dac1c31660d103000246000a6c6f6164466163746f724900097468726573686f6c6478703f40000000000014770800000020000000007878770800000000000000017400383c3f786d6c2076657273696f6e3d22312e302220656e636f64696e673d225554462d3822203f3e3c546573743e646174613c2f546573743e78 :end parameter 
2015-01-16 10:56:47.407 GMT Thread[ORB-Worker-2,5,ORB] (XID = 264), (SESSIONID = 1), (DATABASE = openjmsdb), (DRDAID = null), Begin compiling prepared statement: insert into message_handles (messageid, destinationid, consumerid, priority, acceptedtime, sequencenumber, expirytime, delivered) values (?,?,?,?,?,?,?,?) :End prepared statement
2015-01-16 10:56:47.412 GMT Thread[ORB-Worker-2,5,ORB] (XID = 264), (SESSIONID = 1), (DATABASE = openjmsdb), (DRDAID = null), End compiling prepared statement: insert into message_handles (messageid, destinationid, consumerid, priority, acceptedtime, sequencenumber, expirytime, delivered) values (?,?,?,?,?,?,?,?) :End prepared statement
2015-01-16 10:56:47.415 GMT Thread[ORB-Worker-2,5,ORB] (XID = 264), (SESSIONID = 1), (DATABASE = openjmsdb), (DRDAID = null), Executing prepared statement: insert into message_handles (messageid, destinationid, consumerid, priority, acceptedtime, sequencenumber, expirytime, delivered) values (?,?,?,?,?,?,?,?) :End prepared statement with 8 parameters begin parameter #1: ID:437f9d34-d89d-1004-8a2f-64db473eadbd :end parameter begin parameter #2: 5 :end parameter begin parameter #3: 6 :end parameter begin parameter #4: 3 :end parameter begin parameter #5: 1421405807364 :end parameter begin parameter #6: 1 :end parameter begin parameter #7: 0 :end parameter begin parameter #8: 0 :end parameter 
2015-01-16 10:56:47.417 GMT Thread[ORB-Worker-2,5,ORB] (XID = 264), (SESSIONID = 1), (DATABASE = openjmsdb), (DRDAID = null), Committing
2015-01-16 10:56:47.418 GMT Thread[ORB-Worker-2,5,ORB] (XID = 264), (SESSIONID = 1), (DATABASE = openjmsdb), (DATABASE = openjmsdb), (DRDAID = null), Rolling back
2015-01-16 10:56:47.418 GMT Thread[ORB-Worker-2,5,ORB] (XID = 264), (SESSIONID = 1), (DATABASE = openjmsdb), (DRDAID = null), Committing
2015-01-16 10:56:54.228 GMT Thread[ORB-Worker-2,5,ORB] (XID = 264), (SESSIONID = 1), (DATABASE = openjmsdb), (DRDAID = null), Committing
2015-01-16 10:56:54.228 GMT Thread[ORB-Worker-2,5,ORB] (XID = 264), (SESSIONID = 1), (DATABASE = openjmsdb), (DRDAID = null), Committing
2015-01-16 10:56:54.228 GMT Thread[ORB-Worker-2,5,ORB] (XID = 264), (SESSIONID = 1), (DATABASE = openjmsdb), (DATABASE = openjmsdb), (DRDAID = null), Rolling back
2015-01-16 10:56:54.228 GMT Thread[ORB-Worker-2,5,ORB] (XID = 264), (SESSIONID = 1), (DATABASE = openjmsdb), (DRDAID = null), Committing
2015-01-16 10:56:54.235 GMT Thread[ORB-Worker-2,5,ORB] (XID = 264), (SESSIONID = 1), (DATABASE = openjmsdb), (DRDAID = null), Committing
2015-01-16 10:56:54.236 GMT Thread[ORB-Worker-2,5,ORB] (XID = 269), (SESSIONID = 1), (DATABASE = openjmsdb), (DRDAID = null), Executing prepared statement: select * from users :End prepared statement
2015-01-16 10:56:54.236 GMT Thread[ORB-Worker-2,5,ORB] (XID = 269), (SESSIONID = 1), (DATABASE = openjmsdb), (DRDAID = null), Committing
2015-01-16 10:56:54.236 GMT Thread[ORB-Worker-2,5,ORB] (XID = 269), (SESSIONID = 1), (DATABASE = openjmsdb), (DATABASE = openjmsdb), (DRDAID = null), Rolling back
2015-01-16 10:56:54.237 GMT Thread[ORB-Worker-2,5,ORB] (XID = 269), (SESSIONID = 1), (DATABASE = openjmsdb), (DRDAID = null), Committing

Connection Properties from DB Visualizer

Yes, any JDBC-aware tool should be able to connect to your Derby database using the Derby JDBC driver. Perhaps the reason you didn't see any records is because you looked in the wrong table (or the wrong schema). Perhaps the reason you didn't see any records is because your message queue software deleted those records. Perhaps the reason you didn't see any records is because you didn't connect to the database that you thought you should connect to. If you can provide more information, perhaps we can help you further. — Bryan Pendleton, Jan 14 '15 at 22:48
@BryanPendleton, Thanks for your time, I've added to the case the details you had asked for. — Srii, Jan 15 '15 at 10:17
Can you find the derby.log which is associated with your database? If so, add -Dderby.language.logStatementText=true to your application and run it again. Then look in the derby.log to see the SQL statements that are being executed against this Derby database. That will give you more clues about where and how your data is being stored. https://db.apache.org/derby/docs/10.11/ref/rrefproper43517.html — Bryan Pendleton, Jan 15 '15 at 14:53
@BryanPendleton, thanks for that tip. There was infact this command already present in the derby.properties file as a suggestion but commented out. I have enabled it and the produced log is added to the case. The log is quite confusing to me as it displays several commits and roll backs. The DB do not display a table 'messages' at all which is seen in trace. I am also enclosing the connection properties of the database from DB Visualizer. — Srii, Jan 16 '15 at 11:12
Several common causes of "no such table" or "table does not exist" are described in this related question: http://stackoverflow.com/questions/22996818/is-it-necessary-to-create-tables-each-time-you-connect-the-derby-database/23051822#23051822 — Bryan Pendleton, Jan 16 '15 at 14:44
Have you tried another DB access tool, such as SQL Squirrel, or Derby's own 'ij' tool, with its powerful 'show' command? — Bryan Pendleton, Jan 16 '15 at 14:51

Where are the messages in OpenJMS queue stored?

0 Answers0