18 replies to this topic

[Rob Donovan]

Hi,

I'm a bit new to Oracle and I'm having a problem...

I have a logic that GLOBAL_LSCALLs 3 screens. One of these screens updates an oracle table.

After the 3 GLOBAL_LSCALLs (under some conditions) I want to ROLLBACK the changes made in the global functions.

However, the ROLLBACK does not seem to 'rollback'.

This is my logic...

GLOBAL_LSCALL(FUN01,0001)
GLOBAL_LSCALL(FUN02,0002)
GLOBAL_LSCALL(FUN03,0003)

IF #ERR = 1 THEN
  UMSG('ROLLBACK',-1)
  #RB = ROLLBACK()
ENDIF

I've also tried putting a ENABLE(&#@SUPP-COMM) before the GLOBAL_LSCALLs.

Anyone any idea what I'm doing wrong!!

Thanks,

Rob D.

[Shaun Rudland]

G'Day Rob,

Just a quick thought on my way to the beer fridge (I know you still have an hour or so to go over that side of the continent) ...

The COMMIT suppression should really be enabled at the very start of the function. Even if this doesn't solve your particular problem, it is good practice, as it's easier for others to find, and should be issued before any processing is performed. I haven't really used GLOBAL FUNCTIONS much, but it would not really be possible for them to COMMIT after their particular section, so I don't see why a ROLLBACK would not be possible. Also, a COMMIT suppression should not be necessary. If you temporarily suppress the global calls, and then set #ERR to 1, does the calling function ROLLBACK it's section ?

.... oops, the crackers and brie have arrived. Good Luck.

[Cleve Haynes]

Hey Rob

I seem to recall the global functions & Oracle commit points were a nightmare....

If it is not rolling back, then it stands to reason that there is a commit happening somewhere (like in the global function). Auto commit points in screens change depending on the type of LS, if I remember correctly. Have a look at the ProIV enviroment guide.

Put the SQL trace on and see if a commit is happening where you are not expecting it...

Cleve.

PS. The weather here is shit.

[Richard Bassett]

Are you running with SQL_TRANSACTION_ERROR set to Y ?

[Rob Donovan]

Hi,

I've found the problem... its a BUG!!!

We've just started our beers over this side

Cleve: The weather is a bit shit over here too

I did have the suppress commit at the beginning, but to make my example easier to explain, I put it all in one logic.

The function that does the GLOBAL_LSCALLs does not have any Oracle files in it, its just a 'container' to run the global functions.

There seems to be a problem with using ROLLBACK() in a function that does not reference any Oracle tables. It just gets ignored!!!

If a put a dummy read of a Oracle table into the calling function, then it rollbacks fine.

I'll report it to support.

Rob D

[Richard Bassett]

> If I put a dummy read of a Oracle table into the calling function, then it rollbacks fine.

Rob,

Most likely the issue is whether there's an active DB transaction at the time you call the global function(s). Probably the global functions decide they can commit themselves if there is no 'parent' transaction.

As such I would say it's not a bug but very likely an intended feature. And arguably desirable.

I presume your 'dummy read' actually reads from the database before making the global calls? My guess would be if you merely included an Oracle filedef in your function but didn't do any actual access (eg. DSELFed or placed in an extra LS which is never invoked) then it wouldn't solve your problem.

Just an educated guess

[Shaun Rudland]

G'Day Rob,

If I understand the situation correctly, then I would not necessarily class this as a bug. I think the point is, is that no Oracle transaction has been initiated, and therefore the ROLLBACK becomes null and void. Could just be a 'half full, half empty' scenario, but that it is how I would look at it.

Regards

Shaun

P.S. Weather great here, just need some bloody rain. I've taken to importing my water from the Boag's factory in Tasmania.

[Shaun Rudland]

G'Day Richard,

