How to update table records (data correction) in Clarion 8

questions
1

Hi all,

I am working in Clarion 8 and need to perform some data correction in a table (for example, updating fields where values are currently 0).

What is the recommended approach in Clarion 8 for updating table data programmatically?

  • Should I use a process loop with NEXT() and UPDATE()?
  • Or is there a better way to do batch corrections?
  • Since Clarion 8 is TPS based, I assume I cannot run direct SQL.

Any guidance, best practices, or example code snippets would be really helpful.

Thanks in advance!

2

If you have TPS files and you want to change the values in a defined set of records to something that can be determined from the record you are using, then you can use a process procedure. Select the ProcessWizard as the type of procedure, tell it what file you want to process, and in the TakeRecord method, put the code you want, like

if fil:ordertotal = 0 then fil:ordertotal = fil:pieces * fil:price.

That embed will be under the ProcessManger in local objects, and looks like this:

image
image497×110 2.75 KB

Having said that, your statement “Since Clarion 8 is TPS based, I assume I cannot run direct SQL” is not correct. A clarion8 program could be running against data stored in an any SQL database, or possibly a bunch of ISAM formats other than TPS. And if the data is in a SQL database, you can certainly use SQL.

If you have a lot of rows to process, then adding a STREAM before you start looping, and FLUSH afterwards will help with speed. LOGOUT/COMMIT (with a commit very few hundred rows) would too.

If you are using the Process template, then the template will handle the looping for you.

1 Like
3

Previously I was updating values using a LOOP like this:

LOOP 
    SET(UBmch)                 
    LOOP UNTIL Access:UBmch.Next()
        IF ERRORCODE() THEN BREAK.
        IF MCH:Units <= 0
            MCH:Units = 1
            Access:UBmch.Update()
        END
    END
END

Now, if I use the Process template with an embed in the TakeRecord method, can I simply write:

IF MCH:Units <= 0 THEN 
    MCH:Units = 1
END

Will this automatically update the record, or do I still need to call Access:UBmch.Update() explicitly inside the process?

I just want to confirm if this is the correct way to do table corrections in Clarion 8.

Thanks

4

You do still need the update. But the template can take care of that for you. If you go to Actions|Process properties for the procedure, you will see:

image
image822×250 10.2 KB

You just select PUT record from that dropdown.

If you look at the code you will see something like:

ThisProcess.TakeRecord PROCEDURE

ReturnValue          BYTE,AUTO

  CODE
  IF MCH:Units <= 0 then MCH:Units = 1.
  ReturnValue = PARENT.TakeRecord()
  PUT(Process:View)
  IF ERRORCODE()
    GlobalErrors.ThrowFile(Msg:PutFailed,'Process:View')
    ThisWindow.Response = RequestCompleted
    ReturnValue = Level:Fatal
  END
  RETURN ReturnValue

which will do what you want. Note that, if you don’t want to waste time writing back records that have not changed, you would use the ValidateRecord embed to filter out records that were fine. So in your simple case you would put in ValidateRecord

if mch:units <= 0 then return record:filtered.

and in the TakeRecord you would just have

MCH:units = 1

because you would only be getting the mch:units<= 0 at that point.

5

Also, when using Process template: If you add filter to the process, you should take into account that if you change a field that you have in your filter, the behavior is unpredictable.
I.e.
If filter says mch:units=0, changing mch:units=1 and then save may move the pointer outside the view.
So better use validaterecord in such cases.

6

No, Incorrect. You just need to select the ODBC driver in the dct and supply the appropriate connection string.
Then you can issue a {prop:SQL}=‘Update blah where blah’

This assumes your data in in an SQL server somewhere of course. If your data is in fact in a TPS file then all the above answers apply.

7

The IF ERRORCODE() THEN BREAK. should not be there. If any error or problem occurs Access:UBmch.Next() will return <> 0 and end the Loop, the ErrorCode() may not be set.

A Process Template will not Update unless you do as JonW showed and select that in the “Action for Process”. I prefer Not to do that. I would rather do as you suggested and use the ABC method to Update so it goes thru the same update code as a Form. In TakeRecord you would have your code:

!-- Take Record Embed 
IF MCH:Units <= 0 THEN 
    MCH:Units = 1
    Access:UBmch.Update()   !or IF .TryUpdate() 
END