Re: [GENERAL] JDBC: logical replication and LSN feedback

[Yason TR]

Thanks, I did not realize that acknowledgements are delayed until the next forceUpdateStatus() call. From the javadocs, I understood that the a read() call also does a forceUpdateStatus() internally, so a manual forceUpdateStatus() is not required. Maybe a forceUpdateStatus() only happens during a read() when the read() effectively received some messages?

 

I will start my experiments again and let you know.

 

Kind regards,

 

Yason TR

 

Sent: Wednesday, September 20, 2017 at 2:22 PM
From: "Dave Cramer" <p...@fastcrypt.com>
To: "Yason TR" <yason...@gmx.com>
Cc: "Achilleas Mantzios" <ach...@matrix.gatewaynet.com>, "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>, List <pgsql-j...@postgresql.org>
Subject: Re: [GENERAL] JDBC: logical replication and LSN feedback



+list

 
First off you are going to get considerably better response from the JDBC list or our github project.

 

Looking at the code; in order to ensure the backend has received the acknowledgement you need to call forceUpdateStatus

 

Otherwise it may not receive the ack

 

 

 
 

 

 

 



 



Dave Cramer

da...@postgresintl.com

www.postgresintl.com



 

On 19 September 2017 at 07:53, Yason TR <yason...@gmx.com> wrote:




Should we read "In the event that replication has been restarted, it's will start from last successfully processed LSN that was sent via feedback to database." that this last succesfully event will be included (again) after a restart of the replication, or that the next event starting from the this last successfully event will be sent?

 

I would expect the second, as this makes the most sense (because the consumers only want each event once), but I am not sure.

 

Thanks a lot and kind regards,

 

Yason TR

 

Sent: Tuesday, September 19, 2017 at 4:14 PM
From: "Achilleas Mantzios" <ach...@matrix.gatewaynet.com>
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] JDBC: logical replication and LSN feedback



On 19/09/2017 16:37, Yason TR wrote:
> Hi all,
>
> I am developing an application which connects to a logical replication slot, to consume the WAL events. These WAL events are then forwarded to a MQ broker.
>
> The heart of the code can be seen as:
>
> while (true) {
> Connection connection = null;
> PGReplicationStream stream = null;
>
> try {
> connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/db", properties);
> stream = connection.unwrap(PGConnection.class).getReplicationAPI().replicationStream().logical().withSlotName("slot").start();
>
> while (true) {
> final ByteBuffer buffer = stream.read();
>
> // ... MQ logic here ... omitted ...
>
> stream.setAppliedLSN(stream.getLastReceiveLSN());
> stream.setFlushedLSN(stream.getLastReceiveLSN());
> }
> } catch (final SQLException e) {
> // ... log exception ... omitted ...
> } finally {
> // ... close stream and connection ... omitted ...
> }
> }
>
> I notice some behavior which I cannot explain and would like to understand so I can alter my code:
>
> - When I restart the application, I notice that the application is retrieving the last event from the previous run again. The result is that this event is sent twice to the MQ broker after a restart of the application. Why is that? Isn't calling `setAppliedLSN(stream.getLastReceiveLSN())` and/or `setFlushedLSN(stream.getLastReceiveLSN())` enough to acknowledge an event, so it will removed from the WAL log and it will not be resent?
>
> - When receiving an event, the corresponding LSN from that event (which is sent in the payload) is not the same as the result of `stream.getLastReceivedLSN()`. Why is that? Which one should I use? Maybe this is correlated to my first question.
>
> - What is the difference between `setAppliedLSN(LSN)` and `setFlushedLSN(LSN)`? The Javadocs are not really helpful here.

The stages of a wal location generally go like : sent -> write -> flush -> replay , at least in terms of physical replication.
I guess applied=replayed ?

Note that from the docs : https://jdbc.postgresql.org/documentation/head/replication.html#logical-replication
it says :
"
In the event that replication has been restarted, it's will start from last successfully processed LSN that was sent via feedback to database.
"

>
> FYI, I also asked this question on https://stackoverflow.com/questions/46301578/postgres-jdbc-logical-replication-lsn-feedback.
>
> Thanks a lot and kind regards,
>
> Yason TR
>
>

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC: logical replication and LSN feedback

[Dave Cramer]

+list

First off you are going to get considerably better response from the JDBC
list or our github project.

Looking at the code; in order to ensure the backend has received the
acknowledgement you need to call forceUpdateStatus

Otherwise it may not receive the ack








Dave Cramer

da...@postgresintl.com
www.postgresintl.com

On 19 September 2017 at 07:53, Yason TR <yason...@gmx.com> wrote:

> Should we read "In the event that replication has been restarted, it's
> will start from last successfully processed LSN that was sent via feedback
> to database." that this last succesfully event will be included (again)
> after a restart of the replication, or that the next event starting from
> the this last successfully event will be sent?
>
> I would expect the second, as this makes the most sense (because the
> consumers only want each event once), but I am not sure.
>
> Thanks a lot and kind regards,
>
> Yason TR
>
> *Sent:* Tuesday, September 19, 2017 at 4:14 PM
> *From:* "Achilleas Mantzios" <ach...@matrix.gatewaynet.com>
> *To:* pgsql-general@postgresql.org
> *Subject:* Re: [GENERAL] JDBC: logical replication and LSN feedback
> On 19/09/2017 16:37, Yason TR wrote:
> > Hi all,
> >
> > I am developing an application which connects to a logical replication
> slot, to consume the WAL events. These WAL events are then forwarded to a
> MQ broker.
> >
> > The heart of the code can be seen as:
> >
> > while (true) {
> > Connection connection = null;
> > PGReplicationStream stream = null;
> >
> > try {
> > connection = 
> > DriverManager.getConnection("jdbc:postgresql://localhost:5432/db",
> properties);
> > stream = connection.unwrap(PGConnection.class).getReplicationAPI().
> replicationStream().logical().withSlotName("slot").start();
> >
> > while (true) {
> > final ByteBuffer buffer = stream.read();
> >
> > // ... MQ logic here ... omitted ...
> >
> > stream.setAppliedLSN(stream.getLastReceiveLSN());
> > stream.setFlushedLSN(stream.getLastReceiveLSN());
> > }
> > } catch (final SQLException e) {
> > // ... log exception ... omitted ...
> > } finally {
> > // ... close stream and connection ... omitted ...
> > }
> > }
> >
> > I notice some behavior which I cannot explain and would like to
> understand so I can alter my code:
> >
> > - When I restart the application, I notice that the application is
> retrieving the last event from the previous run again. The result is that
> this event is sent twice to the MQ broker after a restart of the
> application. Why is that? Isn't calling 
> `setAppliedLSN(stream.getLastReceiveLSN())`
> and/or `setFlushedLSN(stream.getLastReceiveLSN())` enough to acknowledge
> an event, so it will removed from the WAL log and it will not be resent?
> >
> > - When receiving an event, the corresponding LSN from that event (which
> is sent in the payload) is not the same as the result of
> `stream.getLastReceivedLSN()`. Why is that? Which one should I use? Maybe
> this is correlated to my first question.
> >
> > - What is the difference between `setAppliedLSN(LSN)` and
> `setFlushedLSN(LSN)`? The Javadocs are not really helpful here.
>
> The stages of a wal location generally go like : sent -> write -> flush ->
> replay , at least in terms of physical replication.
> I guess applied=replayed ?
>
> Note that from the docs : https://jdbc.postgresql.org/documentation/head/
> replication.html#logical-replication
> it says :
> "
> In the event that replication has been restarted, it's will start from
> last successfully processed LSN that was sent via feedback to database.
> "
>
> >
> > FYI, I also asked this question on https://stackoverflow.com/
> questions/46301578/postgres-jdbc-logical-replication-lsn-feedback.
> >
> > Thanks a lot and kind regards,
> >
> > Yason TR
> >
> >
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] JDBC: logical replication and LSN feedback

[Yason TR]

Should we read "In the event that replication has been restarted, it's will start from last successfully processed LSN that was sent via feedback to database." that this last succesfully event will be included (again) after a restart of the replication, or that the next event starting from the this last successfully event will be sent?

 

I would expect the second, as this makes the most sense (because the consumers only want each event once), but I am not sure.

 

Thanks a lot and kind regards,

 

Yason TR

 

Sent: Tuesday, September 19, 2017 at 4:14 PM
From: "Achilleas Mantzios" <ach...@matrix.gatewaynet.com>
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] JDBC: logical replication and LSN feedback

On 19/09/2017 16:37, Yason TR wrote:
> Hi all,
>
> I am developing an application which connects to a logical replication slot, to consume the WAL events. These WAL events are then forwarded to a MQ broker.
>
> The heart of the code can be seen as:
>
> while (true) {
> Connection connection = null;
> PGReplicationStream stream = null;
>
> try {
> connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/db", properties);
> stream = connection.unwrap(PGConnection.class).getReplicationAPI().replicationStream().logical().withSlotName("slot").start();
>
> while (true) {
> final ByteBuffer buffer = stream.read();
>
> // ... MQ logic here ... omitted ...
>
> stream.setAppliedLSN(stream.getLastReceiveLSN());
> stream.setFlushedLSN(stream.getLastReceiveLSN());
> }
> } catch (final SQLException e) {
> // ... log exception ... omitted ...
> } finally {
> // ... close stream and connection ... omitted ...
> }
> }
>
> I notice some behavior which I cannot explain and would like to understand so I can alter my code:
>
> - When I restart the application, I notice that the application is retrieving the last event from the previous run again. The result is that this event is sent twice to the MQ broker after a restart of the application. Why is that? Isn't calling `setAppliedLSN(stream.getLastReceiveLSN())` and/or `setFlushedLSN(stream.getLastReceiveLSN())` enough to acknowledge an event, so it will removed from the WAL log and it will not be resent?
>
> - When receiving an event, the corresponding LSN from that event (which is sent in the payload) is not the same as the result of `stream.getLastReceivedLSN()`. Why is that? Which one should I use? Maybe this is correlated to my first question.
>
> - What is the difference between `setAppliedLSN(LSN)` and `setFlushedLSN(LSN)`? The Javadocs are not really helpful here.

The stages of a wal location generally go like : sent -> write -> flush -> replay , at least in terms of physical replication.
I guess applied=replayed ?

Note that from the docs : https://jdbc.postgresql.org/documentation/head/replication.html#logical-replication
it says :
"
In the event that replication has been restarted, it's will start from last successfully processed LSN that was sent via feedback to database.
"

>
> FYI, I also asked this question on https://stackoverflow.com/questions/46301578/postgres-jdbc-logical-replication-lsn-feedback.
>
> Thanks a lot and kind regards,
>
> Yason TR
>
>

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC: logical replication and LSN feedback

[Achilleas Mantzios]

On 19/09/2017 16:37, Yason TR wrote:

Hi all,

I am developing an application which connects to a logical replication slot, to 
consume the WAL events. These WAL events are then forwarded to a MQ broker.

The heart of the code can be seen as:

while (true) {
 Connection connection = null;
 PGReplicationStream stream = null;

 try {
 connection = 
DriverManager.getConnection("jdbc:postgresql://localhost:5432/db", properties);
 stream = 
connection.unwrap(PGConnection.class).getReplicationAPI().replicationStream().logical().withSlotName("slot").start();

 while (true) {
 final ByteBuffer buffer = stream.read();

 // ... MQ logic here ... omitted ...

 stream.setAppliedLSN(stream.getLastReceiveLSN());
 stream.setFlushedLSN(stream.getLastReceiveLSN());
 }
 } catch (final SQLException e) {
 // ... log exception ... omitted ...
 } finally {
 // ... close stream and connection ... omitted ...
 }
}

I notice some behavior which I cannot explain and would like to understand so I 
can alter my code:

- When I restart the application, I notice that the application is retrieving 
the last event from the previous run again. The result is that this event is 
sent twice to the MQ broker after a restart of the application. Why is that? 
Isn't calling `setAppliedLSN(stream.getLastReceiveLSN())` and/or 
`setFlushedLSN(stream.getLastReceiveLSN())` enough to acknowledge an event, so 
it will removed from the WAL log and it will not be resent?

- When receiving an event, the corresponding LSN from that event (which is sent 
in the payload) is not the same as the result of `stream.getLastReceivedLSN()`. 
Why is that? Which one should I use? Maybe this is correlated to my first 
question.

- What is the difference between `setAppliedLSN(LSN)` and `setFlushedLSN(LSN)`? 
The Javadocs are not really helpful here.


The stages of a wal location generally go like : sent -> write -> flush -> 
replay , at least in terms of physical replication.
I guess applied=replayed ?

Note that from the docs : 
https://jdbc.postgresql.org/documentation/head/replication.html#logical-replication
it says :
"
In the event that replication has been restarted, it's will start from last 
successfully processed LSN that was sent via feedback to database.
"



FYI, I also asked this question on 
https://stackoverflow.com/questions/46301578/postgres-jdbc-logical-replication-lsn-feedback.

Thanks a lot and kind regards,

Yason TR




--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] jdbc driver vis Release 10

[Dave Cramer]

Well we won't be fixing any bugs in anything but the latest version if that
makes a difference to you.

Dave Cramer

da...@postgresintl.com
www.postgresintl.com

On 25 August 2017 at 21:34, Rob Sargent  wrote:

>
>
> On Aug 25, 2017, at 5:55 PM, Dave Cramer  wrote:
>
> There are newer versions out there!
>
> Dave Cramer
>
> da...@postgresintl.com
> www.postgresintl.com
>
> On 25 August 2017 at 19:53, Rob Sargent  wrote:
>
>>
>>
>> On 08/25/2017 05:34 PM, Dave Cramer wrote:
>>
>> The JDBC driver release is divorced from the server release.
>>
>> Thanks
>>
>> Dave Cramer
>>
>> da...@postgresintl.com
>> www.postgresintl.com
>>
>> On 23 August 2017 at 19:33, Rob Sargent  wrote:
>>
>>> I see no mention of a new jdbc driver on the release notes for Beta 1.
>>> Does that mean there isn't one?
>>>
>>>
>>>
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>
>> Yes, so I've learned.  I just ran my software using jdbc 9.4.1212 via
>> j00Q 3.9 against pg10-beta3 without a problem.  I do very vanilla stuff.
>>
>>
>
> Yes. I'll get there as needed but wanted to know if any piece needed
> changing NOW
>

Re: [GENERAL] jdbc driver vis Release 10

[Rob Sargent]

> On Aug 25, 2017, at 5:55 PM, Dave Cramer  wrote:
> 
> There are newer versions out there!
> 
> Dave Cramer
> 
> da...@postgresintl.com
> www.postgresintl.com
> 
>> On 25 August 2017 at 19:53, Rob Sargent  wrote:
>> 
>> 
>>> On 08/25/2017 05:34 PM, Dave Cramer wrote:
>>> The JDBC driver release is divorced from the server release.
>>> 
>>> Thanks
>>> 
>>> Dave Cramer
>>> 
>>> da...@postgresintl.com
>>> www.postgresintl.com
>>> 
 On 23 August 2017 at 19:33, Rob Sargent  wrote:
 I see no mention of a new jdbc driver on the release notes for Beta 1.  
 Does that mean there isn't one?
 
 
 
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general
>>> 
>> Yes, so I've learned.  I just ran my software using jdbc 9.4.1212 via j00Q 
>> 3.9 against pg10-beta3 without a problem.  I do very vanilla stuff.
>>  
> 
Yes. I'll get there as needed but wanted to know if any piece needed changing 
NOW 

Re: [GENERAL] jdbc driver vis Release 10

[Dave Cramer]

There are newer versions out there!

Dave Cramer

da...@postgresintl.com
www.postgresintl.com

On 25 August 2017 at 19:53, Rob Sargent  wrote:

>
>
> On 08/25/2017 05:34 PM, Dave Cramer wrote:
>
> The JDBC driver release is divorced from the server release.
>
> Thanks
>
> Dave Cramer
>
> da...@postgresintl.com
> www.postgresintl.com
>
> On 23 August 2017 at 19:33, Rob Sargent  wrote:
>
>> I see no mention of a new jdbc driver on the release notes for Beta 1.
>> Does that mean there isn't one?
>>
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
> Yes, so I've learned.  I just ran my software using jdbc 9.4.1212 via j00Q
> 3.9 against pg10-beta3 without a problem.  I do very vanilla stuff.
>
>

Re: [GENERAL] jdbc driver vis Release 10

[Rob Sargent]

On 08/25/2017 05:34 PM, Dave Cramer wrote:

The JDBC driver release is divorced from the server release.

Thanks

Dave Cramer

da...@postgresintl.com 
www.postgresintl.com 

On 23 August 2017 at 19:33, Rob Sargent > wrote:


I see no mention of a new jdbc driver on the release notes for
Beta 1.  Does that mean there isn't one?




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org

)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



Yes, so I've learned.  I just ran my software using jdbc 9.4.1212 via 
j00Q 3.9 against pg10-beta3 without a problem.  I do very vanilla stuff.

Re: [GENERAL] jdbc driver vis Release 10

[Dave Cramer]

The JDBC driver release is divorced from the server release.

Thanks

Dave Cramer

da...@postgresintl.com
www.postgresintl.com

On 23 August 2017 at 19:33, Rob Sargent  wrote:

> I see no mention of a new jdbc driver on the release notes for Beta 1.
> Does that mean there isn't one?
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] jdbc driver vis Release 10

[Rob Sargent]

On 08/23/2017 06:09 PM, David G. Johnston wrote:
On Wed, Aug 23, 2017 at 4:33 PM, Rob Sargent >wrote:


I see no mention of a new jdbc driver on the release notes for
Beta 1.  Does that mean there isn't one?


​Whose release notes?  PostgreSQL Server?  I don't believe the server 
release notes ever talk about external projects.​


Its seems quite a few significant changes (including version 
numbering) have happened to the JDBC project in the past year but you 
will need to go directly to the project's site/GitHub to keep abreast 
of all of it.


David J.

Thanks

Re: [GENERAL] jdbc driver vis Release 10

[David G. Johnston]

On Wed, Aug 23, 2017 at 4:33 PM, Rob Sargent  wrote:

> I see no mention of a new jdbc driver on the release notes for Beta 1.
> Does that mean there isn't one?


​Whose release notes?  PostgreSQL Server?  I don't believe the server
release notes ever talk about external projects.​

Its seems quite a few significant changes (including version numbering)
have happened to the JDBC project in the past year but you will need to go
directly to the project's site/GitHub to keep abreast of all of it.

David J.

Re: [GENERAL] jdbc 9.4-1208 driver for PostgreSQL 9.5?

[Albe Laurenz]

Joek Hondius wrote:
> (I hope i am on the right list)

pgsql-jdbc would have been the perfect list.

> jdbc.postgresql.org lists version 9.4 build 1208 as the lastest.
> Is this the correct version to use with PostgreSQL 9.5 (or even 9.6-beta)?
> I cannot find info on this elsewhere.

Yes, you should just use the latest driver.
Don't be worried if they don't have the same version number.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] JDBC behaviour

[Craig Ringer]

On 18 February 2016 at 16:13, Vladimir Sitnikov  wrote:

If you want to shoot yourself in a foot for fun and profit, you can
> try https://github.com/pgjdbc/pgjdbc/pull/477.
>

I think this should be incorporated, once it's ready, as a non-default
connection option. It's handy for porting applications.

I think PostgreSQL's behaviour is the most correct, but if people are
porting apps and want to wear the performance impact of all those
savepoints and have written their code to handle partially-failed xacts,
then sure, they can have that.

I'm constantly astounded by how many people are willing to simply ignore
errors and carry on with the transaction without even properly verifying
that the error was the exact one they expected though. Seriously bad
application development and it *will* bite them. The best, most correct
thing to do remains to retry the whole transaction.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] [JDBC] JDBC behaviour

[John R Pierce]

On 2/18/2016 12:26 AM, Sridhar N Bamandlapally wrote:

If we want transactions in "begin-end" then its fine,

but in this case all these transactions are independent with 
autocommit off,


with autocommit OFF, when you issue the first query, jdbc generates a 
postgresql BEGIN;  this starts a postgresql transaction. To end the 
transaction,  you have to explicitly .commit() or .rollback() 


with autocommit ON, then jdbc lets each query execute standalone, this 
is postgresql's default behavior if you don't use BEGIN...




--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] JDBC behaviour

[Sridhar N Bamandlapally]

Ok, let me put this way

in JDBC we have *setAutoCommit( false ) *, and all dmls are independent
transactions

and when any transaction fails then the session not allowing next
transactions

in Java when we do setAutoCommit( false ) its behaving like all
transactions in BEGIN-END block, this is not expected behavior

i guess this is bug





On Thu, Feb 18, 2016 at 2:00 PM, Vladimir Sitnikov <
sitnikov.vladi...@gmail.com> wrote:

