IT SOLUTIONS
Your full service technology partner! 
-Collapse +Expand
Paradox
Search Paradox Group:

Advanced
-Collapse +Expand Paradox To/From
To/FromCODEGuides
-Collapse +Expand Paradox Store
PRESTWOODSTORE

Prestwood eMagazine

October Edition
Subscribe now! It's Free!
Enter your email:

   ► MB LobbyCorel Paradox / ObjectPAL Coding BoardObjectPAL Topic   Print This     

updating table from table with TCursor

updating table from table with TCursor in ObjectPAL topic (part of our Corel Paradox / ObjectPAL Coding group).

Quick Search: updating   TCursor   updating table   updating table table  
Leo
Civilized Africa

Hey
I'm trying to update the old table from one that the changes live on. The idea is to find in the input table all the records that doesn't occur in the old table and add them. I've done some code with two nested loops. Outside loop is a while and the inside one is a scan. For some reason the inside loop scan is only called once, because the msgInfo after "proven inocent" only apears once. I think this means the while only run's once but I can't figure out why. This is the code I've come up with:

isNew = true ;//guilty untill proven inocent

if tcInput.open(inTable) and tcOutput.open(toTable) and tcOutput.edit() then
while tcInput.eot() = false
scan tcOutput
for upper(tcOutput."ProductName") = upper(tcInput."ProductName")
and upper(tcOutput."PresentationCode") = upper(tcInput."PresentationCode")
and upper(tcOutput."Volume") = upper(tcInput."Volume")

: isNew = false ;//proven inocent
msgInfo(string(isNew),tcOutput."ProductName")
endScan

if isNew then ;//dealing with the guilty
tcOutput.end()
tcOutput.insertRecord() ; Insert a new record
tcOutput.copyRecord(tcInput) ; Copy from tcInput to tcOutput
tcOutput.postRecord()
endIf
tcInput.nextRecord()
endWhile

tcOutput.endEdit()
tcInput.close()
tcOutput.close()
else
errorShow()
endIf
This is in P9 and the code is in a script file
any clues? Thanks

Another Leo
_ _ ___________________ _ _
We are Micro$oft
You will be assimilated
Resistance is futile
 Posted 18 years ago (Thread Starter)
Comment Quote
About Leo -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #8802, 37 replies
Thread Started 1/2/2003 10:33:00 PM
View Counter=4926
Last Reply Posted 1/24/2018 3:15:52 AM)
Location=Civilized Africa  
Joined=19 years ago   MB Posts=96  
CliffSuttle
Michigan
Not sure what your probably is, but there does seem to be better ways to accomplish the task. Why not use locate commands and make the outside loop a scan. I see that you are not sure about capitalization in the table, but there is a function called "IgnoreCaseInLocate" that would solve that problem.

Cliff Suttle
Antler Software Technologies
www.AntlerSoftware.com
Cliff's Corner Author, Prestwood eMag

 Posted 18 years ago
Comment Quote
About CliffSuttle -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About CliffSuttle

Mr. Suttle is the President of Antler Software Technologies and is one of the leading authorities on relational data bases in the mid-west. He has been working with Paradox since version 1.0 for DOS (circa 1987).


Post ID #8803 (Level 1.1)  Reply to 8802
Thread Started 1/2/2003 11:34:00 PM
View Counter=2
Location=Michigan  
Joined=20 years ago   MB Posts=90   KB Posts=4  
Langley McKelvy
Harris County Texas, USA
CopyFromArray() copies the fields in the order they appear and the receiving table has to match the original. There is an exception. If the recieving table matches the original table, but has extra fields on the end, then it will work leaving the end fields empty.
Mac

[i]"A king will have his way in his own hall, be it folly or wisdom."[/i] - Gandalf
 Posted 18 years ago
Comment Quote
About Langley McKelvy -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Langley McKelvy
Computer Crimes Investigator / Computer Forensics Specialist Paradox for DOS/Windows Programmer

Post ID #8850 (Level 1.2)  Reply to 8802
Reply Posted 1/8/2003 6:11:00 AM
Location=Harris County Texas, USA  
Joined=19 years ago   MB Posts=387  
Langley McKelvy
Harris County Texas, USA
>>"So what was first the chicken or the egg?"

George Carlin answered this question quite nicely.

"The chicken is an actual chicken, while an egg is a potential chicken. Since actuality precedes potentiality [in a pristine environment], the chicken had to come first."
Mac

[i]"A king will have his way in his own hall, be it folly or wisdom."[/i] - Gandalf
 Posted 18 years ago
Comment Quote
About Langley McKelvy -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Langley McKelvy
Computer Crimes Investigator / Computer Forensics Specialist Paradox for DOS/Windows Programmer

Post ID #8908 (Level 1.3)  Reply to 8802
Reply Posted 1/13/2003 6:00:00 AM
Location=Harris County Texas, USA  
Joined=19 years ago   MB Posts=387  
Tony M
 (Inactive)
I believe you want your 'if isnew' code to be inside the scan loop, if you might have multiple records that could match.

Also, you are doing a postrecord(), rather than unlockrecord(). At the very least, do both. However, if posting the record could cause a flyaway, that could be your problem. The scan might not be completing properly because the tcOutPut tcursor has changed locations. You might have to do a locate, or series of locates, instead of a scan. By doing a postrecord(), you are leaving each record locked that you change. If you do postrecord(), then unlockrecord(), you get around that. But then you may still have a record that has jumped to another location in the table and messed up the scan.

On another note, I'd code this a bit differently; but then that's me.
Catch each error so you know where it came from:
switch
case not tcInput.open(inTable) :
errorshow("tcInPut Open")
return
case not tcOutput.open(toTable) :
errorshow("tcOutPut Open")
return
case not tcOutput.edit() :
errorshow("tcOutPut Edit")
return
endSwitch
 Posted 18 years ago
Comment Quote
About Tony M -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.

Post ID #8804 (Level 1.4)  Reply to 8802
Reply Posted 1/2/2003 11:45:00 PM
Location= 
Joined=19 years ago   MB Posts=410   KB Comments=1  
Tony M
 (Inactive)
You are wrapping your entire construct in an 'if ... then ... else errorshow() endif" construct. Get rid of the if... completely.

Just start with the switch ... endswitch, then use the while....endwhile construct. No ELSE clause needed, since the errorshow() within each case statement will catch any of those error conditions.

If none of those conditions exist, then the rest of your code will execute. If an error condition exists, then none will execute since after the errorshow() in each case statement there is a return.

switch
....
endswitch

while
...
endwhile
 Posted 18 years ago
Comment Quote
About Tony M -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.

Post ID #8815 (Level 1.5)  Reply to 8802
Reply Posted 1/3/2003 12:16:00 PM
Location= 
Joined=19 years ago   MB Posts=410   KB Comments=1  
solinox
 (Inactive)
Austin, Texas
How about this: Subtract your "old" table from your "new" table, then add the remaining "new" table to the "old" table. If you're needing to hang on to all your "new" table records for something else, just copy it to a temporary table before doing the subtract and delete it after the add. So it would look something like this:

tcInput.open(inTable)
tcOutput.open(outTable)
tcInput.copy(tempTable)
tcInput.close()
tcInput.open(tempTable)
tcOutput.subtract(tcInput)
tcInput.add(tcOutput)
tcInput.close()
tcOutput.close()
 Posted 18 years ago
Comment Quote
About solinox -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #8821 (Level 1.6)  Reply to 8802
Reply Posted 1/6/2003 5:19:00 AM
Location=Austin, Texas  
Joined=19 years ago   MB Posts=15  
solinox
 (Inactive)
Austin, Texas
You might try the subtract/add routine and see how it works with your data. My understanding is that subtract looks at the fields that are similar between the two tables, anything that *could* be a key, not just a declared primary key. And since it appears that your fields have the same names, an Add() should put everything in the right place. I'd be interested to find out if that routine works with your data.
 Posted 18 years ago
Comment Quote
About solinox -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #8829 (Level 1.7)  Reply to 8802
Reply Posted 1/7/2003 5:18:00 AM
Location=Austin, Texas  
Joined=19 years ago   MB Posts=15  
Tony M
 (Inactive)
While subtract *may* compare fields appropriately, 'add' does not. It adds fields in order.

So by adding a key, you are causing the fields in the table to be 'misaligned'; an add would fail.

As to your locate issue, how about a secondary index that has the fields to match in it?

You could switchindex to that 2ndary, and use a qlocate or setrange to find a match. These would be virtually instantaneous. Just be sure to switchindex() to release the index before adding a 'new' record.
 Posted 18 years ago
Comment Quote
About Tony M -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.

Post ID #8830 (Level 1.8)  Reply to 8802
Reply Posted 1/7/2003 5:53:00 AM
Location= 
Joined=19 years ago   MB Posts=410   KB Comments=1  
Tony M
 (Inactive)
And actually, you could do a setrange on just *his* key, then only compare the other fields in a scan (or qlocate/setrange). That's if the tcursor has any records at that point; if not you know the record is 'new'.

This makes your record comparison instantaneous; you should speed the operation x20 or so, more as your destination table increases in size. :)
 Posted 18 years ago
Comment Quote
About Tony M -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.

Post ID #8831 (Level 1.9)  Reply to 8802
Reply Posted 1/7/2003 5:56:00 AM
Location= 
Joined=19 years ago   MB Posts=410   KB Comments=1  
Tony M
 (Inactive)
To give an idea of what I mean....

Create a *NON* case sensitive secondary index in your version of the table on

"ProductName"
"PresentationCode"
"Volume"

Call it "myProductName"
var
tcInput,
tcOutput,
tcNewKey tcursor
stInTable,
stToTable string
dyCopy dynarray[] anytype
liNewKey longint
endvar
stInTable="hisTable.db"
stToTable="myTable.db"

switch
case not tcInput.open(stInTable) :
errorshow("tcInPut Open")
return
case not tcOutput.open(stToTable,"myProductName") : ; open on 2ndary index
errorshow("tcOutPut Open")
tcInput.close()
return
case not tcOutput.edit() :
errorshow("tcOutPut Edit")
tcInput.close()
tcOutput.close()
return
endSwitch

; this is a routine for creating your own unique keys
; newkeys.db has 2 fields
; 'tablename', (alpha) which holds the table to create a key for...Primary index, and qlocate in UPPER case
; 'currentkey', which is the current key (longint)
tcNewKey.open("newKeys.db")
if not tcNewKey.qlocate(upper(tcOutput.tablename())) then
tcNewKey.edit()
tcNewKey.insertafterrecord()
tcNewKey."tablename"=upper(tcOutput.tablename())
; I usually set the 'default' value of 'currentkey' field to zero
; but show manual method here
tcNewKey."currentkey"=0
tcNewKey.unlockrecord()
tcNewKey.endedit()
endif