I hope your educated guess is incorrect. I would want the 'parent' function to be in charge of COMMIT processing, and not have arbitrary decisions made by the 'children'. If a function was coded as per Rob's example, and then at some future date an RDBMS table access was inserted before the calls, then the whole processing dynamics of the function could be altered. You may well be right, and as I stated before, I have had very little to do with global functions, but I would prefer a logical transaction to be bounded by a function, unless explicit COMMITs and ROLLBACKs are used.

Regards

Shaun

[Bill Loven]

Hey Rob,

Is ther a way for you to determine the initial set of rows(RBMS terminoligy) or records(Proiv terminoligy) that your functions will be working on?

If there is, and I noticed that you are using 5.0 and 5.5, have you considered using menory files?

We are an Oracle shop and since 5.0 we use a lot of memory files. This allows us to clear and load the memory files from our Oracle tables. All screens, paging screens, global lscalls and so on update the memory files. If you are using GUI and the user clicks the OK button or whatever button is used to accept the transaction or if you are (God Forbid) still using Green Screen and the user completes the transaction, use a global lscall to write all additions and changes to your Oracle tables. If the user cancels or terminates actions from the Parent Screen, nothing is written to your Oracle tables, in effect a Rollback.

This was a trick that Darren Rowley, from Proiv in Irvine CA, taught us.

Bill

[Rob Donovan]

Hi Bill,

That would not really work for us, but thanks.

Wouldnt you get all sorts of problems doing that, unless you have some mechanism for locking things???

What happens if once you've coppied things to you're memory file and while the user is processing the screen, another user updates the same record?? Or does your app not work like that?

Thanks,

Rob D.

[Bill Loven]

Rob,

My app locks the primary(parent record) so that any user trying to access the parent or any children gets a file in use error.

Another thing we are moving to is Soft Locks. This is something else that Darren showed us.

You basically have a table with the username, you can get this from DUAL, File Name, and key values. You can then tell the second, third, fourth, etc user requesting the same parent, who has it locked.

When the transaction is complete, the row from the Soft Lock table is removed.

Any Granparents to the parent record are not locked.

Bill.

[Rob Donovan]

Hi,

You dont need to read the Oracle table, just putting it into a dummy LU and not calling is good enough to fix it. So it must be a Genn bug.

There are no extra commits in anywhere, just the ROLLBACK never gets issued (after looking throught the trace).

Then, at the next 'normal point', it gets commited. This being function exit in my case.

Rob D.

[Dan Shannon]

Rob

If my memory serves me right, this is similar to a bug that Swallow Tech reported to PRO-IV in v4.6 and had fixed. In their case they had a screen that didn't write any Oracle files, but called several globals that did. The kernel failed to issue a COMMIT at the end of the main screen.

In any case it ought to be regarded as a BUG - because if you want to issue a ROLLBACK, that should be unconditional regardless of the state of the function - after all one would assume that the coder knows under what circumstances a rollback is required?

In general my attitude to PRO-IV's automatic commit processing is to turn it off. If you want workfiles, I suggest using global temporary tables. If you want soft locks, you can use PRO-ISAM files to provide these (which won't conflict with transaction processing) or it *is* possible to use PL/SQL procedures (in 9i at least) to write to lock tables using the PRAGMA AUTONOMOUS_TRANSACTION to ensure commits happen on the tables without having to commit the rest of the transaction. I'd also strongly advocate writing everything that updates tables in PL/SQL rather than in PRO-IV because it's way more re-usable and phenomenally much quicker - especially in 10i if the rumoured fully-compiled PL/SQL materialises. If you want me to be really extreme, I think you should only use PRO-IV for screens

Cheers

Dan

PS What I really meant was, if ROLLBACK doesn't do a rollback, then it's a bug.

[Richard Bassett]

Well, luckily, seems Rob was way ahead of me and I need some more education

[Rob Donovan]

I'm new to Oracle so Im not really up to speed yet, and need all the input I can get.

Not sure what the bugs are or 'feauters'

The 'trace' normally helps me out.

Thanks,

Rob D.