Обсуждение: problem with stored procedure ,transaction and jdbc
Hello everyone,
I have a probem with stored procedure ,transaction and JDBC.
i am confused with the relation between stored procedure, transcation
and jdbc.
supposed I have code looks like:
set autocommit to false
call strored procedure 1
call strored procedure 2
when I call the second stored procedure, exception raised
so I close the connection and the callable statement.
My question is can I enclose the two stored procedure in a parent
transcaton
use JDBC ? If not , why?
Regards
Jiang
Yes you can enclose two stored procedures inside a transaction. Can you call the second one on it's own without the first one without getting an exception ? Dave On 12-Aug-05, at 12:47 PM, Jiangyi wrote: > Hello everyone, > I have a probem with stored procedure ,transaction and JDBC. > i am confused with the relation between stored procedure, > transcation and jdbc. > > supposed I have code looks like: > > set autocommit to false > call strored procedure 1 > call strored procedure 2 > when I call the second stored procedure, exception raised > so I close the connection and the callable statement. > > My question is can I enclose the two stored procedure in a parent > transcaton > use JDBC ? If not , why? > > Regards > Jiang > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > >
have you considered to enclose both stored procedures in a transaction inside
another stored procedure, and call just one from the java code, something
like :
CREATE FUNCTION stored_procedure_3()....
BEGIN;
BEGIN TRANSACTION;
PERFORM .. call strored procedure 1
PERFORM .. call strored procedure 2
COMMIT;
END;
' LANGUAGE plpgsql;
and your java code would be :
set autocommit to false
call strored procedure 3
Just and idea... ok ? I'm pretty new to java and postgresql too, so ... you
know...
Hoe this help.
Andres
>On Friday 12 August 2005 05:09 pm, Dave Cramer wrote:
> Yes you can enclose two stored procedures inside a transaction.
>
> Can you call the second one on it's own without the first one without
> getting an exception ?
>
> Dave
>
> On 12-Aug-05, at 12:47 PM, Jiangyi wrote:
> > Hello everyone,
> > I have a probem with stored procedure ,transaction and JDBC.
> > i am confused with the relation between stored procedure,
> > transcation and jdbc.
> >
> > supposed I have code looks like:
> >
> > set autocommit to false
> > call strored procedure 1
> > call strored procedure 2
> > when I call the second stored procedure, exception raised
> > so I close the connection and the callable statement.
> >
> > My question is can I enclose the two stored procedure in a parent
> > transcaton
> > use JDBC ? If not , why?
> >
> > Regards
> > Jiang
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
--
Andres Ledesma
=================
On Fri, 12 Aug 2005, Andres Ledesma wrote: > have you considered to enclose both stored procedures in a transaction inside > another stored procedure, and call just one from the java code, something > like : > > CREATE FUNCTION stored_procedure_3().... > BEGIN; > BEGIN TRANSACTION; > PERFORM .. call strored procedure 1 > PERFORM .. call strored procedure 2 > COMMIT; > END; > ' LANGUAGE plpgsql; > Postgresql does not allow transactions to be started/stopped inside of a backend function. It does support savepoints which do allow work to be saved and rolled back to, but the function call is still wrapped in an outer transaction. For the example above leaving the BEGIN TRANSACTION/COMMIT off may give you the behavior desired (because the query itself is wrapped in a transaction). Kris Jurka
On 12-Aug-05, at 10:30 PM, Jiangyi wrote:
> I call the first strored procedure to insert a master record, and
> call the second
> stored procedure once or more to insert detailed records. I will
> not call the
> second stored procedure if the first call failed. But there are
> situations the second
> call will fail because of the table constraints. So I wish if any
> of the call to the second
> stored procedure fails, rollback them all.
Yes, this is the way postgresql works, if anything fails inside the
transaction it will be rolled back
>
> I noticed that many example of postgresql jdbc stored procedure
> only set autocommit
> to false before calling but none of the example use commit or
> rollback in their code.
>
> I'am not certain about how to use transaction in jdbc along side
> stored procedure.
>
> try {
> conn.setAutoCommit(false);
> new_deal = conn.prepareCall(
> "{? = call new_deal(?)}");
> new_deal.setString(2, "data");
> new_deal.execute();
> int dealno = new_deal.getInt(1);
>
> new_deal_detail = conn.prepareCall(
> "{? = call new_deal_detail(?,?)}");
> for (int i = 0; i < num; i++) {
> new_deal_detail.registerOutParameter(1,
> Types.BIT); //jdbc bug? why Types.Boolean cannot be used?
What version of the driver are you using? Later ones will support
Boolean
> new_deal_detail.setString(2, "some data");
> new_deal_detail.setInt(3, dealno);
> new_deal_detail.execute();
> if (new_deal_detail.getBoolean(1)) {
> licenses.add(lics[i]);
> i++;
> } else {
> logger.warning("prelicense generated may be
> duplicated. regenerate it.");
> }
> }
> // conn.commit();
> } catch (SQLException ex) {
> logger.warning(ex.toString());
> // try {
> // conn.rollback();
> // } catch (SQLException ex1) {
> // logger.warning(ex1.toString());
> // }
> } finally {
> try {
> new_deal_detail.close();
> new_deal.close();
> conn.close();
> } catch (SQLException ex3) {
> logger.warning(ex3.toString());
> }
> }
>
> Here is my code, can it run as I will?
>
> Another question, can I use save point in JDBC? When I call
> conn.setSavepint(),
> the call raise a exception to tell me it is not supported?
Later versions of the driver should support save points.
>
> Regards
> Jiang
>
> 在 2005-8-13，上午1:09，Dave Cramer 写道：
>
>
>> Yes you can enclose two stored procedures inside a transaction.
>>
>> Can you call the second one on it's own without the first one
>> without getting an exception ?
>>
>> Dave
>> On 12-Aug-05, at 12:47 PM, Jiangyi wrote:
>>
>>
>>
>>
>>> Hello everyone,
>>> I have a probem with stored procedure ,transaction and JDBC.
>>> i am confused with the relation between stored procedure,
>>> transcation and jdbc.
>>>
>>> supposed I have code looks like:
>>>
>>> set autocommit to false
>>> call strored procedure 1
>>> call strored procedure 2
>>> when I call the second stored procedure, exception raised
>>> so I close the connection and the callable statement.
>>>
>>> My question is can I enclose the two stored procedure in a parent
>>> transcaton
>>> use JDBC ? If not , why?
>>>
>>> Regards
>>> Jiang
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 4: Have you searched our list archives?
>>>
>>> http://archives.postgresql.org
>>>
>>>
>>>
>>>
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>>
>>
>>
>
>
>