scan tcInput :
; if you can find 'his' key, then check to see if those other fields match
; you could actually skip this step, and do the full setRange(), as long as
; you make that secondary index NON-case sensitive
if tcOutput.qlocate(tcInput."ProductName") then
tcOutPut.setrange(tcInput."ProductName",tcInput."PresentationCode",tcInput."Volume",tcInput."Volume")
if tcOutput.nrecords()>0 then ; if those other fields match, reset the setrange and do next record
tcOutput.setrange()
loop
endif
tcOutput.setrange()
endif
; otherwise, copy the record to a dynarray
tcInput.copytoarray(dyCopy)

; myIndexField is the field you created to contain your new key
; liNewKey is your own key
if not tcNewKey.qlocate(upper(tcOutput.tablename())) then
msgstop("Error","BIG error. Key not found."
tcNewKey.close()
quitloop
endif
tcNewKey.edit()
liNewKey=tcNewKey."currentkey"+1
tcNewKey."currentkey"=liNewKey
try
tcNewKey.unlockrecord()
onFail
errorclear()
endTry
tcNewKey.endedit()
dyCopy["myIndexField"]=liNewKey
tcOutPut.insertafterrecord()
tcOutput.copyfromarray(dyCopy) ; copy record data, including new key, to 'your' table
try
tcOutput.unlockrecord()
onFail
errorshow("error copying record")
endTry
endscan
tcOutput.endedit()
tcOutput.close()
tcInput.close()
Whether the above is the exact logic of what you need, I can't be sure. You may need to test for more matches between the two tables/records. Hopefully it can get you started along the right path (my view of right, anyway Big Grin! )
 Posted 18 years ago
Comment Quote
About Tony M -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.

Post ID #8832 (Level 1.10)  Reply to 8802
Reply Posted 1/7/2003 7:32:00 AM
Location= 
Joined=19 years ago   MB Posts=410   KB Comments=1  
Tony M
 (Inactive)
I'd like to add that the 'new key' routine is actually a library method I use:
method cmGetKey(var tc tcursor, var liKey longint) logical
; this is a routine for creating your own unique keys
; tcNewKey has 2 fields; 'tablename', (alpha) which holds the table to create a key for...Primary index, and qlocate in UPPER case
; 'currentkey', which is the current key (longint)
tcNewKey.forcerefresh() ; in case another network user has entered a new table key
if not tcNewKey.qlocate(upper(tc.tablename())) then
tcNewKey.edit()
tcNewKey.insertafterrecord()
tcNewKey."tablename"=upper(tc.tablename())
liKey=1
tcNewKey."currentkey"=liKey
try
tcNewKey.unlockrecord()
onFail
liKey=0
tcNewKey.canceledit() ; something is wrong, so cancel the current editing session
return False
endTry
else
liKey=tcNewKey."currentkey"+1
tcNewKey.edit()
tcNewKey."currentkey"=liKey
try
tcNewKey.unlockrecord()
onFail
liKey=0
tcNewKey.canceledit()
tcNewKey.close()
return False
endTry
endif
tcNewKey.endedit()
return True
endMethod

When I want to get a key for a table, I use

if lib.cmGetKey(tcCurrent,liNewKey) then
tcCurrent."keyfield"=liNewKey
endif
By using tc.tablename(), you will always either find your table in the 'keys' table, or create a new record and begin a new key series.

I don't OPEN tcNewKey here, since I do that in the lib open(); where I also test for being able to open it.
(above is somewhat from memory, since I don't have this lib/routine here)
 Posted 18 years ago
Comment Quote
About Tony M -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.

Post ID #8835 (Level 1.11)  Reply to 8802
Reply Posted 1/7/2003 10:26:00 AM
Location= 
Joined=19 years ago   MB Posts=410   KB Comments=1  
Tony M
 (Inactive)
If you use a DYNARRAY to copy[to,from]array, then you don't have the normal 'fields must match' issue.

Paradox matches up dynarray data, based on the dynarray element name, to fields with the same name.

If there are fields without matching dynarray elements, they are simply skipped.

If there are dynarray elements without matching fields, they are simply skipped.

That's why, in my example, I used a dynarray. You needed to add your own key, and by using a dynarray it didn't matter that your table didn't exactly match the source table.
 Posted 18 years ago
Comment Quote
About Tony M -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.

Post ID #8852 (Level 1.12)  Reply to 8802
Reply Posted 1/8/2003 6:26:00 AM
Location= 
Joined=19 years ago   MB Posts=410   KB Comments=1  
Tony M
 (Inactive)
I'd like to add that this is particularly useful for taking a wide table and inserting the correct field data into several 'normalized' tables.

Incoming Table:
incoming.db
[liKey] *
[stFieldOne]
[stFieldTwo]
[stFieldThree]
[stFieldFour]
[stFieldFive]

3 Tables to copy data to:
Master.db
[liKey] *
[stFieldOne]
[stFieldTwo]

ChildOne.db
[liKey] *
[stFieldThree]

ChildTwo.db
[liKey] *
[stFieldFour]
[stFieldFive]

var
tcI,
tcM,
tcO,
tcT tcursor
dyC dynarray
endvar

tcI.open("incoming.db")
tcM.open("master.db")
tcM.edit()
tcO.open("childOne.db")
tcO.edit()
tcT.open("childTwo.db")
tcT.edit()

scan tcI :
tcM.copytoarray(dyC)
if not tcM.qlocate(dyC["liKey"]) then
tcM.insertafterrecord()
endif
tcM.copyFromArray(dyC)
tcM.unlockrecord()

if not tcO.qlocate(dyC["liKey"]) then
tcO.insertafterrecord()
endif
tcO.copyFromArray(dyC)
tcO.unlockrecord()

if not tcT.qlocate(dyC["liKey"]) then
tcT.insertafterrecord()
endif
tcT.copyFromArray(dyC)
tcT.unlockrecord()


endScan
close()AllTcursors
In this way, you took an incoming 'wide' table and converted it all at once to disparate normalized tables.

I made the index key the same in order to keep things simple.

You could, of course, always have secondary indexes on the 'child' tables, open them on the 2ndary indexes, and do qlocates on a different value from the dynarray or 'master.db' table that you just qlocated to the proper record.

NOTE: I didn't include error checking here. You will want to add your own, of course.
 Posted 18 years ago
Comment Quote
About Tony M -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.

Post ID #8853 (Level 1.13)  Reply to 8802
Reply Posted 1/8/2003 7:43:00 AM
Location= 
Joined=19 years ago   MB Posts=410   KB Comments=1  
Tony M
 (Inactive)
On another note, I use the tcursor methodology rather than a query and table add for a specific purpose.

NETWORKS

Rather than move data enmasse, and have the server trying to keep up with all those record changes, as well as the network traffic created, I use tcursors.

Fewer lock problems than trying to do a table add.

More importantly, I'm updating ONE record at a time which Paradox, and the network, handle VERY well.

I've found a significant performance boost when using tcursor to add/update records on the network.
 Posted 18 years ago
Comment Quote
About Tony M -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.

Post ID #8855 (Level 1.14)  Reply to 8802
Reply Posted 1/8/2003 7:54:00 AM
Location= 
Joined=19 years ago   MB Posts=410   KB Comments=1  
Tony M
 (Inactive)
That setrange code ONLY works if you have the 2ndary index in place as described.

And having the ."volume" field twice at the end wasn't a mistake. Needed twice as the range on the final field - you just have an exact match so your start and end range values are the same.
 Posted 18 years ago
Comment Quote
About Tony M -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.

Post ID #8866 (Level 1.15)  Reply to 8802
Reply Posted 1/9/2003 5:36:00 AM
Location= 
Joined=19 years ago   MB Posts=410   KB Comments=1  
Tony M
 (Inactive)
You used tcInput."hisKey" as the first value. Not sure what that is; another name for 'ProductName'? Below is the code I posted, which is apparently different from what you used (?).
tcOutPut.setrange(tcInput."ProductName",tcInput."PresentationCode",tcInput."Volume",tcInput."Volume")
It relies on the specific 2ndary index on the 3 fields above on the TARGET table.

If I'm not making sense, please let me know. I often code easier than explain in English (and yes, that's my native language).
 Posted 18 years ago
Comment Quote
About Tony M -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.

Post ID #8867 (Level 1.16)  Reply to 8802
Reply Posted 1/9/2003 5:47:00 AM
Location= 
Joined=19 years ago   MB Posts=410   KB Comments=1  
Tony M
 (Inactive)
Aha! Caughtya! Big Grin!

Well, I understand about confidentiality.

But if you withhold info, you have to remember to add in that bit of knowledge that only you know about whenever you get a suggestion.

Basically, you just add 'hiskey' to the beginning of the 2ndary index. Then add it at the beginning as another parameter to the setrange.

At least, that appears to be the case from what you are saying.

2ndary
"hisKey"
"ProductName"
"PresentationCode"
"Volume"
tcOutPut.setrange(
tcInput."hisKey",
tcInput."ProductName",
tcInput."PresentationCode",
tcInput."Volume",
tcInput."Volume"
)
 Posted 18 years ago
Comment Quote
About Tony M -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.

Post ID #8877 (Level 1.17)  Reply to 8802
Reply Posted 1/10/2003 5:21:00 AM
Location= 
Joined=19 years ago   MB Posts=410   KB Comments=1  
Tony M
 (Inactive)
Works is good Big Grin!

Fast is good Big Grin!

I mention this, since you may want to test the incoming data before attempting to apply it to YOUR table(s). The article may help you with that.

On another note, that code I posted to copy from one table to several others had an error.

scan tcI :  
tcM.copytoarray(dyC)
should have been
scan tcI :  
tcI.copytoarray(dyC)
This, in order to start the dynarray out with data from the Incoming table, rather than the Master table.
 Posted 18 years ago
Comment Quote
About Tony M -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.

Post ID #8906 (Level 1.18)  Reply to 8802
Reply Posted 1/13/2003 3:39:00 AM
Location= 
Joined=19 years ago   MB Posts=410   KB Comments=1  
Tony M
 (Inactive)
if tcFirstStop.locate("Propriatarycode",tcSource.Propriatarycode

Is tcFirstStop indexed on "Propriatarycode"? If so, replace locate with qlocate; should speed you up about 10x. If not, creating a secondary index on "Propriatarycode" should help substantially.

if tcStockTable.locate

Same with tcStockTable.

Let us know; we can always look for other areas to improve speed if this doesn't help.

Realize, though, that sometimes there IS no easy way to speed things up.
 Posted 18 years ago
Comment Quote
About Tony M -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.

Post ID #9150 (Level 1.19)  Reply to 8802
Reply Posted 3/3/2003 4:25:00 AM
Location= 
Joined=19 years ago   MB Posts=410   KB Comments=1  
Tony M
 (Inactive)

I was wondering if that screening method with SetRange you did on the first table, could be applied here and if it would help with the speed.

In the first example, you were using a scan within a scan, I think, to work on multiple records. We used SetRange() to narrow down the number of records your code had to scan thru in the second scan loop. Here, we don't have a second set of records to scan; only a single record to (q)locate.

So here, you are only working with one record once the scan has arrived at the 'master' record within the scan.

I think qlocate will get you to the answer (matching record or not) as quickly as setrange would.

You CAN save a few milliseconds per record, however, in another way.

  dyCopy["StockCode"] = tcStockTable.StockCode
tcDestination.insertafterrecord()
tcDestination.copyFromArray(dyCopy)
could be

  tcDestination.insertafterrecord() 
tcDestination."stockCode"=tcStockTable."stockCode"
Rather than copying to a dynarray first, then copying from the dynarray, copy directly from tcursor to tcursor.

That dynarray methodology is great for the type of thing you needed before (disparate table structures and a generic routine to handle them). But where you can, is best to go direct and cut out the middleman (IMHO).

 
 Posted 18 years ago
Comment Quote
About Tony M -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.

Post ID #9156 (Level 1.20)  Reply to 8802
Reply Posted 3/3/2003 6:23:00 AM
Location= 
Joined=19 years ago   MB Posts=410   KB Comments=1  
Tony M
 (Inactive)
OOOOOOPS!!!!!

I missed the initial copytoarray() !!!

I thought you were only transferring the stockcode field, rather than a whole record.

Ignore my suggestion about saving time copying directly. You appear to have it right!

Sorry for any confusion....

 
 Posted 18 years ago
Comment Quote
About Tony M -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.

Post ID #9158 (Level 1.21)  Reply to 8802
Reply Posted 3/3/2003 6:53:00 AM
Location= 
Joined=19 years ago   MB Posts=410   KB Comments=1  
Tony M
 (Inactive)
One potential show stopper is if you don't have a

tcDestination.unlockrecord()

in there after the

tcDestination.copyfromarray()

Without an explicit unlock, EVERY record you add will be locked by the time you are done.

This can slow Paradox and create many 'issues'.

Just wanted to be sure to mention it in case you didn't have it in there.

I normally do something like:
tcDestination.copyfromarray()
try
tcDestination.unlockrecord()
onFail
errorclear() ; actually, handle the error or at least create a log file of errors
endTry
 Posted 18 years ago
Comment Quote
About Tony M -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.

Post ID #9174 (Level 1.22)  Reply to 8802
Reply Posted 3/4/2003 3:20:00 PM
Location= 
Joined=19 years ago   MB Posts=410   KB Comments=1  
Leo
Civilized Africa
Good suggestions all and thanks.
The most obvious mistake I’ve now found is that I have to reset isNew to true before the while loop is repeated since it may have been set to false if the record was a duplicate. Once it’s set to false no other record would be copied, and since the first record was duplicated none were copied.

The code inside the scan, after the colon, will only be executed when the new record match the old – in other words it is not new and that is why isNew is set to false. The stuff in the “if isNew” should be executed if the scan don’t find the new record in the old table in other words when the record really is new.

I tried locate() first and it didn’t bother the capitalisation (so probably IgnoreCaseInLocate is switched on already/by default) but it seemed to have a limit on the number of columns in each record I can compare. I have to compare at least the three I showed and possibly more once I get more specs.

Jed I’m doing the postrecord() in order to save the record I just copied so it doesn’t get overwritten by the next one. Help files led me to believe that this is necessary, though we all know how trustworthy they are. Is it really necessary to bother with that and what should I really do so that doesn’t happen and to make sure no record is lost?

Jed I like the switch you use but I can’t figure out where to put the code I want executed if an error doesn’t occur Big Grin! LOL.
About the flyaway I haven’t a clue. Didn’t seem like anything was flying when I tried to run it. LOL
Geebers on this learning curve I’m pulling like 8 g’s. It’s making me feel dizzy! Not long before i'll be Geek Alert!
Another Leo
_ _ ___________________ _ _
We are Micro$oft
You will be assimilated
Resistance is futile
 Posted 18 years ago (Thread Starter)
Comment Quote
About Leo -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #8805 (Level 1.23)  Reply to 8802
Reply Posted 1/3/2003 4:37:00 AM
Location=Civilized Africa  
Joined=19 years ago   MB Posts=96  
Leo
Civilized Africa
Thanks Jed I’m doing it exactly like you suggested and it works well now. I have another problem now though. The input file is 9935 records big for the first update, where I have two records in the old file. Every record in the update file is compared with every record in the old file. If the record isn’t found it gets added. The next record from the update file is compared to all the records in the old file including the record that’s just been added. This means the job of comparing becomes bigger and bigger all the time. That means the inside loop has to compare 1x2x3x4x5x6x7x8x9x…x9935 records. That means this is highly inefficient code and it will probably take a week for the record to be updated the first time round if the old table was emptied. So anybody got any ideas how I make this more efficient?
Another Leo
_ _ ___________________ _ _
We are Micro$oft
You will be assimilated
Resistance is futile
 Posted 18 years ago (Thread Starter)
Comment Quote
About Leo -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #8819 (Level 1.24)  Reply to 8802
Reply Posted 1/5/2003 9:22:00 PM
Location=Civilized Africa  
Joined=19 years ago   MB Posts=96  
Leo
Civilized Africa
This sounds like an excellent idea solinox, I’ll do my best to make it fit.

I have a few problems though that my situation brings about. It works like this: I get this table from a third party. The primary-key he uses is only four characters long and therefore codes will get recycled if records change a lot. On our side we have to keep all the records for at least 3 years. So when he changes and recycles his primary key we will end up with two records with that primary key with different particulars. That meant we had to add our own primary key to our copy of his tables and that we have several records in there for each of his primary codes. You will see in the code I posted at the top that I check his ProductName presentation code and volume in the scan. This is necessary to know if the record is really old or in fact the result of a recycled primary-key code.

So what I’d like to know before I implement your solution solinox, is does the subtract method check the whole record to see if its identical, or just the primary key. Also, my outTable now looks different from the inTable because of the new primary-key I had to add. I’ve already found out to my frustration that I can’t copy the records straight into the output table because they get dumped in the wrong columns. So since I did the code at the top I had to add them one column at a time. Thus I replaced the tcOutput.copyRecord with this

tcOutput."ProductName"	= upper(tcInput."ProductName")
tcOutput."PresentationCode" = upper(tcInput."PresentationCode")
tcOutput."Volume" = upper(tcInput."Volume")
tcOutput."VolumeDescription" = upper(tcInput."VolumeDescription")
tcOutput."ManufacturerCode" = upper(tcInput."ManufacturerCode")
tcOutput."EffectiveDate" = upper(tcInput."EffectiveDate")
tcOutput."DiscontinuedDate" = upper(tcInput."DiscontinuedDate")
I’m sorry to dump this on you guys. I don’t really think this work is meant for beginners like me LOL.

I’d copy my modified table to a second temp table and implement solinox’s solution but I’m not sure what the result will be with the duplicate primary key and all that.

Thanks for all the helps so far :)
Another Leo
_ _ ___________________ _ _
We are Micro$oft
You will be assimilated
Resistance is futile
 Posted 18 years ago (Thread Starter)
Comment Quote
About Leo -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #8828 (Level 1.25)  Reply to 8802
Reply Posted 1/6/2003 10:27:00 PM
Location=Civilized Africa  
Joined=19 years ago   MB Posts=96  
Leo
Civilized Africa
Geebers Jed thats a mouth full! Big Grin! Thanx. I'll spend today working through that and integrating it into my work when I understand it.
In the mean time I managed to work down the code I posted first to avoid unnecesary cycles so that it now only take less than 40 minutes to scan through the almost 10 000 records in the table. Its not really that great but least its better than the two weeks it was gonna take with the code as it was on my first try.
I'll report back on what I manage to do with your code Big Grin! Thanx a milion :)
Another Leo
_ _ ___________________ _ _
We are Micro$oft
You will be assimilated
Resistance is futile
 Posted 18 years ago (Thread Starter)
Comment Quote
About Leo -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #8847 (Level 1.26)  Reply to 8802
Reply Posted 1/7/2003 10:03:00 PM
Location=Civilized Africa  
Joined=19 years ago   MB Posts=96  
Leo
Civilized Africa
I may have run into a snag here. The database admin just told me that this Stock table I have to get the data into, will now have to grow by several other fields that doesn’t include the data to be imported i.e. they want to put the price of the product in there also. This stuff was actually finalised before but the third party changed his table layouts... Now before I screw stuff up too badly I’d like to know if this copyFromArray will add the fields in order. I hope to hear it will take the field name into consideration and that order doesn’t concern it at all. If it doesn’t I’ll have to make a tempStock table also with only his data and the new key, test against a tempInput table and finally update the info from the tempStock table to the Stock table? Sounds confusing. Unless there’s some other simple way to get around it? I have asked about splitting the Stock and stock prieces into different tables with a 1-1 relationship but there seems to be a good reason not to do that.
Another Leo
_ _ ___________________ _ _
We are Micro$oft
You will be assimilated
Resistance is futile
 Posted 18 years ago (Thread Starter)
Comment Quote
About Leo -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #8848 (Level 1.27)  Reply to 8802
Reply Posted 1/8/2003 3:57:00 AM
Location=Civilized Africa  
Joined=19 years ago   MB Posts=96  
Leo
Civilized Africa
Jed that is so way cool! That normalisation example is what I’ve been looking for for ages! My dad’s been nagging at me to fix up his literature database and I’ve tried working it over with SQL on and off for almost two years. There’s just always something that doesn’t work. I bet I’ll be getting a bigger Christmas pressie this year LOL.
Anyhow back to the update story. I fitted your code to the tables and I found out what you said up there about the dynamic array trick. Quite funky actually.
Just for fun I decided to put this data block on it to give credit where its due:
{Import script
Author: Jedstar
Applied: Leo
Inspired: Solinox
Finds new records in Med.db and adds them to Stock.db}
I have two questions now: Why is it necessary to have the secondary index on the stock table if we have this code there apparently doing the same thing?
tcOutPut.setrange(tcInput."HisKey",tcInput."ProductName",tcInput."PresentationCode",
I’m almost sure this is my misunderstanding what the code does.
Second the code works great on the first pass. I then changed some records in his input table. When I ran it again it added the records I changed – like it should – but also it added some other records – making duplicates. Of 9935 I changed two – so I should have had 9937 after running the script a second time but I got 10200 or there about. I’m thinking I did something wrong but I don’t know what.
About the efficiency of the code, it cut my code’s time almost in half! Most excellent! Thanx :)
Another Leo
_ _ ___________________ _ _
We are Micro$oft
You will be assimilated
Resistance is futile
 Posted 18 years ago (Thread Starter)
Comment Quote
About Leo -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #8864 (Level 1.28)  Reply to 8802
Reply Posted 1/9/2003 12:41:00 AM
Location=Civilized Africa  
Joined=19 years ago   MB Posts=96  
Leo
Civilized Africa
Regarding the second issue of duplication of the wrong records. I’ve looked at the wrongly duplicated records and they all have lots of empty fields. Specifically many of the fields I sort on are empty. I didn’t think it would matter, as one empty field is the same as another. Isn’t it? Can this be why it’s not working perfectly? This third party data is so much hassle I’d leave it out if it wasn’t so crucial. Unfortunately the whole project depends on it Big Grin! arrrr
Another Leo
_ _ ___________________ _ _
We are Micro$oft
You will be assimilated
Resistance is futile
 Posted 18 years ago (Thread Starter)
Comment Quote
About Leo -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #8865 (Level 1.29)  Reply to 8802
Reply Posted 1/9/2003 5:21:00 AM
Location=Civilized Africa  
Joined=19 years ago   MB Posts=96  
Leo
Civilized Africa
Jed the data I have to import is from this third party and is proprietary. Most of my users are already subscribed to his service to get the info. My program will lose functionality without his info, and users will have to input droves of data manually – basically every thing they ever want to have in their stock table. Of course they still can do it manually if they really don’t like to subscribe to his service.
Anyhow because it is proprietary I’m not allowed to associate my product with his before he’s approved it. Now his primary key looks like “Unique (His Company Name) Product Code” and so I’ve rather left it out of the stuff I’ve posted lest some one tells him and he sew me and takes my underpants. Also I thought it was quicker to post and would take up les space if I didn’t post all the fields I have to check on.
All this by the way of saying ProductName is the second field I have to compare my records for. I suppose this means that one line is where all my trouble is? I'll try and fix it from what you said up there. If it still doesn't work can I give you the code privately?
Oh and you're quite good at explaining thats why I keep coming back to you cause this is very new to me.
Another Leo
_ _ ___________________ _ _
We are Micro$oft
You will be assimilated
Resistance is futile
 Posted 18 years ago (Thread Starter)
Comment Quote
About Leo -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #8876 (Level 1.30)  Reply to 8802
Reply Posted 1/10/2003 12:54:00 AM
Location=Civilized Africa  
Joined=19 years ago   MB Posts=96  
Leo
Civilized Africa
Thanx Jed that works well now Big Grin! Most Excelent Big Grin!

The weird thing about this confidentiality is that he has to aprove our program before we can associate with his data. But we have to make it work first. And for that we need his data. LOL Big Grin! So what was first the chicken or the egg? LOL Big Grin!

For an empty table the scan time is 37 minutes to add all the data. For a full table with one exception the time is like 15 sec's - I tested my first try code and the second pass took more than an hour, before I crashed it manually and it still hadn't finished, just to show how cool this code works Big Grin!

My last isshue (I sincerely hope) Is that I still get 6 duplicates. On the third pass they get added again and on the forth pass once more. When I removed those 6 recods from the input table nothing gets duplicated. I'll report on what their problem was after today, but since its only ever those 6 I'm thinking its the records. Now I have to find what those 6 records have in commen.
Another Leo
_ _ ___________________ _ _
We are Micro$oft
You will be assimilated
Resistance is futile
 Posted 18 years ago (Thread Starter)
Comment Quote
About Leo -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #8902 (Level 1.31)  Reply to 8802
Reply Posted 1/12/2003 9:54:00 PM
Location=Civilized Africa  
Joined=19 years ago   MB Posts=96  
Leo
Civilized Africa
Ok I’ve found the problem. In the stock table I want the data imported to, I use referential integrity on the presentation code field. The data that our favourite third party has supplied us isn’t exactly perfect, and I think referential integrity is not set on his tables. I found four codes that weren’t in his presentation code parent table. Because the referential integrity wouldn’t allow those codes to be entered, the fields were left blank resulting in the input table being different from the output. Of course since his data is error prone proved now, I’ll have to go write error handling code to make sure records with invalid referential integrity isn’t added over and over. Harrrrr
Another Leo
_ _ ___________________ _ _
We are Micro$oft
You will be assimilated
Resistance is futile
 Posted 18 years ago (Thread Starter)
Comment Quote
About Leo -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #8904 (Level 1.32)  Reply to 8802
Reply Posted 1/13/2003 1:42:00 AM
Location=Civilized Africa  
Joined=19 years ago   MB Posts=96  
Leo
Civilized Africa
Then again...
The egg is an actual egg, while the chicken is a potential egg, and even then only under ideal conditions ie it has to be a live chicken, a hen with access to a rooster .... etc etc LOL Big Grin!
Another Leo
_ _ ___________________ _ _
We are Micro$oft
You will be assimilated
Resistance is futile
 Posted 18 years ago (Thread Starter)
Comment Quote
About Leo -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #8916 (Level 1.33)  Reply to 8802
Reply Posted 1/13/2003 9:43:00 PM
Location=Civilized Africa  
Joined=19 years ago   MB Posts=96  
Leo
Civilized Africa
This darn problem haunts me again!
I’ve now had to extend the program to also import all the child tables of this parent table. I’ve built some code that works fine. The problem is that there are 44 child tables and the first one I tried the code on took 4 hours. I’ve been trying to use this masking idea that Tony did for the parent but I just can’t get my head around it. The body of the code looks like this:
scan tcSource :
;//find the record in FirstStop to get Typecodes and Productname
if tcFirstStop.locate("Propriatarycode",tcSource.Propriatarycode) then
tcSource.copytoarray(dyCopy)

;//find the record in SeStock using Typecodes and Productname
if tcStockTable.locate( "ProductName",tcFirstStop.ProductName,
"Typecode1 ",tcFirstStop.Typecode1,
"Typecode2 ",tcFirstStop.Typecode2) then

;// copy the appropriate StockCode to the array
dyCopy["StockCode"] = tcStockTable.StockCode
tcDestination.insertafterrecord()
tcDestination.copyFromArray(dyCopy)
endIf
endIf
endScan
tcDestination.endEdit()
Can some one please help me to fit that system to speed it up a little? There’s a little more complication to this one cause I have to first get the new primary key from the table I created in the code that Tony did. I’m starting to panic cause I’m overdue and my boss is getting nervous around me.
Another Leo
_ _ ___________________ _ _
We are Micro$oft
You will be assimilated
Resistance is futile
 Posted 18 years ago (Thread Starter)
Comment Quote
About Leo -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9147 (Level 1.34)  Reply to 8802
Reply Posted 3/3/2003 3:07:00 AM
Location=Civilized Africa  
Joined=19 years ago   MB Posts=96  
Leo
Civilized Africa
Thanks for the suggestion Tony.
PropriartaryCode is the primary key of FirstStop so
tcFirstStop.qLocate(tcSource.PropriartaryCode)
works great. I had to make a Second key to open Stock table with like
tcStockTable.open(sStockTable,"SecondKey")
to change the second one to
tcStockTable.qLocate(tcFirstStop.ProductName,
tcFirstStop.TypeCode1,
tcFirstStop.TypeCode2)
I’ll test the speed now to see if there is any improvement.
I was wondering if that screening method with SetRange you did on the first table, could be applied here and if it would help with the speed. It’s more complicated so I can’t figure out how to apply it. That’s because I have to take the Source table’s primary key, find the ProductCode and two TypeCodes in FirstStop and use that to find the ProductCode in SeStock that is the new primary key. The idea is that the destination table will have the product code we created with your Unique key routine as a primary key instead of the proprietary code, in the child tables.
Thanks for the help. This one looks like its beating me. I sure hope there is some way to speed this up cause this code can not run for 4 days to import the starting data, or to update it every 3 months.
Another Leo
_ _ ___________________ _ _
We are Micro$oft
You will be assimilated
Resistance is futile
 Posted 18 years ago (Thread Starter)
Comment Quote
About Leo -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9153 (Level 1.35)  Reply to 8802
Reply Posted 3/3/2003 5:19:00 AM
Location=Civilized Africa  
Joined=19 years ago   MB Posts=96  
Leo
Civilized Africa
Thanks for the help Tony and the lesson. Somehow I feel I should have seen that scan within a scan thing before. I must have been confused by the similarity between the two routines so I didn’t notice the differences so well. I guess this comes with being a second language OPAL speaker. The two qLocate’s seem to put some zing under its tail though. The old code did less than 8 records per second. On some early testing I got 35 and 173 records per second! I estimate that my 15000 record table that took 30 min's will take about 7 minutes or less now! Have to do some more testing under the full loud though. Thanks again.
Another Leo
_ _ ___________________ _ _
We are Micro$oft
You will be assimilated
Resistance is futile
 Posted 18 years ago (Thread Starter)
Comment Quote
About Leo -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9165 (Level 1.36)  Reply to 8802
Reply Posted 3/3/2003 9:40:00 PM
Location=Civilized Africa  
Joined=19 years ago   MB Posts=96  
Most Recent Post
Leo
Civilized Africa
I tested the whole system on all the records and it only took 3 and a half hours. Considering my first prediction was 68 days from running the original code on small tables to test, this is quite good. I didn’t have the unlock record there so I’ll add it. Thanks. I hope this is the last of this problem now.
Another Leo
_ _ ___________________ _ _
We are Micro$oft
You will be assimilated
Resistance is futile
 Posted 18 years ago (Thread Starter)
Comment Quote
About Leo -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9180 (Level 1.37)  Reply to 8802
Reply Posted 3/5/2003 5:09:00 AM
Location=Civilized Africa  
Joined=19 years ago   MB Posts=96  

Revive Thread!

Add a comment to revive this old thread and make this archived thread more useful.

Write a Comment...
Full Editor
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1156A1
Enter key:
Icon: A Post    Thread    Idea    Important!    Cool    Sad    No    Yes    Includes a Link...   
Thread #8802 Counter
4926
Since 4/2/2008

Regarding...

Linked Knowledge Base Article.

This thread is linked to the following KB article.


Mike Prestwood
1. Using ObjectPAL's TCursor Object Type

Exploring ObjectPAL's TCursor object type.

Posted to KB Topic: OPAL: Language Details
13 years ago, and updated 12 years ago
(2 KB Comments)

KB Post
Nothing New Since Your Last Visit
40968
Hits

Corel Paradox / ObjectPAL Coding

Go ahead!   Use Us! Call: 916-726-5675  Or visit our new sales site: 
www.prestwood.com


©1995-2021 Prestwood IT Solutions.   [Security & Privacy]