Обсуждение: Handling null values in char fields from JDBC
I am running Postgresql 7.1.3 and using the 7.1-1.2 jar jdbc driver. I noticed that when I used a
ResultSet.getString("charfield")call to fetch null data from the char field I get a null pointer exception error. My
questionis two fold..
1) Is this normal behavior for the jdbc driver?
2) Is there a recommended way of trapping for a null value in a char field (besides just going into the db with psql
andsetting all null values to '')?
I have looked thru two different Postgresql manuals but have not been able to find anything referring to null value
handlingat the jdbc level.
If anyone can pt me in the right direction I
would really appreciate it.
Thanks!
--
_______________________________________________
Sign-up for your own FREE Personalized E-mail at Mail.com
http://www.mail.com/?sr=signup
1 cent a minute calls anywhere in the U.S.!
http://www.getpennytalk.com/cgi-bin/adforward.cgi?p_key=RG9853KJ&url=http://www.getpennytalk.com
Are you using ResultSet.getString and then ResultSet.wasNull?
Paulo Merson
Summa Technologies - www.summa-tech.com
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Sean Duffy
Sent: Tuesday, January 22, 2002 9:22 AM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] Handling null values in char fields from JDBC
I am running Postgresql 7.1.3 and using the 7.1-1.2 jar jdbc driver. I
noticed that when I used a ResultSet.getString("charfield") call to
fetch null data from the char field I get a null pointer exception
error. My question is two fold..
1) Is this normal behavior for the jdbc driver?
2) Is there a recommended way of trapping for a null value in a char
field (besides just going into the db with psql and setting all null
values to '')?
I have looked thru two different Postgresql manuals but have not been
able to find anything referring to null value handling at the jdbc
level.
If anyone can pt me in the right direction I
would really appreciate it.
Thanks!
--
_______________________________________________
Sign-up for your own FREE Personalized E-mail at Mail.com
http://www.mail.com/?sr=signup
1 cent a minute calls anywhere in the U.S.!
http://www.getpennytalk.com/cgi-bin/adforward.cgi?p_key=RG9853KJ&url=htt
p://www.getpennytalk.com
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Sean,
Just to clarify? Is there a null in the data on the server?
Dave
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Sean Duffy
Sent: Tuesday, January 22, 2002 9:22 AM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] Handling null values in char fields from JDBC
I am running Postgresql 7.1.3 and using the 7.1-1.2 jar jdbc driver. I
noticed that when I used a ResultSet.getString("charfield") call to
fetch null data from the char field I get a null pointer exception
error. My question is two fold..
1) Is this normal behavior for the jdbc driver?
2) Is there a recommended way of trapping for a null value in a char
field (besides just going into the db with psql and setting all null
values to '')?
I have looked thru two different Postgresql manuals but have not been
able to find anything referring to null value handling at the jdbc
level.
If anyone can pt me in the right direction I
would really appreciate it.
Thanks!
--
_______________________________________________
Sign-up for your own FREE Personalized E-mail at Mail.com
http://www.mail.com/?sr=signup
1 cent a minute calls anywhere in the U.S.!
http://www.getpennytalk.com/cgi-bin/adforward.cgi?p_key=RG9853KJ&url=htt
p://www.getpennytalk.com
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
-----Original Message-----
From: "Paulo Merson" <paulo@summa-tech.com>
Date: Tue, 22 Jan 2002 09:46:44 -0500
To: <pgsql-jdbc@postgresql.org>
Subject: Re: [JDBC] Handling null values in char fields from JDBC
Paulo -
I am using ResultSet.getString but Im not
sure how I can use ResultSet.wasNull. Is
there an example I could look at?
Thanks :)
Sean -
> Are you using ResultSet.getString and then ResultSet.wasNull?
>
> Paulo Merson
> Summa Technologies - www.summa-tech.com
>
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Sean Duffy
> Sent: Tuesday, January 22, 2002 9:22 AM
> To: pgsql-jdbc@postgresql.org
> Subject: [JDBC] Handling null values in char fields from JDBC
>
>
>
> I am running Postgresql 7.1.3 and using the 7.1-1.2 jar jdbc driver. I
> noticed that when I used a ResultSet.getString("charfield") call to
> fetch null data from the char field I get a null pointer exception
> error. My question is two fold..
> 1) Is this normal behavior for the jdbc driver?
> 2) Is there a recommended way of trapping for a null value in a char
> field (besides just going into the db with psql and setting all null
> values to '')?
>
> I have looked thru two different Postgresql manuals but have not been
> able to find anything referring to null value handling at the jdbc
> level.
>
> If anyone can pt me in the right direction I
> would really appreciate it.
>
> Thanks!
> --
>
> _______________________________________________
> Sign-up for your own FREE Personalized E-mail at Mail.com
> http://www.mail.com/?sr=signup
>
>
> 1 cent a minute calls anywhere in the U.S.!
>
> http://www.getpennytalk.com/cgi-bin/adforward.cgi?p_key=RG9853KJ&url=htt
> p://www.getpennytalk.com
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
_______________________________________________
Sign-up for your own FREE Personalized E-mail at Mail.com
http://www.mail.com/?sr=signup
1 cent a minute calls anywhere in the U.S.!
http://www.getpennytalk.com/cgi-bin/adforward.cgi?p_key=RG9853KJ&url=http://www.getpennytalk.com
-----Original Message-----
From: "Dave Cramer" <Dave@micro-automation.net>
Date: Tue, 22 Jan 2002 10:03:06 -0500
To: "'Sean Duffy'" <sean.duffy@programmer.net>, <pgsql-jdbc@postgresql.org>
Subject: Re: [JDBC] Handling null values in char fields from JDBC
Dave -
Yes when the field is added as a char field
it contains all null values. I currently go
in with psql and set the null values to ''
but would like to handle the situation a
little more elegantly.
Thanks for getting back to me :)
Sean
> Sean,
>
> Just to clarify? Is there a null in the data on the server?
>
> Dave
>
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Sean Duffy
> Sent: Tuesday, January 22, 2002 9:22 AM
> To: pgsql-jdbc@postgresql.org
> Subject: [JDBC] Handling null values in char fields from JDBC
>
>
>
> I am running Postgresql 7.1.3 and using the 7.1-1.2 jar jdbc driver. I
> noticed that when I used a ResultSet.getString("charfield") call to
> fetch null data from the char field I get a null pointer exception
> error. My question is two fold..
> 1) Is this normal behavior for the jdbc driver?
> 2) Is there a recommended way of trapping for a null value in a char
> field (besides just going into the db with psql and setting all null
> values to '')?
>
> I have looked thru two different Postgresql manuals but have not been
> able to find anything referring to null value handling at the jdbc
> level.
>
> If anyone can pt me in the right direction I
> would really appreciate it.
>
> Thanks!
> --
>
> _______________________________________________
> Sign-up for your own FREE Personalized E-mail at Mail.com
> http://www.mail.com/?sr=signup
>
>
> 1 cent a minute calls anywhere in the U.S.!
>
> http://www.getpennytalk.com/cgi-bin/adforward.cgi?p_key=RG9853KJ&url=htt
> p://www.getpennytalk.com
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
--
_______________________________________________
Sign-up for your own FREE Personalized E-mail at Mail.com
http://www.mail.com/?sr=signup
1 cent a minute calls anywhere in the U.S.!
http://www.getpennytalk.com/cgi-bin/adforward.cgi?p_key=RG9853KJ&url=http://www.getpennytalk.com
I have looked through all the documentation that I can find, as well as the mailing list archives, and I haven't been able to find the answer to this. I'm currently running pgsql 7.0.2. What I'm trying to do is make a large number of writes to a single table in an efficient way. Currently our code makes a separate request for each INSERT statement, incurring too many round trips. I want to convert this to the batch interface, i.e., addBatch() and executeBatch(), but I read that this is implemented as multiple round trips anyway in 7.1.x and before. I also read that this might be fixed in 7.2, but I've been unable to verify this in the documentation. Can anyone enlighten me on the state of the batching interface? or is there some better way to do this? Thank you, Tony DeWitt
You should use wasNull after each getXxx method if the column being read
allows null. It's as simple as:
text = rs.getString(2);
if (rs.wasNull()) {
System.out.println("This column is SQL null");
} else {
// process data
}
It's particularly useful when you are reading values to basic data type
variables (not objects) because there is no value that uniquely
represents the SQL null. For example getInt returns 0 (zero) if the
value is null but it would also return 0 when the value is zero.
(ResultSet.getString must return null when the column value is null; I'm
using it in Posrgres JDBC driver and it works fine.)
Check
http://java.sun.com/j2se/1.4/docs/api/java/sql/ResultSet.html#wasNull()
Paulo Merson
Summa Technologies - www.summa-tech.com
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Sean Duffy
Sent: Tuesday, January 22, 2002 1:34 PM
To: Paulo Merson; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Handling null values in char fields from JDBC
-----Original Message-----
From: "Paulo Merson" <paulo@summa-tech.com>
Date: Tue, 22 Jan 2002 09:46:44 -0500
To: <pgsql-jdbc@postgresql.org>
Subject: Re: [JDBC] Handling null values in char fields from JDBC
Paulo -
I am using ResultSet.getString but Im not
sure how I can use ResultSet.wasNull. Is
there an example I could look at?
Thanks :)
Sean -
> Are you using ResultSet.getString and then ResultSet.wasNull?
>
> Paulo Merson
> Summa Technologies - www.summa-tech.com
>
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Sean Duffy
> Sent: Tuesday, January 22, 2002 9:22 AM
> To: pgsql-jdbc@postgresql.org
> Subject: [JDBC] Handling null values in char fields from JDBC
>
>
>
> I am running Postgresql 7.1.3 and using the 7.1-1.2 jar jdbc driver.
I
> noticed that when I used a ResultSet.getString("charfield") call to
> fetch null data from the char field I get a null pointer exception
> error. My question is two fold..
> 1) Is this normal behavior for the jdbc driver?
> 2) Is there a recommended way of trapping for a null value in a char
> field (besides just going into the db with psql and setting all null
> values to '')?
>
> I have looked thru two different Postgresql manuals but have not been
> able to find anything referring to null value handling at the jdbc
> level.
>
> If anyone can pt me in the right direction I
> would really appreciate it.
>
> Thanks!
> --
>
> _______________________________________________
> Sign-up for your own FREE Personalized E-mail at Mail.com
> http://www.mail.com/?sr=signup
>
>
> 1 cent a minute calls anywhere in the U.S.!
>
>
http://www.getpennytalk.com/cgi-bin/adforward.cgi?p_key=RG9853KJ&url=htt
> p://www.getpennytalk.com
>
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
_______________________________________________
Sign-up for your own FREE Personalized E-mail at Mail.com
http://www.mail.com/?sr=signup
1 cent a minute calls anywhere in the U.S.!
http://www.getpennytalk.com/cgi-bin/adforward.cgi?p_key=RG9853KJ&url=htt
p://www.getpennytalk.com
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
On Tue, 22 Jan 2002 13:42:48 -0500, you wrote:
>I want to convert this to the batch interface, i.e., addBatch() and
>executeBatch(), but I read that this is implemented as multiple round trips
>anyway in 7.1.x and before. I also read that this might be fixed in 7.2, but
>I've been unable to verify this in the documentation.
>
>Can anyone enlighten me on the state of the batching interface? or is there
>some better way to do this?
In a discussion in august 2001 on this list we reached the
conclusion summarized below. However, AFAIK nobody is working on
this yet.
There is more in the list archive, when it is back online.
-+-+-
The current implementation of Statement.executeBatch() in the
JDBC driver does not provide any performance improvement
compared to processing statements individually. This is because
the driver executes the statements one-by-one when
executeBatch() is called, using one round trip per statement. We
intend to reimplement executeBatch() in the following way.
Statement.executeBatch() will send all statements in a single
semicolon separated query string, with only one round trip to
the backend. This will provide a performance improvement, as
intended by the JDBC specification.
The updateCounts array will be set as described below. Note that
the JDBC spec defines the meaning of the following special
values:
-2 the statement was executed successfully but the number of
affected rows is unknown
-3 the statement failed to execute successfully
If all statements succeed, executeBatch() returns an
updateCounts array with a row count for each statement in the
batch, however the value for all but the last statement will be
-2. The value for the last statement will be a proper update
count.
If a statement fails, executeBatch() throws a
BatchUpdateException containing an updateCounts array with a row
count of -3 for each statement in the batch.
Note that the behaviour will be the same when autocommit is
enabled and when it is disabled. Even with autocommit enabled,
the backend will commit or rollback all statements in the
semicolon-separated query string as a unit.
The new implementation of executeBatch() will cause a change in
behaviour of the driver: the driver will no longer return update
counts for all statements in a batch like it currently does, it
will return -2 ("unknown") for most statements. However, this
behaviour is allowed by the JDBC spec and applications should be
prepared to handle it.
-+-+-
Regards,
René Pijlman <rene@lab.applinet.nl>
Tony, The batch API works in one round trip in 7.2. thanks, --Barry Tony DeWitt wrote: > I have looked through all the documentation that I can find, as well as the > mailing list archives, and I haven't been able to find the answer to this. > I'm currently running pgsql 7.0.2. > > What I'm trying to do is make a large number of writes to a single table in > an efficient way. Currently our code makes a separate request for each > INSERT statement, incurring too many round trips. > > I want to convert this to the batch interface, i.e., addBatch() and > executeBatch(), but I read that this is implemented as multiple round trips > anyway in 7.1.x and before. I also read that this might be fixed in 7.2, but > I've been unable to verify this in the documentation. > > Can anyone enlighten me on the state of the batching interface? or is there > some better way to do this? > > > Thank you, > Tony DeWitt > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >
Paulo -
Thanks for all your help ..
This is exactly what I am looking for.
Sean -
-----Original Message-----
From: "Paulo Merson" <paulo@summa-tech.com>
Date: Tue, 22 Jan 2002 14:11:38 -0500
To: <pgsql-jdbc@postgresql.org>
Subject: Re: [JDBC] Handling null values in char fields from JDBC
> You should use wasNull after each getXxx method if the column being read
> allows null. It's as simple as:
>
> text = rs.getString(2);
> if (rs.wasNull()) {
> System.out.println("This column is SQL null");
> } else {
> // process data
> }
>
> It's particularly useful when you are reading values to basic data type
> variables (not objects) because there is no value that uniquely
> represents the SQL null. For example getInt returns 0 (zero) if the
> value is null but it would also return 0 when the value is zero.
> (ResultSet.getString must return null when the column value is null; I'm
> using it in Posrgres JDBC driver and it works fine.)
>
> Check
> http://java.sun.com/j2se/1.4/docs/api/java/sql/ResultSet.html#wasNull()
>
> Paulo Merson
> Summa Technologies - www.summa-tech.com
>
>
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Sean Duffy
> Sent: Tuesday, January 22, 2002 1:34 PM
> To: Paulo Merson; pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Handling null values in char fields from JDBC
>
>
>
> -----Original Message-----
> From: "Paulo Merson" <paulo@summa-tech.com>
> Date: Tue, 22 Jan 2002 09:46:44 -0500
> To: <pgsql-jdbc@postgresql.org>
> Subject: Re: [JDBC] Handling null values in char fields from JDBC
>
> Paulo -
>
> I am using ResultSet.getString but Im not
> sure how I can use ResultSet.wasNull. Is
> there an example I could look at?
>
> Thanks :)
>
> Sean -
>
> > Are you using ResultSet.getString and then ResultSet.wasNull?
> >
> > Paulo Merson
> > Summa Technologies - www.summa-tech.com
> >
> > -----Original Message-----
> > From: pgsql-jdbc-owner@postgresql.org
> > [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Sean Duffy
> > Sent: Tuesday, January 22, 2002 9:22 AM
> > To: pgsql-jdbc@postgresql.org
> > Subject: [JDBC] Handling null values in char fields from JDBC
> >
> >
> >
> > I am running Postgresql 7.1.3 and using the 7.1-1.2 jar jdbc driver.
> I
> > noticed that when I used a ResultSet.getString("charfield") call to
> > fetch null data from the char field I get a null pointer exception
> > error. My question is two fold..
> > 1) Is this normal behavior for the jdbc driver?
> > 2) Is there a recommended way of trapping for a null value in a char
> > field (besides just going into the db with psql and setting all null
> > values to '')?
> >
> > I have looked thru two different Postgresql manuals but have not been
> > able to find anything referring to null value handling at the jdbc
> > level.
> >
> > If anyone can pt me in the right direction I
> > would really appreciate it.
> >
> > Thanks!
> > --
> >
> > _______________________________________________
> > Sign-up for your own FREE Personalized E-mail at Mail.com
> > http://www.mail.com/?sr=signup
> >
> >
> > 1 cent a minute calls anywhere in the U.S.!
> >
> >
> http://www.getpennytalk.com/cgi-bin/adforward.cgi?p_key=RG9853KJ&url=htt
> > p://www.getpennytalk.com
> >
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
> --
>
> _______________________________________________
> Sign-up for your own FREE Personalized E-mail at Mail.com
> http://www.mail.com/?sr=signup
>
>
> 1 cent a minute calls anywhere in the U.S.!
>
> http://www.getpennytalk.com/cgi-bin/adforward.cgi?p_key=RG9853KJ&url=htt
> p://www.getpennytalk.com
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
--
_______________________________________________
Sign-up for your own FREE Personalized E-mail at Mail.com
http://www.mail.com/?sr=signup
1 cent a minute calls anywhere in the U.S.!
http://www.getpennytalk.com/cgi-bin/adforward.cgi?p_key=RG9853KJ&url=http://www.getpennytalk.com
Hi I have recently shifted from using MySQL to PostgreSQL. I have a field in my table which is of type char(10) . When I do a getString() for this field using JDBC, I get a string of 10 characters even if the actual data is say 5 characters in length. The remaining 5 characters are spaces. This was not happening while using MySQL. How can I get the actual data without spaces when I do a getString() without having to add any kind of trimming code in the Java code. regards Sulaksana
On Wed, 23 Jan 2002, Sulakshana Awsarikar wrote: > Hi > > I have recently shifted from using MySQL to PostgreSQL. I have a field in my > table which is of type char(10) . > > When I do a getString() for this field using JDBC, I get a string of 10 > characters even if the actual data is say 5 characters in length. The > remaining 5 characters are spaces. > > This was not happening while using MySQL. > > How can I get the actual data without spaces when I do a getString() without > having to add any kind of trimming code in the Java code. > > regards > Sulaksana You can either change the datatype to VARCHAR, or add a TRIM() to the column in the select statement. Ola -- Ola Sundell ola@miranda.org - olas@wiw.org - ola.sundell@personalchemistry.com http://miranda.org/~ola
Is there any way to execute batches of stored procedures? The actual problem is that I want to do a batch of INSERTs, but it is likely that at least one INSERT will fail - and postgres aborts the entire transaction. I'd like to push some logic down into a stored procedure, but maybe I just have to do a batch of DELETEs then a batch of INSERTs. Thanks, Tony -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Barry Lind Sent: Tuesday, January 22, 2002 3:47 PM To: Tony DeWitt Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] JDBC batching Tony, The batch API works in one round trip in 7.2. thanks, --Barry Tony DeWitt wrote: > I have looked through all the documentation that I can find, as well as the > mailing list archives, and I haven't been able to find the answer to this. > I'm currently running pgsql 7.0.2. > > What I'm trying to do is make a large number of writes to a single table in > an efficient way. Currently our code makes a separate request for each > INSERT statement, incurring too many round trips. > > I want to convert this to the batch interface, i.e., addBatch() and > executeBatch(), but I read that this is implemented as multiple round trips > anyway in 7.1.x and before. I also read that this might be fixed in 7.2, but > I've been unable to verify this in the documentation. > > Can anyone enlighten me on the state of the batching interface? or is there > some better way to do this? > > > Thank you, > Tony DeWitt > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
It does not appear to be so. I grabbed 7.2rc2, and the JDBC driver shipped with it still implements a batch update with multiple round trips. I verified this both by sniffing the network packets, reading the shipped code, and disassembling the Statement class that I'm running. I even checked the CVS repository at jdbc.postgresql.org. Am I missing something here? Thanks, Tony -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Barry Lind Sent: Tuesday, January 22, 2002 3:47 PM To: Tony DeWitt Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] JDBC batching Tony, The batch API works in one round trip in 7.2. thanks, --Barry Tony DeWitt wrote: > I have looked through all the documentation that I can find, as well as the > mailing list archives, and I haven't been able to find the answer to this. > I'm currently running pgsql 7.0.2. > > What I'm trying to do is make a large number of writes to a single table in > an efficient way. Currently our code makes a separate request for each > INSERT statement, incurring too many round trips. > > I want to convert this to the batch interface, i.e., addBatch() and > executeBatch(), but I read that this is implemented as multiple round trips > anyway in 7.1.x and before. I also read that this might be fixed in 7.2, but > I've been unable to verify this in the documentation. > > Can anyone enlighten me on the state of the batching interface? or is there > some better way to do this? > > > Thank you, > Tony DeWitt > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
Very strange. I was sure this had been changed in 7.2. I know there was a discussion on how to change it on the mail lists, and I thought that it had been done. But I guess not. (although other changes were made to this code for 7.2, strange). Anyway, if someone wants to submit a patch against current sources to add this capability, that would certainly be welcome. I would also recommend looking back at the mail list archives for the discussion on how this should be done, as there are some fine points about the jdbc spec in this area that are not clear or obvious. The biggest area of problem is that if the batch is executed in one call, then the driver will only get the result (i.e. rowcount) of the last statement in that batch, instead of getting a result for each statement. There were some on the mail list that felt this would be a bug and therefore we couldn't use a single roundtrip. However I think upon careful reading of the jdbc spec this is allowed, but you need to set some special codes that indicate the result of a particular statement is unknown or something like that. This would be a change in behavior since the results of executing a batch would be different than they currently are so both methods may need to be supported to avoid breaking existing apps. Sorry for giving you incorrect information about the status of this, but I really did think it was already done. thanks, --Barry Tony DeWitt wrote: > It does not appear to be so. I grabbed 7.2rc2, and the JDBC driver shipped > with it still implements a batch update with multiple round trips. I > verified this both by sniffing the network packets, reading the shipped > code, and disassembling the Statement class that I'm running. I even checked > the CVS repository at jdbc.postgresql.org. > > Am I missing something here? > > > Thanks, > Tony > > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Barry Lind > Sent: Tuesday, January 22, 2002 3:47 PM > To: Tony DeWitt > Cc: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] JDBC batching > > > Tony, > > The batch API works in one round trip in 7.2. > > thanks, > --Barry > > > Tony DeWitt wrote: > > >>I have looked through all the documentation that I can find, as well as >> > the > >>mailing list archives, and I haven't been able to find the answer to this. >>I'm currently running pgsql 7.0.2. >> >>What I'm trying to do is make a large number of writes to a single table >> > in > >>an efficient way. Currently our code makes a separate request for each >>INSERT statement, incurring too many round trips. >> >>I want to convert this to the batch interface, i.e., addBatch() and >>executeBatch(), but I read that this is implemented as multiple round >> > trips > >>anyway in 7.1.x and before. I also read that this might be fixed in 7.2, >> > but > >>I've been unable to verify this in the documentation. >> >>Can anyone enlighten me on the state of the batching interface? or is >> > there > >>some better way to do this? >> >> >>Thank you, >>Tony DeWitt >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 4: Don't 'kill -9' the postmaster >> >> >> > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > >
Hello, I am very happy with Tomcat 4! it is much faster than Tomcat 3x I can read from Postgresql no problem. But I can't write to it via JSP... I have a weird permissions problem. Anyone have an idea of who should own what??? Cheers Tony Grant -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html
Tony, If you can read from the postgres database, then AFAIK there is nothing in the jdbc, or tomcat that would stop you from writing to it Can you get a look at the postgres logs?, or even the tomcat logs? Dave -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of tony Sent: Tuesday, January 29, 2002 3:34 PM To: pgsql-jdbc@postgresql.org Cc: justin@postgresql.org Subject: [JDBC] tomcat 4 gurus Hello, I am very happy with Tomcat 4! it is much faster than Tomcat 3x I can read from Postgresql no problem. But I can't write to it via JSP... I have a weird permissions problem. Anyone have an idea of who should own what??? Cheers Tony Grant -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Maybe the user has been granted select privileges on the table in question, but not insert or update privileges. On Tue, 29 Jan 2002, Dave Cramer wrote: > I can read from Postgresql no problem. > > But I can't write to it via JSP... I have a weird permissions problem. > Anyone have an idea of who should own what??? -- Stuart Robinson [stuart@zapata.org] Spiritual Leader of Robot Resistance
On Tue, 2002-01-29 at 21:51, Dave Cramer wrote: > If you can read from the postgres database, then AFAIK there is nothing > in the jdbc, or tomcat that would stop you from writing to it > > Can you get a look at the postgres logs?, or even the tomcat logs? Just as I thought, a wierd permissions problem somewhere... The machine unfortunately won't let me ssh into it anymore (another new "feature") I'll check it on the spot tomorrow Thanks for the input Cheers Tony Grant -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html
On Tue, 29 Jan 2002 09:59:24 -0800, you wrote: >Very strange. I was sure this had been changed in 7.2. I know there >was a discussion on how to change it on the mail lists, and I thought >that it had been done. But I guess not. I remember the discussion and our conclusion, but I don't remember anyone implementing it. Regards, René Pijlman <rene@lab.applinet.nl>
Hi I have recently started using pgsql and am using the PostgreSQL 7.1.1 driver. On using the getConnection() method from the PreparedStatement interface the java.lang.AbstractMethodError exception is thrown. Similarly for methods like getRow() from the ResultSet interface. Aren't these methods implemented in the Postgresql driver ? If these methods are not implemented, is there any alternative method(s) that can be used in place of these. Is there some other version of PostgresSQL driver where these methods are implemented ? regards Sulakshana
Before testing my application with PostgreSQL, I have tested it with MySQL
and MS SQL Server 7.0 and their respective JDBC drivers. The application
works without throwing any kind of exceptions.
The very basis of using drivers that are supposed to implement the JDBC API
specs is that the application can use any backend database without having to
modify code.
Clearly this is missing here if the ResultSet interface of Postgresql driver
does not give me a getRow() implementation or the PreparedStatement
interface does not give me a getConnection() implementation.
Is there any postgresql driver which does provide these implemetations ?
Modifying the application because of lack of implementation of abstract
methods is not an option that can be considered.
regards
Sulakshana
----- Original Message -----
From: "Stefano Reksten" <sreksten@sdb.it>
To: "Sulakshana Awsarikar" <sulakshana@mithi.com>
Sent: Thursday, January 31, 2002 4:40 PM
Subject: Re: [JDBC] getConnection() method of PreparedStatement
> At 16.12 31/01/02 +0530, you wrote:
> >On using the getConnection() method from the PreparedStatement interface
the
> >java.lang.AbstractMethodError exception is thrown.
> >Similarly for methods like getRow() from the ResultSet interface.
>
> Sulakshana,
> you should not ask a connection from the PreparedStatement, but you should
do
> Class.forName("org.postgresql.Driver");
> DriverManager.getConnection("jdbc:postgresql:<database name>",
> "<user>", >"<password>");
> PreparedStatement pstmt = c.prepareStatement("<query>");
> ...
> ResultSet rs = pstmt.executeQuery();
>
> You will get a proper class that *implements* the interfaces. Interfaces
> are just a "contract" between classes. HTH
>
> Ciao,
> Stefano
>
>
>
>
Sulakshana-
BTW, the problem with JDBC is that you still have to change your code
between databases, since the SQL needs to change. (Example, getting
sequences) If you really dont want to ever change your SQL or jdbc code
between postgresql, db2, ms-sql, etc, get an object-mapping tool like
castor or toplink. I'm working on the castor project to make sure the
tool supports postgresql. (I'm also going to try to get rowsets
implemented in the jdbc driver by 7.3, hopefully. :-)
There are other drivers available, I'm sure. Check out sourceforge.net.
Good luck.
--- Sulakshana Awsarikar <sulakshana@mithi.com> wrote:
> Before testing my application with PostgreSQL, I have tested it with
> MySQL and MS SQL Server 7.0 and their respective JDBC drivers. The
> application works without throwing any kind of exceptions.
>
> The very basis of using drivers that are supposed to implement the JDBC
> API
> specs is that the application can use any backend database without
> having to modify code.
>
> Clearly this is missing here if the ResultSet interface of Postgresql
> driver
> does not give me a getRow() implementation or the PreparedStatement
> interface does not give me a getConnection() implementation.
>
> Is there any postgresql driver which does provide these implemetations ?
> Modifying the application because of lack of implementation of abstract
> methods is not an option that can be considered.
>
> regards
> Sulakshana
>
>
> ----- Original Message -----
> From: "Stefano Reksten" <sreksten@sdb.it>
> To: "Sulakshana Awsarikar" <sulakshana@mithi.com>
> Sent: Thursday, January 31, 2002 4:40 PM
> Subject: Re: [JDBC] getConnection() method of PreparedStatement
>
>
> > At 16.12 31/01/02 +0530, you wrote:
> > >On using the getConnection() method from the PreparedStatement
> interface
> the
> > >java.lang.AbstractMethodError exception is thrown.
> > >Similarly for methods like getRow() from the ResultSet interface.
> >
> > Sulakshana,
> > you should not ask a connection from the PreparedStatement, but you
> should
> do
> > Class.forName("org.postgresql.Driver");
> > DriverManager.getConnection("jdbc:postgresql:<database name>",
> > "<user>", >"<password>");
> > PreparedStatement pstmt = c.prepareStatement("<query>");
> > ...
> > ResultSet rs = pstmt.executeQuery();
> >
> > You will get a proper class that *implements* the interfaces.
> Interfaces
> > are just a "contract" between classes. HTH
> >
> > Ciao,
> > Stefano
> >
> >
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
=====
Virtually, Ned Wolpert <wolpert@yahoo.com>
"Who watches the watchmen?" -Juvenal, 120 AD (4e75)
__________________________________________________
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions!
http://auctions.yahoo.com
It sounds to me like you are runing a driver built for Java1.1 but trying to call methods from Java1.2 or higher. If you have the correct version of the driver for your runtime environment, you shouldn't get any AbstractMethodErrors. thanks, --Barry Sulakshana Awsarikar wrote: > Hi > > I have recently started using pgsql and am using the PostgreSQL 7.1.1 > driver. > > On using the getConnection() method from the PreparedStatement interface the > java.lang.AbstractMethodError exception is thrown. > Similarly for methods like getRow() from the ResultSet interface. > > Aren't these methods implemented in the Postgresql driver ? If these methods > are not implemented, is there any alternative method(s) that can be used in > place of these. > > Is there some other version of PostgresSQL driver where these methods are > implemented ? > > regards > Sulakshana > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >
Thanks everybody ! I have now switched to the 7.1.3 driver and atleast part
of my problem is solved.
Thanks again
regards
Sulakshana
----- Original Message -----
From: "Ned Wolpert" <wolpert@yahoo.com>
To: "Sulakshana Awsarikar" <sulakshana@mithi.com>; "Stefano Reksten"
<sreksten@sdb.it>
Cc: <pgsql-jdbc@postgresql.org>
Sent: Thursday, January 31, 2002 7:34 PM
Subject: Re: [JDBC] getConnection() method of PreparedStatement
> Sulakshana-
>
> BTW, the problem with JDBC is that you still have to change your code
> between databases, since the SQL needs to change. (Example, getting
> sequences) If you really dont want to ever change your SQL or jdbc code
> between postgresql, db2, ms-sql, etc, get an object-mapping tool like
> castor or toplink. I'm working on the castor project to make sure the
> tool supports postgresql. (I'm also going to try to get rowsets
> implemented in the jdbc driver by 7.3, hopefully. :-)
>
> There are other drivers available, I'm sure. Check out sourceforge.net.
>
> Good luck.
>
> --- Sulakshana Awsarikar <sulakshana@mithi.com> wrote:
> > Before testing my application with PostgreSQL, I have tested it with
> > MySQL and MS SQL Server 7.0 and their respective JDBC drivers. The
> > application works without throwing any kind of exceptions.
> >
> > The very basis of using drivers that are supposed to implement the JDBC
> > API
> > specs is that the application can use any backend database without
> > having to modify code.
> >
> > Clearly this is missing here if the ResultSet interface of Postgresql
> > driver
> > does not give me a getRow() implementation or the PreparedStatement
> > interface does not give me a getConnection() implementation.
> >
> > Is there any postgresql driver which does provide these implemetations ?
> > Modifying the application because of lack of implementation of abstract
> > methods is not an option that can be considered.
> >
> > regards
> > Sulakshana
> >
> >
> > ----- Original Message -----
> > From: "Stefano Reksten" <sreksten@sdb.it>
> > To: "Sulakshana Awsarikar" <sulakshana@mithi.com>
> > Sent: Thursday, January 31, 2002 4:40 PM
> > Subject: Re: [JDBC] getConnection() method of PreparedStatement
> >
> >
> > > At 16.12 31/01/02 +0530, you wrote:
> > > >On using the getConnection() method from the PreparedStatement
> > interface
> > the
> > > >java.lang.AbstractMethodError exception is thrown.
> > > >Similarly for methods like getRow() from the ResultSet interface.
> > >
> > > Sulakshana,
> > > you should not ask a connection from the PreparedStatement, but you
> > should
> > do
> > > Class.forName("org.postgresql.Driver");
> > > DriverManager.getConnection("jdbc:postgresql:<database name>",
> > > "<user>", >"<password>");
> > > PreparedStatement pstmt = c.prepareStatement("<query>");
> > > ...
> > > ResultSet rs = pstmt.executeQuery();
> > >
> > > You will get a proper class that *implements* the interfaces.
> > Interfaces
> > > are just a "contract" between classes. HTH
> > >
> > > Ciao,
> > > Stefano
> > >
> > >
> > >
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
>
> =====
> Virtually, Ned Wolpert <wolpert@yahoo.com>
>
> "Who watches the watchmen?" -Juvenal, 120 AD (4e75)
>
> __________________________________________________
> Do You Yahoo!?
> Great stuff seeking new owners in Yahoo! Auctions!
> http://auctions.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>