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. -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
