Procedure Passing Two File and Record Parameters for deep assign

xxetfxx · May 19, 2021, 2:42am

Hello,

I am creating a conversion wizard to copy topspeed data files to odbc data files (same structure)

What I need to attain is to avoid repeating the same code so I tried to create a procedure passing the file parameter and record parameter. With that, I can only call the procedure without repeating the code.

CopyTPS2SQL          PROCEDURE  (FILE pTPS,FILE pSQL,*STRING pTpsRecord,*STRING pSQLRecord,STRING pProgressText,LONG pProgressBar,UNSIGNED pFeq) ! Declare Procedure
! Start of "Data Section"
! [Priority 1300]

! [Priority 4000]
TotalRecords      LONG

! End of "Data Section"
! Start of "Local Data After Object Declarations"
! [Priority 5000]

! End of "Local Data After Object Declarations"

  CODE
! Start of "Processed Code"
! [Priority 4000]

   TotalRecords = RECORDS(pTPS)
   pProgressBar = 0
   pFeq{PROP:RangeHigh} = TotalRecords
   pProgressText = 'Progress: 0/' & TotalRecords & ' Copied'
   DISPLAY
   
   LOGOUT(5,pSQL)
   SET(pTPS)
   LOOP
  NEXT(pTPS)
  IF ERRORCODE() THEN BREAK END
  pSQLRecord :=: pTPSRecord
  ADD(pSQL)
  pProgressBar += 1
  pProgressText = 'Progress: ' & pProgressBar & '/' & TotalRecords & ' Copied'
  DISPLAY
  YIELD()
   END
   COMMIT()

In the procedure above, I passed the file record to a STRING.

Code below is how I call the procedure from above

   !START OF USERINFO
   ProgressBar2 += 1
   ProgressText2 = 'Progress: Converting UserInfo Record'
   DISPLAY()
   OPEN(UserInfov4T)
   IF NOT ERRORCODE()
      Access:UserInfo.Open
      Access:UserInfo.UseFile
      IF RECORDS(UserInfov4T)
         CopyTPS2SQL(UserInfov4T,UserInfo,UserInfov4T:Record,UserInfo:Record,ProgressText3,ProgressBar3,?ProgressBar3)
      END
      Access:UserInfo.Close
      CLOSE(UserInfov4T)
   END
   !END OF USERINFO

This procedure of copying works. However, deep assignment does not work as it is used to be.

Here is the tps file record

And here is the ODBC(MySQL) file record converted

I am wondering for better approach to this concern. I hope the community helps me regarding this.

Thank you for your kind assistance.

seanh · May 19, 2021, 3:08am

That looks like the fields are different between TPS and SQL
What’s you procedure prototype?

CopyTPS2SQL(pTPS *FILE,pSQL  *FILE)

I would think would be enough then
pTPS.Record :=: pSQL.Record

xxetfxx · May 19, 2021, 3:26am

Do you mean this?
CopyTPS2SQL(*FILE pTPS ,*FILE pSQL)

Have tried it but i got errors

CarlBarnes · May 19, 2021, 4:48am

You cannot do a deep assign unless you prototype your procedure * String as a * Named Group, but you cannot do that because you want to pass a variety of files.

Try not passing the Record, instead get it using Prop:Record like

pTpsRecord &Group

pTpsRecord &= pTps{Prop:Record}

And same for pSQL.

Edit
It might work to change the record * STRING to * GROUP (I don’t think it will) but using Prop: Record it’s 2 less things to pass and you cannot mismatch the Records and Files and have a bad transfer

xxetfxx · May 19, 2021, 5:57am

I tried your code, here is what I do

!!! <summary>
!!! Generated from procedure template - Source
!!! </summary>
CopyTPS2SQL          PROCEDURE  (FILE pTPS,FILE pSQL,STRING pProgressText,LONG pProgressBar,UNSIGNED pFeq) ! Declare Procedure
! Start of "Data Section"
! [Priority 1300]

! [Priority 4000]
TotalRecords      LONG
pTPSRecord        &GROUP
pSQLRecord        &GROUP
! End of "Data Section"
! Start of "Local Data After Object Declarations"
! [Priority 5000]

! End of "Local Data After Object Declarations"

  CODE