> > but in this case all these transactions are independent with autocommit
> off,
>
> At database level, there is no "autocommit=off".
> There's just "begin-end".
>
> It is database who forbids .commit, not the JDBC driver.
> Vladimir
>

Re: [GENERAL] [JDBC] JDBC behaviour

[Vladimir Sitnikov]

> but in this case all these transactions are independent with autocommit off,

At database level, there is no "autocommit=off".
There's just "begin-end".

It is database who forbids .commit, not the JDBC driver.
Vladimir


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] JDBC behaviour

[Sridhar N Bamandlapally]

If we want transactions in "begin-end" then its fine,

but in this case all these transactions are independent with autocommit off,

user choice to continue with commit or rollback

Thanks
Sridhar



On Thu, Feb 18, 2016 at 1:43 PM, Vladimir Sitnikov <
sitnikov.vladi...@gmail.com> wrote:

> >Is it a bug or do we have other any alternate way to handle this ?
>
> PostgreSQL is strongly against "partial commits to the database". If
> you think a bit about it, it is not that bad.
> You got an error, what is the business case to commit the partial
> transaction then?
>
> Exceptions should not be used for a "control flow", should they?
>
>
> If you want to shoot yourself in a foot for fun and profit, you can
> try https://github.com/pgjdbc/pgjdbc/pull/477.
> What it does, it creates savepoints before each statement, then it
> rollbacks to that savepoint in case of failure.
>
> Vladimir
>

Re: [GENERAL] [JDBC] JDBC behaviour

[Vladimir Sitnikov]

>Is it a bug or do we have other any alternate way to handle this ?

PostgreSQL is strongly against "partial commits to the database". If
you think a bit about it, it is not that bad.
You got an error, what is the business case to commit the partial
transaction then?

Exceptions should not be used for a "control flow", should they?


If you want to shoot yourself in a foot for fun and profit, you can
try https://github.com/pgjdbc/pgjdbc/pull/477.
What it does, it creates savepoints before each statement, then it
rollbacks to that savepoint in case of failure.

Vladimir


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC behaviour

[Sridhar N Bamandlapally]

Hi

We are facing issue with PostgreSQL JDBC behaviour

in Java, under autocommit false mode,

1. In between if any transaction then for next transaction, throws
exception saying "current transaction is aborted, commands ignored until
end of transaction block"

2. Even if exception is suppressed with try-catch then too for next
transaction, throws exception saying "current transaction is aborted,
commands ignored until end of transaction block"

3. The same is not happening with Oracle or SQL-Server, in this with-out
any exception handling it works

Is it a bug or do we have other any alternate way to handle this ?

Please I need some help in this

Thanks
Sridhar

Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA

[Vladimir Sitnikov]

> I hope I have been the most clear as my poor level of English could be..

It would be great if you could express that in java + sql as well, so
the exact code can be added to JDBC driver test suite as a regression
test.
Vladimir


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA

[Vladimir Sitnikov]

>As I understand, it's all what you need, isn't you 

Ideally I would like to see a pull request at
https://github.com/pgjdbc/pgjdbc/pulls, however your code seems to be
good enough so somebody else can pick it up, simplify a bit, and file
a PR.
Vladimir


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA

[Vladimir Sitnikov]

>Ok. I understand, to put there a pull request, I must to register into this 
>webpage ??

Exactly.

Vladimir


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA

[Corradini, Carlos]

Yes, I was thinking to post my solution in this list for any who need to know 
how to receive more than one cursors from a function stored in a PostgreSQL 
database, but, living and working in Argentina, made an impossible work last 
week ( abnormal tasks to do are normal here  ) , but now I have 5 minutes, 
then, I decided to post the java code.

The solution is very, very simple having a stored function with, for 
example, 2 input parameters and 2 cursors returned, I do not use RETURNS SETOF 
REFCURSORS in the function, but I have use the key OUT as out parameter ( just 
I use in Oracle Stored Procedures ) in the function, then, in the java program, 
after made the connection to the database, I have used the CallableStatement 
setting the value of all the inputs parameters with the values and type needed 
in the function, and as for the data returned ( in this case the cursors ), I 
have used the REGISTEROUTPARAMETER ( this method is provided by the 
calllablestatement java class ) with each value returned ( in this case the 
cursors ) numbered from 1 for the first out parameter and n for the last, 
informing the type TYPES.OTHER too. After made the java call ( 
callablestatement.execute() ), I set a public ( in this example ) variable as a 
RECORDSET and assigning each cursor to each RECORDSET variable : 

I use the integer variable called num_cursor for the first returned and 
num_cursor_02 for the second ( je .. I have broken my mind and brain thinking 
for the perfect names ... ).

rs = (ResultSet) cs.getObject(num_cursor);
rs1 = (ResultSet) cs.getObject(num_cursor_02);

the data obtained by the GETOBJECT method, being a cursor, must be parsed to a 
RECORDSET, and not, is very important this point, never use the name you gave 
to each cursor in the function stored, simply provide a number for which cursor 
want to put in each recordset variable. after, using a java loop as "while 
recordset.next()" method, you can extract each value with getxxx(namedvariable) 
returned into each cursor

and it's all ...

I hope I have been the most clear as my poor level of English could be

Many thanks for all and specially to the postgresql community list !!



-Mensaje original-
De: Kevin Grittner [mailto:kgri...@gmail.com] 
Enviado el: viernes, 11 de diciembre de 2015 06:54 p.m.
Para: Corradini, Carlos
CC: Adrian Klaver; pgsql-j...@postgresql.org; pgsql-general@postgresql.org; 
Kris Jurka
Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in 
JAVA

On Fri, Dec 11, 2015 at 2:11 PM, Corradini, Carlos 
<ccorrad...@correoargentino.com.ar> wrote:

> with your and Mr. Kevin explanations, the Java program have worked 
> fine and have printed the data obtained from a two cursors inside a 
> PostgreSQL Database Stored Function.
>
> Then, I can confirm that this version of DB ( 9.4 ) use the OUT 
> parameter with refcursors and works fine. The JDBC interface provided 
> by the Server Postgresql can read the data inserted into these two 
> cursors via a callablestatement.registeroutparameter.

For the benefit of others who may later have a similar problem and find this 
thread, it would be great if you could provide a little self-contained example 
of a Java program which uses the technique that you settled on.

Thanks!

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA

[Corradini, Carlos]

 RAISE NOTICE 'v_user_id : %',v_user_id;
RAISE NOTICE 'v_fecha : %', v_fecha;
insert into dw_bsc.perspective
(name, short_desc, description, usr_id_ins, 
usr_date_ins, usr_id_upd, usr_date_upd)
values
(v_name, v_short_desc, v_descr, v_user_id, 
v_fecha, null, null);
open perspectives_cursor FOR select 'ok. 
insert' as resultado1;
c1 := perspectives_cursor;
-- return next perspectives_cursor;
open goals_persps_cursor FOR select 'null' as 
resultado2;
c2 := goals_persps_cursor;
-- return next goals_persps_cursor;


else
RAISE NOTICE 'El id pasado al procedure no es 
0, imposible procesar INSERT !!!';
open perspectives_cursor FOR select 'NULL' as 
resultado1;
c1 := perspectives_cursor;
-- return next perspectives_cursor;
open goals_persps_cursor FOR select 'null' as 
resultado2;
c2 := goals_persps_cursor;
-- return next goals_persps_cursor;

end if;
-- exception
--  when others then
--  raise notice 'Se dio el error número 
%',sqlstate,' con descripción %', sqlerrm;
-- end;

end case;

end;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION dw_bsc.proc_perspectives(character varying, integer, character 
varying, character varying, character varying, integer, date)
  OWNER TO usr_dw_bsc_sys_adm;
GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying, integer, 
character varying, character varying, character varying, integer, date) TO 
public;
GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying, integer, 
character varying, character varying, character varying, integer, date) TO 
usr_dw_bsc_sys_adm;
GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying, integer, 
character varying, character varying, character varying, integer, date) TO 
ro_dw_bsc_sys_adm;


One more thing, I am a DBA ORACLE and not a Developer, please, excuse me if I 
made "horrors" in the programming, I promise to be more effective next time 
!

As I understand, it's all what you need, isn't you 

-Mensaje original-
De: Vladimir Sitnikov [mailto:sitnikov.vladi...@gmail.com] 
Enviado el: lunes, 14 de diciembre de 2015 10:20 a.m.
Para: Corradini, Carlos
CC: Kevin Grittner; Adrian Klaver; List; pgsql-general@postgresql.org; Kris 
Jurka
Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in 
JAVA

> I hope I have been the most clear as my poor level of English could be..

It would be great if you could express that in java + sql as well, so the exact 
code can be added to JDBC driver test suite as a regression test.
Vladimir

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA

[Corradini, Carlos]

Ok. I understand, to put there a pull request, I must to register into this 
webpage ??

-Mensaje original-
De: Vladimir Sitnikov [mailto:sitnikov.vladi...@gmail.com] 
Enviado el: lunes, 14 de diciembre de 2015 10:53 a.m.
Para: Corradini, Carlos
CC: Kevin Grittner; Adrian Klaver; List; pgsql-general@postgresql.org; Kris 
Jurka
Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in 
JAVA

>As I understand, it's all what you need, isn't you 

Ideally I would like to see a pull request at 
https://github.com/pgjdbc/pgjdbc/pulls, however your code seems to be good 
enough so somebody else can pick it up, simplify a bit, and file a PR.
Vladimir

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA

[Corradini, Carlos]

I forgot one more thing ...

The return I made in the stored function is : RETURNS RECORD AS '

Excuse me for the forget ...

-Mensaje original-
De: Kevin Grittner [mailto:kgri...@gmail.com] 
Enviado el: viernes, 11 de diciembre de 2015 06:54 p.m.
Para: Corradini, Carlos
CC: Adrian Klaver; pgsql-j...@postgresql.org; pgsql-general@postgresql.org; 
Kris Jurka
Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in 
JAVA

On Fri, Dec 11, 2015 at 2:11 PM, Corradini, Carlos 
<ccorrad...@correoargentino.com.ar> wrote:

> with your and Mr. Kevin explanations, the Java program have worked 
> fine and have printed the data obtained from a two cursors inside a 
> PostgreSQL Database Stored Function.
>
> Then, I can confirm that this version of DB ( 9.4 ) use the OUT 
> parameter with refcursors and works fine. The JDBC interface provided 
> by the Server Postgresql can read the data inserted into these two 
> cursors via a callablestatement.registeroutparameter.

For the benefit of others who may later have a similar problem and find this 
thread, it would be great if you could provide a little self-contained example 
of a Java program which uses the technique that you settled on.

Thanks!

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA

[Corradini, Carlos]

Dear Mr. Adrian

Well, I must be honest, with your and Mr. Kevin explanations, the Java
program have worked fine and have printed the data obtained from a two
cursors inside a PostgreSQL Database Stored Function.

Then, I can confirm that this version of DB ( 9.4 ) use the OUT
parameter with refcursors and works fine. The JDBC interface provided by
the Server Postgresql can read the data inserted into these two cursors
via a callablestatement.registeroutparameter.

Many thanks 

-Mensaje original-
De: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Enviado el: viernes, 11 de diciembre de 2015 12:27 p.m.
Para: Corradini, Carlos; pgsql-j...@postgresql.org;
pgsql-general@postgresql.org
CC: bo...@ejurka.com
Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF
refcursor in JAVA

On 12/11/2015 07:10 AM, Corradini, Carlos wrote:
> Mr. Adrian, first let me say many thanks for your replies, were very 
> helpful for me. But, I must to say this other .
>
> I take a copy from the function from the gui tool of pgadmin III 
> called query sql, the original function name all the parameters, I do 
> not know why this gui tool change that.

I have no I idea either, seems silly to me.

>
> You say that I am returning only one cursor, but I can say that I am 
> doing exactly what are you explaining in your example, I am doing 
> return next for each cursor to return.

 From your previous post:

CREATE OR REPLACE FUNCTION dw_bsc.proc_perspectives(
 character varying,
 integer,
 character varying,
 character varying,
 character varying,
 integer,
 date)
   RETURNS SETOF refcursor AS



You use two cursors internally, but are returning only one above. Unless
you do something like Kevin Grittner and I showed there is no way for
Postgres to know how to assign the internal cursors to return values
that can be used externally.

>
> I think I am doing something wrong in the java program but I can't 
> realize what is this .I will go now to see the examples in the 
> URL's you did put below.
>
> Many thanks for all and excuse me for disturbing you and make you 
> waste your free time in this.

Well the point of the list is to answer questions and if I did not have
the time or the interest I would not answer, so do not worry about it.

>
> Again, many thanks
>
> -Mensaje original-
> De: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
> Enviado el: viernes, 11 de diciembre de 2015 11:37 a.m.
> Para: Corradini, Carlos; pgsql-j...@postgresql.org; 
> pgsql-general@postgresql.org
> CC: bo...@ejurka.com
> Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF 
> refcursor in JAVA
>
> On 12/11/2015 04:56 AM, Corradini, Carlos wrote:
>> Mr. Adrian, here i transcribe the code of the function
>
> Notes in line.
>
>>
>> -- Function: dw_bsc.proc_perspectives(character varying, integer, 
>> character varying, character varying, character varying, integer,
> date)
>>
>> -- DROP FUNCTION dw_bsc.proc_perspectives(character varying, integer,

>> character varying, character varying, character varying, integer,
> date);
>>
>> CREATE OR REPLACE FUNCTION dw_bsc.proc_perspectives(
>>   character varying,
>>   integer,
>>   character varying,
>>   character varying,
>>   character varying,
>>   integer,
>>   date)
>> RETURNS SETOF refcursor AS
>
> First you can name your function parameters:
>
> http://www.postgresql.org/docs/9.4/interactive/plpgsql-declarations.ht
> ml
> #PLPGSQL-DECLARATION-PARAMETERS
>
> 40.3.1. Declaring Function Parameters
>
>
> If you do that then you can simplify the below. In other words in 
> above the first parameter becomes:
>
> v_oper varchar(1)
>
> Saves creating a NULL variable and assigning to it as below.
>
>> $BODY$
>> declare
>>
>>   v_oper varchar(1) := null;
>>   v_id integer := null;
>>   v_name varchar(50) := null;
>>   v_short_desc varchar(150) := null;
>>   v_descr varchar(500) := null;
>>   v_user_id integer := null;
>>   v_fecha date := null;
>>   v_resu integer := null;
>>   perspectives_cursor refcursor := null;
>>   goals_persps_cursor refcursor := null;
>>   null_cursor refcursor := null;
>>
>>
>> begin
>>
>>   v_oper := $1;
>>   v_id := $2;
>>   v_name := $3;
>>   v_short_desc := $4;
>>   v_descr := $5;
>>   v_user_id := $6;
>>   v_fecha := $7;
>>
>>
>>   -- oper R = READ
>>   -- oper D = DELETE
>>   -- oper M = UPDATE
>>   -- oper I = INSERT
>
>
> 
>>
>

Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA

[Adrian Klaver]

On 12/11/2015 04:56 AM, Corradini, Carlos wrote:

Mr. Adrian, here i transcribe the code of the function


Notes in line.



-- Function: dw_bsc.proc_perspectives(character varying, integer,
character varying, character varying, character varying, integer, date)

-- DROP FUNCTION dw_bsc.proc_perspectives(character varying, integer,
character varying, character varying, character varying, integer, date);

CREATE OR REPLACE FUNCTION dw_bsc.proc_perspectives(
 character varying,
 integer,
 character varying,
 character varying,
 character varying,
 integer,
 date)
   RETURNS SETOF refcursor AS


First you can name your function parameters:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS

40.3.1. Declaring Function Parameters


If you do that then you can simplify the below. In other words in above 
the first parameter becomes:


v_oper varchar(1)

Saves creating a NULL variable and assigning to it as below.


$BODY$
declare

 v_oper varchar(1) := null;
 v_id integer := null;
 v_name varchar(50) := null;
 v_short_desc varchar(150) := null;
 v_descr varchar(500) := null;
 v_user_id integer := null;
 v_fecha date := null;
 v_resu integer := null;
 perspectives_cursor refcursor := null;
 goals_persps_cursor refcursor := null;
 null_cursor refcursor := null;


begin

 v_oper := $1;
 v_id := $2;
 v_name := $3;
 v_short_desc := $4;
 v_descr := $5;
 v_user_id := $6;
 v_fecha := $7;


 -- oper R = READ
 -- oper D = DELETE
 -- oper M = UPDATE
 -- oper I = INSERT






I can extract the data contained into cursor named perspectives_cursor
in the java application,  but the data caontained into cursor named
goals_persps_cursor not.


Well you are only returning one refcursor, so that is all you are going 
to get. To return more than one cursor, modified example from the docs:


http://www.postgresql.org/docs/9.4/interactive/plpgsql-cursors.html
See bottom of page for original example.

CREATE OR REPLACE FUNCTION public.myfunc(cur_a refcursor, cur_b 
refcursor, arg_1 varchar(1))

 RETURNS SETOF refcursor
 LANGUAGE plpgsql
AS $function$
BEGIN
RAISE NOTICE 'arg_1 is %', arg_1;
OPEN cur_a FOR SELECT * FROM tbl_a;
RETURN NEXT $1;
OPEN cur_b FOR SELECT * FROM tbl_b;
RETURN NEXT $2;
END;
$function$
;

test=> begin ;
BEGIN
test=> SELECT * FROM myfunc('a', 'b', '1');
NOTICE:  arg_1 is 1
 myfunc

 a
 b
(2 rows)

test=> fetch all from a;
 fld_1
---
 1
 2
 3
(3 rows)

test=> fetch all from b;
 fld_1
---
 4
 5
 6
(3 rows)


See this post from Kevin Grittner for an alternate method:

http://www.postgresql.org/message-id/cacjxusmy_zngfhbia+-qqur8poy87vu-l1e6hppwndu2skj...@mail.gmail.com

He also includes some notes on how to make this work with JDBC.



This function was tested by this


begin;
select * from dw_bsc.proc_perspectives('R', 1, null, null, null, null,
null);

fetch all from "";
end;

inside the query gui tool provided by pgAdmin III

The connection into the java application was changed to
con.setAutoCommit(false);

I think I do not forget nothing else

Some help will be appreciated very, very, very much !


-Mensaje original-
De: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Enviado el: jueves, 10 de diciembre de 2015 06:25 p.m.
Para: Corradini, Carlos; pgsql-j...@postgresql.org;
pgsql-general@postgresql.org
CC: bo...@ejurka.com
Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF
refcursor in JAVA

On 12/10/2015 05:38 AM, Corradini, Carlos wrote:

Dear Gurus :

  First let me say hello from Buenos Aires, Argentina.
