Create Statement and Commit on DB

[4GLNewbie]

I came back with a new trouble, people!

I have some problems with a code that seems very simple to me.
But it happens sometimes it does not work. Still unable to discover why.

I have source code like this.

[ some instructions ]
RUN Function1.
RUN Function2.

In Function1 there is:

CREATE <tablename>
ASSIGN <tablename>.<fileld> = <value>
[other fields assignments]
VALIDATE <tablename>

In Function2 there is:

PAUSE 3.
RUN <app>.

App is another application that should read from db what i set up on Function1.
Must say that the db where i write is external, on the net. App is also an application that reads from that db.
It happens sometimes App opens whitout showing values correctly.
To debug the problem, i tried opening App on the same data after a while.
So if i open App giving it the parameter after some time, it opens in the correct way.

Now, the point is..

Is it possible that data is written on db after i executed App? Procedures are exectuted ONE AFTER ANOTHER, not in multitasking ( just to say something! ), right?
It may depend on db settings on the remote db server or did i something wrong?

That happens only sometimes, it seems that commit is not done before executing App, but after it runs, when data is not ready yet.

Any clue?

 

[TomBascom]

You need to gain an understanding of record and transaction scope. Your psuedo code is not detailed enough to say for sure but it sounds to me like you have a transaction that is scoped to the procedure block which contains this code and that when the error that you describe occurs the transaction is backed out.

Progress is not like SQL. There is a great deal of implicit transaction processing and error handling.

You can compile your code with the XREF option to see what that actual record and transation scope is -- I strongly suspect that it is much broader than you think that it is.

 

[4GLNewbie]

I think i understood what you mean, TomBascom.

In my code is not used something like
block1:
DO TRANSACTION:
ON ERROR UNDO block1, LEAVE block1.
[assignements]
END TRANSACTION.

Code is done like this in Function1:
ASSIGN
<tablename>.<primary-key> = NEXT-VALUE (<sequence1>, <db-alias>)
... conditions and assignements ...
<tablename>.<field-n> = n-value.

But i dont think there is an error while writing on the db.
I suppose there is not a rollback, because the primary key is obtained by a NEXT-VALUE function on a SEQUENCE, like i wrote.
I assume a NEXT-VALUE can not be rolled back without specific istructions, am i wrong? So if i find the record with that particular id on database, it has been written.

Sorry to be so unsure about things, i started using progress a few weeks ago..

Turning back to my explanation..
In fact, if i open a a db-data-browser and look for the row interested, it seems to be correctly written.
Meanwhile, on the pc that executes my code, App shows that cannot find the record identified by the key. Dont know how App works.
If i re-open App with the key i passed before after few time ( seconds or minutes ), it works.

May the Next-value call be invoked at the same time and return the same value to two or more applications, for example? So when i complete the transaction there is a duplicate-primarykey error?
Or, if there is an error while assigning values, how can it be rolled back and still correct values being seen on the db?

P.S. : is there any way to have control upon sql-instructions, executing an insert or update with sql statements ( like done in vb, i mean, where you can do an insert and wait for errors in doing changes on db )?

---

If db is distribuited and has some tasks that maintain data aligned, i suppose in fact that there may be some syncro probs. I mean, when I write data somewhere and App tries to read it when in fact it has not still been updated.
Maybe this is not a Progress trouble, but one that regards database distribution.

Sounds more possible this way?

 

[TomBascom]

1) Did you compile with XREF and confirm that your suppositions regarding record and transaction scope are correct?

2) SEQUENCES cannot be rolled back. At all. You can reset them but they are immune to transaction rollback. If a transaction rolls back the sequence value is generally lost (you could store it in a variable and code some stuff to work around that -- but that would be pretty unusual and I'm not sure what the point would be).

3) Do you have some real code to post which demonstrates your problem?

4) Do not try to mix SQL and 4GL inside a 4GL program. You won't be happy. There are some SQL statements available from within the 4GL but they are SQL-89, they won't behave in a "normal" SQL context, you will still be subject to record and transaction scoping issues that are foreign to a SQL programmer and you will end up in an insane asylum from trying. You've been warned. Expect no sympathy.

From your description I think that your records and your transactions are almost certainly scoped to the containing procedure.

It sounds like you are complaining that the record is being created by session A and that you believe that session B, possibly running on another PC should be able to see it? Whether or not B can see the record depends on the scope of the record & transaction on A and the state of the transaction. If it has not yet been committed then session B will not be able to see it (an exception is that a NO-LOCK read will be able to see the key values). If the record and transaction scope are at wider than you think they are, and it is fairly easy for them to be wider than beginning Progress programmers expect, then this is a likely state of affairs.

 

[4GLNewbie]

Going in order.

It is yet under verify, but i think it was the VALIDATE statement on data my problem. Db-sided trouble, so! I dont have a way to check what it validates.
And also add wrong description of the results of bug by who checked what was written on db, i think. As i said, it is not under my control so i asked who managed it to give me directions.. When they told me that record was correctly written in every case, it was not true.

I ll answer your questions.

1. No, i didnt compile as you suggested, i had not time to try this way. Had to resolve things as fast as i could.
I tried to disconnect from db and after reconnection, search for what i had written. Noticed that error occurred only when a specified field was not set. So i suppose there is a Validate somewhere.
In fact, when i look fot that record to check values, could not find it on db!

2. Thanks, it was only a idea that jumped in my mind. Happens when you dont know how to find any clue..

3. Cannot post more code. Sorry. It seems it is not necessary anymore.

4. Didnt want to put together vb-like code and progress one. It was the last way i had to try detect error. Something like last chance...


Thanks a lot for your help and suggestions, TomBascom. They have been very useful to me to understand Progress and to proceed excluding clues one after another..

I think it is not a problem of transaction scoping.
Code is very simple and makes things in sequence. Dont think i miss something when i read it.
It is like find conditions -> write on db -> call App.

The fact that only a certain type of records give the problem, makes me think that problem is on the record validation rules.
But who told me that every single record was written as expected, didnt say the true..

P.S.: if data is only to be shown, always used a NO-LOCK option..

I ll post how it ends. See you!

 

[4GLNewbie]

The problem was not related to commit, at the end.

Wrong description of the bug and unability to tell me the true situation on the remote db.

The problem was instead related to record validation.

Consider the thread no more useful. Thanks a lot.

Regards.