I.T. Discussion Community!
-Collapse +Expand
Paradox
Search Paradox Group:

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

Prestwood eMagazine

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

   ► MB LobbyCorel Paradox / ObjectPAL Coding BoardParadox Tables Topic   Print This     

Opening multiple tables at once

Opening multiple tables at once in Paradox Tables topic (part of our Corel Paradox / ObjectPAL Coding group).

Quick Search: Opening   multiple   Opening multiple   Opening multiple tables   at once  
Pete.D2
-- --

I need to open a data table and run through it checking a particular field for matches in a separate look up table.

I need to flip repeatedly between two open tCursors. I declared my two tCursors and opened them expecting them to open independently, like so:

tc.open("one")

get the string

tc2.open("two"), expecting two to be opened in another work area.

search a match for the string in Two's FIND field

if found manipulate the string

tc2.close()

update one's data with the string, expecting one to just be there and selected.

move to the next record and repeat

What am I missing in the way these tables/tcursors need to be opened or selected? The way it is now tc2 doesn't seem to be being selected when it's opened. NONE of code examples I checked online used two tCursors in the same code block.

 Posted 16 months ago (Thread Starter)
Comment Quote
About Pete.D2 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #17081, 18 replies
Thread Started 4/29/2018 5:58:52 PM
View Counter=1116
Last Reply Posted 6/8/2018 7:05:29 AM)
Location=-- -- 
Joined=33 months ago   MB Posts=19  
davidc
Kansas City, KS

I would do it something like this:

hT.attach("h.db")
hTC.open(hT)
hTC.edit()
dT.attach("d.db")
dTC.open(dT)

scan dTC:
   if hTC.locate("key",dTC."field12") then
      hTC."field3"="whatever"
   endif
endscan

hTC.endedit()
hTC.close()
dTC.close()

You may also want to consider whether it can just be done through a Query.

Peace,
David Cheney

 Posted 16 months ago
Comment Quote
About davidc -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #17082 (Level 2) and Parent is 17081
Reply Posted 4/29/2018 11:25:28 PM
Location=Kansas City, KS  
Joined=17 years ago   MB Posts=44  
Pete.D2
-- --

It can't be a query because I actually have to use a MATCH to do a substring search. The keyword is embedded in a text string. The goal is to replace strings like Limited with Ltd.

I wasn't using the attach method, I was just opening the tCursor. Maybe that's the answer in being able to switch between two tables.

 Posted 16 months ago (Thread Starter)
Comment Quote
About Pete.D2 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #17085 (Level 1.1)  Reply to 17082 and Parent is 17081
Thread Started 4/30/2018 6:27:11 AM
Location=-- -- 
Joined=33 months ago   MB Posts=19  
Moderator
Steven.G
Myrtle Beach, SC USA

> I wasn't using the attach method

that's not a requirement.. you haven't showed us the actual code, or the error you're getting, so we can only guess, or suggest generic things that might be the solution

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

Prestwood IT Solutions
8421 Auburn Blvd, Suite 256, Citrus Heights, CA 95610
Office: 916-726-5675 | Fax: 916-726-5676 | http://www.prestwood.com
 Posted 16 months ago
Comment Quote
About Steven.G -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #17086 (Level 3) and Parent is 17081
Reply Posted 4/30/2018 8:00:40 AM
Location=Myrtle Beach, SC USA 
Joined=35 months ago   MB Posts=300  
Pete.D2
-- --

Here you go. The error occurs at the line:

lFound = workString.Match( ".. " + tc."FIND" + " ..", x, y)

It says the field FIND isn't found because it's looking at the wrong table.

--original code--

Method pushButton(var eventInfo Event)

;*** This button updates a target field in a live table based on a matching string being found in a lookup table ( VIPREPLACE for example)
;*** If a replacement occurs field V2 is marked with a '1'


var

strTargetTable String ;the live table
strBatchTable String ;the table with the search terms
strColumn String ;the field being targetted for replacement
strMarkField String
tc TCursor ;the live table
tc2 TCursor ;the table with the search terms
workString String ;starts out as the live data and is transformed with data from the batch table's FIND field
lFound Logical ;we need two finds, one to indicate that at least one find was made, and one find to track each attempt on a single record
lFound2 Logical ;this variable is used to mark the fact that at least one find was made this batch
x String ;used for matching
y String ;used for matching
a String ;used to contruct a padded string
b String ;used to contruct a padded string
c String ;used to contruct a padded string

endVar

strBatchTable = "VIPREPLACE.DB"
strTargetTable = "TDMA.DB"
strColumn = "LNAME"
strMarkField = "V2"

lFound = False
lFound2 = False


;Might be a good idea to put a messagebox warning here to confirm these settings before running the batch!

;Open the table and move to the first record.
tc.Open(strTargetTable) ;the table with live data
tc.Home()


;Loop through all the records in the table.
while not tc.Eot()

;State which record we're on in the bottom-left corner status bar.
message(tc.RecNo())

;Generate a padded text string from the original field's data with one space on either side.
a = tc.(strColumn)
b = a.rtrim()
c = b.ltrim()
workString = " " + c + " "


;Open the batch FIND and REPLACE table and move to the first record.
tc2.Open(strBatchTable)
tc2.Home()


;Loop through all the records in the FIND and REPLACE table.
while not tc2.Eot()

;Clear temporary string parsing variables.
x = ""
y = ""

;Look in the batch table and check if this batch string plus padding is within the supplied search string.

lFound = workString.Match( ".. " + tc."FIND" + " ..", x, y) ;note that a space is added fore and aft to the search term

if lFound then

lFound2 = True

;This FIND value in the table was found, so adjust the variables accordingly.
workString = x + " " + tc."REPLACE" + " " + y ;this replacement may be blank. Probably there will be an issue with a space fore or aft

workString = workString.rtrim()
workString = workString.ltrim()

endIf

;Move to the next FIND and REPLACE record. In this way the same search term will be searched thru with the entire batch table of search terms
tc.NextRecord()

endWhile


;Close access to the Find and Replace table.
tc2.Close()


;Check whether at least one find was made.
if lFound then

;Mark that a change has occurred
tc.Edit()
tc.(strmarkField) = "1" ;log the change to the mark field
tc.EndEdit()

;Update the field. Data will either be excised or a section replaced
tc.Edit()
tc.(strColumn) = workString
tc.EndEdit()

endIf

;Move to the next record.
tc.NextRecord()

endWhile


;Close access to the live table.
tc.Close()


if lFound2 then

Display("Batch REMOVE - changes made")

else

Display("Batch REMOVE done - no changes made")

endif


Stat.text = "Batch REMOVE done"
beep()

endmethod

 Posted 16 months ago (Thread Starter)
Comment Quote
About Pete.D2 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #17087 (Level 4) and Parent is 17081
Reply Posted 4/30/2018 8:12:58 AM
Location=-- -- 
Joined=33 months ago   MB Posts=19  
Moderator
Steven.G
Myrtle Beach, SC USA

looks to me like the fields FIND and REPLACE are in tc, and the text field you're manipulating is in tc2

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

Prestwood IT Solutions
8421 Auburn Blvd, Suite 256, Citrus Heights, CA 95610
Office: 916-726-5675 | Fax: 916-726-5676 | http://www.prestwood.com
 Posted 16 months ago
Comment Quote
About Steven.G -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #17088 (Level 5) and Parent is 17081
Reply Posted 4/30/2018 8:22:21 AM
Location=Myrtle Beach, SC USA 
Joined=35 months ago   MB Posts=300  
rum
Frankfort, KY USA

Question:   How mant Search terms are you planning to have?

 Posted 16 months ago