! Start of "Processed Code"
! [Priority 4000]
   TotalRecords = RECORDS(pTPS)
   pProgressBar = 0
   pFeq{PROP:RangeHigh} = TotalRecords
   pProgressText = 'Progress: 0/' & TotalRecords & ' Copied'
   DISPLAY
   pTPSRecord &= pTPS{PROP:Record}
   pSQLRecord &= pSQL{PROP:Record}
   LOGOUT(5,pSQL)
   SET(pTPS)
   LOOP
      NEXT(pTPS)
      IF ERRORCODE() THEN BREAK END
      pSQLRecord :=: pTPSRecord
      ADD(pSQL)
      pProgressBar += 1
      pProgressText = 'Progress: ' & pProgressBar & '/' & TotalRecords & ' Copied'
      DISPLAY
      YIELD()
   END
   COMMIT()
! End of "Processed Code"
! Start of "Procedure Routines"
! [Priority 2600]

! End of "Procedure Routines"
! Start of "Local Procedures"
! [Priority 5000]

! End of "Local Procedures"

No records been copied, but I think I’m already close to get the right code, I will try another shot. Thank you very much for helping me with this.

jslarve · May 19, 2021, 7:28am

You might also want to decrease the frequency of DISPLAY.

e.g.

IF NOT pProgressBar % 500 ! Every 500 records
DISPLAY
END

xxetfxx · May 19, 2021, 7:37am

Thank you for the nice tip, @jslarve

xxetfxx · May 19, 2021, 8:58am

