Thanks sam, that was worth the wait. > -----Original Message----- > From: Samuel Sadek [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, February 26, 2002 11:39 PM > To: [EMAIL PROTECTED] > 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 > [EMAIL PROTECTED], [EMAIL PROTECTED], > [EMAIL PROTECTED] > 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:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
-- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
