Re: problem with stored procedure ,transaction and jdbc
От | Dave Cramer |
---|---|
Тема | Re: problem with stored procedure ,transaction and jdbc |
Дата | |
Msg-id | 4EE11CA6-4533-44CB-B5FD-B99328A9C014@fastcrypt.com обсуждение исходный текст |
Ответ на | problem with stored procedure ,transaction and jdbc (Jiangyi <jiangyi@sjtu.edu.cn>) |
Список | 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 >> >> >> > > >
В списке pgsql-jdbc по дате отправления: