Here’s a class I was playing with a while back. Maybe you can borrow something from it.JSDEEP2.ZIP (1.4 KB)
I thought about suggesting that but it seemed kind of complicated for a one time conversion. You have to probably skip groups and deal with arrays i.e. IsGroup and HowMany
On thinking about it more, it could be less of a black box compared to deep assign. A window could be displayed showing where WHO did not match up so there would be a sort of an orphan. You could use TUFO to highlight type changes.
2 Likes
Hello,
Finally I got a good transfer by the use of WHO() and WHAT()
This is what I do in my code:
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(pTPS)
LOOP
NEXT(pTPS)
IF ERRORCODE() THEN BREAK END
fieldnum# = 0
LOOP
fieldnum# += 1
IF WHO(pSQLRecord,fieldnum#) = '' THEN BREAK END
Fields.AddPair(WHAT(pSQLRecord,fieldnum#),WHAT(pTPSRecord,fieldnum#))
END
Fields.AssignRightToLeft()
ADD(pSQL)
pProgressBar += 1
pProgressText = 'Progress: ' & pProgressBar & '/' & TotalRecords & ' Copied'
IF NOT pProgressBar % 500
DISPLAY
END
YIELD()
END
COMMIT()
END
pAccessSQL.Close
CLOSE(pTPS)
END
Fields.Kill
Thank you Sir @jslarve, and all who helped me here to finally get the right code.
The Fields.AddPair() is building a Queue of references. You only need to do that once at the top before your file Loop, right after the pSQLRecord &= pSQL{PROP:Record}. The way you have it now you add X more assignments of the same fields every record in the file.
You are assuming the fields will be in the identical order so you will not be adding any new fields in the middle of the record, or removing fields. You’ll’ have to check but I think this will assign Arrays as a Group so you cannot change the field specification
F LONG !Don't use implicits
Fields.Init
pTPSRecord &= pTPS{PROP:Record}
pSQLRecord &= pSQL{PROP:Record}
LOOP F=1 TO 999 !moved up here to do once
IF WHO(pSQLRecord,F) = '' THEN BREAK.
Fields.AddPair(WHAT(pSQLRecord,F),WHAT(pTPSRecord,F))
END
TotalRecords = RECORDS(pTPS)
OPEN(pTPS)
...
NEXT(pTPS)
IF ERRORCODE() THEN BREAK END
GET(pSQL,0) !<-- Prevent Dup Error ? probably an issue only for PUT
CLEAR(pSQLRecord) !<-- I would clear
Fields.AssignRightToLeft()
ADD(pSQL)
You probably want some error checking on ADD. I would also compare the RECORDS(TPS) = SQL.
I would move the YIELD() to do with the DISPLAY every 100 records to make it go faster
2 Likes
Not just that, it should definitely not be inside the table’s loop. Just at the beginning, before loop through the records. Otherwise, you end up with a jillion redundant pairs inside the fieldpairs object, adding overhead and time with each loop.
3 Likes
Yup, I was trying to say that. I was pondering how to estimate how many “jillion”. His sample file had 8 fields and if I guess 1000 records the Pairs Queue ends up with 8000 rows. The AssignRightToLeft() would have done 8, 16,24,32 … assignments.
If there are F fields and R records I think that’s (N*(N+1))/2 * R = 1000*1001/2*8 = 500,500 assignments with just 1,000 records. With 10,000 records that becomes 400 million assignments, with 100,000 that’s 40 billion assignments and a queue of 800,000 rows. If you happen to get paid by the hour then this may be your best method
1 Like
Hello @CarlBarnes,
Am I doing the right thing? Here is my code
Fields FieldPairsClass
F LONG !Field Loop
TotalRecords LONG
pTPSRecord &GROUP
pSQLRecord &GROUP
Fields.Init
pTPSRecord &= pTPS{PROP:Record}
pSQLRecord &= pSQL{PROP:Record}
LOOP F = 1 TO 999
IF WHO(pSQLRecord,F) = '' THEN BREAK END
Fields.AddPair(WHAT(pSQLRecord,F),WHAT(pTPSRecord,F))
END
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(pTPS)
LOOP
NEXT(pTPS)
IF ERRORCODE() THEN BREAK END
CLEAR(pSQLRecord)
Fields.AssignRightToLeft()
ADD(pSQL)
pProgressBar += 1
pProgressText = 'Progress: ' & pProgressBar & '/' & TotalRecords & ' Copied'
IF NOT pProgressBar % 500
DISPLAY
END
YIELD()
END
COMMIT()
END
pAccessSQL.Close
CLOSE(pTPS)
END
Fields.Kill
I never thought that I just need to assign fields outside the loop to avoid ending up with a jillion redundant pairs
Hi - I am coming a bit late to this conversation but if I am not mistaken your current code will only assign fields in the same order from one file structure to the other.
Carl said this with “You are assuming the fields will be in the identical order so you will not be adding any new fields in the middle of the record, or removing fields”.
I thought of making a queue of position and field name and matching them up but seemed to remember seeing a solution to this before. A quick search shows an article in Clarion Magazine cmag-2006-12.pdf by Alan Telford where he does just that:
A Customized Deep Assign Function
by Alan Telford
Published 2006-12-12
so maybe it is a problem solved nearly 15 years ago
you can get the pdf with the article and a zip containing the source at:
hope that saves you some time - and thanks to Dave Harms for making all the issues of Clarion Magazine freely available - there is a wealth of info in there!
cheers
Geoff R
That’s pretty much what my class (posted above) does, but I made use of the fieldpairs class to do the pairing.
ah yes sorry Jeff - I am just looking at that now. Cheers.
Could use a bit of optimization and could probably combine a couple of methods, but the basic idea is there.
1 Like
The code looks right. There is the issue of you assuming the fields are in the same order in TPS and SQL so not sure if it is the right approach.
With 200 files to convert I would probably go the way Mike posted in #5 using a Class.
You generate an inherited class for each file that Derives an .Assign() method that uses the Deep Assign. That works because you actually generate the code with the Prefixes e,g. UserInfo:Record :=: UserInfov4T:Record. That Assign method could easily be modified for specific file fields.
I would make Properties in the Class for Tps FILE, Sql FILE and SQL FileManger. Those could be set by an INIT() … or I think I would generate a CONSTRUCT to set those. The Constructor or Init() could register the class in a Queue of Class Refs so you could loop through that Queue and run them all.
The class could be extended to do all kinds of things to verify or test the conversion. Like to exercise the .Assign() method. Maybe filling a TPS record’s fields with 123, call the .Assign() and see the SQL fields all contain 123. With 200 files to convert it would be nice to code some ways to check its right.
Even if you don’t use the Class you should probably load a ConvertFile QUEUE with all the data (TPSfile, SQLfile, SqlFileManager, …) and run the convert looping through the Queue. That Queue could contain the FieldPairs class which could be adjusted for certain files, or generated as Jeff did.
When this is done right its kind of fun the way you can easily adapt and code extra things like checks it works right.
I think Capesoft File Manager 3 is built to do this job for you, do it right and make it easy. Have you looked at it? After convert it helps you make file changes.
This link has a lot of info on converting TPS to SQL.