Starting a transaction within a SP

Original Subject: Informix 2.80 CLI ODBC Driver Aik Khoon wrote: > > Hello, > > I have a problem using this ODBC driver Informix CLI 2.80 to > execute a stored procedure with transaction. e.g. > > my stored procedure do a update statement : > > create procedure updatetab(new integer) > begin work;> update.... > commit work; > end procedure Sorry Aik, I cannot answer your question. However, the code in your SP nudges me back onto my soapbox. While it is perfectly legal to start a transaction within a stored procedure I have always felt it is an invitation to a disaster. Most programmers start a transaction and execute explicit SQL statements intermixed with calls to stored procedures. This is especially true of folks working in a mode-ANSI database, where the BEGIN WORK statement has no meaning and everything they do is inside a big transaction (yes, peppered with the occasional COMMIT). Unless something has changed in 7.30, Informix is rather unforgiving of a BEGIN WORK when you are already within a transaction. Yet this kind of error (with a coresponding abort) is exactly what you are headed for if you place the BEGIN WORK inside the procedure. -- -- Jake (Collecting soapboxes as fast as I wear them out) +------------------------------------------------------------+ | The expedient performance of a task with excessive concern | | regarding its duration-to-completion engenders a virtual | | certainty of diminished benefit therefrom. | | -- Benjamin Franklin (but he said it in 3 words) | +------------------------------------------------------------+