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