Comment Quote
About rum -Collapse +Expand
Visit Profile
Membership pending.
Member subscribes to this thread with a verified email.
Member pending approval.
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 #17089 (Level 6) and Parent is 17081
Reply Posted 4/30/2018 9:31:57 AM
Location=Frankfort, KY USA 
Joined=26 months ago   MB Posts=88  
Pete.D2
-- --

Possibly hundreds. Thinking the search term table will need to be ordered from the longest terms down to avoid situations like "VIP SE" being cut in half by and earlier replacement of "VIP".

It's almost working now. Rookie mistake missing mixing up tc and tc2. Thanks Steven.

 Posted 16 months ago (Thread Starter)
Comment Quote
About Pete.D2 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #17090 (Level 7) and Parent is 17081
Reply Posted 4/30/2018 9:49:26 AM
Location=-- -- 
Joined=33 months ago   MB Posts=19  
rum
Frankfort, KY USA

I saw that I issue and renamed the TC & TC2 to livedataTC  and searchtermsTC, to keep myself from being confused

I would suggest that you stop opening and closing the Searchterms inside the Livedata Loop.

   If you are determined to do use a tcursor for the search terms,  it would be faster to open it outside the loop, then goto to HOME at the the top of each loop (rather than close and reopen)

If you have a speed issue, You might consider loading the search terms into a dynamic string array one time,  and loop through them.   much faster than using two Tcursors.

Also,  you might look at setGenFilter,  rather than looping through EVERY record searching for those that may need to be changed.

Best regards,

 Posted 16 months ago
Comment Quote
About rum -Collapse +Expand
Visit Profile
Membership pending.
Member subscribes to this thread with a verified email.
Member pending approval.
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 #17091 (Level 8) and Parent is 17081
Reply Posted 4/30/2018 9:55:03 AM
Location=Frankfort, KY USA 
Joined=26 months ago   MB Posts=88  
Pete.D2
-- --

The reason I didn't want to use an array is that there is a Replace field to consider. Arrays in Paradox are single dimension aren't they?

 Posted 16 months ago (Thread Starter)
Comment Quote
About Pete.D2 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #17093 (Level 1.1)  Reply to 17091 and Parent is 17081
Thread Started 4/30/2018 11:35:43 AM
Location=-- -- 
Joined=33 months ago   MB Posts=19  
Moderator
Steven.G
Myrtle Beach, SC USA

your attention to details, and putting in comments, is excellent.. 99% of the folks never do that

like Jeff said, once you get it working, it can be streamlined in several different ways.. moving forward, any of those options can make it run quicker.. and can make debugging easier, when the tables DO get huge

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

Prestwood IT Solutions
8421 Auburn Blvd, Suite 256, Citrus Heights, CA 95610
Office: 916-726-5675 | Fax: 916-726-5676 | http://www.prestwood.com
 Posted 16 months ago
Comment Quote
About Steven.G -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #17092 (Level 9) and Parent is 17081
Reply Posted 4/30/2018 11:17:51 AM
Location=Myrtle Beach, SC USA 
Joined=35 months ago   MB Posts=300  
rum
Frankfort, KY USA

I would use a dynamic array.   it should do the trick

 Posted 16 months ago
Comment Quote
About rum -Collapse +Expand
Visit Profile
Membership pending.
Member subscribes to this thread with a verified email.
Member pending approval.
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 #17094 (Level 10) and Parent is 17081
Reply Posted 4/30/2018 1:19:16 PM
Location=Frankfort, KY USA 
Joined=26 months ago   MB Posts=88  
Pete.D2
-- --

This is the semi final code. It works fine. I'll revisit the array idea if speed is an issue. Thanks all for the quick and useful responses.

Method pushButton(var eventInfo Event)

;*** This button updates a target field in a live table based on a matching string being found in a lookup table ( VIPREPLACE for example)
;*** If a replacement occurs field V2 is marked with a '1'


var

