Thread: Handling null values in char fields from JDBC

Handling null values in char fields from JDBC

From
"Sean Duffy"
Date:
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



Re: Handling null values in char fields from JDBC

From
"Paulo Merson"
Date:
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


Re: Handling null values in char fields from JDBC

From
"Dave Cramer"
Date:
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



Re: Handling null values in char fields from JDBC

From
"Sean Duffy"
Date:
-----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



Re: Handling null values in char fields from JDBC

From
"Sean Duffy"
Date:
-----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



JDBC batching

From
"Tony DeWitt"
Date:
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


Re: Handling null values in char fields from JDBC

From
"Paulo Merson"
Date:
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


Re: JDBC batching

From
Rene Pijlman
Date:
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>

Re: JDBC batching

From
Barry Lind
Date:
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
>
>



Re: Handling null values in char fields from JDBC

From
"Sean Duffy"
Date:
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



Getting exact string length from char fields in JDBC

From
"Sulakshana Awsarikar"
Date:
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


Re: Getting exact string length from char fields in JDBC

From
Ola Sundell
Date:
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


Re: JDBC batching

From
"Tony DeWitt"
Date:
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


Re: JDBC batching

From
"Tony DeWitt"
Date:
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


Re: JDBC batching

From
Barry Lind
Date:
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
>
>
>



tomcat 4 gurus

From
tony
Date:
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


Re: tomcat 4 gurus

From
"Dave Cramer"
Date:
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)



Re: tomcat 4 gurus

From
Stuart Robinson
Date:
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


Re: tomcat 4 gurus

From
tony
Date:
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


Re: JDBC batching

From
Rene Pijlman
Date:
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>

getConnection() method of PreparedStatement

From
"Sulakshana Awsarikar"
Date:
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


Re: getConnection() method of PreparedStatement

From
"Sulakshana Awsarikar"
Date:
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
>
>
>
>


Re: getConnection() method of PreparedStatement

From
Ned Wolpert
Date:
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

Re: getConnection() method of PreparedStatement

From
Barry Lind
Date:
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
>
>



Re: getConnection() method of PreparedStatement

From
"Sulakshana Awsarikar"
Date:
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)
>
>