embedded-DB, muc, conversation log, history

Hello,

maybe this topic was allready discussed, but I didn’t find a solution in another thread.

I’ve created a web based MUC application using the Smack API.

Now the admin of the chat should be able to store the chat conversation (id, usernam, time, message,…) over the web application. I guess a xml format would be the best for storing reasons.

Im using the embedded DB of openfire. I know that the Monitoring plugin stores conversations/ group chat conversations.

I guess that the conversation is stored in the embedded DB under Openfire\embedded-db\openfire.log - right?

Could you may explain how I can access the log/DB to read out the data I need?

(It should be something using SQL statments, right?)

I think I just don’t get the step about how to access and use the stored data in the embedded DB.

Thank you for advice!

There is a java based db editor included with the openfire embedded db. I have never used it, but you will need to use that to edit the db correctly. You should use an external db such as MySQL for a production sever so you have a full compliment of admin tools and backup solutions at your disposal.

Thank you for answering so quickly.

I guess you mean the HSQL Database Manager (embedded-db-viewer.bat)?!

So I should use a mysql DB instead of the embedded, ok. I just thought there would be no need for this. Because I just want to store the group chat conversation in an xml or text file.

Could you explain your answer a bit more precisely?

Is there no way of accessing the embedded DB and reading out the relevant data?

Ok, I figured out some points.

At first I did the mistake that openfire was running when I tried to use the embedded-db.viewer.bat - I just closed Openfire and I was able to start the viewer, see the DB and test some sql statements.

Then I tried to do a connection to the embedded HSQLDB in this way (just some important parts):

this.dbURL = “jdbc:hsqldb:file:C:/Programme/Openfire/embedded-db/openfire”;

this.dbUser = “SA”;
this.dbPassword = “”;

Class.forName(“org.hsqldb.jdbcDriver”);

Connection c = DriverManager.getConnection(dbURL, dbUser, dbPassword);

Everything should be fine, but then i got this:

java.sql.SQLException: The database is already in use by another process: org.hsqldb.persist.NIOLockFile@9f736229[file =C:\Programme\Openfire\embedded-db\openfire.lck, exists=true, locked=false, valid=false, fl =null]: java.lang.Exception: java.io.IOException: Der Prozess kann nicht auf die Datei zugreifen, da ein anderer Prozess einen Teil der Datei gesperrt hat : C:\Programme\Openfire\embedded-db\openfire.lck

Does anyone has a solution for this problem?

Soo, I think I found out why I get this exception…

The problem about the embedded hsqldb of Openfire is the following: If you started the Openfire server the HSQLDB will be in use. If you then start to access the DB with the help of an process or application, you are just not able to access the DB because it’s already in use.

So it is not possible that two processes are accessing the HSQLDB at the same time.

Could a mod or anybody else confirm this problem?

And is there maybe a workaround for that?

Because of this problem I will use an external DB, like @Todd Getz had already recommended to me.