strTargetTable String ;the live table
strBatchTable String ;the table with the search terms
strColumn String ;the field being targetted for replacement
strMarkField String
tc TCursor ;the live table
tc2 TCursor ;the table with the search terms
workString String ;starts out as the live data and is transformed with data from the batch table's FIND field
lFound Logical ;match result per batchtable entry
lFound2 Logical ;any match at all
lFound3 Logical ;match result per live data entry
x String ;used for matching
y String ;used for matching
a String ;used to contruct a padded string
b String ;used to contruct a padded string
c String ;used to contruct a padded string

endVar

strBatchTable = "VIPREPLACE2.DB"
strTargetTable = "TDMA.DB"
strColumn = "LNAME"
strMarkField = "V2"

lFound = False
lFound2 = False
lFound3 = False


;Open the table and move to the first record.
tc.Open(strTargetTable) ;the table with live data
tc.Home()


;Loop through all the records in the table.
while not tc.Eot()

;State which record we're on in the bottom-left corner status bar.

;Generate a padded text string from the original field's data with one space on either side.
a = tc.(strColumn)
b = a.rtrim()
c = b.ltrim()
workString = " " + c + " "


;Open the batch FIND and REPLACE table and move to the first record.
tc2.Open(strBatchTable)
tc2.Home()


;Loop through all the records in the FIND and REPLACE table.
while not tc2.Eot()

;Clear temporary string parsing variables.
x = ""
y = ""

;Look in the batch table and check if this batch string plus padding is within the supplied search string.

f = tc2."FIND".ltrim()
g = f.rtrim()

lFound = workString.Match( ".. " + g + " ..", x, y) ;note that a space is added fore and aft to the search term

if lFound then

lFound3 = True ; this live data record will need replacing
lFound2 = True ; at least one record will be changed

;This FIND value in the table was found, so adjust the variables accordingly.
if tc2."REPLACE".isBlank() then

workString = x + " " + y

else

workString = x + " " + tc2."REPLACE" + " " + y ;this replacement may be blank. Probably there will be an issue with a space fore or aft

endIf

workString = workString.rtrim()
workString = workString.ltrim()

endIf

;Move to the next FIND and REPLACE record. In this way the same search term will be searched thru with the entire batch table of search terms
tc2.NextRecord()

endWhile


;Check whether at least one find was made.
if lFound3 then

;Mark that a change has occurred
tc.Edit()
tc.(strmarkField) = "1" ;log the change to the mark field
tc.EndEdit()

;Update the field. Data will either be excised or a section replaced
tc.Edit()
tc.(strColumn) = workString
tc.EndEdit()

endIf

lFound3 = False

;Move to the next record.
tc.NextRecord()

endWhile


;Close access to the live table.
tc.Close()


if lFound2 then

Display("Batch REMOVE - changes made")

else

Display("Batch REMOVE done - no changes made")

endif


Stat.text = "Batch REMOVE done"
;beep()

endmethod

 Posted 16 months ago (Thread Starter)
Comment Quote
About Pete.D2 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #17095 (Level 11) and Parent is 17081
Reply Posted 4/30/2018 1:46:26 PM
Location=-- -- 
Joined=33 months ago   MB Posts=19  
Pete.D2
-- --

The batch table did get bigger.  Can DynArrays be two dimensional? I need to to store both the Find and Replace fields in the array.

My plan is to simply scan the batch table at the beginning of the method.

 Posted 15 months ago (Thread Starter)
Comment Quote
About Pete.D2 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #17123 (Level 12) and Parent is 17081
Reply Posted 5/17/2018 10:13:16 AM
Location=-- -- 
Joined=33 months ago   MB Posts=19  
rum
Frankfort, KY USA

ARRAYS  are  KEY/VALUE  pairs

The KEY is unique,  the value can be duplicated.  DYNARRAYS allow you to NAME the key rather than number.

I would use two standard string arrays

Array1  would hold a list of changes to make,  in the squence they need to be made.

