Re: 4gl return value problem

"June C. Hunt" <june_c_hunt@hotmail.com> wrote in message news:<c3ud16$9r8$1@terabinaries.xmission.com>... > daisy wrote: > > > >"June C. Hunt" <june_c_hunt@hotmail.com> wrote in message > >news:<2sd8c.52394$Fh4.329@twister.nyroc.rr.com>... > > > daisy wrote: > > > > I had a question occurred when my 4gl main program calling a > > > > function. There seems to be something wrong with the return value > > > > > > > > Statements in my main program : > > > > > > > > ud_fun() > > > > ........... > > > > let p=1 > > > > while p > > > > if del_fun() then exit while end if > > > > let p=0 > > > > end while > > > > if p then > > > > rollback work > > > > prompt "error... " for char answer > > > > end if > > > > ............ > > > > end function > > > > > > > > > > > > del_fun() > > > > ........ > > > > if m_ac31('2',n.acm02,n.acm00,n.acm01, > > > > o[i].acn02 thru o[i].acn15) then > > > > display "m_ac31 error ",o[i].acn02 at 21,1 > > > > sleep 5 > > > > return 1 > > > > end if > > > > .................. > > > > end function > > > > > > > > statements in my function > > > > > > > > m_ac31() > > > > ............... > > > > if status or sqlca.sqlerrd[3]<1 then > > > > display "Upd ac31 err-",status > > > > sleep 5 > > > > return 1 > > > > end if > > > > ...... > > > > > > > > > > > > My problem is that I 'sometimes ' found that the m_ac31 > > > > function was performed successfully (because there is no error message > > > > displayed ) > > > > but the results comed out wrong ; which means there were error > > > > during the function was called but my main program did not catch the > > > > return value .Could any one tell me where the problem is ? > > > > > > > > My Informix was old SE. I do not know what is the problem in my > > > > program. > > > > > > Since you don't specify what you mean when you say the results are > wrong, > > > I'm just guessing at where to start. Was data changed that shouldn't > have > > > been? Was data skipped? Were one or more unexpected records changed? > Were > > > one or more expected records missed while others were selected? Simply > > > saying the results are "wrong" leaves a whole lot of possibilities. > > > > > > Without knowing exactly what "wrong" means, my first suggestion would be > to > > > change the error checking in m_ac31. I would check SQLCA.SQLCODE rather > > > than STATUS - or check both. The STATUS variable will indicate errors > from > > > both the form and from SQL statements whereas SQLCA.SQLCODE indicates > errors > > > (or success) of just the SQL statements. Since you only show the error > > > checking, we can't be sure what comes before it in your code; I'd be > more > > > comfortable checking the SQLCA record. I would also be inclined to > evaluate > > > SQLCA.SQLERRD[3] against a known value. For example, I would find out > how > > > many records I expect to process, and would compare SQLCA.SQLERRD[3] > against > > > that value rather than zero. Is it possible that the problem isn't > that no > > > records were processed but rather more records than you were expecting > were > > > processed? With these changes, at least you'd be sure you were checking > the > > > results of the last SQL statement and that you were processing as many > > > records as you were expecting. Note that this doesn't necessarily mean > the > > > correct records were processed, but at least the actual number of > records > > > can be determined. > > > > > >Sorry that I did not describe my question clearly. > >The statemenst in m_ac31 were as follows > > update ac31 set acp05=acp05-damt, > > acp06=acp06-camt, > > acp07=acp07-amt, > > acp08=acp08-amt, > > acp09=acp09-ramt > > where acp00=x.acn00 and acp01=x.acn04 and> >acp02=ym > > and acp03=dno and acp04=x.acn05 > > if status or sqlca.sqlerrd[3]<1 then > > let damt=damt*-1 > > let camt=camt*-1 > > let amt=amt*-1 > > let ramt=ramt*-1 > > insert into ac31 values> > > >(x.acn00,x.acn04,ym,dno,x.acn05,damt,camt, > > amt,amt,ramt) > > if status or sqlca.sqlerrd[3]<1 then > > display "Upd ac31 err-",status > > sleep 5 > > return 1 > > end if > > end if > > > > What bothered me was that sometimes I found that the record to be > >updated were actually exist, but after the program were completed and > >check the result > >I found that the record were not updated at all and the return value > >did not pass to the calling procedure (in my situation that's function > >del_fun()). > > > > Once I manually cleard the wrong result and I run the program again, > >but it > >comed out right . I don't know if it's problem of file lock , or is > >there any > >mistakes in my program. > > > > Please give me some advice . > > Thanks again > > No obvious mistakes in the program - what we can see of it, anyway. Is it > possible that the UPDATE was rolled back as a part of a larger group of > transactions in the ud_fun function? I'm also a little curious about the > purpose of the WHILE loop around that function call, but for the moment will > assume that you are showing just a portion of that code. What else is going > on between the BEGIN WORK/COMMIT WORK in that function? > > If it were a locking issue, I'd exect it to be caught with the > SQLCA.SQLERRD[3]<1 check (assuming that a single record is being identified > by the WHERE clause and that multiple records aren't being updated). Even > so, I prefer to lock the record myself before attempting an update - just to > be sure that I've got it. > > So - without seeing the rest of the code, my best guess is that the > transaction was rolled back. What else is going on in ud_fun? here are statements of ud() function ud() ............ begin work #file ac31 and ac32 are to be updated in del_fun() and m_ac31() lock table ac31 in share mode lock table ac32 in share mode let p=1 while p if del_fun() then exit while end if if ad_fun() then exit while end if let p=0

