SQL select statement components from memvars

[MikeMV]

Hello,

I need to do some field value replacements across multiple tables; rather than rewrite the procedure for every table (around 100 of) them, I would like to setup a table with a list of the table name / field name and have a single block of code cycle to through the list. Basically I am looking for construct a select statement with table/field names pulled from a memory variable, but I am at a loss on how to make this work. I tried setting up something like below:

SELECT 1
USE tables
*
GO TOP
tablename=ALLTRIM(table)
fieldname=ALLTRIM(field)
*
DO WHILE .NOT. EOF()
*
DELETE VIEW vexisting
*
CREATE SQL VIEW vexisting;
REMOTE CONNECTION SysProData;
AS SELECT tablename.fieldname FROM tablename;
ORDER BY tablename.fieldname
*
DBSETPROP ("vexisting", "View", "SendUpdates", .T.)
DBSETPROP("vexisting" , "VIEW","WhereType",3)
DBSETPROP("vexisting.StockCode", "Field", "Updatable", .T.)
DBSETPROP("vexisting.StockCode", "Field", "UpdateName", [tablename.fieldname])

Go back from here to check for EOF() on tables and if not skip and populate the memvars with the next record's value.

Can this be made to work?

I will greatly appreciate your assistance.

 

[Olaf Doschke]

yes, use makro substitution, that is after you set the variable tablename, fieldname use &tablename. for tablename and &fieldname. for fieldname The final dot often is not necessary, but in a situation like tablename.fieldname you'll need it at least for tablename: &tablename..&fieldname

First dot tells makro substitution the substitution stops, second dot is the one that belongs between tablename and fieldname.

Bye, Olaf.

 

[TamarGranor]

A couple of other notes. Don't ever SELECT a work area by number. Use SELECT 0 to get an empty work area (or USE ... IN 0) and once you've opened a table, refer to it by the alias.

SCAN-ENDSCAN is faster and less (human) error-prone than DO WHILE NOT EOF().

With those, I'd rewrite the structure of your code as:

SELECT 0
USE Tables

SCAN
* Do your thing
ENDSCAN

I'll also point out that in your example, you're creating the same view name over and over, but with a different definition each time. Seems to me you want to either build a view name from the table name or store the desired view name in your table. No point in creating a view and then destroying to create another.

Tamar

 

[MikeMV]

Thanks Olaf and Tamar.

The macro substitution worked perfectly and I am going to try using SCAN ENDSCAN.

I have a question Tamar, the reason I create the table on the fly and delete it is that I have to manipulate over 100 tables in this fashion, if I create a permanent view for each one I'll end up with that many views; is there a drawback to creating tables on the fly? Am I misunderstanding you and there is a better way to handle this?

This application will not be used often, maybe every 2-3 months for a single run, performance is not a big issue.

I will greatly appreciate your feedback.

 

[Olaf Doschke]

If you don't need tables permanently you can work with cursors instead.

But you create remote views on the fly here, not tables. You could create them permanently, there is no reason to generate them again and again, if there aren't any changes to the number and structure of the remote tables. But it's not wrong to generate views by using some meta data, it may have some advantages in dynamically changing what you need. Just don't delete and recreate the views in a central DBC but in a local DBC, and you're fine.

Bye, Olaf.

 

[MikeMV]

Thanks Olaf,

The DBC I work with is a local DBC only used for the purpose of compiling the data to be posted to the main SQL DB.

I have this working but I am getting a cursor update conflict error, if I can't work it out I am going to post some info here to see if you guys can help.

 

[MikeMV]

Tamar,

I started changing the code to use SCAN/ENDSCAN. I ran into a glitch which is related to the way I am trying to design the flow. I am not sure if I should address the question here or open a new thread, please let me know your thought.

I am running into a field name capitalization issue. When I run the code, the first iteration works well, the tablename and fieldname variables get stored properly, for example the first run it ends up as BomStructure.ParentPart.

On the second run when I look at the debugger it looks OK as BomStructure.Component, the code works as far as being able to create the view and populating the data, but when I issue the following command:

REPLACE ALL &fieldname WITH m.new FOR &fieldname=m.old

I get the following error:

Variable 'COMPONENT' is not found.

I am not sure if the error is that FoxPro is passing the variable name in all caps and the SQL DB is rejecting it, of if it is another issue and the error message is just capitalizing the variable name.

If I ignore the error I get the same results on the following combination of table/field names such as InvMaster.StockCode

I will greatly appreciate it if you had the time to give the code a look and see if anything pops up.

SET DEFAULT TO P:\Projects\StockCodeChange
OPEN DATABASE vstockcodechange
SET DATABASE TO vstockcodechange
*
SELECT 0
USE tables INDEX tables
*
GO TOP
tablename=ALLTRIM(table)
fieldname=ALLTRIM(field)
*
SELECT 0
USE StockCodeChange INDEX OldStockCode
GO TOP
m.old=ALLTRIM(Old)
m.new=ALLTRIM(New)
*
SELECT tables
*
SCAN && Through table/field list in tables
*
tablename=ALLTRIM(table)
fieldname=ALLTRIM(field)

DELETE VIEW vexisting
*
CREATE SQL VIEW vexisting;
REMOTE CONNECTION SysProData;
AS SELECT &tablename..&fieldname FROM &tablename;
ORDER BY &tablename..&fieldname
*
DBSETPROP ("vexisting", "View", "SendUpdates", .T.)
DBSETPROP("vexisting" , "VIEW","WhereType",3)
DBSETPROP("vexisting.&fieldname", "Field", "Updatable", .T.)
DBSETPROP("vexisting.&fieldname", "Field", "UpdateName", [&tablename..&fieldname])
*
SELECT 0
USE vexisting
*
SELECT StockCodeChange
*
SCAN && Through every stock code in StockCodeChange
*
m.old=ALLTRIM(Old)
m.new=ALLTRIM(New)
*
SELECT vExisting
GO BOTTOM
REPLACE ALL &fieldname WITH m.new FOR &fieldname=m.old
*
IF NOT TABLEUPDATE(1, .t., [vexisting])
AERROR(laError)
MessageBox([Can not Update vinventory ]+laError[1,2])
EXIT
ENDIF
*
LOOP
*
ENDSCAN && Replace Stock Code cycle
*
SELECT tables
*
ENDSCAN && Cycle through tables
*
CLEAR
CLEAR EVENTS
CLOSE ALL
CLOSE DATABASES ALL
*
RETURN

 

[Olaf Doschke]

If you scan through some alias "A" and adress a field of another alias "B" you get this error, as the field named &fieldname (in this case "Component") is not within Alias "A".

do

REPLACE &fieldname WITH m.new FOR &fieldname = m.old IN (tablename)

I haven't taken a close look at your code, but it seems you mix code generating views with code using the views or at least operating on data. Seperate this in two routines and you'll have much less problems.

Bye, Olaf.