Opening multiple tables at once

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.

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

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.

> 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

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

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

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

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.

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,

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?

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

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

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

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.

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

I would loop through Array1,

for i from 1 to array1.size()

; get the change

strChange = array

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

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.

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

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.