I took this emails addresses from internet ( page www.postgresql.org
<http://www.postgresql.org> )

  Now I will try to explain which is my problem (
excuse my poor level of English, please ). I have a Java application
that must read a data provided by two ( 2 ) cursors returned by a
function stored in a database. I know to retrieve data if the function



have one ( 1 ) cursor, but with two I can't. I will very pleased if
any of you, in your free time of course, can explain me how, inside
the java program, after connecting via jdbc to the database, I extract



the data returned by the second cursor.


Can you provide the function code that is returning the cursors?



  Many thanks ( muchas gracias ) and I wait for yours
replies as soon as you can.




--
Adrian Klaver
adrian.kla...@aklaver.com




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA

[Adrian Klaver]

On 12/11/2015 07:10 AM, Corradini, Carlos wrote:

Mr. Adrian, first let me say many thanks for your replies, were very
helpful for me. But, I must to say this other .

I take a copy from the function from the gui tool of pgadmin III called
query sql, the original function name all the parameters, I do not know
why this gui tool change that.


I have no I idea either, seems silly to me.



You say that I am returning only one cursor, but I can say that I am
doing exactly what are you explaining in your example, I am doing return
next for each cursor to return.


From your previous post:

CREATE OR REPLACE FUNCTION dw_bsc.proc_perspectives(
character varying,
integer,
character varying,
character varying,
character varying,
integer,
date)
  RETURNS SETOF refcursor AS



You use two cursors internally, but are returning only one above. Unless 
you do something like Kevin Grittner and I showed there is no way for 
Postgres to know how to assign the internal cursors to return values 
that can be used externally.




I think I am doing something wrong in the java program but I can't
realize what is this .I will go now to see the examples in the URL's
you did put below.

Many thanks for all and excuse me for disturbing you and make you waste
your free time in this.


Well the point of the list is to answer questions and if I did not have 
the time or the interest I would not answer, so do not worry about it.




Again, many thanks

-Mensaje original-
De: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Enviado el: viernes, 11 de diciembre de 2015 11:37 a.m.
Para: Corradini, Carlos; pgsql-j...@postgresql.org;
pgsql-general@postgresql.org
CC: bo...@ejurka.com
Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF
refcursor in JAVA

On 12/11/2015 04:56 AM, Corradini, Carlos wrote:

Mr. Adrian, here i transcribe the code of the function


Notes in line.



-- Function: dw_bsc.proc_perspectives(character varying, integer,
character varying, character varying, character varying, integer,

date)


-- DROP FUNCTION dw_bsc.proc_perspectives(character varying, integer,
character varying, character varying, character varying, integer,

date);


CREATE OR REPLACE FUNCTION dw_bsc.proc_perspectives(
  character varying,
  integer,
  character varying,
  character varying,
  character varying,
  integer,
  date)
RETURNS SETOF refcursor AS


First you can name your function parameters:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-declarations.html
#PLPGSQL-DECLARATION-PARAMETERS

40.3.1. Declaring Function Parameters


If you do that then you can simplify the below. In other words in above
the first parameter becomes:

v_oper varchar(1)

Saves creating a NULL variable and assigning to it as below.


$BODY$
declare

  v_oper varchar(1) := null;
  v_id integer := null;
  v_name varchar(50) := null;
  v_short_desc varchar(150) := null;
  v_descr varchar(500) := null;
  v_user_id integer := null;
  v_fecha date := null;
  v_resu integer := null;
  perspectives_cursor refcursor := null;
  goals_persps_cursor refcursor := null;
  null_cursor refcursor := null;


begin

  v_oper := $1;
  v_id := $2;
  v_name := $3;
  v_short_desc := $4;
  v_descr := $5;
  v_user_id := $6;
  v_fecha := $7;


  -- oper R = READ
  -- oper D = DELETE
  -- oper M = UPDATE
  -- oper I = INSERT






I can extract the data contained into cursor named perspectives_cursor
in the java application,  but the data caontained into cursor named
goals_persps_cursor not.


Well you are only returning one refcursor, so that is all you are going
to get. To return more than one cursor, modified example from the docs:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-cursors.html
See bottom of page for original example.

CREATE OR REPLACE FUNCTION public.myfunc(cur_a refcursor, cur_b
refcursor, arg_1 varchar(1))
   RETURNS SETOF refcursor
   LANGUAGE plpgsql
AS $function$
BEGIN
  RAISE NOTICE 'arg_1 is %', arg_1;
  OPEN cur_a FOR SELECT * FROM tbl_a;
  RETURN NEXT $1;
  OPEN cur_b FOR SELECT * FROM tbl_b;
  RETURN NEXT $2;
END;
$function$
;

test=> begin ;
BEGIN
test=> SELECT * FROM myfunc('a', 'b', '1');
NOTICE:  arg_1 is 1
   myfunc

   a
   b
(2 rows)

test=> fetch all from a;
   fld_1
---
   1
   2
   3
(3 rows)

test=> fetch all from b;
   fld_1
---
   4
   5
   6
(3 rows)


See this post from Kevin Grittner for an alternate method:

http://www.postgresql.org/message-id/CACjxUsMy_zngFHBia+-QQuR8pOy87VU-L1
e6hppwndu2skj...@mail.gmail.com

He also includes some notes on how to make this work with JDBC.



This function was tested by this


begin;
select * from dw_bsc.proc_perspectives('R', 1, null, null, null, null,
null);

fetch all from "";
end;

inside the query gui tool provided by pgAdm

Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA

[Kevin Grittner]

On Fri, Dec 11, 2015 at 2:11 PM, Corradini, Carlos
 wrote:

> with your and Mr. Kevin explanations, the Java
> program have worked fine and have printed the data obtained from a two
> cursors inside a PostgreSQL Database Stored Function.
>
> Then, I can confirm that this version of DB ( 9.4 ) use the OUT
> parameter with refcursors and works fine. The JDBC interface provided by
> the Server Postgresql can read the data inserted into these two cursors
> via a callablestatement.registeroutparameter.

For the benefit of others who may later have a similar problem and
find this thread, it would be great if you could provide a little
self-contained example of a Java program which uses the technique
that you settled on.

Thanks!

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA

[Corradini, Carlos]

Mr. Adrian, first let me say many thanks for your replies, were very
helpful for me. But, I must to say this other .

I take a copy from the function from the gui tool of pgadmin III called
query sql, the original function name all the parameters, I do not know
why this gui tool change that.

You say that I am returning only one cursor, but I can say that I am
doing exactly what are you explaining in your example, I am doing return
next for each cursor to return.

I think I am doing something wrong in the java program but I can't
realize what is this .I will go now to see the examples in the URL's
you did put below.

Many thanks for all and excuse me for disturbing you and make you waste
your free time in this.

Again, many thanks

-Mensaje original-
De: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Enviado el: viernes, 11 de diciembre de 2015 11:37 a.m.
Para: Corradini, Carlos; pgsql-j...@postgresql.org;
pgsql-general@postgresql.org
CC: bo...@ejurka.com
Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF
refcursor in JAVA

On 12/11/2015 04:56 AM, Corradini, Carlos wrote:
> Mr. Adrian, here i transcribe the code of the function

Notes in line.

>
> -- Function: dw_bsc.proc_perspectives(character varying, integer,
> character varying, character varying, character varying, integer,
date)
>
> -- DROP FUNCTION dw_bsc.proc_perspectives(character varying, integer,
> character varying, character varying, character varying, integer,
date);
>
> CREATE OR REPLACE FUNCTION dw_bsc.proc_perspectives(
>  character varying,
>  integer,
>  character varying,
>  character varying,
>  character varying,
>  integer,
>  date)
>RETURNS SETOF refcursor AS

First you can name your function parameters:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-declarations.html
#PLPGSQL-DECLARATION-PARAMETERS

40.3.1. Declaring Function Parameters


If you do that then you can simplify the below. In other words in above 
the first parameter becomes:

v_oper varchar(1)

Saves creating a NULL variable and assigning to it as below.

> $BODY$
> declare
>
>  v_oper varchar(1) := null;
>  v_id integer := null;
>  v_name varchar(50) := null;
>  v_short_desc varchar(150) := null;
>  v_descr varchar(500) := null;
>  v_user_id integer := null;
>  v_fecha date := null;
>  v_resu integer := null;
>  perspectives_cursor refcursor := null;
>  goals_persps_cursor refcursor := null;
>  null_cursor refcursor := null;
>
>
> begin
>
>  v_oper := $1;
>  v_id := $2;
>  v_name := $3;
>  v_short_desc := $4;
>  v_descr := $5;
>  v_user_id := $6;
>  v_fecha := $7;
>
>
>  -- oper R = READ
>  -- oper D = DELETE
>  -- oper M = UPDATE
>  -- oper I = INSERT



>
> I can extract the data contained into cursor named perspectives_cursor
> in the java application,  but the data caontained into cursor named
> goals_persps_cursor not.

Well you are only returning one refcursor, so that is all you are going 
to get. To return more than one cursor, modified example from the docs:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-cursors.html
See bottom of page for original example.

CREATE OR REPLACE FUNCTION public.myfunc(cur_a refcursor, cur_b 
refcursor, arg_1 varchar(1))
  RETURNS SETOF refcursor
  LANGUAGE plpgsql
AS $function$
BEGIN
 RAISE NOTICE 'arg_1 is %', arg_1;
 OPEN cur_a FOR SELECT * FROM tbl_a;
 RETURN NEXT $1;
 OPEN cur_b FOR SELECT * FROM tbl_b;
 RETURN NEXT $2;
END;
$function$
;

test=> begin ;
BEGIN
test=> SELECT * FROM myfunc('a', 'b', '1');
NOTICE:  arg_1 is 1
  myfunc

  a
  b
(2 rows)

test=> fetch all from a;
  fld_1
---
  1
  2
  3
(3 rows)

test=> fetch all from b;
  fld_1
---
  4
  5
  6
(3 rows)


See this post from Kevin Grittner for an alternate method:

http://www.postgresql.org/message-id/CACjxUsMy_zngFHBia+-QQuR8pOy87VU-L1
e6hppwndu2skj...@mail.gmail.com

He also includes some notes on how to make this work with JDBC.

>
> This function was tested by this
>
>
> begin;
> select * from dw_bsc.proc_perspectives('R', 1, null, null, null, null,
> null);
>
> fetch all from "";
> end;
>
> inside the query gui tool provided by pgAdmin III
>
> The connection into the java application was changed to
> con.setAutoCommit(false);
>
> I think I do not forget nothing else
>
> Some help will be appreciated very, very, very much !
>
>
> -Mensaje original-
> De: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
> Enviado el: jueves, 10 de diciembre de 2015 06:25 p.m.
> Para: Corradini, Carlos; pgsql-j...@postgresql.org;
> pgsql-general@postgresql.org
> CC: bo...@ejurka

Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA

[Corradini, Carlos]

,
p.SHORT_DESC = v_short_desc,
p.USR_ID_UPD = v_user_id,
p.USR_DATE_UPD = v_fecha
where P.ID = v_id;
open perspectives_cursor FOR select 'ok.
update' as resultado1;
return next perspectives_cursor;
open goals_persps_cursor FOR select
'null' as resultado2;
return next goals_persps_cursor;
open null_cursor FOR select 'null' as
resultado3;
return next null_cursor;

end if;

when 'I' then

if (v_id = 0) then

RAISE NOTICE 'v_name : %', v_name;
RAISE NOTICE 'v_short_desc : %',
v_short_desc;
RAISE NOTICE 'v_descr : %', v_descr;
RAISE NOTICE 'v_user_id : %',v_user_id;
RAISE NOTICE 'v_fecha : %', v_fecha;
insert into dw_bsc.perspective
(name, short_desc, description,
usr_id_ins, usr_date_ins, usr_id_upd, usr_date_upd)
values
(v_name, v_short_desc, v_descr,
v_user_id, v_fecha, null, null);
open perspectives_cursor FOR select 'ok.
insert' as resultado1;
return next perspectives_cursor;
open goals_persps_cursor FOR select
'null' as resultado2;
return next goals_persps_cursor;
open null_cursor FOR select 'null' as
resultado3;
return next null_cursor;

else
RAISE NOTICE 'El id pasado al procedure
no es 0, imposible procesar INSERT !!!';
open perspectives_cursor FOR select
'NULL' as resultado1;
return next perspectives_cursor;
open goals_persps_cursor FOR select
'null' as resultado2;
return next goals_persps_cursor;
open null_cursor FOR select 'null' as
resultado3;
return next null_cursor;
end if;

end case;

end;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100
  ROWS 1000;
ALTER FUNCTION dw_bsc.proc_perspectives(character varying, integer,
character varying, character varying, character varying, integer, date)
  OWNER TO usr_dw_bsc_sys_adm;
GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying,
integer, character varying, character varying, character varying,
integer, date) TO public;
GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying,
integer, character varying, character varying, character varying,
integer, date) TO usr_dw_bsc_sys_adm;
GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying,
integer, character varying, character varying, character varying,
integer, date) TO ro_dw_bsc_sys_adm;

I can extract the data contained into cursor named perspectives_cursor
in the java application,  but the data caontained into cursor named
goals_persps_cursor not. 

This function was tested by this 


begin;
select * from dw_bsc.proc_perspectives('R', 1, null, null, null, null,
null);

fetch all from "";
end;

inside the query gui tool provided by pgAdmin III 

The connection into the java application was changed to
con.setAutoCommit(false);

I think I do not forget nothing else

Some help will be appreciated very, very, very much !


-Mensaje original-
De: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Enviado el: jueves, 10 de diciembre de 2015 06:25 p.m.
Para: Corradini, Carlos; pgsql-j...@postgresql.org;
pgsql-general@postgresql.org
CC: bo...@ejurka.com
Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF
refcursor in JAVA

On 12/10/2015 05:38 AM, Corradini, Carlos wrote:
> Dear Gurus :
>
>  First let me say hello from Buenos Aires, Argentina. 
> I took this emails addresses from internet ( page www.postgresql.org 
> <http://www.postgresql.org> )
>
>  Now I will try to explain which is my problem ( 
> excuse my poor level of English, please ). I have a Java application 
> that must read a data provided by two ( 2 ) cursors returned by a 
> function stored in a database. I know to retrieve data if the function

> have one ( 1 ) cursor, but with two I can't. I will very pleased if 
> any of you, in 

Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA

[Kevin Grittner]

On Thu, Dec 10, 2015 at 7:38 AM, Corradini, Carlos
 wrote:

> I have a Java application that must read a
> data provided by two ( 2 ) cursors returned by a function stored in a
> database. I know to retrieve data if the function have one ( 1 ) cursor, but
> with two I can’t. I will very pleased if any of you, in your free time of
> course, can explain me how, inside the java program, after connecting via
> jdbc to the database, I extract the data returned by the second cursor.

I will illustrate the principle of how a single function call can
return two cursors with psql, but you should be able to code up an
equivalent in Java, if you decide this is a good approach:

test=# create table feline (c text);
CREATE TABLE
test=# create table canine (c text);
CREATE TABLE
test=# insert into feline values ('lion'), ('tiger'), ('ocelot');
INSERT 0 3
test=# insert into canine values ('wolf'), ('jackal');
INSERT 0 2
test=# create or replace function twocursors(out f refcursor, out c
refcursor)
test-#   language plpgsql
test-# as $$
test$# declare
test$#   curs1 CURSOR FOR SELECT * FROM feline;
test$#   curs2 CURSOR FOR SELECT * FROM canine;
test$# begin
test$#   curs1 := 'feline';
test$#   open curs1;
test$#   f := curs1;
test$#   curs2 := 'canine';
test$#   open curs2;
test$#   c := curs2;
test$# end;
test$# $$;
CREATE FUNCTION
test=# begin; select twocursors();
BEGIN
   twocursors
-
 (feline,canine)
(1 row)

test=# fetch feline;
  c
--
 lion
(1 row)

test=# fetch canine;
  c
--
 wolf
(1 row)

test=# fetch feline;
   c
---
 tiger
(1 row)

test=# fetch canine;
   c

 jackal
(1 row)

test=# fetch feline;
   c

 ocelot
(1 row)

I don't think you can get two ResultSet objects as cursors from a
single function call through JDBC in PostgreSQL, so you either need
to make two separate calls to get the two result sets (with the
usual dance to get those as cursors) and pull from the two
ResultSet objects as required, or use server-side fetches as
illustrated above.  I would expect it to be much faster to use the
two result sets, since each fetch above would involve a round trip
from the client to PostgreSQL.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA

[Adrian Klaver]

On 12/10/2015 05:38 AM, Corradini, Carlos wrote:

Dear Gurus :

 First let me say hello from Buenos Aires, Argentina. I
took this emails addresses from internet ( page www.postgresql.org
 )

 Now I will try to explain which is my problem ( excuse
my poor level of English, please ). I have a Java application that must
read a data provided by two ( 2 ) cursors returned by a function stored
in a database. I know to retrieve data if the function have one ( 1 )
cursor, but with two I can’t. I will very pleased if any of you, in your
free time of course, can explain me how, inside the java program, after
connecting via jdbc to the database, I extract the data returned by the
second cursor.


Can you provide the function code that is returning the cursors?



 Many thanks ( muchas gracias ) and I wait for yours
replies as soon as you can.




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC and inet type

[Tim Smith]

Great, thanks!

On 4 December 2015 at 12:17, Bill Moran  wrote:
> On Fri, 4 Dec 2015 09:41:24 +
> Tim Smith  wrote:
>
>> When I use "preparedStatement.setString(5,ip);" to send values to a
>> stored function, it obviously gets sent to postgres as "character
>> varying".
>>
>> Postgres obviously complains loudly and says " Hint: No function
>> matches the given name and argument types. You might need to add
>> explicit type casts.".
>>
>> What is the appropriate workaround ?
>
> You can define param 5 as varchar in your query, as Rob suggests:
>
> CREATE FUNCTION some_function(int, int, int, int, int, varchar) ...
>
> Then cast the 5th parameter to INET within your function.
>
> You can also cast the value in your SQL.
>
> sql = "SELECT some_function($, $, $, $, $::INET)"; ...
>
> You could also create an Inet class in Java and implement the
> SQLData interface, then use setObject() instead of setString().
> It doesn't appear as if anyone has done this yet, but it would
> be nice if it were incluced in the JDBC driver.
>
> The first answer is probably best for stored procedures, as it
> simplifies things down the road. The second solution is more
> universal, as it works for non-function-calling SQL as well.
> The third solution is probably _really_ the correct one, from
> a pedantic standpoint, but it's a bit more work to implement.
>
> --
> Bill Moran


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC and inet type

[rob stone]

On Fri, 2015-12-04 at 09:41 +, Tim Smith wrote:
> Hi,
> 
> When I use "preparedStatement.setString(5,ip);" to send values to a
> stored function, it obviously gets sent to postgres as "character
> varying".
> 
> Postgres obviously complains loudly and says " Hint: No function
> matches the given name and argument types. You might need to add
> explicit type casts.".
> 
> What is the appropriate workaround ?
> 
> Thanks!
> 
> Tim
> 
> 

"preparedStatement.setString(5,ip);" will set parameter number five to
the string held in the variable.
That's what setString does. It expects a string to be passed.
Your function's fifth IN argument needs to be defined as a "string".
Within the function you will have to cast it to inet.

HTH,

Rob


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC and inet type

[Bill Moran]

On Fri, 4 Dec 2015 09:41:24 +
Tim Smith  wrote:

> When I use "preparedStatement.setString(5,ip);" to send values to a
> stored function, it obviously gets sent to postgres as "character
> varying".
> 
> Postgres obviously complains loudly and says " Hint: No function
> matches the given name and argument types. You might need to add
> explicit type casts.".
> 
> What is the appropriate workaround ?

You can define param 5 as varchar in your query, as Rob suggests:

CREATE FUNCTION some_function(int, int, int, int, int, varchar) ...

Then cast the 5th parameter to INET within your function.

You can also cast the value in your SQL.

sql = "SELECT some_function($, $, $, $, $::INET)"; ...

You could also create an Inet class in Java and implement the
SQLData interface, then use setObject() instead of setString().
It doesn't appear as if anyone has done this yet, but it would
be nice if it were incluced in the JDBC driver.

The first answer is probably best for stored procedures, as it
simplifies things down the road. The second solution is more
universal, as it works for non-function-calling SQL as well.
The third solution is probably _really_ the correct one, from
a pedantic standpoint, but it's a bit more work to implement.

-- 
Bill Moran


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] Upgrading from PG 8.3.3 to 9.3.4 - FATAL: invalid value for parameter TimeZone: PST

[Tom Lane]

David Wall d.w...@computer.org writes:
 The exception occurs when JDBC tries to connect to PG:

 2014-05-31 22:14:34,351 ERROR (eSignForms) SQLException: 
 ConnectionPool.makeConnection(esf) to URL: 
 jdbc:postgresql://localhost.localdomain:25432/zingr:
 2014-05-31 22:14:34,352 ERROR (eSignForms)   Message:   FATAL: invalid 
 value for parameter TimeZone: PST
 org.postgresql.util.PSQLException: FATAL: invalid value for parameter 
 TimeZone: PST

Hm.  libpq will try to set TimeZone at connection time if it sees a value
for the environment variable PGTZ; so, if you were using libpq, this
would be explainable by different environment settings in different cases.
There is probably some comparable behavior in the JDBC driver, but I don't
know exactly what.

One hole in this type of theory is that it doesn't explain a behavioral
difference between 8.3.x and 9.3.x; unless maybe the JDBC driver's
behavior changed in this regard since then.  libpq has done the PGTZ thing
for a very long time.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] Cannot insert to 'path' field using EclipseLink

[Steven Schlansker]

 On Mar 12, 2014, at 10:12 AM, Daryl Foster daryl.fos...@oncenter.com wrote:
 
 java.lang.ClassCastException: org.postgresql.geometric.PGpath cannot be cast 
 to org.postgresql.geometric.PGpath
 

