Serge,
The class is JamesJDBCMailRepository in method (public void store(MailImpl
mc)):
Scan for the start/end markers to find modified code. Remember I just simply
copied the contents of the byte array output into a string and written this
directly via setString into the message_body field within inbox table in
Oracle 8.1.7.
S.
public void store(MailImpl mc) {
//System.err.println("storing " + mc.getName());
Connection conn = null;
try {
conn = datasource.getConnection();
//Need to determine whether need to insert this record, or
update it.
//Begin a transaction
conn.setAutoCommit(false);
PreparedStatement checkMessageExists =
conn.prepareStatement(sqlQueries.getSqlString("checkMessageExistsSQL",
true));
checkMessageExists.setString(1, mc.getName());
checkMessageExists.setString(2, repositoryName);
ResultSet rsExists = checkMessageExists.executeQuery();
boolean exists = rsExists.next() && rsExists.getInt(1) > 0;
rsExists.close();
checkMessageExists.close();
if (exists) {
//Update the existing record
PreparedStatement updateMessage =
conn.prepareStatement(sqlQueries.getSqlString("updateMessageSQL", true));
updateMessage.setString(1, mc.getState());
updateMessage.setString(2, mc.getErrorMessage());
if (mc.getSender() == null) {
updateMessage.setNull(3, java.sql.Types.VARCHAR);
} else {
updateMessage.setString(3, mc.getSender().toString());
}
StringBuffer recipients = new StringBuffer();
for (Iterator i = mc.getRecipients().iterator();
i.hasNext(); ) {
recipients.append(i.next().toString());
if (i.hasNext()) {
recipients.append("\r\n");
}
}
updateMessage.setString(4, recipients.toString());
updateMessage.setString(5, mc.getRemoteHost());
updateMessage.setString(6, mc.getRemoteAddr());
updateMessage.setTimestamp(7, new
java.sql.Timestamp(mc.getLastUpdated().getTime()));
updateMessage.setString(8, mc.getName());
updateMessage.setString(9, repositoryName);
updateMessage.execute();
updateMessage.close();
//Determine whether the message body has changed, and
possibly avoid
// updating the database.
MimeMessage messageBody = mc.getMessage();
boolean saveBody = false;
if (messageBody instanceof MimeMessageWrapper) {
MimeMessageWrapper message =
(MimeMessageWrapper)messageBody;
saveBody = message.isModified();
} else {
saveBody = true;
}
if (saveBody) {
updateMessage =
conn.prepareStatement(sqlQueries.getSqlString("updateMessageBodySQL",
true));
ByteArrayOutputStream headerOut = new
ByteArrayOutputStream();
OutputStream bodyOut = null;
if (sr == null) {
//If there is no filestore, use the byte array to
store headers
// and the body
bodyOut = headerOut;
} else {
//Store the body in the stream repository
bodyOut = sr.put(mc.getName());
}
//Write the message to the headerOut and bodyOut.
bodyOut goes straight to the file
MimeMessageWrapper.writeTo(messageBody, headerOut,
bodyOut);
bodyOut.close();
//Store the headers in the database
updateMessage.setBytes(1, headerOut.toByteArray());
updateMessage.setString(2, mc.getName());
updateMessage.setString(3, repositoryName);
updateMessage.execute();
updateMessage.close();
}
} else {
//Insert the record into the database
PreparedStatement insertMessage =
conn.prepareStatement(sqlQueries.getSqlString("insertMessageSQL", true));
insertMessage.setString(1, mc.getName());
insertMessage.setString(2, repositoryName);
insertMessage.setString(3, mc.getState());
insertMessage.setString(4, mc.getErrorMessage());
if (mc.getSender() == null) {
insertMessage.setNull(5, java.sql.Types.VARCHAR);
} else {
insertMessage.setString(5, mc.getSender().toString());
}
StringBuffer recipients = new StringBuffer();
for (Iterator i = mc.getRecipients().iterator();
i.hasNext(); ) {
recipients.append(i.next().toString());
if (i.hasNext()) {
recipients.append("\r\n");
}
}
insertMessage.setString(6, recipients.toString());
insertMessage.setString(7, mc.getRemoteHost());
insertMessage.setString(8, mc.getRemoteAddr());
insertMessage.setTimestamp(9, new
java.sql.Timestamp(mc.getLastUpdated().getTime()));
MimeMessage messageBody = mc.getMessage();
ByteArrayOutputStream headerOut = new
ByteArrayOutputStream();
OutputStream bodyOut = null;
if (sr == null) {
//If there is no sr, then use the same byte array to
hold the headers
// and the body
bodyOut = headerOut;
} else {
//Store the body in the file system.
bodyOut = sr.put(mc.getName());
}
//Write the message to the headerOut and bodyOut. bodyOut
goes straight to the file
MimeMessageWrapper.writeTo(messageBody, headerOut, bodyOut);
bodyOut.close();
//Store the headers in the database
// Sam. Converted byte array output to string as
// START
// Oracle cannot handle bytes in LONG data type.
//insertMessage.setBytes(10, headerOut.toByteArray());
byte[] byteArray = headerOut.toByteArray();
StringBuffer strBuf = new StringBuffer();
for (int i=0; i < byteArray.length;i++)
strBuf.append((char)byteArray[i]);
insertMessage.setString(10, strBuf.toString());
// END
insertMessage.execute();
insertMessage.close();
}
conn.commit();
conn.setAutoCommit(true);
synchronized (this) {
notifyAll();
}
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("Exception caught while storing mail
Container: " + e);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException sqle) {
//ignore
}
}
}
}
>Can you provide code for the Oracle workaround for the LOB? We either have
>to do that or apply an Oracle specific patch to call setString instead of
>setBytes and with the later I can almost guarantee next month we'll get
someone complaining how their Oracle repository is corrupting their
>messages
by this shortcut.
>Serge Knystautas
>Loki Technologies - Unstoppable Websites
>http://www.lokitech.com/
----- Original Message -----
From: "Samuel Sadek" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, February 24, 2002 7:14 PM
Subject: Re: How to make James work with Oracle database?
>Apparently if you want to implement a LOB equivalent to hold 8-bit binary
>data you'll need to implement an Oracle specific JDBC code to the
>JamesJDBCMailRepository class and others which will prove to be mundane
and
>too drawn down. Also careful considerations will have to be made for other
>potential db vendor unexpected behaviour such as mysql, mssql, and others.
I
>suspect there may be some discrepancies with these as well. The solution
>which will prove very tedious but will have to do is to make db specific
>JDBC code implementations for all repositories that there exist within
>James, and to release as patch fix to users.
>
>The method I used to get round the problem was to convert the binary
output
>to string and have this written to an Oracle LONG data type whether it's
>7-bits or 8-bits long and has worked via usage of setString method as
using
>setBytes will not work for Oracle at all as it is not supported by it.
>
>Sam.
>
>
> >Well, I'm glad it's working, but we'll have to figure out how to
>actually
> >apply this to the tree. The message 'message_body' needs to be >binary,
> >not
> >text as messages are allowed to be 8-bit data. I'm sorry I haven't >had
> >time
> >to jump in sooner, but I believe you'll have more luck if you make
> >message_body the LOB equivalent of varbinary, not varchar2.
>Accordingly,
> >I
> >think setBytes is the correct call to make. If we have to, we can add a
> >special patch to deal with Oracle bug.
>
> >I think other people have mentioned the idea of using CMP client code to
> >create repositories, but realize you'll get a performance hit by using a
> >mapping layer or remote client classes to connect to your EJB server.
>
> >Serge Knystautas
> >Loki Technologies - Unstoppable Websites
> >http://www.lokitech.com/
>
>----- Original Message -----
>From: "Samuel Sadek" <[EMAIL PROTECTED]>
>To: <[EMAIL PROTECTED]>
>Sent: Sunday, February 24, 2002 5:25 PM
>Subject: RE: How to make James work with Oracle database?
>
>
> >GREAT NEWS!
> >
> >I HAVE SOLVED THE LONG-AWAITED ISSUE WITH JAMES 2.02A WITH ORACLE 8.1.7.
> >
> >The problem I found is actually a bug not foreseen by Apache-james dev
> >group. This is why using explicit JDBC code is not advisable to handle
>pure
> >SQL code.
> >
> >The problem arose in the JamesJDBCMailRepository class when both the
>headers
> >and message body get written into the message_body table field in the
>inbox
> >table. Depending which data type you use, in my case I used LONG (max 2GB
> >pure chars), you need to cast down to string in the jdbc code and not
pass
> >in the array as bytes as this type is not supported by Oracle for LONG
>data
> >type. Instead you need to convert the byte array output (from
OutputStream
> >object) into String and set the message_body field's parameter to contain
> >this converted string value via .setString method and not .setByte
method!
> >
> >That's all there is to it! Obviously all instances of .setByte methods in
> >the JamesJDBCMailRepository class would need to be updated when
> >accommodating instances of a user modifying a mail's message body.
> >
> >Sam.
> >
> >Please find the below the createSql clause in sqlResources.xml I used to
> >create the 'inbox' table for Oracle 8.1.7:
> >
> ><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 NOT NULL ,
> > last_updated date NOT NULL ,
> > PRIMARY KEY (message_name, repository_name)
> > )
> > TABLESPACE system
> > </sql>
> >
> >I have one further suggestion. Why not use Entity Container Managed
> >Persistence (CMP) type EJB for handling sql statements in the mentioned
> >class? It provides useful features such as proper transaction management,
> >security, concurrency, etc... It may be worth considering this
alternative
> >route...
> >
> >S.
> >
> >
> > >One more thing. Although I'm able to query the Oracle db to extract the
> > >headers such as sender, recipients fields from the inbox table via
>SQLPlus,
> > >I'm not able to receive them neither from my fully compliant POP client
> > >software. What is going on????!?!?!?
> >
> > >S.
> >
> > >but the message body field should contain readable plain text when you
> > >select it.
> >
> > >-----Original Message-----
> > >From: Samuel Sadek [mailto:[EMAIL PROTECTED]]
> > >Sent: Saturday, February 23, 2002 11:00 AM
> > >To: [EMAIL PROTECTED]
> > >Subject: RE: How to make James work with Oracle database?
> > >
> > >
> > >LONG data type caters for instances of field lengths > 4000 with
maximum
> > >capacity of 2GB of text. It strictly holds text value. Using
> > >varchar2 which
> > >is equivalent to varchar (MySQL) holds text up to 4000 characters
> > >in length
> > >which is inadequate. That's the reason of my using LONG.
> > >
> > >Can anyone please help with this issue?
> > >
> > >Sam.
> > >
> > > >long is the wrong datatype ... I dont know what Oracle datatype
> > >it >should
> > > >be, but MySQL has one called LONGTEXT that I use.
> > > >d.
> > > >
> > > >-----Original Message-----
> > > >From: Samuel Sadek [mailto:[EMAIL PROTECTED]]
> > > >Sent: Saturday, February 23, 2002 1:05 AM
> > > >To: [EMAIL PROTECTED]
> > > >Subject: RE: How to make James work with Oracle database?
> > > >
> > > >
> > > >Danny,
> > > >
> > > >I've followed the suggested instructions below and these are the
> > > >output I've
> > > >got from my POP server:
> > > >
> > > > >what does the content of the message_body field contain? it
> > > >should >hold a
> > > > >complete mail message with all its headers.
> > > >
> > > >Initially I sqlplus'd into my oracle db and executed the following
SQL
> > > >statements:
> > > >
> > > >SQL> select message_state from inbox;
> > > >
> > > >MESSAGE_STATE
> > > >------------------------------
> > > >root
> > > >root
> > > >root
> > > >root
> > > >root
> > > >root
> > > >
> > > >6 rows selected.
> > > >
> > > >SQL> select message_body from inbox;
> > > >
> > > >MESSAGE_BODY
> > > >------------------------------------------------------------------
> > > >--------------
> > > >333533323336333533373334333733353337333233363435333234343335333033
> > > >36333133373334
> > > >
> > > >333533323336333533373334333733353337333233363435333234343335333033
> > > >36333133373334
> > > >
> > > >333533323336333533373334333733353337333233363435333234343335333033
> > > >36333133373334
> > > >
> > > >333533323336333533373334333733353337333233363435333234343335333033
> > > >36333133373334
> > > >
> > > >333533323336333533373334333733353337333233363435333234343335333033
> > > >36333133373334
> > > >
> > > >333533323336333533373334333733353337333233363435333234343335333033
> > > >36333133373334
> > > >
> > > >6 rows selected.
> > > >
> > > >Could you please explain to me what root in message_state means and
if
>it
> > > >reflects successful mail delivery.
> > > >
> > > >
> > > > >Does the POP server serve the mail correctly? you could try
> > > >telnetting >to
> > > > >port 110, the commands are
> > > >
> > > >I've telnetted to my POP server and got the following response back:
> > > >
> > > >+OK secemail.dnsalias.com POP3 server (JAMES POP3 Server 2.0a2) ready
> > > >
> > > >which suggests to me that it's up and running.
> > > >
> > > > >USER [username]
> > > >
> > > >logged in as a recipient attempting to read the delivered mail
> > > >and got the
> > > >following response back:
> > > >
> > > >+OK
> > > >
> > > > >PASS [password]
> > > >
> > > >entered valid password for above user and got back:
> > > >
> > > >+OK Welcome monica
> > > >
> > > > >LIST
> > > >
> > > >+OK 6 24000
> > > >1 4000
> > > >2 4000
> > > >3 4000
> > > >4 4000
> > > >5 4000
> > > >6 4000
> > > >.
> > > >
> > > > >RETR [number of a message from list]
> > > >
> > > >typed in RETR 3 to yield third message content and got back:
> > > >
> > > >+OK Message follows
> > > >333533323336333533373334333733353337333233363435333234343335333033
> > > >36333133373334
> > > >333633383333343133323330333334333337333333363331333634343337333533
> > > >36333533363433
> > > >333433303337333333363335333633333336333533363434333633313336333933
> > > >36343333323435
> > > >333633343336343533373333333633313336343333363339333633313337333333
> > > >32343533363333
> > > >333634363336343433333435333034343330343133353332333633353336333333
> > > >36333533363339
> > > >333733363336333533363334333334313332333033363336333733323336343633
> > > >36343433323330
> > > >333733323336333833363335333634353336333933373335333634343332343533
> > > >36333233373334
> > > >333633393336343533373334333633353337333233363435333633353337333433
> > > >32343533363333
> > > >333634363336343433323330333233383335343233333331333333393333333433
> > > >32343533333337
> > > >333333333332343533333337333333333332343533333339333333333335343433
> > > >32333933303434
> > > >333034313332333033323330333233303332333033323330333233303332333033
> > > >32333033323330
> > > >333233303336333233373339333233303337333333363335333633333336333533
> > > >36343433363331
> > > >333633393336343333323435333633343336343533373333333633313336343333
> > > >36333933363331
> > > >333733333332343533363333333634363336343433323330333233383334343133
> > > >34333133343434
> > > >333433353335333333323330333533333334343433353334333533303332333033
> > > >35333333363335
> > > >333733323337333633363335333733323332333033333332333234353333333033
> > > >36333133333332
> > > >333233393332333033373337333633393337333433363338333233303335333333
> > > >34343433353334
> > > >333533303332333033343339333433343332333033333332333333383333333433
> > > >30343433303431
> > > >333233303332333033323330333233303332333033323330333233303332333033
> > > >32333033323330
> > > >333633363336343633373332333233303333343333363434333634363336343533
> > > >36333933363333
> > > >333633313334333033373333333633353336333333363335333634343336333133
> > > >36333933363433
> > > >333234353336333433363435333733333336333133363433333633393336333133
> > > >37333333323435
> > > >333633333336343633363434333334353333343233303434333034313332333033
> > > >32333033323330
> > > >333233303332333033323330333233303332333033323330333233303335333333
> > > >36333133373334
> > > >333234333332333033333332333333333332333033343336333633353336333233
> > > >32333033333332
> > > >333333303333333033333332333233303333333033333330333334313333333033
> > > >33333333333431
> > > >333333353333333733323330333234323333333033333330333333303333333033
> > > >30343433303431
> > > >333533323336333533363333333633353336333933373336333633353336333433
> > > >33343133323330
> > > >333633363337333233363436333634343332333033363338333634363337333333
> > > >37333433333332
> > > >333333313333333733323434333333333333333933323434333333343333333033
> > > >32343433333331
> > > >333333323333333433323435333633393336343533323434333633313336333433
> > > >36333433373332
> > > >333234353336333233373334333634363337333033363335333634353337333733
> > > >36343633373332
> > > >333634333336333433323435333633333336343633363434333233303332333833
> > > >35343233333332
> > > >333333313333333733323435333333333333333933323435333333343333333033
> > > >32343533333331
> > > >333333323333333433353434333233303336333833363335333634333336343633
> > > >33343433363436
> > > >333733323336333133363435333633373336333533323339333034343330343133
> > > >30333933363332
> > > >333733393332333033373332333633383336333533363435333633393337333533
> > > >36343433323435
> > > >333633323337333433363339333634353337333433363335333733323336343533
> > > >36333533373334
> > > >333234353336333333363436333634343332333033373337333633393337333433
> > > >36333833323330
> > > >333633353337333333363434333733343337333033323330333233383334333533
> > > >37333833363339
> > > >333634343332333033333333333234353333333233333332333233303332333333
> > > >33333833323339
> > > >333034343330343133303339333633393336333433323330333333313333333633
> > > >36333533353330
> > > >333633353333333733323434333333303333333033333330333333353333333833
> > > >37333033323434
> > > >333333303333333033303434333034313330333933363336333634363337333233
> > > >32333033363434
> > > >333634363336343533363339333633333336333133343330333733333336333533
> > > >36333333363335
> > > >333634343336333133363339333634333332343533363334333634353337333333
> > > >36333133363433
> > > >333633393336333133373333333234353336333333363436333634343333343233
> > > >32333033353333
> > > >333633313337333433323433333233303333333233333333333233303334333633
> > > >36333533363332
> > > >333233303333333233333330333333303333333233323330333333303333333033
> > > >33343133333330
> > > >333333323333343133333333333333353332333033323432333333303333333033
> > > >33333033333330
> > > >.
> > > >
> > > > >QUIT
> > > >
> > > >and then quitted pop session from console.
> > > >
> > > > >d.
> > > >
> > > >Can you please tell me if any aspect of oracle config is incorrect,
the
> > > >message's body is actually stored correctly in the db, and if so,
> > > >what could
> > > >be the cause of my pop3 client not able to receive the message header
>and
> > > >more importantly, the message body?
> > > >
> > > >One point I need to make is I'm able to query the message headers
> > > >from the
> > > >db correctly...
> > > >
> > > >Would appreciate your feedback.
> > > >
> > > >Thanks in advance,
> > > >
> > > >Sam.
> > > >
> > > > >-----Original Message-----
> > > > >From: Samuel Sadek [mailto:[EMAIL PROTECTED]]
> > > > >Sent: Friday, February 22, 2002 7:44 PM
> > > > >To: [EMAIL PROTECTED]
> > > > >Subject: RE: How to make James work with Oracle database?
> > > > >
> > > > >
> > > > >I'm experiencing some major problems with JAMES hooked with
> > >Oracle 8.1.7.
> > > > >The problem doesn't reside receiving, processing new mail and
> > > > >thus storing
> > > > >the resulting message in the inbox table in Oracle as I've ran
> > >a check on
> > > > >the size of the table via "select count(*) from inbox", but
> > > > >actually reading
> > > > >the new mail off from inbox table using a customised POP mail
client
> > > > >software using JavaMail. I've used the same client to access
> > > > >other POP mail
> > > > >with no problems at all, but when I attempt to read from JAMES
> > > > >POP server it
> > > > >doesn't receive it!!!
> > > > >
> > > > >Please find below the create SQL clause I've used to create the
> > > > >inbox table
> > > > >in the first place:
> > > > >
> > > > ><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 NOT NULL ,
> > > > > last_updated date NOT NULL ,
> > > > > PRIMARY KEY (message_name, repository_name)
> > > > > )
> > > > > TABLESPACE system
> > > > ></sql>
> > > > >
> > > > >I'm also aware of Sun's problem with ot reading data off from
> > > > >clob so I've
> > > > >used a long data type which is standard and should not be the
> > > > >cause of the
> > > > >problem (althought I'm hoping that's not the case!).
> > > > >
> > > > >This thread seems to be rather long-winded and pervasive but I
would
> > > > >appreciate it your comments/feedback on the matter.
> > > > >
> > > > >Thanks.
> > > > >
> > > > >Sam.
_________________________________________________________________
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]>
