Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem calling stored function

Problem calling stored function

From
"Francesco Storti"
Date:
Hi to everyone,
I'm having this problem:
I have implemented a stored function that take as argument an integer, in its body execute some inserts and deletes depending on the argument, and returns 1 when ended.
I call from my java code the pgsql function in this way:
 

CallableStatement cs = con.prepareCall("{? = call comp(?)}");

cs.registerOutParameter(1, Types.INTEGER);

cs.setInt(2, min);

cs.execute();

Where comp is the name of the function and min is the argument passed.
When i run my java code, I obtain 1 as result, but the inserts and update are not performed.
From command line the stored function works, so it's not a problem of the stored function.
Is there a problem in performing insert and update in a stored function called by java code?
I use jdk 1.5, postgres 8.2 and JDBC4 postgresql driver 8.2-506
 
The connection with jdbc works, because I execute other insert not in this stored function within java code.
Thanks for your help and excuse me and my newbye...

Re: Problem calling stored function

From
Tom Lane
Date:
"Francesco Storti" <francesco.storti@gmail.com> writes:
> Is there a problem in performing insert and update in a stored function
> called by java code?

Should work.  Maybe you're rolling back the transaction later, or
something like that?

            regards, tom lane

Re: Problem calling stored function

From
"Francesco Storti"
Date:
2008/1/17, Tom Lane tgl@sss.pgh.pa.us:

Should work.  Maybe you're rolling back the transaction later, or
something like that?

                       regards, tom lane

 
Thank for the answer first ;)
I use rollback only if throwed an SQL exception.
Here is my code:
 

public static int ExecSP(DBParameters dbp, int min) throws Exception{

CallableStatement cs = null;

Connection con = null;

Exception errore = null;

int result =0;

boolean someError = false;

try {

con = DBUtils.connect(dbp);

cs = con.prepareCall("{? = call comp(?)}");

cs.registerOutParameter(1, Types.INTEGER);

cs.setInt(2, min);

cs.execute();

result = cs.getInt(1);

}

catch (SQLException e) {

errore = new Exception (""+e.getErrorCode()+ ": "+

"Error in executing stored procedures for minute: "+ min +" - " + e.getMessage());

con.rollback();

}

catch (Exception e) {

if (someError)

errore = e;

else

errore = new Exception ("Error in executing stored procedures for minute: " + min +" - "+e.getMessage());

con.rollback();

}

finally {

try {

if (cs != null)

cs.close();

}

catch (SQLException e) {}

cs = null;

try {

if (con != null)

con.close();

}

catch (SQLException e) {}

con = null;

}

if (errore != null)

throw errore;

else

return result;

}

 

Re: Problem calling stored function

From
tivvpgsqljdbc@gtech-ua.com
Date:
Francesco Storti wrote:
> 2008/1/17, Tom Lane tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>:
>
>
>     Should work.  Maybe you're rolling back the transaction later, or
>     something like that?
>
>                            regards, tom lane
>
>
>
> Thank for the answer first ;)
> I use rollback only if throwed an SQL exception.
> Here is my code:
>
May be you should commit before closing connection if everything's OK?

Re: Problem calling stored function

From
Oliver Jowett
Date:
Francesco Storti wrote:
> 2008/1/17, Tom Lane tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>:
>
>
>     Should work.  Maybe you're rolling back the transaction later, or
>     something like that?
>
>                            regards, tom lane
>
>
>
> Thank for the answer first ;)
> I use rollback only if throwed an SQL exception.

You never call commit() so the transaction will be rolled back when you
close the connection. (I'm assuming you have autocommit off, you didn't
include your connection setup code)

-O

Re: Problem calling stored function

From
"Francesco Storti"
Date:


2008/1/18, Oliver Jowett <oliver@opencloud.com>:
Francesco Storti wrote:
> 2008/1/17, Tom Lane tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>:
>
>
>     Should work.  Maybe you're rolling back the transaction later, or
>     something like that?
>
>                            regards, tom lane
>
>
>
> Thank for the answer first ;)
> I use rollback only if throwed an SQL exception.

You never call commit() so the transaction will be rolled back when you
close the connection. (I'm assuming you have autocommit off, you didn't
include your connection setup code)

-O
 
 
 
Confirmed that I'm a newbye...
As sed by Oliver and tivvpgsqljdbc i've missed the commit ...
Thanks a lot for your help!