[prev in list] [next in list] [prev in thread] [next in thread]
List: james-user
Subject: RE: How to make James work with Oracle database?
From: "Danny Angus" <danny () thought ! co ! uk>
Date: 2002-02-27 8:22:52
[Download RAW message or body]
Thanks sam, that was worth the wait.
> -----Original Message-----
> From: Samuel Sadek [mailto:samuel_sadek@hotmail.com]
> Sent: Tuesday, February 26, 2002 11:39 PM
> To: james-user@jakarta.apache.org
> Subject: RE: How to make James work with Oracle database?
>
>
> GREAT NEWS!!!
>
> Problem with James 2.02a integrated with Oracle 8.1.7 has now
> been resolved.
> What's even better is that there has been minimal changes made to the
> version 2.02a of the code!!!
>
> The only change required is in the SqlResources.xml and
> config.xml scripts
> whereby a separate clause for Oracle 8.1.7 db handling is all
> that is ever
> required.
>
> Instead of using any LOB data type I have used the LONG RAW
> binary type (max
> 2GB) which serves for this purpose.
>
>
> Here's the required snippet to be patched into config.xml:
>
> <database-connections>
> ...
> <data-sources>
> <data-source name="maildb"
> class="org.apache.james.util.mordred.JdbcDataSource">
> <driver>oracle.jdbc.driver.OracleDriver</driver>
> <dburl>jdbc:oracle:thin:@orange.secemail.dnsalias.com:1521:mail01</dburl>
> <user>anyuser</user>
> <password>anypassword</password>
> </data-source>
> ...
> </database-connections>
>
> Here's the required snippet to be patched into sqlResources.xml:
>
> <dbMatchers>
> ...
> <dbMatcher db="oracle" databaseProductName="oracle.*"/>
> ...
> </dbMatchers>
>
>
> <sqlDefs name="org.apache.james.mailrepository.JDBCMailRepository">
> ...
> <sql name="createTable" db="oracle">
> CREATE TABLE ${table} (
> message_name varchar2(200) NOT NULL ,
> repository_name varchar2(200) NOT NULL ,
> message_state varchar2(30) NOT NULL ,
> error_message varchar2(200) NULL ,
> sender varchar2(200) ,
> recipients varchar2(1000) NOT NULL ,
> remote_host varchar2(100) NOT NULL ,
> remote_addr varchar2(20) NOT NULL ,
> message_body long raw NOT NULL ,
> last_updated date NOT NULL ,
> PRIMARY KEY (message_name, repository_name)
> )
> TABLESPACE system
> </sql>
> ...
> </sqlDefs>
>
>
> <sqlDefs name="org.apache.james.mailrepository.JDBCSpoolRepository">
> ...
> <sql name="createTable" db="oracle">
> CREATE TABLE ${table} (
> message_name varchar2(200) NOT NULL ,
> repository_name varchar2(200) NOT NULL ,
> message_state varchar2(30) NOT NULL ,
> error_message varchar2(200) NULL ,
> sender varchar2(200) NULL ,
> recipients varchar2(1000) NOT NULL ,
> remote_host varchar2(100) NOT NULL ,
> remote_addr varchar2(20) NOT NULL ,
> message_body long raw NOT NULL ,
> last_updated date NOT NULL ,
> PRIMARY KEY (message_name, repository_name)
> )
> </sql>
> ...
> </sqlDefs>
>
>
> And this is it! Can you please submit my credentials
> Samuel.Sadek@kpmg.co.uk, Samuel_Sadek@Hotmail.com,
> Samuel_Sadek@yahoo.co.uk
> when you publish this snippet script code for next release for James?
>
> Also the problem I had to do with mail attachments has been fixed
> by using
> LONG RAW as opposed to LOB data types.
>
> Thanks for all your help, and especially to Darrell.
>
> Keep me posted for any future releases for James.
>
> Sam.
>
>
> >Hi Samuel,
>
> >Thanks for your submission. It's great that you want to get
> involved. Next
> >time please have a look at http://jakarta.apache.org/site/source.html
> >(especially the "Patches" section), so that you can submit code in a
> > >format which helps us.
>
> >Regarding the specifics of your solution, I'm pretty strongly
> against the
> >inclusion of Vendor-specific JDBC code in James, particularly as part of
> >the core. I've done some work with Oracle in the past, and I
> was able to
> >store Large Binary fields using standard JDBC functionality,
> without having
> >to resort to using the oracle.sql.* classes.
>
> >There are some limitations with the Oracle Thin JDBC driver,
> which does not
> >fully support the JDBC spec (notably, certain LOB features).
> Fortunately,
> >using the type3 Oracle driver (which uses native code on the
> client), gets
> >around these issues, as it fully supports the JDBC spec.
> (However, I'm not
> >certain that we would actually require the features missing from
> the Thin
> >driver.)
>
> >If this is the fix that you want to use, then feel free to use a patched
> >version of James yourself. But I'm currently -1 on this particular patch
> >being applied to CVS.
>
> >So where to go from here? I would like to try a couple of things (but I
> >don't have Oracle installed):
> >1) Try using a "LONG RAW" datatype in place of "LONG" or "BLOB". I know
> >that this datatype is deprecated in Oracle (as is LONG), but it may
> > >function perfectly with the current James code. This assumes that
> >PreparedStatement.setBytes() works on this datatype.
> >2) Maybe look at trying to modify the
> "PreparedStatement.setBytes()" >call
> >(which I think was the original culprit) to instead use
> >"PreparedStatement.setBinaryStream()". This should work with
> Oracle >BLOB
> >columns, using the Type3 Oracle JDBC Driver. (as well as working with
> > >Oracle LONG RAW columns and hopefully MSSQL NTEXT columns). This would
> > >eliminate the need for reading the entire message body into a
> >ByteArrayOutputStream >before handing it into the PreparedStatement, but
> >will require some more >serious changes, including a way to obtain the
> >entire message body as an >InputStream. (Or is this already possible,
> >Serge?)
>
> >Hope this helps,
> >ciao
> >Daz
>
> On Mon, 25 Feb 2002 21:02, you wrote:
> >Serge,
> >
> >Please find the final patch code for Oracle. You'll need to download two
> >vital classes from Oracle to support its JDBC driver (classes12.zip &
> >nls_charset12.zip for national char set support).
> >
> <snip...>
>
>
> _________________________________________________________________
> Chat with friends online, try MSN Messenger: http://messenger.msn.com
>
>
> --
> To unsubscribe, e-mail:
<mailto:james-user-unsubscribe@jakarta.apache.org>
For additional commands, e-mail: <mailto:james-user-help@jakarta.apache.org>
--
To unsubscribe, e-mail: <mailto:james-user-unsubscribe@jakarta.apache.org>
For additional commands, e-mail: <mailto:james-user-help@jakarta.apache.org>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic