You are viewing a plain text version of this content. The canonical link for it is here.
Posted to server-user@james.apache.org by Miroslav Nachev <mi...@space-comm.com> on 2002/12/20 11:29:20 UTC
James 2.1 & PostgreSQL Store Mail Repository
Hi,
We try to start James 2.1 with PostgreSQL but we have some problems
with storing of mails in the database. The error is very strange
because the field "message_body" is from type "bytea" (long binary
data) but the error is:
java.lang.RuntimeException: Exception caught while storing mail
Container:
java.sql.SQLException: ERROR:
column "message_body" is of type 'bytea' but expression is of
type 'integer'
You will need to rewrite or cast the expression
Regards,
Miro.
RE: James 2.1 & PostgreSQL Store Mail Repository
Posted by "Noel J. Bergman" <no...@devtech.com>.
Simon,
Ah, OK. :-) Just FYI, the word "type" with respect to a JDBC driver has a
specific meaning related to how the driver talks to the database. See:
http://java.sun.com/products/jdbc/driverdesc.html.
If you can, please submit a patch for your version of sqlResources.xml. If
you cannot, please re-apply your changes to the latest version of the file
from the v2.1 download and send it to me. I'll create the patch, and apply
it as soon as v2.1 is tagged.
--- Noel
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: James 2.1 & PostgreSQL Store Mail Repository
Posted by simon <si...@sydneybluegum.com>.
Noel,
Do lnow why I used the word type.
On the download page it just say jdbc3 jdk1.4
I used the postgresql type bytea for the message_body column.
<sql name="createTable" db="postgresql">
CREATE TABLE ${table} (
message_name varchar (200) NOT NULL,
repository_name varchar (255) NOT NULL,
message_state varchar (30) NOT NULL ,
error_message varchar (200) NULL ,
sender varchar (255) NULL ,
recipients text NOT NULL ,
remote_host varchar (255) NOT NULL ,
remote_addr varchar (20) NOT NULL ,
message_body bytea NOT NULL ,
last_updated timestamp NOT NULL,
PRIMARY KEY (message_name, repository_name)
)
</sql>
and the length function.
<sql name="retrieveMessageBodySizeSQL" db="postgresql">SELECT
length(message_body) FROM ${table} WHERE message_name = ? AND
repository_name = ?</sql>
What is the best method of getting these changes into csv?
I think the bytea type has only been around for the last couple of major
release.
I store nulls as \000 and \ as \\ etc.. in the database.
Thanks.
Regards,
Simon
Noel J. Bergman wrote:
>Simon,
>
>
>
>>I am using the current stable jdbc driver for jdk1.4 type 3 with james
>>and all is ok.
>>
>>
>
>
>
>>pg73jdbc3.jar from http://jdbc.postgresql.org/download.html
>>
>>
>
>Thank you for your report. :-)
>
>Do you mean a type 4 driver for JDBC v3? I would not expect a type 3
>driver.
>
>What changes, if any, did you have to make from the stock sqlResources.xml
>as distributed by the project?
>
> --- Noel
>
>
>--
>To unsubscribe, e-mail: <ma...@jakarta.apache.org>
>For additional commands, e-mail: <ma...@jakarta.apache.org>
>
>
>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
RE: James 2.1 & PostgreSQL Store Mail Repository
Posted by "Noel J. Bergman" <no...@devtech.com>.
Simon,
> I am using the current stable jdbc driver for jdk1.4 type 3 with james
> and all is ok.
> pg73jdbc3.jar from http://jdbc.postgresql.org/download.html
Thank you for your report. :-)
Do you mean a type 4 driver for JDBC v3? I would not expect a type 3
driver.
What changes, if any, did you have to make from the stock sqlResources.xml
as distributed by the project?
--- Noel
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: James 2.1 & PostgreSQL Store Mail Repository
Posted by simon <si...@sydneybluegum.com>.
I am using the current stable jdbc driver for jdk1.4 type 3 with james
and all is ok.
pg73jdbc3.jar
from
http://jdbc.postgresql.org/download.html
Regards,
Simon
Miroslav Nachev wrote:
> Hi,
>
> We try to start James 2.1 with PostgreSQL but we have some problems
>with storing of mails in the database. The error is very strange
>because the field "message_body" is from type "bytea" (long binary
>data) but the error is:
> java.lang.RuntimeException: Exception caught while storing mail
> Container:
> java.sql.SQLException: ERROR:
> column "message_body" is of type 'bytea' but expression is of
> type 'integer'
> You will need to rewrite or cast the expression
>
>
> Regards,
> Miro.
>
>------------------------------------------------------------------------
>
>--
>To unsubscribe, e-mail: <ma...@jakarta.apache.org>
>For additional commands, e-mail: <ma...@jakarta.apache.org>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
RE: James 2.1 & PostgreSQL Store Mail Repository
Posted by "Noel J. Bergman" <no...@devtech.com>.
Simon,
I have merged your changes into the James v2 branch. They will be in the
Nightly Build later tonight. Would you please test?
--- Noel
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
RE: James 2.1 & PostgreSQL Store Mail Repository
Posted by "Noel J. Bergman" <no...@devtech.com>.
Simon,
I acknowledge receipt of your sqlResources.xml for PostgreSQL. Yes, I'd
like to have multiple confirmations that your changes work. I don't run
PostgreSQL, myself.
Most probably, this will go out in the first point release after v2.1.
--- Noel
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: James 2.1 & PostgreSQL Store Mail Repository
Posted by simon <si...@sydneybluegum.com>.
Miro,
Sorry about the delay, Noel had asked me to send my sqlResources.xml a
week or two ago.
If you describe your inbox table it might be missing the last_update
column.
I am using PostgreSQL 7.2.3.
and have set column type as timestamp instead of date.
So if you change
last_updated date NOT NULL,
to
last_updated timestamp NOT NULL,
it might work.
Have attached an updated sqlResources.xml from CVS with my postgresql
changes added.
Please try and if all ok maybe Noel could update CVS.
Regards,
Simon
Noel J. Bergman wrote:
>Tobe,
>
>Not in the CREATE -- in the INSERT:
>
><sql name="insertMessageSQL">
>INSERT INTO ${table} (message_name, repository_name, message_state,
>error_message, sender, recipients, remote_host, remote_addr, last_updated,
>message_body) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
></sql>
>
>As you notice, the order of the last two fields is different between the
>field list specified in record insertion, and table creation.
>
> --- Noel
>
>-----Original Message-----
>From: tobe [mailto:torbjorn.gannholm@swipnet.se]
>Sent: Tuesday, December 24, 2002 11:26
>To: James Users List
>Subject: Re: James 2.1 & PostgreSQL Store Mail Repository
>
>
>The CREATE TABLE statement below is probably wrong if field 9 should be
>non-text.
>Actually message_body is the 9th field below, so probably the real field
>9 is missing.
>
>/tobe
>
>Noel J. Bergman wrote:
>
>
>
>>If you look at
>>org.apache.james.mailrepository.JDBCMailRepository.store(MailImpl), you
>>
>>
>will
>
>
>>find a series of PreparedStatement.setX statements. There is only one
>>
>>
>entry
>
>
>>that is of a non-text type, and that is field 9. The message body is field
>>10, and is set directly from a stream. If there were an off-by-one error
>>
>>
>in
>
>
>>the JDBC driver (0 based instead of 1 based), it might account for that
>>problem. Do you have the source for their JDBC driver to check it?
>>
>>I see that you added a new create table statement to sqlResources.xml for
>>PostgreSQL:
>>
>> CREATE TABLE ${table} (
>> message_name varchar (200) NOT NULL,
>> repository_name varchar (255) NOT NULL,
>> message_state varchar (30) NOT NULL ,
>> error_message varchar (1000) NULL ,
>> sender varchar (255) NULL ,
>> recipients text NOT NULL ,
>> remote_host varchar (255) NOT NULL ,
>> remote_addr varchar (20) NOT NULL ,
>> message_body bytea NOT NULL ,
>> last_updated date NOT NULL,
>> CONSTRAINT PK_${table} PRIMARY KEY
>> (
>> message_name,
>> repository_name
>> )
>> )
>>
>>I don't spot anything wrong, but then again, I don't use PostgreSQL. You
>>might also take a look at InsertMessageSQL, which is where the field names
>>and order are laid out.
>>
>> --- Noel
>>
>>-----Original Message-----
>>From: Miroslav Nachev [mailto:miro@space-comm.com]
>>Sent: Friday, December 20, 2002 5:29
>>To: james-dev@jakarta.apache.org; james-user@jakarta.apache.org
>>Subject: James 2.1 & PostgreSQL Store Mail Repository
>>
>>
>> Hi,
>>
>> We try to start James 2.1 with PostgreSQL but we have some problems
>>with storing of mails in the database. The error is very strange
>>because the field "message_body" is from type "bytea" (long binary
>>data) but the error is:
>> java.lang.RuntimeException: Exception caught while storing mail
>> Container:
>> java.sql.SQLException: ERROR:
>> column "message_body" is of type 'bytea' but expression is of
>> type 'integer'
>> You will need to rewrite or cast the expression
>>
>>
>> Regards,
>> Miro.
>>
>>
>>--
>>To unsubscribe, e-mail:
>>
>>
><ma...@jakarta.apache.org>
>
>
>>For additional commands, e-mail:
>>
>>
><ma...@jakarta.apache.org>
>
>
>>
>>
>>
>>
>
>
>
>--
>To unsubscribe, e-mail: <ma...@jakarta.apache.org>
>For additional commands, e-mail: <ma...@jakarta.apache.org>
>
>
>--
>To unsubscribe, e-mail: <ma...@jakarta.apache.org>
>For additional commands, e-mail: <ma...@jakarta.apache.org>
>
>
>
>
RE: James 2.1 & PostgreSQL Store Mail Repository
Posted by "Noel J. Bergman" <no...@devtech.com>.
Tobe,
Not in the CREATE -- in the INSERT:
<sql name="insertMessageSQL">
INSERT INTO ${table} (message_name, repository_name, message_state,
error_message, sender, recipients, remote_host, remote_addr, last_updated,
message_body) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
</sql>
As you notice, the order of the last two fields is different between the
field list specified in record insertion, and table creation.
--- Noel
-----Original Message-----
From: tobe [mailto:torbjorn.gannholm@swipnet.se]
Sent: Tuesday, December 24, 2002 11:26
To: James Users List
Subject: Re: James 2.1 & PostgreSQL Store Mail Repository
The CREATE TABLE statement below is probably wrong if field 9 should be
non-text.
Actually message_body is the 9th field below, so probably the real field
9 is missing.
/tobe
Noel J. Bergman wrote:
>If you look at
>org.apache.james.mailrepository.JDBCMailRepository.store(MailImpl), you
will
>find a series of PreparedStatement.setX statements. There is only one
entry
>that is of a non-text type, and that is field 9. The message body is field
>10, and is set directly from a stream. If there were an off-by-one error
in
>the JDBC driver (0 based instead of 1 based), it might account for that
>problem. Do you have the source for their JDBC driver to check it?
>
>I see that you added a new create table statement to sqlResources.xml for
>PostgreSQL:
>
> CREATE TABLE ${table} (
> message_name varchar (200) NOT NULL,
> repository_name varchar (255) NOT NULL,
> message_state varchar (30) NOT NULL ,
> error_message varchar (1000) NULL ,
> sender varchar (255) NULL ,
> recipients text NOT NULL ,
> remote_host varchar (255) NOT NULL ,
> remote_addr varchar (20) NOT NULL ,
> message_body bytea NOT NULL ,
> last_updated date NOT NULL,
> CONSTRAINT PK_${table} PRIMARY KEY
> (
> message_name,
> repository_name
> )
> )
>
>I don't spot anything wrong, but then again, I don't use PostgreSQL. You
>might also take a look at InsertMessageSQL, which is where the field names
>and order are laid out.
>
> --- Noel
>
>-----Original Message-----
>From: Miroslav Nachev [mailto:miro@space-comm.com]
>Sent: Friday, December 20, 2002 5:29
>To: james-dev@jakarta.apache.org; james-user@jakarta.apache.org
>Subject: James 2.1 & PostgreSQL Store Mail Repository
>
>
> Hi,
>
> We try to start James 2.1 with PostgreSQL but we have some problems
>with storing of mails in the database. The error is very strange
>because the field "message_body" is from type "bytea" (long binary
>data) but the error is:
> java.lang.RuntimeException: Exception caught while storing mail
> Container:
> java.sql.SQLException: ERROR:
> column "message_body" is of type 'bytea' but expression is of
> type 'integer'
> You will need to rewrite or cast the expression
>
>
> Regards,
> Miro.
>
>
>--
>To unsubscribe, e-mail:
<ma...@jakarta.apache.org>
>For additional commands, e-mail:
<ma...@jakarta.apache.org>
>
>
>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: James 2.1 & PostgreSQL Store Mail Repository
Posted by tobe <to...@swipnet.se>.
The CREATE TABLE statement below is probably wrong if field 9 should be
non-text.
Actually message_body is the 9th field below, so probably the real field
9 is missing.
/tobe
Noel J. Bergman wrote:
>If you look at
>org.apache.james.mailrepository.JDBCMailRepository.store(MailImpl), you will
>find a series of PreparedStatement.setX statements. There is only one entry
>that is of a non-text type, and that is field 9. The message body is field
>10, and is set directly from a stream. If there were an off-by-one error in
>the JDBC driver (0 based instead of 1 based), it might account for that
>problem. Do you have the source for their JDBC driver to check it?
>
>I see that you added a new create table statement to sqlResources.xml for
>PostgreSQL:
>
> CREATE TABLE ${table} (
> message_name varchar (200) NOT NULL,
> repository_name varchar (255) NOT NULL,
> message_state varchar (30) NOT NULL ,
> error_message varchar (1000) NULL ,
> sender varchar (255) NULL ,
> recipients text NOT NULL ,
> remote_host varchar (255) NOT NULL ,
> remote_addr varchar (20) NOT NULL ,
> message_body bytea NOT NULL ,
> last_updated date NOT NULL,
> CONSTRAINT PK_${table} PRIMARY KEY
> (
> message_name,
> repository_name
> )
> )
>
>I don't spot anything wrong, but then again, I don't use PostgreSQL. You
>might also take a look at InsertMessageSQL, which is where the field names
>and order are laid out.
>
> --- Noel
>
>-----Original Message-----
>From: Miroslav Nachev [mailto:miro@space-comm.com]
>Sent: Friday, December 20, 2002 5:29
>To: james-dev@jakarta.apache.org; james-user@jakarta.apache.org
>Subject: James 2.1 & PostgreSQL Store Mail Repository
>
>
> Hi,
>
> We try to start James 2.1 with PostgreSQL but we have some problems
>with storing of mails in the database. The error is very strange
>because the field "message_body" is from type "bytea" (long binary
>data) but the error is:
> java.lang.RuntimeException: Exception caught while storing mail
> Container:
> java.sql.SQLException: ERROR:
> column "message_body" is of type 'bytea' but expression is of
> type 'integer'
> You will need to rewrite or cast the expression
>
>
> Regards,
> Miro.
>
>
>--
>To unsubscribe, e-mail: <ma...@jakarta.apache.org>
>For additional commands, e-mail: <ma...@jakarta.apache.org>
>
>
>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
RE: James 2.1 & PostgreSQL Store Mail Repository
Posted by "Noel J. Bergman" <no...@devtech.com>.
If you look at
org.apache.james.mailrepository.JDBCMailRepository.store(MailImpl), you will
find a series of PreparedStatement.setX statements. There is only one entry
that is of a non-text type, and that is field 9. The message body is field
10, and is set directly from a stream. If there were an off-by-one error in
the JDBC driver (0 based instead of 1 based), it might account for that
problem. Do you have the source for their JDBC driver to check it?
I see that you added a new create table statement to sqlResources.xml for
PostgreSQL:
CREATE TABLE ${table} (
message_name varchar (200) NOT NULL,
repository_name varchar (255) NOT NULL,
message_state varchar (30) NOT NULL ,
error_message varchar (1000) NULL ,
sender varchar (255) NULL ,
recipients text NOT NULL ,
remote_host varchar (255) NOT NULL ,
remote_addr varchar (20) NOT NULL ,
message_body bytea NOT NULL ,
last_updated date NOT NULL,
CONSTRAINT PK_${table} PRIMARY KEY
(
message_name,
repository_name
)
)
I don't spot anything wrong, but then again, I don't use PostgreSQL. You
might also take a look at InsertMessageSQL, which is where the field names
and order are laid out.
--- Noel
-----Original Message-----
From: Miroslav Nachev [mailto:miro@space-comm.com]
Sent: Friday, December 20, 2002 5:29
To: james-dev@jakarta.apache.org; james-user@jakarta.apache.org
Subject: James 2.1 & PostgreSQL Store Mail Repository
Hi,
We try to start James 2.1 with PostgreSQL but we have some problems
with storing of mails in the database. The error is very strange
because the field "message_body" is from type "bytea" (long binary
data) but the error is:
java.lang.RuntimeException: Exception caught while storing mail
Container:
java.sql.SQLException: ERROR:
column "message_body" is of type 'bytea' but expression is of
type 'integer'
You will need to rewrite or cast the expression
Regards,
Miro.
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>