You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@activemq.apache.org by Johan Hallgren <jh...@apiworldwide.com> on 2006/03/24 04:38:56 UTC

Adding support for MaxDB in ActiveMQ JDBC persistence layer

Hi list,

I am currently in the process of evaluating ActiveMQ. Reading http://activemq.org/JDBC+Support I see that it supports quite a lot of databases, but unfortunately not the one that my company is using (MaxDB (formerly SAPDB), http://www.mysql.com/products/maxdb/). I try to configure activemq to use it by copying its JDBC jar file to activemq's lib directory, and configuring activemq.xml to use it like this:

  <bean id="maxdb-ds" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="com.sap.dbtech.jdbc.DriverSapDB"/>
    <property name="url" value="jdbc:sapdb://localhost/DatabaseInstanceName"/>
    <property name="username" value="activemquser"/>
    <property name="password" value="activemquser"/>
  </bean>

I used the MySQL sample config section as a template and substituted values for my MaxDB instance (the class value I used as is, since I didn't know what to use there). However, when starting ActiveMQ, I get these errors (I have created the database user mentioned above, but not created any tables in that schema):

ACTIVEMQ_HOME: /usr/local/activemq-4.0-M4
Loading message broker from: xbean:activemq.xml
INFO  BrokerService                  - ActiveMQ 4.0-M4 JMS Message Broker (localhost) is starting
INFO  BrokerService                  - For help or more information please see:http://www.logicblaze.com
WARN  JDBCPersistenceAdapter         - Database driver NOT recognized: [sap_db].  Will use default JDBC implementation.
WARN  DefaultJDBCAdapter             - Could not create JDBC tables; they couldalready exist. Failure was: CREATE TABLE ACTIVEMQ_MSGS(ID INTEGER NOT NULL, CONTAINER VARCHAR(250), MSGID_PROD VARCHAR(250), MSGID_SEQ INTEGER, EXPIRATION BIGINT, MSG BLOB, PRIMARY KEY ( ID ) ) Message: [-4006] (at 128): Unknown domain name:BIGINT SQLState: I4006 Vendor code: -4006

...Further similar errors snipped.

>From my understanding of what is happening, it seems that the SQL statements that are used by ActiveMQ won't work on MaxDB (BIGINT is indeed a field type that is not recognized by that database). My questions are thus:

* Does anyone successfully use ActiveMQ with MaxDB, and if so, how should I configure activemq.xml to make it work?
* If it does indeed not work, what would it take to add support for MaxDB to ActiveMQ? The ActiveMQ web site further mentions that "If you have a JDBC database which we don't support then please let us know what JDBC driver string you're getting." If someone would tell me how, I'd be happy to provide the JDBC string I'm getting (could it be "sap_db", as seen in the output snippet above?), and help in any other way I can to add support for this database.

Thanks in advance for any insight!

Regards,
Johan Hallgren

Re: Adding support for MaxDB in ActiveMQ JDBC persistence layer

Posted by James Strachan <ja...@gmail.com>.
Thanks for the heads up Johan.

I've added MaxDB to the wiki page...
http://docs.codehaus.org/display/ACTIVEMQ/JDBC+Support

On 3/29/06, Johan Hallgren <jh...@apiworldwide.com> wrote:
> Hiram,
>
> Thanks for the rapid response. I downloaded the latest snapshot (3/29), and from the log output it seems that MaxDB is now detected on startup:
>
> INFO  JDBCPersistenceAdapter         - Database driver recognized: [sap_db]
>
> I then set the log4j.properties to do DEBUG logging (as instructed in that file), and restarted the broker. I am attaching the log file to the issue, as requested below. I further changed the example producer and consumer pieces to use durable storage, and successfully ran those and observed that the messages seemed to be correctly stored in the database between production and consumption.
>
> Please let me know if you need any further information from me, and thanks again for your prompt supprt on this issue.
>
> /Johan H
>
> -----Original Message-----
> From: Hiram Chirino [mailto:hiram@hiramchirino.com]
> Sent: Tue 3/28/2006 11:47 AM
> To: activemq-users@geronimo.apache.org
> Subject: RE: Adding support for MaxDB in ActiveMQ JDBC persistence layer
>
>
> Hi Johan,
>
> I've just commited some intial support for MaxDB into the source tree.  When
> you get a chance, please let me know if it properly auto-detects your
> database properly.  Also could you run with the DEBUG logging enabled and
> attach the log file to issue:
>
> https://issues.apache.org/activemq/browse/AMQ-660
>
> That should help me resolve the "Old message cleanup failed due to:
> com.sap.dbtech.jdbc.exceptions.DatabaseException: [-9404]: System error: AK
> System error: VAK724 1" error.
>
> Regards,
> Hiram
>
> --
> View this message in context: http://www.nabble.com/Adding-support-for-MaxDB-in-ActiveMQ-JDBC-persistence-layer-t1334466.html#a3634495
> Sent from the ActiveMQ - User forum at Nabble.com.
>
>
>
>


--

James
-------
http://radio.weblogs.com/0112098/

RE: Adding support for MaxDB in ActiveMQ JDBC persistence layer

Posted by Johan Hallgren <jh...@apiworldwide.com>.
Hiram,

Thanks for the rapid response. I downloaded the latest snapshot (3/29), and from the log output it seems that MaxDB is now detected on startup:

INFO  JDBCPersistenceAdapter         - Database driver recognized: [sap_db]

I then set the log4j.properties to do DEBUG logging (as instructed in that file), and restarted the broker. I am attaching the log file to the issue, as requested below. I further changed the example producer and consumer pieces to use durable storage, and successfully ran those and observed that the messages seemed to be correctly stored in the database between production and consumption. 

Please let me know if you need any further information from me, and thanks again for your prompt supprt on this issue.

/Johan H

-----Original Message-----
From: Hiram Chirino [mailto:hiram@hiramchirino.com]
Sent: Tue 3/28/2006 11:47 AM
To: activemq-users@geronimo.apache.org
Subject: RE: Adding support for MaxDB in ActiveMQ JDBC persistence layer
 

Hi Johan,

I've just commited some intial support for MaxDB into the source tree.  When
you get a chance, please let me know if it properly auto-detects your
database properly.  Also could you run with the DEBUG logging enabled and
attach the log file to issue:

https://issues.apache.org/activemq/browse/AMQ-660

That should help me resolve the "Old message cleanup failed due to:
com.sap.dbtech.jdbc.exceptions.DatabaseException: [-9404]: System error: AK
System error: VAK724 1" error.

Regards,
Hiram

--
View this message in context: http://www.nabble.com/Adding-support-for-MaxDB-in-ActiveMQ-JDBC-persistence-layer-t1334466.html#a3634495
Sent from the ActiveMQ - User forum at Nabble.com.



RE: Adding support for MaxDB in ActiveMQ JDBC persistence layer

Posted by Hiram Chirino <hi...@hiramchirino.com>.
Hi Johan,

I've just commited some intial support for MaxDB into the source tree.  When
you get a chance, please let me know if it properly auto-detects your
database properly.  Also could you run with the DEBUG logging enabled and
attach the log file to issue:

https://issues.apache.org/activemq/browse/AMQ-660

That should help me resolve the "Old message cleanup failed due to:
com.sap.dbtech.jdbc.exceptions.DatabaseException: [-9404]: System error: AK
System error: VAK724 1" error.

Regards,
Hiram

--
View this message in context: http://www.nabble.com/Adding-support-for-MaxDB-in-ActiveMQ-JDBC-persistence-layer-t1334466.html#a3634495
Sent from the ActiveMQ - User forum at Nabble.com.


RE: Adding support for MaxDB in ActiveMQ JDBC persistence layer

Posted by Johan Hallgren <jh...@apiworldwide.com>.
Hi James,

Thanks for your swift reply. I tried to follow the tips in http://docs.codehaus.org/display/ACTIVEMQ/JDBC+Support and http://activemq.codehaus.org/maven/apidocs/org/apache/activemq/store/jdbc/Statements.html to enable the persistance tables to be created on MaxDB, and found that with these data type definition changes, the tables could be created:

  <persistenceAdapter>
    <journaledJDBC journalLogFiles="5" dataDirectory="../activemq-data" dataSource="#maxdb-ds">
      <statements>
        <statements binaryDataType ="LONG BYTE" longDataType ="INTEGER" stringIdDataType="VARCHAR(250) ASCII"/>
      </statements>
    </journaledJDBC>
  </persistenceAdapter>

MaxDB uses INTEGER for all types of integer numbers and LONG BYTE for a large binary object. Also, MaxDB only allows a total primary key field length of 1024 bytes per table, so if you try and create the persistance layer in a Unicode database instance you have to explicitly set the stringIdDataType to ASCII as above (and it doesn't hurt to do it even if you run an ASCII database instance).

Anyway, now when I try to start the broker, I get all normal-looking INFO messages in the console window, execpt for these two:

WARN  JDBCPersistenceAdapter         - Database driver NOT recognized: [sap_db].  Will use default JDBC implementation.
WARN  JDBCPersistenceAdapter         - Old message cleanup failed due to: com.sap.dbtech.jdbc.exceptions.DatabaseException: [-9404]: System error: AK System error: VAK724 1

The startup then proceeds to state that the message broker has started, but the second of the above WARN messages then repeat whenever cleanup is attempted.

I am hoping that thses changes cover what needs to be done as regards tweaking the datatypes. How can I find out what the JDBC connection type name is for the MaxDB JDBC driver, so that it can be added to ActiveMQ? Also, is there a way to have the example producer and consumer in http://www.activemq.org/Examples use persistent storage to test that it actually works with MaxDB (the producer output says "Using non-durable publishing", so I assume the persistence layer is never used there)?

Any further insight much appreciated!

/Johan H


-----Original Message-----
From: James Strachan [mailto:james.strachan@gmail.com]
Sent: Fri 3/24/2006 7:57 AM
To: activemq-users@geronimo.apache.org
Subject: Re: Adding support for MaxDB in ActiveMQ JDBC persistence layer
 
Hi Johan

So mostly we need to work out the flavour of SQL that MaxDB supports
and to figure out what the DDL should look like etc. We have a number
of JDBC adaptors for the various different drives together with how
they handle data types and blobs etc. It could be we just need to
write one for MaxDB.

Also it could be we need to tweak a little the SQL data types we use -
I"ve just added a new section to this page describing how to do
that....

http://docs.codehaus.org/display/ACTIVEMQ/JDBC+Support

Once we basically know the right combination of options that work for
MaxDB, we can add an entry into ActiveMQ so that we auto-discover the
database from the JDBC connection type name and do the right thing.

e.g. here is the current list of auto-discovered configurations of
JDBC, where the file name is the JDBC connection type name returned
from the JDBC driver

http://svn.apache.org/repos/asf/incubator/activemq/trunk/activemq-core/src/main/resources/META-INF/services/org/apache/activemq/store/jdbc/

James

On 3/23/06, Johan Hallgren <jh...@apiworldwide.com> wrote:
> Hi list,
>
> I am currently in the process of evaluating ActiveMQ. Reading http://activemq.org/JDBC+Support I see that it supports quite a lot of databases, but unfortunately not the one that my company is using (MaxDB (formerly SAPDB), http://www.mysql.com/products/maxdb/). I try to configure activemq to use it by copying its JDBC jar file to activemq's lib directory, and configuring activemq.xml to use it like this:
>
>   <bean id="maxdb-ds" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
>     <property name="driverClassName" value="com.sap.dbtech.jdbc.DriverSapDB"/>
>     <property name="url" value="jdbc:sapdb://localhost/DatabaseInstanceName"/>
>     <property name="username" value="activemquser"/>
>     <property name="password" value="activemquser"/>
>   </bean>
>
> I used the MySQL sample config section as a template and substituted values for my MaxDB instance (the class value I used as is, since I didn't know what to use there). However, when starting ActiveMQ, I get these errors (I have created the database user mentioned above, but not created any tables in that schema):
>

[snip]
--

James
-------
http://radio.weblogs.com/0112098/


Re: Adding support for MaxDB in ActiveMQ JDBC persistence layer

Posted by James Strachan <ja...@gmail.com>.
Hi Johan

So mostly we need to work out the flavour of SQL that MaxDB supports
and to figure out what the DDL should look like etc. We have a number
of JDBC adaptors for the various different drives together with how
they handle data types and blobs etc. It could be we just need to
write one for MaxDB.

Also it could be we need to tweak a little the SQL data types we use -
I"ve just added a new section to this page describing how to do
that....

http://docs.codehaus.org/display/ACTIVEMQ/JDBC+Support

Once we basically know the right combination of options that work for
MaxDB, we can add an entry into ActiveMQ so that we auto-discover the
database from the JDBC connection type name and do the right thing.

e.g. here is the current list of auto-discovered configurations of
JDBC, where the file name is the JDBC connection type name returned
from the JDBC driver

http://svn.apache.org/repos/asf/incubator/activemq/trunk/activemq-core/src/main/resources/META-INF/services/org/apache/activemq/store/jdbc/

James

On 3/23/06, Johan Hallgren <jh...@apiworldwide.com> wrote:
> Hi list,
>
> I am currently in the process of evaluating ActiveMQ. Reading http://activemq.org/JDBC+Support I see that it supports quite a lot of databases, but unfortunately not the one that my company is using (MaxDB (formerly SAPDB), http://www.mysql.com/products/maxdb/). I try to configure activemq to use it by copying its JDBC jar file to activemq's lib directory, and configuring activemq.xml to use it like this:
>
>   <bean id="maxdb-ds" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
>     <property name="driverClassName" value="com.sap.dbtech.jdbc.DriverSapDB"/>
>     <property name="url" value="jdbc:sapdb://localhost/DatabaseInstanceName"/>
>     <property name="username" value="activemquser"/>
>     <property name="password" value="activemquser"/>
>   </bean>
>
> I used the MySQL sample config section as a template and substituted values for my MaxDB instance (the class value I used as is, since I didn't know what to use there). However, when starting ActiveMQ, I get these errors (I have created the database user mentioned above, but not created any tables in that schema):
>
> ACTIVEMQ_HOME: /usr/local/activemq-4.0-M4
> Loading message broker from: xbean:activemq.xml
> INFO  BrokerService                  - ActiveMQ 4.0-M4 JMS Message Broker (localhost) is starting
> INFO  BrokerService                  - For help or more information please see:http://www.logicblaze.com
> WARN  JDBCPersistenceAdapter         - Database driver NOT recognized: [sap_db].  Will use default JDBC implementation.
> WARN  DefaultJDBCAdapter             - Could not create JDBC tables; they couldalready exist. Failure was: CREATE TABLE ACTIVEMQ_MSGS(ID INTEGER NOT NULL, CONTAINER VARCHAR(250), MSGID_PROD VARCHAR(250), MSGID_SEQ INTEGER, EXPIRATION BIGINT, MSG BLOB, PRIMARY KEY ( ID ) ) Message: [-4006] (at 128): Unknown domain name:BIGINT SQLState: I4006 Vendor code: -4006
>
> ...Further similar errors snipped.
>
> From my understanding of what is happening, it seems that the SQL statements that are used by ActiveMQ won't work on MaxDB (BIGINT is indeed a field type that is not recognized by that database). My questions are thus:
>
> * Does anyone successfully use ActiveMQ with MaxDB, and if so, how should I configure activemq.xml to make it work?
> * If it does indeed not work, what would it take to add support for MaxDB to ActiveMQ? The ActiveMQ web site further mentions that "If you have a JDBC database which we don't support then please let us know what JDBC driver string you're getting." If someone would tell me how, I'd be happy to provide the JDBC string I'm getting (could it be "sap_db", as seen in the output snippet above?), and help in any other way I can to add support for this database.
>
> Thanks in advance for any insight!
>
> Regards,
> Johan Hallgren
>
>


--

James
-------
http://radio.weblogs.com/0112098/