Re: problem with stored procedure ,transaction and 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
>>
>>
>>
>
>
>