PS: I am using almost identical structure of topspeed and odbc table (you can see that UserID in Topspeed File is only STRING(8), while ODBC File UserID has STRING(10)

When using deep assignment on my code

   TotalRecords = RECORDS(pTPS)
   pProgressBar = 0
   pFeq{PROP:RangeHigh} = TotalRecords
   pProgressText = 'Progress: 0/' & TotalRecords & ' Copied'
   DISPLAY
   pTPSRecord &= pTPS{PROP:Record}
   pSQLRecord &= pSQL{PROP:Record}
   LOGOUT(5,pSQL)
   SET(pTPS)
   LOOP
      NEXT(pTPS)
      IF ERRORCODE() THEN BREAK END
      pSQLRecord :=: pTPSRecord
      ADD(pSQL)
      pProgressBar += 1
      pProgressText = 'Progress: ' & pProgressBar & '/' & TotalRecords & ' Copied'
      IF NOT pProgressBar % 500
         DISPLAY
      END
      YIELD()
   END
   COMMIT()

There are no records copied

but when I don’t use deep assignment
pSQLRecord = pTPSRecord

Records are copied but bad transfer

But when I manually handcoded the copying process without using the CopyTPS2SQL procedure

   !START OF USERINFO
   ProgressBar2 += 1
   ProgressText2 = 'Progress: Converting UserInfo Record'
   DISPLAY()
   OPEN(UserInfov4T)
   IF NOT ERRORCODE()
      Access:UserInfo.Open
      Access:UserInfo.UseFile
      IF RECORDS(UserInfov4T)
         ?ProgressBar3{PROP:RangeHigh} = RECORDS(UserInfov4T)
         ProgressBar3 = 0
         ProgressText3 = 'Progress: 0/' & RECORDS(UserInfov4T) & ' Copied'
         DISPLAY()
         LOGOUT(5,UserInfo)
         SET(UserInfov4T)
         LOOP
            NEXT(UserInfov4T)
            IF ERRORCODE() THEN BREAK END
            UserInfo:Record :=: UserInfov4T:Record
            APPEND(UserInfo)
            ProgressBar3 += 1
            ProgressText3 = 'Progress: ' & ProgressBar3 & '/' & RECORDS(UserInfov4T) & ' Copied'
            DISPLAY()
            YIELD()
         END
         COMMIT()
!         CopyTPS2SQL(UserInfov4T,UserInfo,ProgressText3,ProgressBar3,?ProgressBar3)
      END
      Access:UserInfo.Close
      CLOSE(UserInfov4T)
   END
   !END OF USERINFO

I got a good transfer

But since I have almost 100 data to convert, I don’t want to write almost the same procedure for all those hundred files, just a call to procedure to do the rest.

CarlBarnes · May 19, 2021, 12:46pm

I was on my phone, now on my laptop. I tried a simple Test of Group Deep Assign 2 ways: Prop:Record or passing *GROUP. Both fail. I would suggest you work out your code with a small program like this that looks at one record and a few fields:

PROGRAM
Service1 FILE,DRIVER('TOPSPEED'),RECLAIM,PRE(SER1), BINDABLE,CREATE,THREAD
Record  RECORD,PRE()
ID         LONG
Desc       STRING(20)
Date       DATE
    END
 END
Service2 FILE,DRIVER('TOPSPEED'),RECLAIM,PRE(SER2), BINDABLE,CREATE,THREAD
Record  RECORD,PRE()
ID         LONG
Desc       STRING(20)
Date       LONG
    END
 END
 
MAP   
CopyTPS2SQL  PROCEDURE  (FILE pTPS,FILE pSQL, *GROUP pTPS_Group , *GROUP pSQL_Group)
END 

CODE
  CLEAR(Service1)
  Ser1:Desc='Hello ' & Today() 
  Ser1:Date=today()
  CopyTPS2SQL(Service1,Service2, SER1:Record, SER2:Record)
 !  ! SER2:Record :=: SER1:Record     !Test 4 this always works
  Message('Ser1:Desc=' & Ser1:Desc &'|Ser2:Desc=' & Ser2:Desc & | 
      '||Ser1:Date=' & Ser1:Date &'|Ser2:Date=' & Ser2:Date,'Deep Assign Record' ) 

RETURN
!---------------------------- 
CopyTPS2SQL  PROCEDURE  (FILE pTPS,FILE pSQL, *GROUP pTPS_Group , *GROUP pSQL_Group) 
pTPSRecord        &GROUP
pSQLRecord        &GROUP
CODE
   pTPSRecord &= pTPS{PROP:Record}
   pSQLRecord &= pSQL{PROP:Record}    
   pSQLRecord :=: pTPSRecord        !Test 1: Prop:Record Assign

!   pSQL_Group :=: pTPS_Group      !Test 2: Passed *GROUP

!Below works but not if record is different. Good to see works somewhat
!   pSQL_Group = pTPS_Group        !Test 3: Assume same record 

RETURN

The Help says Deep Assign is done by the Compiler. I thought it could be done it at Runtime but the above code confirms it cannot, unless I have a bug.

Maybe someone else has some ideas as it is fairly common to want to convert all files from TPS to SQL, and fairly common to change some fields especially date/time. Post a new Question about TPS to SQL, and changing record fields. Search here as it probably already exists. I know Clarion Mag has some articles on converting TPS to SQL. Also check ClarionLive.com

I would write a Utility Template to generate code like you found worked for every file. That may already exist. You can take that code and change it for individual files and fields. Note I think you had a field different ExpiryDate <> UserExpiryDate.

seanh · May 19, 2021, 10:35pm

Maybe a different method
try and find the DCT2SQL templates. They might be on icetips. They allow a conversion of your dct files to a couple varietys of SQL including MySql

CarlBarnes · May 19, 2021, 11:24pm

Those are on Robert’s GitHub and there are some videos on ClarionLive

MarkGoldberg · May 20, 2021, 1:49am

DCT2SQL sounds like a promising idea.

For the deep assign work around, how about using a FieldPairsClass approach see Libsrc\Win\ABUtil.[inc|clw]

jslarve · May 20, 2021, 1:54am

I’ve used the fieldpairs class quite a bit for that purpose. It works great.

BoxSoft · May 20, 2021, 10:14am

Another alternative is to create a class that does the copying, but use a virtual method to do the deep assign of the fields.

CopyTPS2SQL         CLASS,TYPE
Copy                  PROCEDURE(FILE pTPS,FILE pSQL,STRING pProgressText,LONG pProgressBar,UNSIGNED pFeq)
Assign                PROCEDURE,VIRTUAL
                    END
  
CopyTPS2SQL.Copy    PROCEDURE(FILE pTPS,FILE pSQL,STRING pProgressText,LONG pProgressBar,UNSIGNED pFeq)
  CODE
  !Your existing code, wherein it calls SELF.Assign()
  
CopyTPS2SQL.Assign  PROCEDURE
  CODE
  !Abstract Virtual
  ASSERT(False, 'CopyTPS2SQL.Assign must be derived')

!For each file  
CT2S_SomeFile       CLASS(CopyTPS2SQL)
Assign                PROCEDURE,DERIVED
                    END

CT2S_SomeFile.Assign  PROCEDURE
  CODE
  SomeFileSQL.Record :=: SomeFileTps.Record

CarlBarnes · May 20, 2021, 2:00pm

You know the above Record Deep Assign works. The ADDRESS( File ) of a file is unique so you can use that instead of passing a file id or name. You could generate a big CASE statement like below, or do it in an editor that has column mode. Hopefully you can keep most your current code.

CopyTPS2SQL          PROCEDURE  (FILE pTPS,FILE pSQL,STRING pProgressText,LONG pProgressBar,UNSIGNED pFeq)
...
       LOOP
          NEXT(pTPS)
          IF ERRORCODE() THEN BREAK END

          DO AssignTps2SqlRecordRtn  !<=== was  pSQLRecord :=: pTPSRecord

          ADD(pSQL)
          pProgressBar += 1
          IF pProgressBar % 1000 = 0 THEN 
             pProgressText = 'Progress: ' & pProgressBar & '/' & TotalRecords & ' Copied'
             DISPLAY
          END  
       END


    AssignTps2SqlRecordRtn ROUTINE
            CASE ADDRESS(pTPS)   
            OF ADDRESS(UserInfov4T) ; UserInfo:Record :=: UserInfov4T:Record 
                                      UserInfo:UserExpiryDate = UserInfov4T:ExpiryDate !Renamed field
            OF ADDRESS(xxxxxxxInfo) ; XxxxInfo:Record :=: XxxxInfov4T:Record
            etc
            ELSE
                 Message('CASE ADDRESS(pTPS)  failed on file ' & pTPS{PROP:Name} )
                 RETURN   !Failed 
            END
            EXIT

CarlBarnes · May 20, 2021, 2:38pm

You could make your file loop “generic” by passing in the FIleManager as a parameter so each file conversion is 1 line of code.

FileTps2Sql(UserInfov4T,UserInfo, Access:UserInfo , ....)

FileTps2Sql  PROCEDURE(FILE pTPS ,FILE pSQL, FileManager pAccess:Sql, ...) 
   CODE  
   ProgressBar2 += 1
   ProgressText2 = 'Progress: Converting UserInfo Record'
   DISPLAY()
   OPEN(pTPS)  !!was (UserInfov4T)
   IF NOT ERRORCODE()
      pAccess:Sql.Open()     !was Access:UserInfo.Open
      pAccess:Sql.UseFile()  !was Access:UserInfo.UseFile
      IF RECORDS(pTPS)
         !Was CopyTPS2SQL(UserInfov4T,UserInfo,ProgressText3,ProgressBar3,?ProgressBar3)
         CopyTPS2SQL(pTPS, pSQL, ProgressText3,ProgressBar3,?ProgressBar3)
      END
      pAccess:Sql.Close()  !was Access:UserInfo.Close
      CLOSE(pTPS) 
   ELSE 
      Message('Open TPS Error ' & ErrorCode() &' '& Error() &' |File: '& Name(pTPS) )
   END

xxetfxx · May 21, 2021, 8:49am

I’ve tried FieldPairClass approach. Implemented the code below

   Fields.Init
   pTPSRecord &= pTPS{PROP:Record}
   pSQLRecord &= pSQL{PROP:Record}
   
   TotalRecords = RECORDS(pTPS)
   OPEN(pTPS)
   IF NOT ERRORCODE()
      pAccessSQL.Open
      pAccessSQL.UseFile
      IF RECORDS(pTPS)
         pFeq{PROP:RangeHigh} = TotalRecords
         pProgressBar = 0
         pProgressText = 'Progress: 0/' & TotalRecords & ' Copied'
         DISPLAY()
         LOGOUT(5,pSQL)
         SET(UserInfov4T)
         LOOP
            NEXT(UserInfov4T)
            IF ERRORCODE() THEN BREAK END
            Fields.AddPair(pSQLRecord,pTPSRecord)
            Fields.AssignRightToLeft()
!            UserInfo:Record :=: UserInfov4T:Record
            ADD(UserInfo)
            pProgressBar += 1
            pProgressText = 'Progress: ' & pProgressBar & '/' & TotalRecords & ' Copied'
            IF NOT pProgressBar % 500
               DISPLAY
            END
            YIELD()
         END
         COMMIT()
      END
      pAccessSQL.Close
      CLOSE(pTPS)
   END
   Fields.Kill

Still got the same result

I will also try other methods in here. Thank you for all the help!

BoxSoft · May 21, 2021, 1:37pm

The FieldPairs class will work only if you initialize it with all the record fields individually.

I still think the best approach is the class with virtual+derived methods that I mentioned above.

CarlBarnes · May 21, 2021, 1:38pm

I see no special handling for GROUP in Field Pairs. You are getting a simple Record=Record.

Below is my simple test just checking 3 fields in a Record assignment:

PROGRAM  
INCLUDE('abutil.inc'),ONCE

Service1 FILE,DRIVER('TOPSPEED'),RECLAIM,PRE(SER1), BINDABLE,CREATE,THREAD
Record  RECORD,PRE()
ID         SHORT        
Desc       STRING(20)
Date       DATE
    END
 END
Service2 FILE,DRIVER('TOPSPEED'),RECLAIM,PRE(SER2), BINDABLE,CREATE,THREAD
Record  RECORD,PRE()
ID         LONG        !<--- Diff so Record=Record Assign breaks
Desc       STRING(30)  !<--- Diff 
Date       LONG        !<--- Diff
    END
 END
 
MAP   
CopyTPS2SQL  PROCEDURE  (BYTE TestNum, FILE pTPS,FILE pSQL, *GROUP pTPS_Group , *GROUP pSQL_Group)
END 
TryTest BYTE    
CODE
  CLEAR(Service1)
  Ser1:ID=1234
  Ser1:Desc='Hello ' & Today() 
  Ser1:Date=today()
  TryTest=4
  CopyTPS2SQL(TryTest,Service1,Service2, SER1:Record, SER2:Record)
! SER2:Record :=: SER1:Record works so LONG = DATE works
  Message('TryTest=' & TryTest & |
      '||Ser1:ID=' & Ser1:ID &'|Ser2:ID=' & Ser2:ID & | 
      '||Ser1:Desc=' & Ser1:Desc &'|Ser2:Desc=' & Ser2:Desc & | 
      '||Ser1:Date=' & Ser1:Date &'|Ser2:Date=' & Ser2:Date,'Deep Assign Record' ) 

RETURN
!---------------------------- 
CopyTPS2SQL  PROCEDURE  (BYTE TestNum, FILE pTPS,FILE pSQL, *GROUP pTPS_Group , *GROUP pSQL_Group) 
pTPSRecord        &GROUP
pSQLRecord        &GROUP 
Fields  FieldPairsClass
CODE
   pTPSRecord &= pTPS{PROP:Record}
   pSQLRecord &= pSQL{PROP:Record} 
   CASE TestNum 
   OF 1
    pSQLRecord :=: pTPSRecord        !Test 1: Prop:Record Assign
   OF 2
    pSQL_Group :=: pTPS_Group      !Test 2: Passed *GROUP
   OF 3
    !Below works but not if record is different. Good to see works somewhat
    pSQL_Group = pTPS_Group        !Test 3: Assume same record 

   OF 4 
   Fields.Init()
   Fields.AddPair(pSQLRecord,pTPSRecord)
   Fields.AssignRightToLeft()     
   Fields.Kill() 
   ELSE
    Message('Unknown TestNum=' & TestNum )
   END 
   RETURN

jslarve · May 21, 2021, 4:11pm

You would need to set up the field matching of the field pairs class before you loop through your table. Use WHO() to match up fields. Then WHAT() would go into your fieldpairs object. e.g. fp.AddPair(WHAT(LeftRecord, LeftNdx),WHAT(RightRecord,RightNdx))

After you do that, loop through the table. Do a fp.AssignLeftToRight to copy the values, then add the record.