daisy wrote: > "June C. Hunt" <june_c_hunt@hotmail.com> wrote: >> [...various exchanges back and forth about a question by Daisy...] > > In function m_ac31() > while updating record in fie ac31 and ac32 , I did check > status and sqlca.sqlerrd[3]< 1 > 1.suppose the program did rollback work , why the error message > showed > "update failed " in main program never appeared? > 2. suppose the situation was as what mentioned above " rollback > as a part of a larger group of transactions in the ud_fun function", > how to check this > situation ? > 3.How to lock the record myself before attempting an update to > make sure > I've got it ? > > thanks fore your message I've not seen detailed platform and version information yet. Detailed means down to the last character in the version number of I4GL. Are you using the c-code compiler or the p-code compiler (I4GL-RDS). It might be relevant to know the database server type (SE, OnLine, IDS, etc) and version, but probably isn't. Did you move the code from an old version? Which old version? The program didn't do a rollback unless you programmed it to do so. The message in main may not have appeared because STATUS got clobbered (zeroed) too soon - version information could be critical here. ROLLBACK can't be part of a larger group of transactions; transactions do not nest. You need to explain your concern more clearly (and I do recognize that English is not your mother tongue). You lock records by using FETCH on a cursor with the FOR UPDATE qualifier - or by using a MODE ANSI database. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

daisy wrote: > "June C. Hunt" <june_c_hunt@hotmail.com> wrote: > > daisy wrote: > > >"June C. Hunt" <june_c_hunt@hotmail.com> wrote: > > > > daisy wrote: > > > > > I had a question occurred when my 4gl main program calling a > > > > > function. There seems to be something wrong with the return value > > > > > > > > > >[I'm clipping and condensing the program sample and hope to summarize the issue.... I hope I don't mess up the format or content too badly. --JCH] Sample code from the program: function ud() ............ begin work #file ac31 and ac32 are to be updated in del_fun() and m_ac31() lock table ac31 in share mode lock table ac32 in share mode let p=1 while p if del_fun() then exit while end if if ad_fun() then exit while end if let p=0 end while if p then rollback work prompt "update failed . . " for char answer else commit work prompt "update successfully " for char answer end if end function function del_fun() ........ if m_ac31('2',n.acm02,n.acm00,n.acm01, o[i].acn02 thru o[i].acn15) then display "m_ac31 error ",o[i].acn02 at 21,1 sleep 5 return 1 end if .................. end function function m_ac31() ............... update ac31 set acp05=acp05-damt, acp06=acp06-camt, acp07=acp07-amt, acp08=acp08-amt, acp09=acp09-ramt where acp00=x.acn00 and acp01=x.acn04 and acp02=ym and acp03=dno and acp04=x.acn05 if status or sqlca.sqlerrd[3]<1 then let damt=damt*-1 let camt=camt*-1 let amt=amt*-1 let ramt=ramt*-1 insert into ac31 values (x.acn00,x.acn04,ym,dno,x.acn05,damt,camt,amt,amt,ramt) if status or sqlca.sqlerrd[3]<1 then display "Upd ac31 err-",status sleep 5 return 1 end if end if....... end function As I understand the issue, on occasion, the program is run and appears to execute successfully but interrogation of the data shows unexpected results. Daisy is asking why the program might complete without displaying any of the error messages that are coded. Responding to Daisy's current questions: > 1.suppose the program did rollback work , why the error message > showed > "update failed " in main program never appeared? If this is the only place in function ud() where ROLLBACK WORK exists and that code were executed, you should have received the "update failed..." prompt. > 2. suppose the situation was as what mentioned above " rollback > as a part of a larger group of transactions in the ud_fun function", > how to check this > situation ? The original code sample did not show the full set of code between the BEGIN WORK/COMMIT WORK statements. Only the call to del_fun() was shown. I was allowing for the fact that there might be other statements that might cause a ROLLBACK WORK. As seen in the current example above, there are two function calls that might cause this - del_fun() and ad_fun(). The original post suggested that the problem exists in del_fun(). My point is that del_fun() may have worked perfectly; it might be ad_fun() that causes a rollback. To know which function caused the rollback, you would have to make sure that there was a difference in the error messages displayed in del_fun() and ad_fun() - and any other called functions - to help you identify which one failed. > 3.How to lock the record myself before attempting an update to > make sure > I've got it ? As Jonathan described, you would declare a cursor (SELECT ... FOR UPDATE) and FETCH the record. And that raises a question about the LOCK TABLE statements. Just seems like overkill if you are updating a single record. Also, without any error checking, you don't know if the lock was successful! So, to summarize, I would still recommend that you check SQLCA.SQLCODE rather than STATUS. Make sure that you've got unique error messages in each function to identify their location. If you are going to continue to lock the table rather than an individual record, check for a successful lock. Better still, lock the individual record - and check for a successful lock. One last thing that I liked to do was run the program (in a test environment) with 'WHENEVER ERROR CONTINUE' commented out. It often showed problems that were masked by the 'WHENEVER' and/or not accounted for with SQL error checking. Sometimes the error occurs where you are leastexpecting it; keep an open mind when looking for problems in the code. -- June Hunt