Thread: JDBC: pl/pgsql function runs but does not do anything
I have a PL/pgsql function called "move_records" that does not take any parameters and does not return any results. It performsinserts on a few tables (as the name suggests, it copies records from one table to another). When I run this function using pgAdminIII's query window, using the command "select move_records()", it executes correctlyand as expected. However, when I call the function via JDBC, it runs, but appears to do nothing. It's as if nothing it does is getting committed.The relevant Java code is: CallableStatement statement = connection.prepareCall("{call move_records()}"); statement.execute(); At first I thought it was not running at all, so I put in "RAISE INFO" statements to see if it was actually working -- andthose did indeed print statements. So I know for sure it is actually running. I tried commenting out different thingsin case it was failing silently, but that made no difference. Running the stored procedure does not return any errorsor warnings. There must be something really obvious I'm overlooking? Any help would be greatly appreciated!
On Jul 13, 2009, at 10:23 AM, Rob W wrote: > At first I thought it was not running at all, so I put in "RAISE > INFO" statements to see if it was actually working -- and those did > indeed print statements. So I know for sure it is actually running. > I tried commenting out different things in case it was failing > silently, but that made no difference. Running the stored procedure > does not return any errors or warnings. > > There must be something really obvious I'm overlooking? Are you running inside a transaction that is being cancelled or not committed? John DeSoi, Ph.D.
--- On Mon, 7/13/09, John DeSoi <desoi@pgedit.com> wrote: > > Are you running inside a transaction that is being > cancelled or not committed? I was not explicitly beginning or ending a transaction. However, this response inspired me to try the obvious (in hindsight): connection.setAutocommit(true) And hey presto, it worked. The odd thing is that not one single example/tutorial I looked at anywhere explicitly sets autocommit,so figuring this out was a guessing game. On the bright side, I won't forget about setting autocommit to truein a hurry! Thanks for the nudge in the right direction.
Rob W wrote on 13.07.2009 16:46: > I was not explicitly beginning or ending a transaction. However, this > response inspired me to try the obvious (in hindsight): > > connection.setAutocommit(true) > > And hey presto, it worked. The odd thing is that not one single > example/tutorial I looked at anywhere explicitly sets autocommit, so figuring > this out was a guessing game. On the bright side, I won't forget about > setting autocommit to true in a hurry! I guess those examples have a connection.commit() somewhere ;) Thomas