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