James Server
  1. James Server
  2. JAMES-247

James Does Not Work With Oracle DB For Spool Repository

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker Blocker
    • Resolution: Fixed
    • Affects Version/s: 2.1.3, 2.2.0
    • Fix Version/s: 2.2.0
    • Labels:
      None
    • Environment:
      Windows XP, Oracle 9i

      Description

      Oracle does not allow two columns of type 'long' in the same table. The sqlResources.xml file defines message_body and message_attributes as type 'long raw' so James fails during the initialization phase because it cannot create the spool table.

      (* I have tried simply substituting blobs but that does not work - the message content is lost passing through James *)

        Activity

        Hide
        Noel J. Bergman added a comment -

        Patch contributed by Steve Short adds support for Oracle by changing to use Blob column and Blob JDBC type. Also add support for db2.

        Show
        Noel J. Bergman added a comment - Patch contributed by Steve Short adds support for Oracle by changing to use Blob column and Blob JDBC type. Also add support for db2.
        Hide
        Vincenzo Gianferrari Pini added a comment -

        The patch breaks the behaviour of MSSQL, getting now:

        17/05/04 17:00:07 ERROR spoolmanager: Exception in processor <error>
        javax.mail.MessagingException: Exception caught while storing Message Container: java.io.IOException: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unsupported data conversion.
        at org.apache.james.mailrepository.AvalonMailRepository.store(AvalonMailRepository.java:314)
        at org.apache.james.transport.mailets.ToRepository.service(ToRepository.java:97)
        at org.apache.james.transport.LinearProcessor.service(LinearProcessor.java:407)
        at org.apache.james.transport.JamesSpoolManager.process(JamesSpoolManager.java:451)
        at org.apache.james.transport.JamesSpoolManager.run(JamesSpoolManager.java:360)
        at java.lang.Thread.run(Unknown Source)
        17/05/04 17:00:07 ERROR spoolmanager: An error occurred processing Mail1084806006702-0 through error
        17/05/04 17:00:07 ERROR spoolmanager: Result was ghost
        17/05/04 17:00:07 DEBUG spoolmanager: ==== Removed from spool mail Mail1084806006702-0====

        I'm using the msbase.jar, mssqlserver.jar and msutil.jar jdbc drivers fo MSSQL.

        As soon as I took out the getBlob commands, things worked again.

        Vincenzo

        Show
        Vincenzo Gianferrari Pini added a comment - The patch breaks the behaviour of MSSQL, getting now: 17/05/04 17:00:07 ERROR spoolmanager: Exception in processor <error> javax.mail.MessagingException: Exception caught while storing Message Container: java.io.IOException: java.sql.SQLException: [Microsoft] [SQLServer 2000 Driver for JDBC] Unsupported data conversion. at org.apache.james.mailrepository.AvalonMailRepository.store(AvalonMailRepository.java:314) at org.apache.james.transport.mailets.ToRepository.service(ToRepository.java:97) at org.apache.james.transport.LinearProcessor.service(LinearProcessor.java:407) at org.apache.james.transport.JamesSpoolManager.process(JamesSpoolManager.java:451) at org.apache.james.transport.JamesSpoolManager.run(JamesSpoolManager.java:360) at java.lang.Thread.run(Unknown Source) 17/05/04 17:00:07 ERROR spoolmanager: An error occurred processing Mail1084806006702-0 through error 17/05/04 17:00:07 ERROR spoolmanager: Result was ghost 17/05/04 17:00:07 DEBUG spoolmanager: ==== Removed from spool mail Mail1084806006702-0==== I'm using the msbase.jar, mssqlserver.jar and msutil.jar jdbc drivers fo MSSQL. As soon as I took out the getBlob commands, things worked again. Vincenzo
        Hide
        Vincenzo Gianferrari Pini added a comment -

        The problem is that the fix applied to fix JAMES-247 (using getBlob instead of getBytes for both message_body and message_attributes) doesn't work with MSSQL (that issues an exception), because the field type is "image".
        I tried with other field types without success, and will look more carefully, but I'm afraid that we will have to insert in the code some special check to see if those fields are compatible with getBlob. It would be ugly though Ideas are welcome.

        Anyhow we can't release 2.2.0RC3 as the final release without fixing it. Current James sites using MSSQL with full db support or even just dbfile with attributes would be broken.

        Show
        Vincenzo Gianferrari Pini added a comment - The problem is that the fix applied to fix JAMES-247 (using getBlob instead of getBytes for both message_body and message_attributes) doesn't work with MSSQL (that issues an exception), because the field type is "image". I tried with other field types without success, and will look more carefully, but I'm afraid that we will have to insert in the code some special check to see if those fields are compatible with getBlob. It would be ugly though Ideas are welcome. Anyhow we can't release 2.2.0RC3 as the final release without fixing it. Current James sites using MSSQL with full db support or even just dbfile with attributes would be broken.
        Hide
        Noel J. Bergman added a comment -

        Schedule fix for the next release candidate.

        Show
        Noel J. Bergman added a comment - Schedule fix for the next release candidate.
        Hide
        Vincenzo Gianferrari Pini added a comment -

        New fix committed. Needed because mssql (and possibly other database products) does not work the way we need with getBlob.
        Created a new sqlResources.xml element ("dbOptions") that allows control of the desired behaviour for each database product, specifically with the new "dbOption"s "getBody" and "getAttributes". They drive the usage of either getBlob or getBytes, the latter being the default.
        The defaults set should be fully backwards compatible (to 2.1.x and 2.2.0ax); only oracle and db2 are set to use getBlob, so no current installation should break, even without modifying SqlResources.xml.
        I fully tested the new dbOptions mechanism and the behaviour of mssql. Other db products should be tested now.

        Show
        Vincenzo Gianferrari Pini added a comment - New fix committed. Needed because mssql (and possibly other database products) does not work the way we need with getBlob. Created a new sqlResources.xml element ("dbOptions") that allows control of the desired behaviour for each database product, specifically with the new "dbOption"s "getBody" and "getAttributes". They drive the usage of either getBlob or getBytes, the latter being the default. The defaults set should be fully backwards compatible (to 2.1.x and 2.2.0ax); only oracle and db2 are set to use getBlob, so no current installation should break, even without modifying SqlResources.xml. I fully tested the new dbOptions mechanism and the behaviour of mssql. Other db products should be tested now.
        Hide
        Noel J. Bergman added a comment -

        Steve Short reported that he "tested #247 on Oracle 9i and DB2 8.1 with no problems experienced." We've also had reports that it works well with MySQL and PostgreSQL.

        Closing this as fixed.

        Show
        Noel J. Bergman added a comment - Steve Short reported that he "tested #247 on Oracle 9i and DB2 8.1 with no problems experienced." We've also had reports that it works well with MySQL and PostgreSQL. Closing this as fixed.

          People

          • Assignee:
            Vincenzo Gianferrari Pini
            Reporter:
            Steve Short
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development