That's a sure sign of ClassLoader confusion. Make sure there is only one copy 
of the driver jar in your application or the JBoss container, but never both. 

 
 On Wed, Mar 12, 2014 at 7:37 AM, Daryl Foster daryl.fos...@oncenter.com 
 wrote:
 I have a java app running in JBoss that uses EclipseLink to persist to a 
 Postgres database. I've added a field with a 'path' datatype to one of the 
 tables but I keep getting the following exception when I try to insert data:
 
 org.postgresql.util.PSQLException: Can't infer the SQL type to use for an 
 instance of org.postgresql.geometric.PGpath. Use setObject() with an 
 explicit Types value to specify the type to use.
 
 Here's the table definition:
 
 CREATE TABLE schema.table_name
 (
 item_iduuid NOT NULL,
 item_path  path NOT NULL
 )
 
 The java entity is representing the item_path field as a ListPoint object, 
 and I'm using a converter to map from the ListPoint object to a PGpath 
 object:
 
 import org.eclipse.persistence.mappings.DatabaseMapping;
 import org.eclipse.persistence.mappings.converters.Converter;
 import org.eclipse.persistence.sessions.Session;
 import org.postgresql.geometric.PGpath;
 import java.awt.Point;
 import java.util.ArrayList;
 import java.util.List;
 import static java.sql.Types.OTHER;
 
 public class PgPathConverter implements Converter
 {
 @Override
 public boolean isMutable ()
 {
 return false;
 }
 
 @Override
 public ListPoint convertDataValueToObjectValue (Object value, 
 Session session)
 {
 // Code that converts PGpath to ListPoint
 }
 
 @Override
 public PGpath convertObjectValueToDataValue (Object value, Session 
 session)
 {
 // Code that converts ListPoint to PGpath
 }
 
 @Override
 public void initialize (DatabaseMapping mapping, Session session)
 {
 mapping.getField ().setSqlType (OTHER);
 }
 }
 
 The entity class is defined as follows:
 
 @Entity
 @Table (
 name   = table_name,
 schema = schema
 )
 @Converter (
 name   = path,
 converterClass = PgPathConverter.class
 )
 public class TableName
 {
 public TableName () {}
 private static final long serialVersionUID = 1L;
 
 @Column (name = item_path)
 @Convert (path)
 private ListPoint m_ItemPath;
 
 @Id
 @Column (
 name = item_id,
 unique   = true,
 nullable = false
 )
 private UUIDm_ItemId;
 
 public UUID getItemId ()
 {
 return m_ItemId;
 }
 
 public ListPoint getItemPath ()
 {
 return m_InkPath;
 }
 
 public void setItemId (UUID itemId)
 {
 m_ItemId = itemId;
 }
 
 public void setInkPath (ListPoint itemPath)
 {
  m_ItemPath = itemPath;
 }
 }
 
 Finally, here's the exception I get when I call `EntityManager.persist 
 (entity)`:
 
 18:10:33,789 ERROR [org.jboss.as.ejb3] (http-/0.0.0.0:8080-1) 
 javax.ejb.EJBTransactionRolledbackException: Exception [EclipseLink-4002] 
 (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): 
 org.eclipse.persistence.exceptions.DatabaseException
 Internal Exception: org.postgresql.util.PSQLException: Can't infer the 
 SQL type to use for an instance of org.postgresql.geometric.PGpath. Use 
 setObject() with an explicit Types value to specify the type to use.
 Error Code: 0
 Call: INSERT INTO schema.table_name (item_id, item_path) VALUES (?, ?)
  bind = [2 parameters bound]
 18:10:33,789 ERROR [org.jboss.as.ejb3.invocation] (http-/0.0.0.0:8080-1) 
 JBAS014134: EJB Invocation failed on component TableNameRepository for 
 method public void com.mycompany.myproject.data.Repository.flush() throws 
 javax.persistence.TransactionRequiredException,javax.persistence.PersistenceException:
  javax.ejb.EJBTransactionRolledbackException: Exception [EclipseLink-4002] 
 (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): 
 org.eclipse.persistence.exceptions.DatabaseException
 Internal Exception: org.postgresql.util.PSQLException: Can't infer the 
 SQL type to use for an instance of org.postgresql.geometric.PGpath. Use 
 setObject() with an explicit Types value to specify the type to use.
 Error Code: 0
 Call: INSERT INTO schema.table_name (item_id, item_path VALUES (?, ?)
  bind = [2 parameters bound]
 at 
 org.jboss.as.ejb3.tx.CMTTxInterceptor.handleInCallerTx(CMTTxInterceptor.java:138)
  

Re: [GENERAL] JDBC performance issue

[David Johnston]

CS DBA wrote
 Hi All;
 
 We have a client running Pentaho to migrate data.
 
 They recently upgraded Pentaho which upgraded their JDBC driver from 8.4 
 (postgresql-8.4-703.jdbc4.jar) to 9.1 (postgresql-8.4-703.jdbc4.jar).  
 They have a test set which updates 1000 rows, with the old driver it 
 takes about 30 seconds, but the new driver takes 3x longer.
 
 This is on PostgreSQL 9.1
 
 Thoughts?

Good News!  This seems to be a re-producible problem.

Bad News!  Either you/your client will need to discover the bottle-neck or
you will need to provide the test set/routine to the public so someone else
can try.

Also, maybe run the test set against and with 9.2 and/or 9.3 to see if the
problem still exists there...

Note, I presume you mean updates 1000s of rows (i.e., considerably more
than 1,000) - 30s to update exactly 1000 rows doesn't seem that impressive
but then again no clue what is involved so maybe that is good.  Note that
means it is unclear if the UPDATE phase or some required SELECT is
mis-behaving which is a critical detail.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/JDBC-performance-issue-tp5790710p5790714.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC performance issue

[Dave Cramer]

You said they upgraded the driver but the versions you stated are the same
???

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Wed, Feb 5, 2014 at 12:23 PM, CS DBA cs_...@consistentstate.com wrote:

 Hi All;

 We have a client running Pentaho to migrate data.

 They recently upgraded Pentaho which upgraded their JDBC driver from 8.4
 (postgresql-8.4-703.jdbc4.jar) to 9.1 (postgresql-8.4-703.jdbc4.jar).
  They have a test set which updates 1000 rows, with the old driver it takes
 about 30 seconds, but the new driver takes 3x longer.

 This is on PostgreSQL 9.1

 Thoughts?

 Thanks in advance...





 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC driver for Postgres 9.3

[Steven Schlansker]

On Sep 26, 2013, at 6:35 AM, Kohler  Manuel manuel.koh...@bsse.ethz.ch 
wrote:

 Hi,
 we are developing a Java based software with Postgres as a DB.
 Could someone tell me if there will be a JDBC driver for 9.3 out soon or
 is it safe and recommended to use the latest JDBC driver available?
 Currently we are using:
 PostgreSQL 9.2-1000 JDBC4 (build 1000)
 

I believe the 9.3 JDBC driver is coming soon, but the latest driver works just 
fine (we use it in production)



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC prepared statement is not treated as prepared statement

[Albe Laurenz]

高健 wrote:
 I change my Java program by adding the following:
 
 org.postgresql.PGStatement pgt = (org.postgresql.PGStatement)pst;
 pgt.setPrepareThreshold(1);
 
 I can see an entry is in pg_prepared_statements now.

Good.

 But the hyperlink's documentation made me a little confused. I also wonder 
 why the threshold option is
 designed .

 The document said:
 
 -
 
 The PostgreSQL™ server allows clients to compile sql statements that are 
 expected to be reused to
 avoid the overhead of parsing and planning the statement for every execution. 
 This functionality is
 available at the SQL level via PREPARE and EXECUTE beginning with server 
 version 7.3
 
  …
 
  An internal counter keeps track of how many times the statement has been 
 executed and when it reaches
 the threshold it will start to use server side prepared statements.
 
  …
 
 ---
 
 What does  clients to compile sql statements  mean?
 
 I think that maybe the document  just want to say:
 
 --
 ---
 
 Before  PG import support for prepared statement,
 
 PG server must parse and plan statement every time when the client send a 
 request.
 
 Even when the same statement will be executed many times.
 
 
 
 After PG import support for prepared statement,
 
 When using those statement which is expected reused, by using prepared 
 statement mechanism,
 
 PG server can avoid overhead of parsing and planning again and again.
 
 
 
 But in order to use prepared statement, The client also must do something:
 
 When using psql,
 
 we need to use Prepare command
 
 
 
 When using java,
 
 we use java.sql.preparedstatement,
 
 but it is not engouth: we also need to use  org.postgresql.PGStatement 's  
 setthreshold method to let
 PG server know.
 
 
 
 The client must do something to let  PG server realize that  client want PG 
 server to use prepared
 statement.
 
 That is why the docmument say clients to compile sql statements.

I think that it is helpful to explain what the JDBC driver does internally.

If you do not set the threshold or the threshold is not yet exceeded,
the driver will execute the statement as a simple statement (which
corresponds to libpq's PQexec).

Once the threshold is exceeded, the next execution will prepare
the statement (corresponding to libpq's PQprepare) and then execute
it (like libpq's PQexecPrepared).

Subsequent executions will only execute the named prepared statement.

I think that compile in the text you quote stands for prepare.

 And for the threshold,
 
 If the threshold has not reached, PG server will consider the sql statement a 
 common one, and will
 parse and plan for it every time.
 
 Only when the threshold is reached, PG server will realize that client need 
 it to hold the statement
 as prepared ,then parsed it and hold the plan.
 
 -
 
 
 
 Is my understanding right?

Server prepared statements are kept in the private memory of the
PostgreSQL backend process.  If you need a statement only once or
twice, it would be wasteful to keep it around.
The idea is that it is worth the effort only if the statement is executed
more than a couple of times.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC prepared statement is not treated as prepared statement

[高健]

Hello :



Server prepared statements are kept in the private memory of the
PostgreSQL backend process.  If you need a statement only once or
twice, it would be wasteful to keep it around.
The idea is that it is worth the effort only if the statement is executed
more than a couple of times.



Thank you . I think it is an exciting point for PG.

This make it clever to choice those always executed sql.



Thanks!

2013/6/18 Albe Laurenz laurenz.a...@wien.gv.at

 高健 wrote:
  I change my Java program by adding the following:
 
  org.postgresql.PGStatement pgt = (org.postgresql.PGStatement)pst;
  pgt.setPrepareThreshold(1);
 
  I can see an entry is in pg_prepared_statements now.

 Good.

  But the hyperlink's documentation made me a little confused. I also
 wonder why the threshold option is
  designed .
 
  The document said:
 
  -
 
  The PostgreSQL™ server allows clients to compile sql statements that are
 expected to be reused to
  avoid the overhead of parsing and planning the statement for every
 execution. This functionality is
  available at the SQL level via PREPARE and EXECUTE beginning with server
 version 7.3
 
   …
 
   An internal counter keeps track of how many times the statement has
 been executed and when it reaches
  the threshold it will start to use server side prepared statements.
 
   …
 
  ---
 
  What does  clients to compile sql statements  mean?
 
  I think that maybe the document  just want to say:
 
 
 --
  ---
 
  Before  PG import support for prepared statement,
 
  PG server must parse and plan statement every time when the client send
 a request.
 
  Even when the same statement will be executed many times.
 
 
 
  After PG import support for prepared statement,
 
  When using those statement which is expected reused, by using prepared
 statement mechanism,
 
  PG server can avoid overhead of parsing and planning again and again.
 
 
 
  But in order to use prepared statement, The client also must do
 something:
 
  When using psql,
 
  we need to use Prepare command
 
 
 
  When using java,
 
  we use java.sql.preparedstatement,
 
  but it is not engouth: we also need to use  org.postgresql.PGStatement
 's  setthreshold method to let
  PG server know.
 
 
 
  The client must do something to let  PG server realize that  client want
 PG server to use prepared
  statement.
 
  That is why the docmument say clients to compile sql statements.

 I think that it is helpful to explain what the JDBC driver does internally.

 If you do not set the threshold or the threshold is not yet exceeded,
 the driver will execute the statement as a simple statement (which
 corresponds to libpq's PQexec).

 Once the threshold is exceeded, the next execution will prepare
 the statement (corresponding to libpq's PQprepare) and then execute
 it (like libpq's PQexecPrepared).

 Subsequent executions will only execute the named prepared statement.

 I think that compile in the text you quote stands for prepare.

  And for the threshold,
 
  If the threshold has not reached, PG server will consider the sql
 statement a common one, and will
  parse and plan for it every time.
 
  Only when the threshold is reached, PG server will realize that client
 need it to hold the statement
  as prepared ,then parsed it and hold the plan.
 
 
 -
 
 
 
  Is my understanding right?

 Server prepared statements are kept in the private memory of the
 PostgreSQL backend process.  If you need a statement only once or
 twice, it would be wasteful to keep it around.
 The idea is that it is worth the effort only if the statement is executed
 more than a couple of times.

 Yours,
 Laurenz Albe

Re: [GENERAL] JDBC prepared statement is not treated as prepared statement

[Albe Laurenz]

高健  wrote:
 I  have one question about prepared statement.
 I use Java via JDBC, then send prepared statement to execute.
 I thought that the pg_prepared_statments  view will have one record after my 
 execution.
 But I can't find.
 
 Is the JDBC's prepared statement  differ from  SQL execute by prepare command 
 ?
 http://www.postgresql.org/docs/current/static/sql-prepare.html
 
 My simple java program is the following:
 
 import java.sql.*;
 
 public class Test01 {
 public static void main(String argsv[]){
 try
  {
Class.forName(org.postgresql.Driver).newInstance();
String url = jdbc:postgresql://localhost:5432/postgres ;
Connection con = 
 DriverManager.getConnection(url,postgres,postgres );
///Phase 1:-Select data from 
 table---
System.out.println(Phase 1start);
String strsql =  select * from customers where cust_id = ?;
PreparedStatement pst=con.prepareStatement(strsql);
pst.setInt(1,3); //find the customer with cust_id of 3.
ResultSet rs = pst.executeQuery();
while (rs.next())
 {
System.out.print(cust_id:+rs.getInt( cust_id));
System.out.println(...cust_name:+rs.getString( cust_name 
 ));
}
 
System.out.println(Phase 1end\n);
 
 
 
///Phase 2:-Use connection again,to select data from 
 data dictionary---
 
System.out.println(Phase 2start);
strsql = select * from pg_prepared_statements;
pst=con.prepareStatement(strsql);
rs = pst.executeQuery();
while (rs.next())
{
   System.out.println(statement:+rs.getString( statement));
}
System.out.println(Phase 2end\n);
 
 
 
///Phase 3:-Use connection again,to select data from 
 table-
 --
System.out.println(Phase 3start);
strsql = select * from customers;
pst=con.prepareStatement(strsql);
rs = pst.executeQuery();
while (rs.next())
{
   System.out.print(cust_id:+rs.getInt( cust_id));
   System.out.println(...cust_name:+rs.getString( cust_name ));
   }
   System.out.println(Phase 3end\n);
   rs.close();
   pst.close();
   con.close();
}
 catch (Exception ee)
 {
System.out.print(ee.getMessage());
}
 }
 }
 
 
 
 The result of it's execution is:
 
 Phase 1start
 
 cust_id:3...cust_name:Taylor
 
 Phase 1end
 
 
 
 Phase 2start
 
 Phase 2end
 
 
 
 Phase 3start
 
 cust_id:1...cust_name:Smith
 
 cust_id:2...cust_name:Brown
 
 cust_id:3...cust_name:Taylor
 
 Phase 3end
 
 
 
 That is to say: my prepared statement is not cached by PG?
 
 Then how to write a  java program to made it's prepared statement realized by 
 PG to treat it as a
 prepared statement?
 
 Thank you.

See http://jdbc.postgresql.org/documentation/head/server-prepare.html

Set the prepare threshold of a PreparedStatement and use the statement
at least as many times.  Then you should see an entry in
pg_prepared_statements.

In your example, no PreparedStatement is used more than once.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC prepared statement is not treated as prepared statement

[高健]

Hello:



Thanks to Laurenz. Your information is very helpful for me.



I change my Java program by adding the following:

org.postgresql.PGStatement pgt = (org.postgresql.PGStatement)pst;
pgt.setPrepareThreshold(1);

I can see an entry is in pg_prepared_statements now.



But the hyperlink's documentation made me a little confused. I also wonder
why the threshold option is designed .



The document said:

-

The PostgreSQL™ server allows clients to compile sql statements that are
expected to be reused to avoid the overhead of parsing and planning the
statement for every execution. This functionality is available at the SQL
level via PREPARE and EXECUTE beginning with server version 7.3

 …

 An internal counter keeps track of how many times the statement has
been executed and when it reaches the threshold it will start to use
server side prepared statements.

 …

---



What does  clients to compile sql statements  mean?

I think that maybe the document  just want to say:

-

Before  PG import support for prepared statement,

PG server must parse and plan statement every time when the client send a
request.

Even when the same statement will be executed many times.



After PG import support for prepared statement,

When using those statement which is expected reused, by using prepared
statement mechanism,

PG server can avoid overhead of parsing and planning again and again.



But in order to use prepared statement, The client also must do something:

When using psql,

we need to use Prepare command



When using java,

we use java.sql.preparedstatement,

but it is not engouth: we also need to use  org.postgresql.PGStatement 's
setthreshold method to let PG server know.



The client must do something to let  PG server realize that  client want PG
server to use prepared statement.

That is why the docmument say clients to compile sql statements.



And for the threshold,

If the threshold has not reached, PG server will consider the sql statement
a common one, and will parse and plan for it every time.

Only when the threshold is reached, PG server will realize that client need
it to hold the statement as prepared ,then parsed it and hold the plan.

-



Is my understanding right?



Thanks

2013/6/17 Albe Laurenz laurenz.a...@wien.gv.at

 高健  wrote:
  I  have one question about prepared statement.
  I use Java via JDBC, then send prepared statement to execute.
  I thought that the pg_prepared_statments  view will have one record
 after my execution.
  But I can't find.
 
  Is the JDBC's prepared statement  differ from  SQL execute by prepare
 command ?
  http://www.postgresql.org/docs/current/static/sql-prepare.html
 
  My simple java program is the following:
 
  import java.sql.*;
 
  public class Test01 {
  public static void main(String argsv[]){
  try
   {
 Class.forName(org.postgresql.Driver).newInstance();
 String url = jdbc:postgresql://localhost:5432/postgres ;
 Connection con =
 DriverManager.getConnection(url,postgres,postgres );
 ///Phase 1:-Select data from
 table---
 System.out.println(Phase 1start);
 String strsql =  select * from customers where cust_id = ?;
 PreparedStatement pst=con.prepareStatement(strsql);
 pst.setInt(1,3); //find the customer with cust_id of 3.
 ResultSet rs = pst.executeQuery();
 while (rs.next())
  {
 System.out.print(cust_id:+rs.getInt( cust_id));
 System.out.println(...cust_name:+rs.getString(
 cust_name ));
 }
 
 System.out.println(Phase 1end\n);
 
 
 
 ///Phase 2:-Use connection again,to select data
 from data dictionary---
  
 System.out.println(Phase 2start);
 strsql = select * from pg_prepared_statements;
 pst=con.prepareStatement(strsql);
 rs = pst.executeQuery();
 while (rs.next())
 {
System.out.println(statement:+rs.getString(
 statement));
 }
 System.out.println(Phase 2end\n);
 
 
 
 ///Phase 3:-Use connection again,to select data
 from table-
  --
 System.out.println(Phase 3start);
 strsql = select * from customers;
 pst=con.prepareStatement(strsql);
 rs = pst.executeQuery();
 while (rs.next())
 {
  

Re: [GENERAL] JDBC driver versions

[John R Pierce]

On 4/16/2013 1:13 PM, Ramsey Gurley wrote:

I'm migrating from postgres 8.3 to 9.2. Included in my application is an old 
jar for the JDBC driver for the 8.3 database. Do I need a separate build with 
an up to date JDBC jar for 9.2 while I am testing out a new database or is it 
safe to use the latest JDBC jar with a very old version of postgres?


latest version should work with older database servers.otherway 
around is less trustworthy (although it /probably/ will work for many 
purposes).






--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC driver versions

[Adrian Klaver]

On 04/16/2013 01:13 PM, Ramsey Gurley wrote:

Hi all,

I'm migrating from postgres 8.3 to 9.2. Included in my application is an old 
jar for the JDBC driver for the 8.3 database. Do I need a separate build with 
an up to date JDBC jar for 9.2 while I am testing out a new database or is it 
safe to use the latest JDBC jar with a very old version of postgres?


From here:
http://jdbc.postgresql.org/download.html#current


Current Version

This is the current version of the driver. Unless you have unusual 
requirements (running old applications or JVMs), this is the driver you 
should be using. It supports Postgresql 7.2 or newer and requires a 1.5 
or newer JVM. It contains support for SSL and the javax.sql package. It 
comes in two flavours, JDBC3 and JDBC4. If you are using the 1.6 or 1.7 
JVM, then you should use the JDBC4 version.





Thanks,

Ramsey




--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC driver versions

[Ramsey Gurley]

Okay, I saw that, but I just wanted to double check. It seemed odd that there 
were builds going all the way back to 8.2 if the 9.2 version should work for 
everything.

Thanks for the reassurance :-)

Ramsey

On Apr 16, 2013, at 1:36 PM, Adrian Klaver wrote:

 On 04/16/2013 01:13 PM, Ramsey Gurley wrote:
 Hi all,
 
 I'm migrating from postgres 8.3 to 9.2. Included in my application is an old 
 jar for the JDBC driver for the 8.3 database. Do I need a separate build 
 with an up to date JDBC jar for 9.2 while I am testing out a new database or 
 is it safe to use the latest JDBC jar with a very old version of postgres?
 
 From here:
 http://jdbc.postgresql.org/download.html#current
 
 
 Current Version
 
 This is the current version of the driver. Unless you have unusual 
 requirements (running old applications or JVMs), this is the driver you 
 should be using. It supports Postgresql 7.2 or newer and requires a 1.5 or 
 newer JVM. It contains support for SSL and the javax.sql package. It comes in 
 two flavours, JDBC3 and JDBC4. If you are using the 1.6 or 1.7 JVM, then you 
 should use the JDBC4 version.
 
 
 
 Thanks,
 
 Ramsey
 
 
 
 -- 
 Adrian Klaver
 adrian.kla...@gmail.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC driver versions

[Adrian Klaver]

On 04/16/2013 01:54 PM, Ramsey Gurley wrote:

Okay, I saw that, but I just wanted to double check. It seemed odd that there 
were builds going all the way back to 8.2 if the 9.2 version should work for 
everything.


It works for Postgres going back to 7.2, but not for older JDK and JDBC 
versions. So per the docs they are kept around for those people that 
need to use them with older software.




Thanks for the reassurance :-)

Ramsey



--
Adrian Klaver
adrian.kla...@gmail.com






--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC not returning update count from updateable view

[Dave Cramer]

As far as I remember this is an artifact of using rules to update a table.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Mon, Feb 25, 2013 at 2:52 PM, Russell Keane russell.ke...@inps.co.ukwrote:

  Hi,

 ** **

 We have a table which is inserted to and update via a view (using rules /
 functions).

 ** **

 We are trying to update this from JDBC but the view update command (on the
 java side) doesn’t return the count of rows updated. I assume this is
 because the postgres update function actually returns a tuple rather than a
 single count.

 ** **

 Any ideas?

 ** **

 A simplified version of the java bit:

 ** **

 JdbcTemplate is: org.springframework.jdbc.core.JdbcTemplate;

 ** **

 *public* *final* *int* updateTest(*final* String updateSQL, 
 *final*Object[] args) {
 

 JdbcTemplate template = createJdbcTemplate();

 *return* template.update(updateSQL, args);

 }

 ** **

 And the postgres object creation (again simplified):

 ** **

 --PG START

 ** **

 drop table if exists msg_table cascade;

 drop sequence if exists msg_seq;

 drop sequence if exists msg_aud_seq;

 create sequence msg_seq;

 create sequence msg_aud_seq;

 ** **

 CREATE TABLE msg_table

 (

 aud_seq int default nextval('msg_aud_seq'),

 status int default 1,

 id int default nextval('msg_seq'),

 val int

 );

 ** **

 create or replace view msg as 

 select

 aud_seq,

 id,

 status,

 val

 from msg_table;

 ** **

 -- audit the original record

 CREATE OR REPLACE FUNCTION audit_original_record(msg) RETURNS void AS $$**
 **

 BEGIN

 UPDATE msg_table

 SET

 status = 2

 WHERE

 aud_seq = $1.aud_seq;

 END;

 $$ LANGUAGE plpgsql;

 ** **

 ** **

 -- insert function

 CREATE OR REPLACE FUNCTION process_insert(msg) RETURNS integer AS $body$**
 **

 declare new_id integer;

 

 BEGIN

 

 INSERT INTO msg_table 

 (

 val

 )

 SELECT

 $1.val

 

 RETURNING id INTO new_id;

 

 return new_id;

 END;

 $body$ LANGUAGE plpgsql;

 ** **

 -- update function

 CREATE OR REPLACE FUNCTION process_update(msg, msg) RETURNS void AS $body$
 

 BEGIN

 INSERT INTO msg_table 

 (

 id,

 val

 )

 SELECT

 $1.id,

 $1.val;

 ** **

 EXECUTE audit_original_record($2);

 ** **

 END;

 $body$ LANGUAGE plpgsql;

 ** **

 -- insert to msg

 create or replace rule msg__rule_ins as on insert to msg

 do instead

 SELECT process_insert(NEW);

 ** **

 -- update to msg

 create or replace rule msg__rule_upd as on update to msg

 do instead

 SELECT

 COUNT(process_update(NEW, OLD))

 WHERE

 NEW.status = 1;

 ** **

 ** **

 alter sequence msg_seq restart 1;

 alter sequence msg_aud_seq restart 1;

 ** **

 delete from msg_table;

 ** **

 insert into msg

 (val)

 values

 (1),

 (2),

 (66);

 ** **

 select * from msg;

 ** **

 update msg

 set val = 5

 where id = 1;

 ** **

 select * from msg;

 ** **

 --PG END

 ** **

 ** **

 Thanks for any help you can give me.

 ** **

 Regards,

 ** **

 *Russell Keane***

 *INPS*

 Follow us https://twitter.com/INPSnews on twitter | visit www.inps.co.uk
 

 ** **

 --
 Registered name: In Practice Systems Ltd.
 Registered address: The Bread Factory, 1a Broughton Street, London, SW8 3QJ
 Registered Number: 1788577
 Registered in England
 Visit our Internet Web site at www.inps.co.uk
 The information in this internet email is confidential and is intended
 solely for the addressee. Access, copying or re-use of information in it by
 anyone else is not authorised. Any views or opinions presented are solely
 those of the author and do not 

Re: [GENERAL] JDBC not returning update count from updateable view

[Russell Keane]

But this works just fine when using libpq via c++.


From: davecra...@gmail.com [mailto:davecra...@gmail.com] On Behalf Of Dave 
Cramer
Sent: 26 February 2013 12:23
To: Russell Keane
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] JDBC not returning update count from updateable view

As far as I remember this is an artifact of using rules to update a table.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Mon, Feb 25, 2013 at 2:52 PM, Russell Keane 
russell.ke...@inps.co.ukmailto:russell.ke...@inps.co.uk wrote:
Hi,

We have a table which is inserted to and update via a view (using rules / 
functions).

We are trying to update this from JDBC but the view update command (on the java 
side) doesn't return the count of rows updated. I assume this is because the 
postgres update function actually returns a tuple rather than a single count.

Any ideas?

A simplified version of the java bit:

JdbcTemplate is: org.springframework.jdbc.core.JdbcTemplate;

public final int updateTest(final String updateSQL, final Object[] args) {
JdbcTemplate template = createJdbcTemplate();
return template.update(updateSQL, args);
}

And the postgres object creation (again simplified):

--PG START

drop table if exists msg_table cascade;
drop sequence if exists msg_seq;
drop sequence if exists msg_aud_seq;
create sequence msg_seq;
create sequence msg_aud_seq;

CREATE TABLE msg_table
(
aud_seq int default nextval('msg_aud_seq'),
status int default 1,
id int default nextval('msg_seq'),
val int
);

create or replace view msg as
select
aud_seq,
id,
status,
val
from msg_table;

-- audit the original record
CREATE OR REPLACE FUNCTION audit_original_record(msg) RETURNS void AS $$
BEGIN
UPDATE msg_table
SET
status = 2
WHERE
aud_seq = $1.aud_seq;
END;
$$ LANGUAGE plpgsql;


-- insert function
CREATE OR REPLACE FUNCTION process_insert(msg) RETURNS integer AS $body$
declare new_id integer;

BEGIN

INSERT INTO msg_table
(
val
)
SELECT
$1.val

RETURNING id INTO new_id;

return new_id;
END;
$body$ LANGUAGE plpgsql;

-- update function
CREATE OR REPLACE FUNCTION process_update(msg, msg) RETURNS void AS $body$
BEGIN
INSERT INTO msg_table
(
id,
val
)
SELECT
$1.idhttp://1.id,
$1.val;

EXECUTE audit_original_record($2);

END;
$body$ LANGUAGE plpgsql;

-- insert to msg
create or replace rule msg__rule_ins as on insert to msg
do instead
SELECT process_insert(NEW);

-- update to msg
create or replace rule msg__rule_upd as on update to msg
do instead
SELECT
COUNT(process_update(NEW, OLD))
WHERE
NEW.status = 1;


alter sequence msg_seq restart 1;
alter sequence msg_aud_seq restart 1;

delete from msg_table;

insert into msg
(val)
values
(1),
(2),
(66);

select * from msg;

update msg
set val = 5
where id = 1;

select * from msg;

--PG END


Thanks for any help you can give me.

Regards,

Russell Keane
INPS
Follow ushttps://twitter.com/INPSnews on twitter | visit 
www.inps.co.ukhttp://www.inps.co.uk/



Registered name: In Practice Systems Ltd.
Registered address: The Bread Factory, 1a Broughton Street, London, SW8 3QJ
Registered Number: 1788577
Registered in England
Visit our Internet Web site at www.inps.co.ukhttp://www.inps.co.uk
The information in this internet email is confidential and is intended solely 
for the addressee. Access, copying or re-use of information in it by anyone 
else is not authorised. Any views or opinions presented are solely those of the 
author and do not necessarily represent those of INPS or any of its affiliates. 
If you are not the intended recipient please contact 
is.helpd...@inps.co.ukmailto:is.helpd...@inps.co.uk

Re: [GENERAL] JDBC not returning update count from updateable view

[Dean Rasheed]

On 25 February 2013 19:52, Russell Keane russell.ke...@inps.co.uk wrote:
 Hi,



 We have a table which is inserted to and update via a view (using rules /
 functions).


In PG 9.1 and later, the recommended way to do this is using INSTEAD
OF triggers on the view.

See the Notes section here:
http://www.postgresql.org/docs/current/static/sql-createview.html

and the example here:
http://www.postgresql.org/docs/current/static/plpgsql-trigger.html#PLPGSQL-VIEW-TRIGGER-AUDIT-EXAMPLE

Regards,
Dean


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] can't access through SSL

[Ray Stell]

On Feb 23, 2013, at 11:05 PM, Maz Mohammadi wrote:
  
 FATAL:  connection requires a valid client certificate. 

I use openssl to verify the chain, I think that would help you know what's 
going on:

openssl verify -CAfile rootca.crt  user.crt

Re: [GENERAL] [JDBC] can't access through SSL

[Adrian Klaver]

On 02/23/2013 08:05 PM, Maz Mohammadi wrote:

I still can’t access my SSL enabled server!!!

Is root.crt supposed to be an exact copy of server.crt file which I use
in my client’s keystore?

I have another observation.  As I start the coordinator node, I don’t
see any file access to the server.key or server.crt file?  Aren’t these
files supposed to be read at start up time or at least when I try to
make a connection from my java application?

Everything I try create a datasource on tomcat I get the follow error on
client and server’s console…

FATAL:  connection requires a valid client certificate.

Am I missing something?


It would seem that from this thread you are working with Postgres-XC not 
Postgres, is that correct?




-maz




--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] can't access through SSL

[Maz Mohammadi]

Correct!

I'm new postgresql and I need to figure this out for a client.  I installed a 
bunch packages on my Ubuntu linux and here I am.  I've learned a lot.  I have 2 
datanodes, coordinator + gtm.  

-maz

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@gmail.com] 
Sent: Sunday, February 24, 2013 4:37 PM
To: Maz Mohammadi
Cc: pgsql-j...@postgresql.org; pgsql-general@postgresql.org
Subject: Re: [GENERAL] [JDBC] can't access through SSL

On 02/23/2013 08:05 PM, Maz Mohammadi wrote:
 I still can't access my SSL enabled server!!!

 Is root.crt supposed to be an exact copy of server.crt file which I 
 use in my client's keystore?

 I have another observation.  As I start the coordinator node, I don't 
 see any file access to the server.key or server.crt file?  Aren't 
 these files supposed to be read at start up time or at least when I 
 try to make a connection from my java application?

 Everything I try create a datasource on tomcat I get the follow error 
 on client and server's console...

 FATAL:  connection requires a valid client certificate.

 Am I missing something?

It would seem that from this thread you are working with Postgres-XC not 
Postgres, is that correct?


 -maz



--
Adrian Klaver
adrian.kla...@gmail.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] can't access through SSL

[Adrian Klaver]

On 02/24/2013 02:35 PM, Maz Mohammadi wrote:

Correct!

I'm new postgresql and I need to figure this out for a client.  I installed a 
bunch packages on my Ubuntu linux and here I am.  I've learned a lot.  I have 2 
datanodes, coordinator + gtm.


Some general pointers on helping to figure this out:

1) Postgres-XC != Postgres. It shares a code base but adds more moving 
parts. Along that line, you will need to be more specific about how you 
have setup Postgres-XC and exactly which part is failing? I for one do 
not use it, so I am not really sure what datanodes, coordinator and gmt 
signify. On a related note XC has its own mailing 
list(https://lists.sourceforge.net/lists/listinfo/postgres-xc-general), 
it may turn out there are people there that can answer the question sooner.



2) JDBC. It would seem from this thread and the other that covered this 
topic that JDBC is not really the issue. To make your life simpler I 
would test your setup using psql until you get it running properly, then 
pull in JDBC to see if it adds any problems. Also, it is generally 
considered not good protocol to cross post the same issue to different 
lists.



3) Simple with more detail is better. Create a minimum use case and then 
provide maximum detail of how it was set up and run. For instance:


a) What are the versions of the software?
b) Where is the client being run from?
c) Where is the server?
d) How are both setup?
e) What is being done between the client and the server?
f) What do you expect to happen?
g) What is actually happening?
i) The actual error message(s)?



-maz




--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] can't access through SSL

[Maz Mohammadi]

Hi Adrian,

Thanks for sharing some pointers with me.  You are right, it's not actually an 
JDBC driver issue.  I posted it on jdbc, because I'm accessing it from a jdbc 
client, I thought there might be some security issues with the JDBC driver.

1) I'm running postgres-xc v. 9.1.  I'm pretty sure that my postgres setup is 
correct.  Another person from this distribution list help me a bit.  This test 
shows me that the ssl is setup correctly on my server...

--
postgres-xc@adminuser-VirtualBox:~/datanode2$ psql 
psql (PGXC 1.0.0, based on PG 9.1.4)
Type help for help.

postgres=# \q
postgres-xc@adminuser-VirtualBox:~/datanode2$ psql -h localhost
psql: FATAL:  connection requires a valid client certificate
FATAL:  no pg_hba.conf entry for host 127.0.0.1, user postgres-xc, database 
postgres, SSL off
postgres-xc@adminuser-VirtualBox:~/datanode2$


2) My client is a tomcat server.  I've placed JDBC3 drivers (jar file) in the 
WEB-INF lib directory of my webapp. 

3) MY jdbc url is jdbc:postgresql://localhost:5432:testdb?ssl=true, and 
believe me username and password are correct.

4) Both postgres and tomcat are running on the same machine (an Ubuntu linux 
virtual box).  

5) when I try to create a JDBC datasource on my tomcat, I enter the JDBC url + 
user + password, and I'm expecting it to be able to connect to it and at least 
get a test successful but I don't.  I get the error that I sent...
Connection attempt failed: FATAL: connection requires a valid client 
certificate

6) I've also specified the following java options..
-Djavax.net.ssl.trustStore=/home/adminuser/pentaho/keycerts/mazstore 
-Djavax.net.ssl.trustStorePassword=password

I'll post this on the other distribution list.  BTW, I don't see much in the 
log files under /var/log directory.

-maz


-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@gmail.com] 
Sent: Sunday, February 24, 2013 8:08 PM
To: Maz Mohammadi
Cc: pgsql-j...@postgresql.org; pgsql-general@postgresql.org
Subject: Re: [GENERAL] [JDBC] can't access through SSL

On 02/24/2013 02:35 PM, Maz Mohammadi wrote:
 Correct!

 I'm new postgresql and I need to figure this out for a client.  I installed a 
 bunch packages on my Ubuntu linux and here I am.  I've learned a lot.  I have 
 2 datanodes, coordinator + gtm.

Some general pointers on helping to figure this out:

1) Postgres-XC != Postgres. It shares a code base but adds more moving parts. 
Along that line, you will need to be more specific about how you have setup 
Postgres-XC and exactly which part is failing? I for one do not use it, so I am 
not really sure what datanodes, coordinator and gmt signify. On a related note 
XC has its own mailing 
list(https://lists.sourceforge.net/lists/listinfo/postgres-xc-general),
it may turn out there are people there that can answer the question sooner.


2) JDBC. It would seem from this thread and the other that covered this topic 
that JDBC is not really the issue. To make your life simpler I would test your 
setup using psql until you get it running properly, then pull in JDBC to see if 
it adds any problems. Also, it is generally considered not good protocol to 
cross post the same issue to different lists.


3) Simple with more detail is better. Create a minimum use case and then 
provide maximum detail of how it was set up and run. For instance:

a) What are the versions of the software?
b) Where is the client being run from?
c) Where is the server?
d) How are both setup?
e) What is being done between the client and the server?
f) What do you expect to happen?
g) What is actually happening?
i) The actual error message(s)?


 -maz



--
Adrian Klaver
adrian.kla...@gmail.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] can't access through SSL

[Adrian Klaver]

On 02/24/2013 05:54 PM, Maz Mohammadi wrote:

Hi Adrian,

Thanks for sharing some pointers with me.  You are right, it's not actually an 
JDBC driver issue.  I posted it on jdbc, because I'm accessing it from a jdbc 
client, I thought there might be some security issues with the JDBC driver.

1) I'm running postgres-xc v. 9.1.  I'm pretty sure that my postgres setup is 
correct.  Another person from this distribution list help me a bit.  This test shows me 
that the ssl is setup correctly on my server...


Which server? As I understand it Postgres-XC can have multiple clusters 
in use, so I am still not sure which one you are connecting to?




--
postgres-xc@adminuser-VirtualBox:~/datanode2$ psql
psql (PGXC 1.0.0, based on PG 9.1.4)
Type help for help.

postgres=# \q
postgres-xc@adminuser-VirtualBox:~/datanode2$ psql -h localhost


To make things easier to debug, use explicit options. The above command 
leaves a lot to env variables and hidden configuration. There is a good 
chance you are not connecting the way you think you are.




psql: FATAL:  connection requires a valid client certificate
FATAL:  no pg_hba.conf entry for host 127.0.0.1, user postgres-xc, database 
postgres, SSL off
postgres-xc@adminuser-VirtualBox:~/datanode2$


At this point do none of the Tomcat/JDBC process . Until you solve the 
above, it just gets you to the same error and it confuses the issue.


The first thing to solve is why you are getting two different error 
messages, in particular why it says SSL is off. The second is whether 
the user and database specified in the error are who are trying to 
connect as and the database you are trying to connect to.


Also have you gone through Table 17-3. SSL Server File Usage at link 
below to see if everything is in place:

http://www.postgresql.org/docs/9.2/interactive/ssl-tcp.html

Have you followed Rays suggestion:

'I use openssl to verify the chain, I think that would help you know 
what's going on:


openssl verify -CAfile rootca.crt  user.crt





2) My client is a tomcat server.  I've placed JDBC3 drivers (jar file) in the 
WEB-INF lib directory of my webapp.

3) MY jdbc url is jdbc:postgresql://localhost:5432:testdb?ssl=true, and 
believe me username and password are correct.

4) Both postgres and tomcat are running on the same machine (an Ubuntu linux 
virtual box).

5) when I try to create a JDBC datasource on my tomcat, I enter the JDBC url + user + 
password, and I'm expecting it to be able to connect to it and at least get a test 
successful but I don't.  I get the error that I sent...
Connection attempt failed: FATAL: connection requires a valid client 
certificate

6) I've also specified the following java options..
-Djavax.net.ssl.trustStore=/home/adminuser/pentaho/keycerts/mazstore 
-Djavax.net.ssl.trustStorePassword=password

I'll post this on the other distribution list.  BTW, I don't see much in the 
log files under /var/log directory.

-maz



--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] can't access through SSL

[Michael Paquier]

On Mon, Feb 25, 2013 at 10:07 AM, Adrian Klaver adrian.kla...@gmail.comwrote:

 1) Postgres-XC != Postgres. It shares a code base but adds more moving
 parts. Along that line, you will need to be more specific about how you
 have setup Postgres-XC and exactly which part is failing? I for one do not
 use it, so I am not really sure what datanodes, coordinator and gmt
 signify. On a related note XC has its own mailing list(
 https://lists.sourceforge.net/lists/listinfo/postgres-xc-general), it may
 turn out there are people there that can answer the question sooner.

Adrian is right, pgsql-general is not the place where to discuss about bugs
or problems of settings regarding Postgres-XC, so please send your requests
to the ML indicated by Adrian so as to allow the developers there
(including me, being an active member of the XC community) solving your
problem.


 2) JDBC. It would seem from this thread and the other that covered this
 topic that JDBC is not really the issue. To make your life simpler I would
 test your setup using psql until you get it running properly, then pull in
 JDBC to see if it adds any problems. Also, it is generally considered not
 good protocol to cross post the same issue to different lists.

I think honestly that the problem is not JDBC itself, but the way the nodes
in an XC cluster interact...


 3) Simple with more detail is better. Create a minimum use case and then
 provide maximum detail of how it was set up and run. For instance:

 a) What are the versions of the software?
 b) Where is the client being run from?
 c) Where is the server?
 d) How are both setup?
 e) What is being done between the client and the server?
 f) What do you expect to happen?
 g) What is actually happening?
 i) The actual error message(s)?

Yes, answering those questions on the XC mailing list would be better when
you report your problem there.
What is the node type where error happens?
Have you setup the SSL certificates on all the nodes?
Or anything that would help resolving what you see.
Does the error happen when connecting directly to a Datanode?
-- 
Michael

Re: [GENERAL] [JDBC] can't access through SSL

[Maz Mohammadi]

I still can't access my SSL enabled server!!!

Is root.crt supposed to be an exact copy of server.crt file which I use in my 
client's keystore?

I have another observation.  As I start the coordinator node, I don't see any 
file access to the server.key or server.crt file?  Aren't these files supposed 
to be read at start up time or at least when I try to make a connection from my 
java application?

Everything I try create a datasource on tomcat I get the follow error on client 
and server's console...

FATAL:  connection requires a valid client certificate.

Am I missing something?

-maz

From: pgsql-jdbc-ow...@postgresql.org [mailto:pgsql-jdbc-ow...@postgresql.org] 
On Behalf Of Maz Mohammadi
Sent: Friday, February 22, 2013 4:33 PM
To: pgsql-j...@postgresql.org
Subject: Re: [JDBC] can't access through SSL

Correction...

After double checking the path to java's keystore file, and correcting 
it...this is the new error.

FATAL:  connection requires a valid client certificate.

Any idea would be greatly appreciated.

-maz

From: Maz Mohammadi
Sent: Friday, February 22, 2013 3:51 PM
To: 'pgsql-j...@postgresql.org'
Subject: RE: [JDBC] can't access through SSL

Hello,

I regenerated some new keys for my postgres server.  I've placed them under 
/var/lib/coord and shared them with the datanodes as well.

After adding the certificates to the keystore for my tomcat java application, I 
get the following error on my server.

LOG:  could not accept SSL connection:  sslv3 alert certificate unkown.

I thought I had to use JDBC 3 for this.

Any ideas?

-maz

From: Maz Mohammadi
Sent: Friday, February 22, 2013 3:45 PM
To: pgsql-j...@postgresql.orgmailto:pgsql-j...@postgresql.org
Subject: RE: [JDBC] can't access through SSL

Thx,  one step closer.

pgsql-j...@postgresql.orgmailto:pgsql-j...@postgresql.org


From: Vitalii Tymchyshyn [mailto:tiv...@gmail.com]
Sent: Friday, February 22, 2013 12:56 PM
To: Maz Mohammadi
Cc: pgsql-j...@postgresql.orgmailto:pgsql-j...@postgresql.org
Subject: Re: [JDBC] can't access through SSL

Try jdbc:postgresql://localhost:5432/testdb?ssl=true

2013/2/22 Maz Mohammadi mmohamm...@pentaho.commailto:mmohamm...@pentaho.com
Hello all,

I'm trying to access a postgres database through a java application (tomcat).  
This is the only entry I have in pg_hba.conf

# TYPE  DATABASEUSERADDRESS METHOD
hostssl all   all 
127.0.0.1/32http://127.0.0.1/32cert

and put the certicate (from /var/lib/postre./coord/server.crt) in the 
cacerts under $JAVA_HOME/...

This is my jdbc URL
jdbc:postgresql://localhost:5432/testdbssl=true

But When I try to create a datasource on tomcat, I get the following error...
Connection attempt failed: FATAL: no pg_hba.conf entry for host 127.0.0.1, 
user progres-xc, database testdbssl=true, SSL off

Any help is greatly appreciated.

-maz



--
Best regards,
 Vitalii Tymchyshyn

Re: [GENERAL] [JDBC] JDBC connection test with SSL on PG 9.2.1 server

[Hari Babu]

On Friday, February 01, 2013 7:53 PM Adrian Klaver wrote:
On 02/01/2013 06:06 AM, Hari Babu wrote:

 We tried the approach as suggested by you but still it is not working as 
 shown in the below log (I had enabled logLevel as 1)
 keystore passowrd is qwerty
 19:26:22.666 (1) PostgreSQL 9.2 JDBC4 (build 1002)
 19:26:23.451 (1) Receive Buffer Size is 43808
 19:26:23.452 (1) Send Buffer Size is 25386
 getConnection returning 
 driver[className=org.postgresql.Driver,org.postgresql.Driver@3f7fa65e]
 Connection1 successful! Conn1:org.postgresql.jdbc4.Jdbc4Connection@6baa9f99
 null
 wrongqwerty
 DriverManager.getConnection(jdbc:postgresql://127.0.0.1:15432/postgres)
  trying 
 driver[className=sun.jdbc.odbc.JdbcOdbcDriver,sun.jdbc.odbc.JdbcOdbcDriver@3597a37c]
 *Driver.connect (jdbc:postgresql://127.0.0.1:15432/postgres)
  trying 
 driver[className=org.postgresql.Driver,org.postgresql.Driver@3f7fa65e]
 19:26:23.835 (2) PostgreSQL 9.2 JDBC4 (build 1002)
 19:26:23.847 (2) Receive Buffer Size is 43808
 19:26:23.848 (2) Send Buffer Size is 25386
 getConnection returning 
 driver[className=org.postgresql.Driver,org.postgresql.Driver@3f7fa65e]
 Connection2 successful! Conn2:org.postgresql.jdbc4.Jdbc4Connection@2e958bb8

 Connect OK

 There is function as SSL_CTX_SETSESSIONCACHEMODE(ctxt, mode) in C library of 
 SSL.
 Can you please let us  know if there is some similar function in JSSE also.

Per a previous post, have you verified that pg_hba.conf is set up to 
properly handle SSL password connections?

Yes, I have modified the pg_hba.conf as follows to handle the SSL connections.

# IPv4 local connections: 
hostsslall all 10.18.0.0/16   cert 

please provide your suggestions.

Regards,
Hari babu.




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] JDBC connection test with SSL on PG 9.2.1 server

[Adrian Klaver]

On 02/04/2013 04:46 AM, Hari Babu wrote:

On Friday, February 01, 2013 7:53 PM Adrian Klaver wrote:

On 02/01/2013 06:06 AM, Hari Babu wrote:


We tried the approach as suggested by you but still it is not working as shown 
in the below log (I had enabled logLevel as 1)
keystore passowrd is qwerty
19:26:22.666 (1) PostgreSQL 9.2 JDBC4 (build 1002)
19:26:23.451 (1) Receive Buffer Size is 43808
19:26:23.452 (1) Send Buffer Size is 25386
getConnection returning 
driver[className=org.postgresql.Driver,org.postgresql.Driver@3f7fa65e]
Connection1 successful! Conn1:org.postgresql.jdbc4.Jdbc4Connection@6baa9f99
null
wrongqwerty
DriverManager.getConnection(jdbc:postgresql://127.0.0.1:15432/postgres)
  trying 
driver[className=sun.jdbc.odbc.JdbcOdbcDriver,sun.jdbc.odbc.JdbcOdbcDriver@3597a37c]
*Driver.connect (jdbc:postgresql://127.0.0.1:15432/postgres)
  trying 
driver[className=org.postgresql.Driver,org.postgresql.Driver@3f7fa65e]
19:26:23.835 (2) PostgreSQL 9.2 JDBC4 (build 1002)
19:26:23.847 (2) Receive Buffer Size is 43808
19:26:23.848 (2) Send Buffer Size is 25386
getConnection returning 
driver[className=org.postgresql.Driver,org.postgresql.Driver@3f7fa65e]
Connection2 successful! Conn2:org.postgresql.jdbc4.Jdbc4Connection@2e958bb8

Connect OK

There is function as SSL_CTX_SETSESSIONCACHEMODE(ctxt, mode) in C library of 
SSL.
Can you please let us  know if there is some similar function in JSSE also.



Per a previous post, have you verified that pg_hba.conf is set up to
properly handle SSL password connections?


Yes, I have modified the pg_hba.conf as follows to handle the SSL connections.

# IPv4 local connections:
hostsslall all 10.18.0.0/16   cert



This is your entire pg_hba,conf?

If so, note the part about no password prompt below:

http://www.postgresql.org/docs/9.2/interactive/auth-methods.html#AUTH-CERT

19.3.10. Certificate Authentication

This authentication method uses SSL client certificates to perform 
authentication. It is therefore only available for SSL connections. When 
using this authentication method, the server will require that the 
client provide a valid certificate. No password prompt will be sent to 
the client. The cn (Common Name) attribute of the certificate will be 
compared to the requested database user name, and if they match the 
login will be allowed. User name mapping can be used to allow cn to be 
different from the database user name.


The following configuration options are supported for SSL certificate 
authentication:


map
Allows for mapping between system and database user names. See Section 
19.2 for details.


I am guessing what you want is:

hostsslall all 10.18.0.0/16   md5

See here for more detail:

http://www.postgresql.org/docs/9.2/interactive/auth-pg-hba-conf.html

In particular:

The first record with a matching connection type, client address, 
requested database, and user name is used to perform authentication. 
There is no fall-through or backup: if one record is chosen and the 
authentication fails, subsequent records are not considered. If no 
record matches, access is denied.







please provide your suggestions.

Regards,
Hari babu.







--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [JDBC] JDBC connection test with SSL on PG 9.2.1 server

[Hari Babu]

 On Tuesday, January 29, 2013 10:12 PM danap wrote:
 Hari Babu wrote:
 On Monday, January 28, 2013 10:20 PM, Dave Cramer wrote:

   On Mon, Jan 28, 2013 at 9:03 AM, Hari Babuharibabu.ko...@huawei.com
 mailto:haribabu.ko...@huawei.com  wrote:

   While testing PostgreSQL JDBC java client to connect to the PG 9.2.1
   database server using SSL.
   we got the following behavior.
   
   The test steps as below：
   
   url = jdbc:postgresql:// + 10.145.98.227 + ':'
 + 8707 + '/'
 + POSTGRES;
 Properties props = new Properties();
 props.setProperty(user, CLIENT);
 props.setProperty(password, 1234@QWER);
 props.setProperty(ssl, true);
   
 System.setProperty(javax.net.ssl.trustStore, 193store);
 System.setProperty(javax.net.ssl.keyStore, 193client.jks);
 System.setProperty(javax.net.ssl.trustStorePassword, qwerty);
 System.setProperty(javax.net.ssl.keyStorePassword, qwerty);
   
 /*Begin the first ssl connection*/
 conn1 = DriverManager.getConnection(url, props);
 System.out.println(Connection1 successful!);
   
   
 System.setProperty(javax.net.ssl.trustStore, 193store);
 System.setProperty(javax.net.ssl.keyStore, 193client.jks);
 System.setProperty(javax.net.ssl.trustStorePassword, qwerty);
 System.setProperty(javax.net.ssl.keyStorePassword, wrongpassword);
   
 /*Begin the second ssl connection*/
 conn2 = DriverManager.getConnection(url, props);
 System.out.println(Connection2 successful!);
   
   Before first connection we set
   System.setProperty(javax.net.ssl.keyStorePassword, qwerty);
 qwerty is
   the right password
   and before second SSL connection we set
   System.setProperty(javax.net.ssl.keyStorePassword, wrongpassword);
   wrongpassword is the wrong password.
   
   we expect the first SSL connection will be successful and second failed
   because of wrong password, but actually we get two successful SSL
   connections.
   We found that if the first SSL connections password set right, all the
   following SSL connections are fine ,even set wrong keystroke password.
   
   1. Is this a defect about JDBC?
   2. Is it SSL behavior to authenticate only once?
   3. Is it system property behavior can be set only once.
   4. Is it because of any other problems?
   
   please give your suggestions?

   JDBC uses java's SSL infrastructure, as such I don't think it's a
 defect in JDBC. It could be because your truststoredoes not require a
 password.

 I removed the trustStorePassword setting from the test, still the second
 connection is getting success with the wrong

 keyStorePassword.

 Can you please set the property logLevel=1, INFO
 and then reply back with the output. You may also
 try logLevel=2, DEBUG for additional information.

 How to set logLevel=1 INFO and logLevel =2 DEBUG. Is it is JDBC logging or 
 something else?

 We tried to get the SSL specific log by setting the system property for 
 javax.net.debug as
 ssl (system.setProperty(javax.net.debug, ssl). With this we got 
 connection logs for each of the connection which are attached in the mail.

 For the first connection, it is opening the keys file and then does init for 
 keyStore and trustStore. But incase of second connection it just uses the 
 previous cached session and does not open any of the file set in the 
 property. So may be that is the reason even if wrong file or password is 
 given before second connection, connection is successful.

  From the logs we feel that SSL caching may be causing the problem.
 Is there any exposed JSSE interface function to disable SSL session caching?
 If you can derive something from the attached logs, please let us know.

 How to set the SSL property sslfactory from application with some valid 
 class?
 Our idea is that JDBC convert function execution goes to the else part of
 if (classname == null).

 The code snippet is attached:

 String classname = info.getProperty(sslfactory);
if (classname == null)
  {
//If sslmode is set, use the libp compatible factory
if (sslmode!=null)
{
  factory = new LibPQFactory(info);
}
else
{
  factory = (SSLSocketFactory)SSLSocketFactory.getDefault();
}
  }
  else
  {
  try
  {
  factory = (SSLSocketFactory)instantiate(classname, info, 
 true, info.getProperty(sslfactoryarg));
  }
  catch (Exception e)
  {
  throw new PSQLException(GT.tr(The SSLSocketFactory class 
 provided {0} could not be instantiated., classname), 
 PSQLState.CONNECTION_FAILURE, e);
  }
  }

 Regards,
 Hari babu.

Hello Hari,

I thought at first setting props.setProperty(loglevel, 1) may derive
additional information. It will not in this case. I already suspected and
believe that the System property is not changing, cached as you indicated.

Re: [GENERAL] [JDBC] JDBC connection test with SSL on PG 9.2.1 server

[Adrian Klaver]

On 02/01/2013 06:06 AM, Hari Babu wrote:







We tried the approach as suggested by you but still it is not working as shown 
in the below log (I had enabled logLevel as 1)
keystore passowrd is qwerty
19:26:22.666 (1) PostgreSQL 9.2 JDBC4 (build 1002)
19:26:23.451 (1) Receive Buffer Size is 43808
19:26:23.452 (1) Send Buffer Size is 25386
getConnection returning 
driver[className=org.postgresql.Driver,org.postgresql.Driver@3f7fa65e]
Connection1 successful! Conn1:org.postgresql.jdbc4.Jdbc4Connection@6baa9f99
null
wrongqwerty
DriverManager.getConnection(jdbc:postgresql://127.0.0.1:15432/postgres)
 trying 
driver[className=sun.jdbc.odbc.JdbcOdbcDriver,sun.jdbc.odbc.JdbcOdbcDriver@3597a37c]
*Driver.connect (jdbc:postgresql://127.0.0.1:15432/postgres)
 trying 
driver[className=org.postgresql.Driver,org.postgresql.Driver@3f7fa65e]
19:26:23.835 (2) PostgreSQL 9.2 JDBC4 (build 1002)
19:26:23.847 (2) Receive Buffer Size is 43808
19:26:23.848 (2) Send Buffer Size is 25386
getConnection returning 
driver[className=org.postgresql.Driver,org.postgresql.Driver@3f7fa65e]
Connection2 successful! Conn2:org.postgresql.jdbc4.Jdbc4Connection@2e958bb8

Connect OK

There is function as SSL_CTX_SETSESSIONCACHEMODE(ctxt, mode) in C library of 
SSL.
Can you please let us  know if there is some similar function in JSSE also.



Per a previous post, have you verified that pg_hba.conf is set up to 
properly handle SSL password connections?




Regards,
Hari Babu.




--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC connection test with SSL on PG 9.2.1 server

[Adrian Klaver]

On 01/28/2013 09:24 PM, Hari Babu wrote:
 While testing PostgreSQL JDBC java client to connect to the PG 9.2.1
 database server using SSL.
 we got the following behavior.
 
 The test steps as below：
 
 url = jdbc:postgresql:// + 10.145.98.227 + ':'
  + 8707 + '/'
  + POSTGRES;
  Properties props = new Properties();
  props.setProperty(user, CLIENT);
  props.setProperty(password, 1234@QWER);
  props.setProperty(ssl, true);
 
System.setProperty(javax.net.ssl.trustStore, 193store);
System.setProperty(javax.net.ssl.keyStore, 193client.jks);
  System.setProperty(javax.net.ssl.keyStorePassword, qwerty);
 
  /*Begin the first ssl connection*/
  conn1 = DriverManager.getConnection(url, props);
  System.out.println(Connection1 successful!);
  
  
System.setProperty(javax.net.ssl.trustStore, 193store);
System.setProperty(javax.net.ssl.keyStore, 193client.jks);
  System.setProperty(javax.net.ssl.keyStorePassword, wrongpassword);
 
  /*Begin the second ssl connection*/
  conn2 = DriverManager.getConnection(url, props);
  System.out.println(Connection2 successful!);
 
 Before first connection we set
 System.setProperty(javax.net.ssl.keyStorePassword, qwerty); qwerty is
 the right password
 and before second SSL connection we set
 System.setProperty(javax.net.ssl.keyStorePassword, wrongpassword);
 wrongpassword is the wrong password.
 
 we expect  the first SSL connection will be successful and second failed
 because of wrong password, but actually we get two successful SSL
 connections.
 We found that  if the first SSL connections password set right, all the
 following SSL connections are fine ,even set wrong keystroke password.
 
 1. Is this a defect about JDBC?
 2. Is it SSL behavior to authenticate only once?
 3. Is it system property behavior can be set only once.
 4. Is it because of any other problems?
 
 please give your suggestions?

Take a look at this section of the manual:

http://www.postgresql.org/docs/9.2/interactive/client-authentication.html

I would start with the section on pg_hba:

http://www.postgresql.org/docs/9.2/interactive/auth-pg-hba-conf.html


My guess is if you look in the pg_hba.conf file for your server you do
not have password authentication set up for the connections you are making.


 
 Regards,
 Hari babu.
 
 
 


-- 
Adrian Klaver
adrian.kla...@gmail.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC - Need to declare variables for values in insert statement

[Dave Cramer]

Bob,

Can you provide a snippet of code so I can understand what you mean by
declare ?


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Mon, Jan 28, 2013 at 7:11 AM, Bob Futrelle bob.futre...@gmail.comwrote:

 Do the declare statements and insert all have to be done in one statement
 execute()?
 That is, what is the scope of variables I declare?
 I see a variety of syntax examples, some for older versions?
 I'm using pg 9.2.2, so what are the rules/syntax for declaring and using
 variables?

 Use case: I collect metadata from XML articles such as title, journalName,
 volume, year.  For each article I need to create a pg table record and
 insert the values for the various metadata items in the article.

 This is my first post to this list.

 Thanks,

  - Bob

Re: [GENERAL] JDBC - Need to declare variables for values in insert statement

[Bob Futrelle]

Here's a small, but complete code example - Bob

package us.tsos.dbs.pg;

import java.sql.*;

/**
 * This is an effort to get a computed value from a Java function
 * (or data object) included properly in the VALUES entries.
 * So, how can I declare an SQL variable and set its value to some Java
value?
 * Results viewed in pgAdmin3  with query 'select * from public.hello'.
 *
 * Jar in classpath is postgresql-9.2-1002.jdbc4.jar
 *
 * @version 0.1  Mon Jan 28 EST 2013
 * @author Bob Futrelle
 */

public class JDBCVariableTest {

Connection db;
Statement st;
Boolean boo;

public static void main(String[] args) throws SQLException {

JDBCVariableTest testIt = new JDBCVariableTest();
testIt.helloVariables();
}

public int f1() { return 3;}

public void helloVariables() throws SQLException  {

int intVar = f1();  // OK in Java, but SQL/JDBC?

try {
db = DriverManager.getConnection(jdbc:postgresql:Articles,
robertfutrelle, my pw);
st = db.createStatement();
boo  = st.execute(CREATE TABLE IF NOT EXISTS hello ( Name VARCHAR NOT NULL
PRIMARY KEY, value int));

// Declare   ??
 // INSTEAD OF THE LITERAL 4 VALUE (which works)
// how do I declare a variable and assign the f1() return value to it
// and then include it so the value 3 appears in the inserted record?
 //st.execute(insert into hello values('aKey',4));
st.execute(insert into hello values('bKey',4));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}



On Mon, Jan 28, 2013 at 8:39 AM, Dave Cramer p...@fastcrypt.com wrote:

 Bob,

 Can you provide a snippet of code so I can understand what you mean by
 declare ?


 Dave Cramer

 dave.cramer(at)credativ(dot)ca
 http://www.credativ.ca


 On Mon, Jan 28, 2013 at 7:11 AM, Bob Futrelle bob.futre...@gmail.comwrote:

 Do the declare statements and insert all have to be done in one statement
 execute()?
 That is, what is the scope of variables I declare?
 I see a variety of syntax examples, some for older versions?
 I'm using pg 9.2.2, so what are the rules/syntax for declaring and using
 variables?

 Use case: I collect metadata from XML articles such as title,
 journalName, volume, year.  For each article I need to create a pg table
 record and insert the values for the various metadata items in the article.

 This is my first post to this list.

 Thanks,

  - Bob

Re: [GENERAL] JDBC - Need to declare variables for values in insert statement

[Edson Richter]

You have two options:

st.execute(insert into hello values ('bKey', +f1()+));

or

PreparedStatement st = db.prepareStatement(insert into hello values 
('bKey', ?));

st.setInteger(1, f1());


where 1 is the first parameter, 2 is the second parameter, and so on.


Regards,

Edson Richter



Em 28/01/2013 16:50, Bob Futrelle escreveu:

Here's a small, but complete code example - Bob

package us.tsos.dbs.pg http://us.tsos.dbs.pg;

import java.sql.*;

/**
 * This is an effort to get a computed value from a Java function
 * (or data object) included properly in the VALUES entries.
 * So, how can I declare an SQL variable and set its value to some 
Java value?

 * Results viewed in pgAdmin3 with query 'select * from public.hello'.
 *
 * Jar in classpath is postgresql-9.2-1002.jdbc4.jar
 *
 * @version 0.1  Mon Jan 28 EST 2013
 * @author Bob Futrelle
 */

public class JDBCVariableTest {

Connection db;
Statement st;
Boolean boo;

public static void main(String[] args) throws SQLException {

JDBCVariableTest testIt = new JDBCVariableTest();
testIt.helloVariables();
}

public int f1() { return 3;}

public void helloVariables() throws SQLException  {

int intVar = f1();  // OK in Java, but SQL/JDBC?

try {
db = DriverManager.getConnection(jdbc:postgresql:Articles, 
robertfutrelle, my pw);

st = db.createStatement();
boo  = st.execute(CREATE TABLE IF NOT EXISTS hello ( Name VARCHAR NOT 
NULL PRIMARY KEY, value int));


// Declare   ??
// INSTEAD OF THE LITERAL 4 VALUE (which works)
// how do I declare a variable and assign the f1() return value to it
// and then include it so the value 3 appears in the inserted record?
//st.execute(insert into hello values('aKey',4));
st.execute(insert into hello values('bKey',4));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}



On Mon, Jan 28, 2013 at 8:39 AM, Dave Cramer p...@fastcrypt.com 
mailto:p...@fastcrypt.com wrote:


Bob,

Can you provide a snippet of code so I can understand what you
mean by declare ?


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Mon, Jan 28, 2013 at 7:11 AM, Bob Futrelle
bob.futre...@gmail.com mailto:bob.futre...@gmail.com wrote:

Do the declare statements and insert all have to be done in
one statement execute()?
That is, what is the scope of variables I declare?
I see a variety of syntax examples, some for older versions?
I'm using pg 9.2.2, so what are the rules/syntax for declaring
and using variables?

Use case: I collect metadata from XML articles such as title,
journalName, volume, year.  For each article I need to create
a pg table record and insert the values for the various
metadata items in the article.

This is my first post to this list.

Thanks,

 - Bob

Re: [GENERAL] JDBC - Need to declare variables for values in insert statement

[Dave Cramer]

Bob,

Ok, have a look at PreparedStatement

Essentially the same

PreparedStatement pstmt= db.prepareStatement(insert into hello values ?)
pstmt.setInt(1,intVar)
pstmt.execute()


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Mon, Jan 28, 2013 at 1:50 PM, Bob Futrelle bob.futre...@gmail.comwrote:

 Here's a small, but complete code example - Bob

 package us.tsos.dbs.pg;

 import java.sql.*;

 /**
  * This is an effort to get a computed value from a Java function
  * (or data object) included properly in the VALUES entries.
  * So, how can I declare an SQL variable and set its value to some Java
 value?
  * Results viewed in pgAdmin3  with query 'select * from public.hello'.
  *
  * Jar in classpath is postgresql-9.2-1002.jdbc4.jar
  *
  * @version 0.1  Mon Jan 28 EST 2013
  * @author Bob Futrelle
  */

 public class JDBCVariableTest {

  Connection db;
 Statement st;
 Boolean boo;

 public static void main(String[] args) throws SQLException {

 JDBCVariableTest testIt = new JDBCVariableTest();
  testIt.helloVariables();
 }

  public int f1() { return 3;}

 public void helloVariables() throws SQLException  {

 int intVar = f1();  // OK in Java, but SQL/JDBC?

 try {
  db = DriverManager.getConnection(jdbc:postgresql:Articles,
 robertfutrelle, my pw);
 st = db.createStatement();
  boo  = st.execute(CREATE TABLE IF NOT EXISTS hello ( Name VARCHAR NOT
 NULL PRIMARY KEY, value int));

  // Declare   ??
  // INSTEAD OF THE LITERAL 4 VALUE (which works)
  // how do I declare a variable and assign the f1() return value to it
 // and then include it so the value 3 appears in the inserted record?
  //st.execute(insert into hello values('aKey',4));
  st.execute(insert into hello values('bKey',4));
 } catch (SQLException e) {
  // TODO Auto-generated catch block
 e.printStackTrace();
  }
 }
 }



 On Mon, Jan 28, 2013 at 8:39 AM, Dave Cramer p...@fastcrypt.com wrote:

 Bob,

 Can you provide a snippet of code so I can understand what you mean by
 declare ?


 Dave Cramer

 dave.cramer(at)credativ(dot)ca
 http://www.credativ.ca


 On Mon, Jan 28, 2013 at 7:11 AM, Bob Futrelle bob.futre...@gmail.comwrote:

 Do the declare statements and insert all have to be done in one
 statement execute()?
 That is, what is the scope of variables I declare?
 I see a variety of syntax examples, some for older versions?
 I'm using pg 9.2.2, so what are the rules/syntax for declaring and using
 variables?

 Use case: I collect metadata from XML articles such as title,
 journalName, volume, year.  For each article I need to create a pg table
 record and insert the values for the various metadata items in the article.

 This is my first post to this list.

 Thanks,

  - Bob

Re: [GENERAL] JDBC - Need to declare variables for values in insert statement

[Bob Futrelle]

Thanks to Edson and Dave for lightning responses.
I'm confident that your answers will do the job.
I'll follow up on the advice AFTER I get my coffee ;-)

I'm really focused on the NLP content of my research,
but I need a DB infrastructure to do it right.
(Not a bunch of files as in ancient times.)

 --- Bob



On Mon, Jan 28, 2013 at 1:59 PM, Edson Richter edsonrich...@hotmail.comwrote:

  You have two options:

 st.execute(insert into hello values ('bKey', +f1()+));

 or

 PreparedStatement st = db.prepareStatement(insert into hello values
 ('bKey', ?));
 st.setInteger(1, f1());


 where 1 is the first parameter, 2 is the second parameter, and so on.


 Regards,

 Edson Richter



 Em 28/01/2013 16:50, Bob Futrelle escreveu:

 Here's a small, but complete code example - Bob

  package us.tsos.dbs.pg;

  import java.sql.*;

  /**
  * This is an effort to get a computed value from a Java function
  * (or data object) included properly in the VALUES entries.
  * So, how can I declare an SQL variable and set its value to some Java
 value?
  * Results viewed in pgAdmin3  with query 'select * from public.hello'.
  *
  * Jar in classpath is postgresql-9.2-1002.jdbc4.jar
  *
  * @version 0.1  Mon Jan 28 EST 2013
  * @author Bob Futrelle
  */

  public class JDBCVariableTest {

  Connection db;
  Statement st;
  Boolean boo;

  public static void main(String[] args) throws SQLException {

  JDBCVariableTest testIt = new JDBCVariableTest();
  testIt.helloVariables();
  }

  public int f1() { return 3;}

  public void helloVariables() throws SQLException  {

  int intVar = f1();  // OK in Java, but SQL/JDBC?

  try {
  db = DriverManager.getConnection(jdbc:postgresql:Articles,
 robertfutrelle, my pw);
  st = db.createStatement();
  boo  = st.execute(CREATE TABLE IF NOT EXISTS hello ( Name VARCHAR NOT
 NULL PRIMARY KEY, value int));

  // Declare   ??
   // INSTEAD OF THE LITERAL 4 VALUE (which works)
  // how do I declare a variable and assign the f1() return value to it
  // and then include it so the value 3 appears in the inserted record?
   //st.execute(insert into hello values('aKey',4));
  st.execute(insert into hello values('bKey',4));
  } catch (SQLException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
  }
  }
 }



 On Mon, Jan 28, 2013 at 8:39 AM, Dave Cramer p...@fastcrypt.com wrote:

 Bob,

  Can you provide a snippet of code so I can understand what you mean by
 declare ?


 Dave Cramer

 dave.cramer(at)credativ(dot)ca
 http://www.credativ.ca


 On Mon, Jan 28, 2013 at 7:11 AM, Bob Futrelle bob.futre...@gmail.comwrote:

 Do the declare statements and insert all have to be done in one
 statement execute()?
 That is, what is the scope of variables I declare?
 I see a variety of syntax examples, some for older versions?
 I'm using pg 9.2.2, so what are the rules/syntax for declaring and using
 variables?

  Use case: I collect metadata from XML articles such as title,
 journalName, volume, year.  For each article I need to create a pg table
 record and insert the values for the various metadata items in the article.

  This is my first post to this list.

  Thanks,

   - Bob

Re: [GENERAL] JDBC - Need to declare variables for values in insert statement

[Edson Richter]

I would recommend the reading of the excellent The Java Tutorial, that 
has a very well explained section about JDBC:

http://docs.oracle.com/javase/tutorial/jdbc/index.html


and the chapter about PreparedStatements:
http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html


Regards,

Edson Richter


Em 28/01/2013 17:09, Bob Futrelle escreveu:

Thanks to Edson and Dave for lightning responses.
I'm confident that your answers will do the job.
I'll follow up on the advice AFTER I get my coffee ;-)

I'm really focused on the NLP content of my research,
but I need a DB infrastructure to do it right.
(Not a bunch of files as in ancient times.)

 --- Bob



On Mon, Jan 28, 2013 at 1:59 PM, Edson Richter 
edsonrich...@hotmail.com mailto:edsonrich...@hotmail.com wrote:


You have two options:

st.execute(insert into hello values ('bKey', +f1()+));

or

PreparedStatement st = db.prepareStatement(insert into hello
values ('bKey', ?));
st.setInteger(1, f1());


where 1 is the first parameter, 2 is the second parameter, and so on.


Regards,

Edson Richter



Em 28/01/2013 16:50, Bob Futrelle escreveu:

Here's a small, but complete code example - Bob

package us.tsos.dbs.pg http://us.tsos.dbs.pg;

import java.sql.*;

/**
 * This is an effort to get a computed value from a Java function
 * (or data object) included properly in the VALUES entries.
 * So, how can I declare an SQL variable and set its value to
some Java value?
 * Results viewed in pgAdmin3 with query 'select * from
public.hello'.
 *
 * Jar in classpath is postgresql-9.2-1002.jdbc4.jar
 *
 * @version 0.1  Mon Jan 28 EST 2013
 * @author Bob Futrelle
 */

public class JDBCVariableTest {

Connection db;
Statement st;
Boolean boo;

public static void main(String[] args) throws SQLException {

JDBCVariableTest testIt = new JDBCVariableTest();
testIt.helloVariables();
}

public int f1() { return 3;}

public void helloVariables() throws SQLException  {

int intVar = f1();  // OK in Java, but SQL/JDBC?

try {
db = DriverManager.getConnection(jdbc:postgresql:Articles,
robertfutrelle, my pw);
st = db.createStatement();
boo  = st.execute(CREATE TABLE IF NOT EXISTS hello ( Name
VARCHAR NOT NULL PRIMARY KEY, value int));

// Declare   ??
// INSTEAD OF THE LITERAL 4 VALUE (which works)
// how do I declare a variable and assign the f1() return value to it
// and then include it so the value 3 appears in the inserted record?
//st.execute(insert into hello values('aKey',4));
st.execute(insert into hello values('bKey',4));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}



On Mon, Jan 28, 2013 at 8:39 AM, Dave Cramer p...@fastcrypt.com
mailto:p...@fastcrypt.com wrote:

Bob,

Can you provide a snippet of code so I can understand what
you mean by declare ?


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Mon, Jan 28, 2013 at 7:11 AM, Bob Futrelle
bob.futre...@gmail.com mailto:bob.futre...@gmail.com wrote:

Do the declare statements and insert all have to be done
in one statement execute()?
That is, what is the scope of variables I declare?
I see a variety of syntax examples, some for older versions?
I'm using pg 9.2.2, so what are the rules/syntax for
declaring and using variables?

Use case: I collect metadata from XML articles such as
title, journalName, volume, year.  For each article I
need to create a pg table record and insert the values
for the various metadata items in the article.

This is my first post to this list.

Thanks,

 - Bob

Re: [GENERAL] JDBC - Need to declare variables for values in insert statement

[Bob Futrelle]

I had read 'through' the JDBC material,
but now reading more deeply with more insight.
The API is useful too.

Anyhoo, PreparedStatement works like a charm, viz.,

PreparedStatement pstmt= db.prepareStatement(insert into hello values
('cKey', ?));
pstmt.setInt(1,intVar);
pstmt.execute();


This also worked, include the function directly, skip the java field.  Nice.

pstmt.setInt(1,f1());


Makes sense, because the setInt() API says that the value resides in the
Java world.

Sets the designated parameter to the given Java int value.


I have about 250 source files on my machine that use PreparedStatements.
I wrote a few of them, but usually by grabbing code from the web.
The rest are demos or parts of systems I used for some of my work.
Used embedded Derby = JavaDB back then - history now.
The fact that the code contained a few PreparedStatements was not what I
focused on.
The code just worked, doing what I needed.

My new understanding now allows me to do a ton of good NLP stuff.

Back to the future.

Thanks all,

 - Bob


On Mon, Jan 28, 2013 at 2:22 PM, Edson Richter edsonrich...@hotmail.comwrote:

  I would recommend the reading of the excellent The Java Tutorial, that
 has a very well explained section about JDBC:
 http://docs.oracle.com/javase/tutorial/jdbc/index.html


 and the chapter about PreparedStatements:
 http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html


 Regards,

 Edson Richter


 Em 28/01/2013 17:09, Bob Futrelle escreveu:

 Thanks to Edson and Dave for lightning responses.
 I'm confident that your answers will do the job.
 I'll follow up on the advice AFTER I get my coffee ;-)

   I'm really focused on the NLP content of my research,
 but I need a DB infrastructure to do it right.
 (Not a bunch of files as in ancient times.)

   --- Bob



 On Mon, Jan 28, 2013 at 1:59 PM, Edson Richter 
 edsonrich...@hotmail.comwrote:

  You have two options:

 st.execute(insert into hello values ('bKey', +f1()+));

 or

 PreparedStatement st = db.prepareStatement(insert into hello values
 ('bKey', ?));
 st.setInteger(1, f1());


 where 1 is the first parameter, 2 is the second parameter, and so on.


 Regards,

 Edson Richter



 Em 28/01/2013 16:50, Bob Futrelle escreveu:

 Here's a small, but complete code example - Bob

  package us.tsos.dbs.pg;

  import java.sql.*;

  /**
  * This is an effort to get a computed value from a Java function
  * (or data object) included properly in the VALUES entries.
  * So, how can I declare an SQL variable and set its value to some Java
 value?
  * Results viewed in pgAdmin3  with query 'select * from public.hello'.
  *
  * Jar in classpath is postgresql-9.2-1002.jdbc4.jar
  *
  * @version 0.1  Mon Jan 28 EST 2013
  * @author Bob Futrelle
  */

  public class JDBCVariableTest {

  Connection db;
  Statement st;
  Boolean boo;

  public static void main(String[] args) throws SQLException {

  JDBCVariableTest testIt = new JDBCVariableTest();
  testIt.helloVariables();
  }

  public int f1() { return 3;}

  public void helloVariables() throws SQLException  {

  int intVar = f1();  // OK in Java, but SQL/JDBC?

  try {
  db = DriverManager.getConnection(jdbc:postgresql:Articles,
 robertfutrelle, my pw);
  st = db.createStatement();
  boo  = st.execute(CREATE TABLE IF NOT EXISTS hello ( Name VARCHAR NOT
 NULL PRIMARY KEY, value int));

  // Declare   ??
   // INSTEAD OF THE LITERAL 4 VALUE (which works)
  // how do I declare a variable and assign the f1() return value to it
  // and then include it so the value 3 appears in the inserted record?
   //st.execute(insert into hello values('aKey',4));
  st.execute(insert into hello values('bKey',4));
  } catch (SQLException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
  }
  }
 }



 On Mon, Jan 28, 2013 at 8:39 AM, Dave Cramer p...@fastcrypt.com wrote:

 Bob,

  Can you provide a snippet of code so I can understand what you mean by
 declare ?


 Dave Cramer

 dave.cramer(at)credativ(dot)ca
 http://www.credativ.ca


 On Mon, Jan 28, 2013 at 7:11 AM, Bob Futrelle bob.futre...@gmail.comwrote:

 Do the declare statements and insert all have to be done in one
 statement execute()?
 That is, what is the scope of variables I declare?
 I see a variety of syntax examples, some for older versions?
 I'm using pg 9.2.2, so what are the rules/syntax for declaring and
 using variables?

  Use case: I collect metadata from XML articles such as title,
 journalName, volume, year.  For each article I need to create a pg table
 record and insert the values for the various metadata items in the article.

  This is my first post to this list.

  Thanks,

   - Bob

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

[Emi Lu]

Yeah, I agree this is the right answer here, since you're using JDBC. By
the time you get a String from the MySQL driver, it's already in Java's
2-bytes-per-char format. And the Postgres driver will deal with the
encoding on the output side. So the code I provided won't help you. I'm
afraid I don't know about Mybatis, but if it's built on JDBC I'd think
you've just got a configuration problem with what encoding the client
expects at either end.

From: Tom lane
I was wondering if the problem wasn't lots simpler than that. Is the
character the OP is trying to convert actually part of LATIN1?


First, the data loading is from psql(unix) to mysql(Unix). Second, DB 
transactions are through JAVA+MyBatis.


Steps:
(1) Read utf8.data@psql from psql.xml into java.ArrayListBean

(2) For each list.rec, save into mysql@latin through mysql.xml

Tried jdbc:mysql://.../mysql_db?...unicode...encoding...=ISO... No. 
This does not work.


For now, through the following method, all letters are correctly 
transformed except È.


What does OP stand for?

Emi
--
public static String utf8_to_latin1(String str)
throws Exception
{
   try
   {
  if(str.indexOf(È)=0)
  {
 str = str.replaceAll(È, E);
  }
  byte[] convertStringToByte = str.getBytes(UTF-8);
  str = new String(convertStringToByte, ISO-8859-1);
  return str;
   }catch(Exception e)
   {
  log.error(utf8_to_latin1 Error:  + e.getMessage());
  log.error(e);
  throw e;
   }
}


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

[Adrian Klaver]

On 12/14/2012 06:06 AM, Emi Lu wrote:




What does OP stand for?


Original Poster.



Emi
--




--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

[Emi Lu]

On 12/14/2012 09:49 AM, Adrian Klaver wrote:

Original Poster

Thanks. And more info:
Mysql
+--++
| Variable_name| Value  |
+--++
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database   | latin1 |
| character_set_filesystem | binary |
| character_set_results| latin1 |
| character_set_server | latin1 |
| character_set_system | utf8   |
+--++
SHOW VARIABLES LIKE character\_set\_database;
+++
| Variable_name  | Value  |
+++
| character_set_database | latin1 |
+++

Psql everywhere is utf8.

Where could be the problem located?

Also by using the java encoding methods, all characters except È are 
transformed correctly.


Thanks alot!
Emi
--
public static String utf8_to_latin1(String str)
   throws Exception
   {
  try
  {
 if(str.indexOf(È)=0)
 {
str = str.replaceAll(È, E);
 }
 byte[] convertStringToByte = str.getBytes(UTF-8);
 str= new String(convertStringToByte, 
ISO-8859-1);

 return str;
  }catch(Exception e)
  {
 log.error(utf8_to_latin1 Error:  + e.getMessage());
 log.error(e);
 throw e;
  }
   }



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

[Tom Lane]

Emi Lu em...@encs.concordia.ca writes:
 For now, through the following method, all letters are correctly 
 transformed except È.

Meh.  That character renders as \310 in your mail, which is not an
assigned code in ISO 8859-1.  The numerically corresponding Unicode
value would be U+0090, which is an unspecified control character.

I surmise that your source data is not actually either Unicode or
ISO 8859-1, but one of the random extended character sets that
Microsoft has loosed upon the world, perhaps windows-1252
http://en.wikipedia.org/wiki/Windows-1252

The conversion code that you're using is quite right to reject the
character as not being valid LATIN1.  What you need to do is figure out
what the data actually is and correct its encoding.  It's evidently
stored wrong in the UTF8 data, if you believe that this code is a
letter.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

[Tom Lane]

I wrote:
 Meh.  That character renders as \310 in your mail, which is not an
 assigned code in ISO 8859-1.  The numerically corresponding Unicode
 value would be U+0090, which is an unspecified control character.

Oh, scratch that, apparently I can't do hex/octal arithmetic in my
head first thing in the morning.  It's really U+00C8 which is perfectly
valid.  I can't see a reason why that character and only that character
would be problematic --- have you done systematic testing to confirm
that that's the only should-be-LATIN1 character that fails?

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

[Adrian Klaver]

On 12/14/2012 07:35 AM, Tom Lane wrote:

I wrote:

Meh.  That character renders as \310 in your mail, which is not an
assigned code in ISO 8859-1.  The numerically corresponding Unicode
value would be U+0090, which is an unspecified control character.


Oh, scratch that, apparently I can't do hex/octal arithmetic in my
head first thing in the morning.  It's really U+00C8 which is perfectly
valid.  I can't see a reason why that character and only that character
would be problematic --- have you done systematic testing to confirm
that that's the only should-be-LATIN1 character that fails?


This is where I am confused, in one of the original posts the OP said:

JAVA codes work for most of characters, but not -È.



regards, tom lane





--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

[Emi Lu]

Hello All,

Meh.  That character renders as \310 in your mail, which is not an
assigned code in ISO 8859-1.  The numerically corresponding Unicode
value would be U+0090, which is an unspecified control character.


Oh, scratch that, apparently I can't do hex/octal arithmetic in my
head first thing in the morning.  It's really U+00C8 which is perfectly
valid.  I can't see a reason why that character and only that character
would be problematic --- have you done systematic testing to confirm
that that's the only should-be-LATIN1 character that fails?


Finally, the problem is resolved:

SHOW VARIABLES LIKE character\_set\_%;
+--++
| Variable_name| Value  |
+--++
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database   | latin1 |
| character_set_filesystem | binary |
| character_set_results| latin1 |
| character_set_server | latin1 |
| character_set_system | utf8   | -- here mysql uses utf8 for 
character_set_system.


Change my java code to:

public static String utf8_to_mysql(String str)
   throws Exception
   {
  try
  {
 byte[] convertStringToByte = str.getBytes(UTF-8);
 str= new String(convertStringToByte, 
UTF-8);

 return str;
  }catch(Exception e)
  {
 log.error(utf8_to_latin1 Error:  + e.getMessage());
 log.error(e);
 throw e;
  }

Have to explicitly specify UTF-8, but cannot leave as empty.

Larry's comments(from MyBatis mailing list) and I tried both from/to 
by UTF8. It works. This is still little bit strange to me. But it works!


 My guess is that it's correct but the client you're using is messing
 it up. If not, then you need to look at your connection strings to
 the 2 databases to make sure they are handling the encodings
 correctly.Unless you set them specifically, I suspect they are using
 your default system encoding - so both may be using utf8 or iso8859.

Thank you very much for all of your help for this!
Emi



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

[Emi Lu]

On 12/14/2012 01:37 PM, Emi Lu wrote:

Hello All,

Meh.  That character renders as \310 in your mail, which is not an
assigned code in ISO 8859-1.  The numerically corresponding Unicode
value would be U+0090, which is an unspecified control character.


Oh, scratch that, apparently I can't do hex/octal arithmetic in my
head first thing in the morning.  It's really U+00C8 which is perfectly
valid.  I can't see a reason why that character and only that character
would be problematic --- have you done systematic testing to confirm
that that's the only should-be-LATIN1 character that fails?


Finally, the problem is resolved:

SHOW VARIABLES LIKE character\_set\_%;
+--++
| Variable_name| Value  |
+--++
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database   | latin1 |
| character_set_filesystem | binary |
| character_set_results| latin1 |
| character_set_server | latin1 |
| character_set_system | utf8   | -- here mysql uses utf8 for
character_set_system.
Another try is that if I change my client tool encoding set, I do not 
even need my java transition. All right, good to learn from this.


Emi



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

[Emi Lu]

Is there a simple way to load UTF8 data in psql to mysql(with latin1
encoding) through JDBC?


All you need to do is to query the source database, then use
ResultSet.getString() to obtain the data and use a PreparedStatement and
PreparedStatement.setString() to insert/update  the data on the target
database.

The JDBC drivers will handle all the conversion.
Do NOT manually convert the data.

getString() and setString() will do everything correctly.


I am not using stmt directly but through Mybatis for all db transactions.

So, this approach will not work.

Thanks.
--
Emi




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

[Edson Richter]

Em 13/12/2012 12:00, Emi Lu escreveu:



Is there a simple way to load UTF8 data in psql to mysql(with latin1
encoding) through JDBC?


All you need to do is to query the source database, then use
ResultSet.getString() to obtain the data and use a PreparedStatement and
PreparedStatement.setString() to insert/update  the data on the target
database.

The JDBC drivers will handle all the conversion.
Do NOT manually convert the data.

getString() and setString() will do everything correctly.


I am not using stmt directly but through Mybatis for all db transactions.


Should not this a Mybatis problem instead?
As stated, JDBC drivers does all the conversion needed automatically, 
but if you have a middleware messing with your enconding, then the 
problem is the middleware, not databases or drivers.


Edson



So, this approach will not work.

Thanks.
--
Emi








--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

[Emi Lu]

I don't think your Java code does what you think it does. You should
read some more about how Java handles string encodings. Here is a method
I wrote some years ago that might also help you. It converts streams,
not strings, but what you need should be pretty close (and simpler):
   /**
* Interprets in according to encIn, and converts it to encOut,
* writing to out. Allocates buffer for the buffer size.
* @param encIn The input encoding.
* @param encOut The output encoding.
* @param in The data to convert.
* @param out Where to send the converted data.
* @param buffer The size of the buffer or 0 for the default.
* @throws IOException
*/
   public void run(String encIn, String encOut, InputStream in,
OutputStream out, int buffer) throws IOException {
 Reader r = null;
 Writer w = null;
 int len;
 char[]  b;
 try {
   if (buffer  0) {
 r = new BufferedReader(new InputStreamReader(in, encIn), buffer);
 w = new BufferedWriter(new OutputStreamWriter(out, encOut),
buffer);
   } else {
 r = new BufferedReader(new InputStreamReader(in, encIn));
 w = new BufferedWriter(new OutputStreamWriter(out, encOut));
 buffer = DEFAULT_BUFFER_SIZE;
   }
   b = new char[buffer];

   while ((len = r.read(b, 0, buffer)) != -1) {
 w.write(b, 0, len);
   }
 } finally {
   try {
 if (r != null) r.close();
   } finally {
 if (w != null) w.close();
   }
 }
   }
Btw, none of this has anything to do with Postgres. :-)
Thank you for the code first. I will try it later. The problem I had as 
mentioned in the subject is:

(1) psql@utf8
(2) mysql@latin1

When I load data from (1) to (2) through Mybatis, french characters 
could not be mapped correctly in (2). I was thinking that psql may have 
methods could help this. But it seems that I have to try from java 
coding side :-(


--
Emi


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

[Paul Jungwirth]

 The JDBC drivers will handle all the conversion.
 Do NOT manually convert the data.

Yeah, I agree this is the right answer here, since you're using JDBC. By
the time you get a String from the MySQL driver, it's already in Java's
2-bytes-per-char format. And the Postgres driver will deal with the
encoding on the output side. So the code I provided won't help you. I'm
afraid I don't know about Mybatis, but if it's built on JDBC I'd think
you've just got a configuration problem with what encoding the client
expects at either end.

Paul


-- 
_
Pulchritudo splendor veritatis.

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

[Thomas Kellerer]

Emi Lu wrote on 13.12.2012 15:00:



Is there a simple way to load UTF8 data in psql to mysql(with latin1
encoding) through JDBC?


All you need to do is to query the source database, then use
ResultSet.getString() to obtain the data and use a PreparedStatement and
PreparedStatement.setString() to insert/update  the data on the target
database.

The JDBC drivers will handle all the conversion.
Do NOT manually convert the data.

getString() and setString() will do everything correctly.


I am not using stmt directly but through Mybatis for all db transactions.

So, this approach will not work.


Then it's a problem of that MyBatis thing.

Thomas





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

[Tom Lane]

Paul Jungwirth p...@illuminatedcomputing.com writes:
 Yeah, I agree this is the right answer here, since you're using JDBC. By
 the time you get a String from the MySQL driver, it's already in Java's
 2-bytes-per-char format. And the Postgres driver will deal with the
 encoding on the output side. So the code I provided won't help you. I'm
 afraid I don't know about Mybatis, but if it's built on JDBC I'd think
 you've just got a configuration problem with what encoding the client
 expects at either end.

I was wondering if the problem wasn't lots simpler than that.  Is the
character the OP is trying to convert actually part of LATIN1?

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

[Emi Lu]

Is there a simple way to load UTF8 data in psql to mysql(with latin1
encoding) through JDBC?

JAVA codes work for most of characters, but not -È. Someone knows why 
the following codes cannot load -È to mysql@latin1?


Thanks a lot!

--
public static String utf8_to_latin1(String str)
   throws Exception
   {
  try
  {
 String stringToConvert = str;
 byte[] convertStringToByte = stringToConvert.getBytes(UTF-8);
 return new String(convertStringToByte, ISO-8859-1);
  }catch(Exception e)
  {
 log.error(utf8_to_latin1 Error:  + e.getMessage());
 log.error(e);
 throw e;
  }
   }


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

[Paul Jungwirth]

I don't think your Java code does what you think it does. You should read
some more about how Java handles string encodings. Here is a method I wrote
some years ago that might also help you. It converts streams, not strings,
but what you need should be pretty close (and simpler):

  /**
   * Interprets in according to encIn, and converts it to encOut,
   * writing to out. Allocates buffer for the buffer size.
   *
   * @param encIn The input encoding.
   * @param encOut The output encoding.
   * @param in The data to convert.
   * @param out Where to send the converted data.
   * @param buffer The size of the buffer or 0 for the default.
   *
   * @throws IOException
   */
  public void run(String encIn, String encOut, InputStream in, OutputStream
out, int buffer) throws IOException {
Reader r = null;
Writer w = null;
int len;
char[]  b;

try {
  if (buffer  0) {
r = new BufferedReader(new InputStreamReader(in, encIn), buffer);
w = new BufferedWriter(new OutputStreamWriter(out, encOut), buffer);
  } else {
r = new BufferedReader(new InputStreamReader(in, encIn));
w = new BufferedWriter(new OutputStreamWriter(out, encOut));
buffer = DEFAULT_BUFFER_SIZE;
  }
  b = new char[buffer];

  while ((len = r.read(b, 0, buffer)) != -1) {
w.write(b, 0, len);
  }
} finally {
  try {
if (r != null) r.close();
  } finally {
if (w != null) w.close();
  }
}
  }

Btw, none of this has anything to do with Postgres. :-)

Paul



On Wed, Dec 12, 2012 at 10:19 AM, Emi Lu em...@encs.concordia.ca wrote:


  Is there a simple way to load UTF8 data in psql to mysql(with latin1
 encoding) through JDBC?

  JAVA codes work for most of characters, but not -È. Someone knows why
 the following codes cannot load -È to mysql@latin1?

 Thanks a lot!

 --
 public static String utf8_to_latin1(String str)
throws Exception
{
   try
   {
  String stringToConvert = str;
  byte[] convertStringToByte = stringToConvert.getBytes(UTF-**8);
  return new String(convertStringToByte, ISO-8859-1);
   }catch(Exception e)
   {
  log.error(utf8_to_latin1 Error:  + e.getMessage());
  log.error(e);
  throw e;

   }
}


 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general




-- 
_
Pulchritudo splendor veritatis.

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

[Thomas Kellerer]

Emi Lu wrote on 12.12.2012 17:17:

Good morning,

Is there a simple way to load UTF8 data in psql to mysql(with latin1 encoding) 
through JDBC?


All you need to do is to query the source database, then use 
ResultSet.getString() to obtain the data and use a PreparedStatement and 
PreparedStatement.setString() to insert/update  the data on the target database.

The JDBC drivers will handle all the conversion.
Do NOT manually convert the data.

getString() and setString() will do everything correctly.

Thomas





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC connections very occasionally hang

[Tom Lane]

Karl Wright daddy...@gmail.com writes:
 ... By the end of that time I usually see between one and three stuck
 threads, all waiting inside the JDBC driver for a response from the
 postgresql server.  I can provide a stack trace if requested.

How about a stack trace from the connected backend?  And what is its
state as shown by the pg_stat_activity and pg_locks views?  It's hard
to tell from what you say here whether the problem is on the server or
client side, which is surely the first thing to isolate.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC Connection Errors

[Craig Ringer]

On 24/08/2011 6:47 AM, Sam Nelson wrote:

Hi list,

A client is hitting an issue with JDBC:
org.postgresql.util.PSQLException: Connection refused. Check that the
hostname and port are correct and that the postmaster is accepting
TCP/IP connections.

-pg_hba.conf is set to trust 0.0.0.0/0 (IPv4 only)
-listen_addresses is *
-I can find no evidence of iptables running on the server.
-PGAdmin connects just fine.
-psql connects just fine.
-I can find no errors in the log file from that day for the user that
the client is trying to log in as.
Get them to run Wireshark and capture the connection attempt, then send 
you the pcap log. That should give you more information about what's 
actually happening.


Check for possible SELinux involvement if the Java client is on Linux.

--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC Connection Errors

[Ian Pilcher]

On 08/28/2011 08:33 AM, Craig Ringer wrote:
 A client is hitting an issue with JDBC:
 org.postgresql.util.PSQLException: Connection refused. Check that the
 hostname and port are correct and that the postmaster is accepting
 TCP/IP connections.

 -pg_hba.conf is set to trust 0.0.0.0/0 (IPv4 only)

Does it work with -Djava.net.preferIPv4Stack=true is specified on the
client command line (or if the property is otherwise set)?  If so, the
problem is that many JVMs default to using IPv6 if available.

-- 

Ian Pilcher arequip...@gmail.com
If you're going to shift my paradigm ... at least buy me dinner first.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC Connection Errors

[Guillaume Lelarge]

On Tue, 2011-08-23 at 19:38 -0400, Dave Cramer wrote:
 The only difference JDBC has over psql is that it has to connect via
 tcpip. Not sure about pgadmin.
 

pgAdmin is like psql on this. It can use TCP connections, and socket
connections.


-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC Connection Errors

[Thomas Markus]

Hi,

- check for open server socket: netstat -tulpen | grep postgres
- try to force ipv4 for java with system property (a recent jre prefers 
ipv6): -Djava.net.preferIPv4Stack=true


regards
Thomas

Am 24.08.2011 00:47, schrieb Sam Nelson:

Hi list,

A client is hitting an issue with JDBC:
org.postgresql.util.PSQLException: Connection refused. Check that the
hostname and port are correct and that the postmaster is accepting
TCP/IP connections.

-pg_hba.conf is set to trust 0.0.0.0/0 (IPv4 only)
-listen_addresses is *
-I can find no evidence of iptables running on the server.
-PGAdmin connects just fine.
-psql connects just fine.
-I can find no errors in the log file from that day for the user that
the client is trying to log in as.

We're working on getting access to more details about how they're
trying to connect, but in the mean time, does anyone know if JDBC has
any issues connecting that psql and PGAdmin wouldn't have?  Is it
possible that JDBC is somehow susceptible to connection issues that
JDBC and psql are not?
---
===
Samuel Nelson
Consistent State
www.consistentstate.com
303-955-0509
===



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC Connection Errors

[Dave Cramer]

The only difference JDBC has over psql is that it has to connect via
tcpip. Not sure about pgadmin.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca




On Tue, Aug 23, 2011 at 6:47 PM, Sam Nelson s...@consistentstate.com wrote:
 Hi list,

 A client is hitting an issue with JDBC:
 org.postgresql.util.PSQLException: Connection refused. Check that the
 hostname and port are correct and that the postmaster is accepting
 TCP/IP connections.

 -pg_hba.conf is set to trust 0.0.0.0/0 (IPv4 only)
 -listen_addresses is *
 -I can find no evidence of iptables running on the server.
 -PGAdmin connects just fine.
 -psql connects just fine.
 -I can find no errors in the log file from that day for the user that
 the client is trying to log in as.

 We're working on getting access to more details about how they're
 trying to connect, but in the mean time, does anyone know if JDBC has
 any issues connecting that psql and PGAdmin wouldn't have?  Is it
 possible that JDBC is somehow susceptible to connection issues that
 JDBC and psql are not?
 ---
 ===
 Samuel Nelson
 Consistent State
 www.consistentstate.com
 303-955-0509
 ===

 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC Connection Errors

[Adrian Klaver]

On Tuesday, August 23, 2011 3:47:33 pm Sam Nelson wrote:
 Hi list,
 
 A client is hitting an issue with JDBC:
 org.postgresql.util.PSQLException: Connection refused. Check that the
 hostname and port are correct and that the postmaster is accepting
 TCP/IP connections.
 
 -pg_hba.conf is set to trust 0.0.0.0/0 (IPv4 only)
 -listen_addresses is *
 -I can find no evidence of iptables running on the server.
 -PGAdmin connects just fine.
 -psql connects just fine.

Are PgAdmin and psql local and the Java client remote?
Is the Java client connecting using IPv6 ?

 -I can find no errors in the log file from that day for the user that
 the client is trying to log in as.
 
 We're working on getting access to more details about how they're
 trying to connect, but in the mean time, does anyone know if JDBC has
 any issues connecting that psql and PGAdmin wouldn't have?  Is it
 possible that JDBC is somehow susceptible to connection issues that
 JDBC and psql are not?
 ---
 ===
 Samuel Nelson
 Consistent State
 www.consistentstate.com
 303-955-0509
 ===

-- 
Adrian Klaver
adrian.kla...@gmail.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC Connection Errors

[Adrian Klaver]

On Tuesday, August 23, 2011 6:58:13 pm Sam Nelson wrote:
 Everything is remote.  I thought of the IPv6 thing, but that seems
 unlikely - all connections are coming from the same system.

The easy way to test is to throw in an IPv6 rule that matches the IPv4 rule 
into 
pg_hba.conf.

 
 Still, we'll ask them and try to get some more details about things like
 that. ---

-- 
Adrian Klaver
adrian.kla...@gmail.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC Connection Errors

[Sam Nelson]

Everything is remote.  I thought of the IPv6 thing, but that seems
unlikely - all connections are coming from the same system.

Still, we'll ask them and try to get some more details about things like that.
---
===
Samuel Nelson
Consistent State
www.consistentstate.com
303-955-0509
===



On Tue, Aug 23, 2011 at 5:46 PM, Adrian Klaver adrian.kla...@gmail.com wrote:
 On Tuesday, August 23, 2011 3:47:33 pm Sam Nelson wrote:
 Hi list,

 A client is hitting an issue with JDBC:
 org.postgresql.util.PSQLException: Connection refused. Check that the
 hostname and port are correct and that the postmaster is accepting
 TCP/IP connections.

 -pg_hba.conf is set to trust 0.0.0.0/0 (IPv4 only)
 -listen_addresses is *
 -I can find no evidence of iptables running on the server.
 -PGAdmin connects just fine.
 -psql connects just fine.

 Are PgAdmin and psql local and the Java client remote?
 Is the Java client connecting using IPv6 ?

 -I can find no errors in the log file from that day for the user that
 the client is trying to log in as.

 We're working on getting access to more details about how they're
 trying to connect, but in the mean time, does anyone know if JDBC has
 any issues connecting that psql and PGAdmin wouldn't have?  Is it
 possible that JDBC is somehow susceptible to connection issues that
 JDBC and psql are not?
 ---
 ===
 Samuel Nelson
 Consistent State
 www.consistentstate.com
 303-955-0509
 ===

 --
 Adrian Klaver
 adrian.kla...@gmail.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC driver throwing error while connecting to the slave server for partitioned table

[Merlin Moncure]

On Mon, Aug 8, 2011 at 8:14 AM, sameera vhora vhorasameera...@gmail.com wrote:
  We are facing below issue after creating slony replication of
 partitioning table in edb server 8.3.

  This issue persist only on slave one not the primary one.

  Below logs we found in tomcats.


  Error in postgresql driver initialization:
  com.edb.util.PSQLException: The connection attempt failed.
  at 
 com.edb.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:176)
  at com.edb.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
  at 
 com.edb.jdbc2.AbstractJdbc2Connection.init(AbstractJdbc2Connection.java:153)
  at 
 com.edb.jdbc3.AbstractJdbc3Connection.init(AbstractJdbc3Connection.java:30)
  at com.edb.jdbc3.Jdbc3Connection.init(Jdbc3Connection.java:24)
  at com.edb.Driver.makeConnection(Driver.java:385)
  at com.edb.Driver.connect(Driver.java:260)
  at java.sql.DriverManager.getConnection(DriverManager.java:582)

try checking the database logs?

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general