the array would be like this

index VALUE
1 TABLE~FIELD~OLDVALUE~NEWVALUE
2 TABLE~FIELD~OLDVALUE~NEWVALUE
3 TABLE~FIELD~OLDVALUE~NEWVALUE
4 TABLE~FIELD~OLDVALUE~NEWVALUE
5 TABLE~FIELD~OLDVALUE~NEWVALUE
6 TABLE~FIELD~OLDVALUE~NEWVALUE

I would loop through Array1,

for i from 1 to array1.size()

; get the change

strChange = array

strchange.breakapart(array2,"~")

;set some vaiables to hold

;array2[1] = the table to change

;array2[2] = the field to change

;array2[3] = the search value that needs to be changed

;array2[4] = the new value

 ;  do the change code now

endfor

FYI, this is all untested, off the top of my head advice, your needs may varywink

 Posted 15 months ago
Comment Quote
About rum -Collapse +Expand
Visit Profile
Membership pending.
Member subscribes to this thread with a verified email.
Member pending approval.
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 #17124 (Level 13) and Parent is 17081
Reply Posted 5/17/2018 11:12:05 AM
Location=Frankfort, KY USA 
Joined=26 months ago   MB Posts=88  
Pete.D2
-- --

Lacking any insight into the multi-dimensionality of Paradox arrays I have already started going down that route. Thanks for verifying the correctness of that decision.

Cheers, Pete

 Posted 15 months ago (Thread Starter)
Comment Quote
About Pete.D2 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #17125 (Level 14) and Parent is 17081
Reply Posted 5/17/2018 11:42:05 AM
Location=-- -- 
Joined=33 months ago   MB Posts=19  
Pete.D2
-- --

Any idea how hard this logic would be to implement in PostgreSQL? The current app is in Paradox but we are knocking on the 2GB size limit for tables. Upgrading is a near to medium term eventuality.

Looking at the Postgres docs it doesn't seem that straight forward to do scans of cursors, let alone a scan within a scan as was done in this code. I don't think what we need to do can be done thru SQL.

BTW, I did try switching in the inner loop over to an array. It ran 1/3 slower! Go figure.

 Posted 14 months ago (Thread Starter)
Comment Quote
About Pete.D2 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #17150 (Level 15) and Parent is 17081
Reply Posted 6/7/2018 8:29:00 PM
Location=-- -- 
Joined=33 months ago   MB Posts=19  
Moderator
Steven.G
Myrtle Beach, SC USA

eventually EVERY APP must be moved from Paradox, in order to have a long, full life.. there are only a handful of us left, supporting the Paradox world

there's always a way to get from Point A to Point B in every platform, even if it's not quite the same way you've been doing it in another platform

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

Prestwood IT Solutions
8421 Auburn Blvd, Suite 256, Citrus Heights, CA 95610
Office: 916-726-5675 | Fax: 916-726-5676 | http://www.prestwood.com
 Posted 14 months ago
Comment Quote
About Steven.G -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #17152 (Level 16) and Parent is 17081
Reply Posted 6/8/2018 5:03:50 AM
Location=Myrtle Beach, SC USA 
Joined=35 months ago   MB Posts=300  
Most Recent Post
Pete.D2
-- --

It's a shame these useful products like Paradox and FoxPro never got upgraded to 64 bit. They would still have their place I think.

 Posted 14 months ago (Thread Starter)
Comment Quote
About Pete.D2 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #17154 (Level 17) and Parent is 17081
Reply Posted 6/8/2018 6:59:22 AM
Location=-- -- 
Joined=33 months ago   MB Posts=19  

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 = P1236A1
Enter key:
Icon: A Post    Thread    Idea    Important!    Cool    Sad    No    Yes    Includes a Link...   
Thread #17081 Counter
1116
Since 4/29/2018
Follow PrestwoodBoards on: 


©1995-2019 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech