Skip site navigation (1) Skip section navigation (2)

Re: problem with stored procedure ,transaction and jdbc

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Jiangyi <jiangyi(at)sjtu(dot)edu(dot)cn>
Cc: List <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: problem with stored procedure ,transaction and jdbc
Date: 2005-08-13 11:51:25
Message-ID: 4EE11CA6-4533-44CB-B5FD-B99328A9C014@fastcrypt.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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
>>
>>
>>
>
>
>


In response to

pgsql-jdbc by date

Next:From: Michael AllmanDate: 2005-08-15 23:01:09
Subject: Re: Postgres XA support
Previous:From: Oliver JowettDate: 2005-08-12 22:10:38
Subject: Re: Timestamp changes committed to HEAD

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group