Variable '(insert field here)' not found when writing to a dbf from another dbf 1

[homersodyssey]

We just added the store 8753 code and created a new directory for it following the pattern of the previous store entries.

So store 8798 works just fine, but when we come to the 8753 store, I get two errors:
"Cannot find variable 'vendor_number'"
"Cannot find variable 'order_number'"

Reindexed both tables that are feeding each other. Kind of stuck now at this point.

Code below (posted the code from store 8798 to show that it is working and the problem code for store 8753)

message_window.label2.caption="Processing Store 8798"
message_window.refresh
set safety off
use \\public\IT_Dept\Programs\Pricebook_Working\storefiles\8798\itemfile exclu
zap
append from \\public\IT_Dept\Programs\Pricebook_Working\svtstorefile2 for store = 8798
replace all ord_num_in with order_number
replace all vnd_num_in with vendor_number
use
use \\public\IT_Dept\Programs\Pricebook_Working\default
scatter memvar

use \\public\IT_Dept\Programs\Pricebook_Working\storefiles\8798\default shared
do update_week_new
use


message_window.label2.caption="Processing Store 8753"
message_window.refresh
set safety off
use \\public\IT_Dept\Programs\Pricebook_Working\storefiles\8753new\itemfile exclu
zap
append from \\public\IT_Dept\Programs\Pricebook_Working\svtstorefile2 for store = 8753
replace all ord_num_in with order_number
replace all vnd_num_in with vendor_number
use
use \\190.69.1.208\public\IT_Dept\Programs\Pricebook_Working\default
scatter memvar

use \\public\IT_Dept\Programs\Pricebook_Working\storefiles\8753\default shared
do update_week_new
use

message_window.release
close database all

 

[Mike Lewis]

What exactly are vendor_number and order_number? Are they variables? Or are they fields? And, if fields, what table are they in?

If they are fields in a table other than itemfile, you need to prefix them with the alias of their table within the REPLACE statements.

In other words, instead of this:

replace all ord_num_in with order_number
replace all vnd_num_in with vendor_number

do something like this:

replace all ord_num_in with [highlight #FCE94F]TheTable.[/highlight]order_number
replace all vnd_num_in with [highlight #FCE94F]TheTable.[/highlight]vendor_number

where TheTable stands for the alias of the table holding those two fields. In fact, it's always good practice to specify the alias in a REPLACE.

If order_number and vendor_number are variables, then they have presumably gone out of scope in the second case. But it's difficult to confirm that without knowing the context of these two bits of code.

I'd also question the fact that you seem to have the same code twice for essentially the same operation. But let's take one thing at a time.

Mike





__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[homersodyssey]

Ran the code with changes proposed, still same issue.

Order_Number and Vendor_Number are fields from svtstorefile2.dbf. We are changing their field names when placing them into the itemfile.dbf



message_window.label2.caption="Processing Store 8753"
message_window.refresh
set safety off
use \\public\IT_Dept\Programs\Pricebook_Working\storefiles\8753new\itemfile exclu
zap
append from \\public\IT_Dept\Programs\Pricebook_Working\svtstorefile2 for store = 8753
replace all ord_num_in with svtstorefile2.order_number
replace all vnd_num_in with svtstorefile2.vendor_number
use
use \\public\IT_Dept\Programs\Pricebook_Working\default
scatter memvar

use \\public\IT_Dept\Programs\Pricebook_Working\storefiles\8753\default shared
do update_week_new
use

message_window.release
close database all

 

[homersodyssey]

To put things into context, another tech and I have been maintaining this code for years since the old programmer retired. No changes to the organization so it was fairly straight forward to keep it running and alive. Usually a user mis-keyed some entry and we would find it for them. This latest issue is because we are expanding the company and need to add new fields to the tables and in some cases, new tables. We have expressed a need to come off of this code for a while. We are now reaping what was sowed.

 

[Mike Lewis]

I think the problem is that svtstorefile2 is not actually open when you do the REPLACEs.

Although you are appending from that table, that doesn't mean that the table will be open. VFP will open it behind the scenes when you do the APPEND, but the table will not necessarily remain open after that.

Better to do this at the start of the program:

IF NOT USED("itemfile)
  use \\public\IT_Dept\Programs\Pricebook_Working\storefiles\8753new\itemfile exclu ALIAS itemfile IN 0
ENDIF
IF NOT USED("svtstorefile2")
  use \\public\IT_Dept\Programs\Pricebook_Working\svtstorefile2 ALIAS svtstorefile2 IN 0
ENDIF
SELECT itemfile

The proceed as before, including:

replace all ord_num_in with svtstorefile2.order_number
replace all vnd_num_in with svtstorefile2.vendor_number

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Well it definitely looks like pretty bad code that you have been obliged to maintain. The fact that the programmer included hard-coded paths to the DBFs is a red flag. I would also be concerned about tables being opened and closed in the same work area throughout the routine, rather than all being opened at the outset, each in its own work area. There are other issues too, but I'll let you focus on the problem in hand.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

So the general data transfer is happening by the APPEND.

Then does itemfile.dbf have both ord_num_in and order_number fields?

If not, if only the number and types of fields match, APPEND will not add the svtstorefile2.order_number to itemfile. If you have other field names, APPEND will not work straight forward, it appends to fields with same name in target and source DBF.

The REPLACE Mike Lewis suggest only works on the premise you position on the correct record of svtstorefile2. And as you REPLACE ALL, this would even require more, a relation so that every itemfile gets the corresponding svtstorefile2 record. I'd not recommend that. Ideally use SQL to get the data over with your own field mapping (order of fields matter here, not names):

Insert into itemfile (<<itemfile fieldlist>>) Select <<svtstorefile2 fieldlist>> FROM svtstorefile2 WHERE store = <<storenumber>>

All parts in << and >> brackets with whatever specific field names or the storenummber. The latter can also be a parameter (might be and you just have posted 8753 to not sidetrack from the main problem).

In earlier VFP versions you can't INSERT INTO ... FROM SELECT-query a workaround is going through an ARRAY and use APPEND FROM ARRAY.

And (totally unrelated) the other thing you could do instead of ZAP is work with a cursor.

Chriss

 

[Chris Miller]

This latest issue is because we are expanding the company and need to add new fields to the tables and in some cases, new tables.

That crossed with my suggestion. In that case it's still a good suggestion to use SQL, but for a simple one-off data migration. The simplest would be

COPY dbfs (and cdx etc) if you need the original DBFs for reference and archive and to retry failed alterations.
Then
ALTER TABLE RENAME order_number TO ord_num_in.
ALTER TABLE also can add new fields, see the help topic.

No idea what VFP does to a DBF that may even still be in an original dBase format when you apply ALTER TABLE, either VFP converts it to a VFP9 dbf or keeps the dBase legacy header (adjusted and/or altered or extended with renamed and new fields). If it fails go back to backup, of course.


Chriss

 

[homersodyssey]

Tried VFP suggestions with no success yet. We are working on migrating this whole suite into an SQL based GUI for our users, but the FXP stuff is still king here and org is not ready yet to let it all go. Too many programs running off of these and multiple other tables. I will update with another attempt at a solution when it fails but gives me a different error.

 

[homersodyssey]

OK, so it appears this may be an issue with the tables not matching the fields in the database. Makes sense, since this is a new folder created from an existing one.
I went into the database designer and looked at the table itemfile. error i received when trying to look at the index is the fields in the table did not match the entries in the database.
Any ideas?

 

[Chris Miller]

That suggests the DBFs you copied were part of a DBC. Either that's missing or you have a new DBC.

In short, what was copied and how is the new DBC created? You can't simply merge a DBC with new table definitions and dbfs with old definitions.


Chriss

 

[homersodyssey]

Was created via a folder creation and then clearing of the previous tables. Changed the default table to match our new store number.

I have gotten this far

Decided to look at the database designer again and then fix the validation issue.
Removed itemfile table and re-added it in the designer.

From the command window.

Input:
close database all
OPEN DATABASE p:\it_dept\programs\pricebook_working\storefiles\8753\storefiles.dbc EXCLUSIVE
validate database recover

Output:
Validate Database STOREFILES:
Rebuilding structural index.... Index rebuilt.
Database container is valid.

Re-ran with same issue.

 

[Chris Miller]

Re-ran with same issue.

Sorry, but in the meantime you reported several issues and got several suggestions what to do.

What did you rerun? With what error reported at what line number?

On the other side, you might not have transcribed the suggested solutions. Here's a concrete example. I create a free fox2x table olddata.dbf as basis, then show what SQL to use instead of append and the alternative table alteration code (several steps before and after adding olddata.dbf to a new dbc). In the samples no indexes are involved at all.

Cd Getenv("TEMP")

Close Tables All
Close Databases

Erase brandnewstoresystem.*
Erase olddata.*
Erase Newdata.*

*create an old dbf (fox2x table)

Create Cursor olddata (Id Int, order_num Char(4), Other Char(20))
Copy To olddata.Dbf Type Fox2x
Use
Insert Into olddata.Dbf Values (1, '1001', 'otherdata')
Insert Into olddata.Dbf Values (2, '1002', 'yetmoreata')
Use

* this was just preparation code, that covers the situation you already have with an old dbf.

* Now for the "data migration" (great word for this simple data flow) by a single SQL-insert FROM SQL-Select for each table:
Create Database brandnewstoresystem.Dbc
Create Table Newdata (Id Int, order_num_in Char(5), otherfields Char(20), newfield Int Default 3)

Insert Into Newdata (Id, order_num_in, otherfields) Select Id, order_num, Other From olddata.Dbf
Use

Use Newdata.Dbf Shared
Browse Nowait

* Even simpler to alter the olddata.dbf table:
Alter Table olddata.Dbf Rename Column order_num To ordernumin Add Column newfield Int

* To use features of dbc tables like long field names and default values, the first step needs to be to
* add fox2x or free dbfs to a dbc
Use In Select('olddata')
Add Table olddata

Alter Table olddata Rename Column ordernumin To order_num_in Alter Column newfield Set Default 3
* notice: default 3 will not be applied to the already existing records.

* creating another new field with default value will put it into all existing records
* which previously didn't have the field

Alter Table olddata Add Column newfield2 Datetime Default Datetime()
SELECT olddata
BROWSE nowait

Move the olddata browse window and you'll also see the newdata browse.
newdata is a copy of the olddata (before that was altered), olddata.dbf was also extended, just by ALTER statements and was added to a DBC to use dbc features.

Maybe it's also helpful to see that - things you can and can't do in free or fox2x or perhaps clipper dbfs but can have in dbfs of a VFP dbc, like longer than 10 char field names.

Also one thing to know is a dbf has its short field names in itself in a header section, index tag expressions (keys) are in a cdx header, but the dbc has info about indexes and fields of its tables, too. You can't just alter dbf or dbc, both things have to match. Therefore altering tables is easiest in a full copy of everything. If the old tables already were in a dbc you copy that over too. And you can't free a dbf from its old dbc and then add it to a new dbc and expect you still get back the long field names and other dbc features it had in the old dbc, that's not transferred. You lose those features when you free a table, you don't gain them back, when you add the table to another dbc. When you copy over all fiels you simply keep everything at the state it is and then can work on the copy with ALTER statements and UPDATEs, INDEX, whatever. But you can't create a new dbc with dbfs and then copy over the old dbfs or anything like that, which causes a mismatch of what the dbc knows about its tables and what the tables know about themselves.

Again, assuming the old data is okay, minus perhaps a few defects like indexes you need to reindex, then that's a taks before any other and you still have it most easy when you start from what you have, dbc and dbfs including their data and ALTER them instead of copying over data, especially as the new dbfs are not 1:1 the same, APPEND fails on that.

And last, not least: When I do ADD TABLE olddata in my code, that's adding a fox2x table to the dbc which never was part of a bdc before, which never had long field names or default values and only gets there by the ALTER statements. These are the usual DDL (data definition language) statements in SQL besides CREATE TABLE, INDEX and some more. ALTER TABLE is a command that rewrites the dbf with new definition, takes over cdx into the altered dbf, takes over the fpt file to the altered dbf (which append skips), changes dbc meta data of the dbf and cdx and als manages to rename fields.

Chriss

PS: The only thing it does not adapt are index keys having the old field name that doesn't exist anymore. Check that for yourself. A best practice is to DELETE TAG ALL and finally redefine all indexes. GenDBC is helpful to create the necessary code from an existing DBC. Once you have that code you can modify it accordingly to let the INDEX commands use the new field names.

That's a lot to digest in one go, take it bite by bite.

 

[Mike Lewis]

No idea what VFP does to a DBF that may even still be in an original dBase format when you apply ALTER TABLE, either VFP converts it to a VFP9 dbf or keeps the dBase legacy header (adjusted and/or altered or extended with renamed and new fields).

For what it's worth, I can answer that question. If you take a dBase table, then apply ALTER TABLE in VFP:

- If the ALTER TABLE removes fields, or add fields that are in dBase (or Foxbase or Fox2x) data types (such as C(1) or L), then the header is still dBase.

- But if you add a field in a VFP-specific data type, such as I or Y, or if the new field supports NULLs, or anything else that is VFP-specific, then the table is converted to a VFP table.

At least, that's what my quick tests show.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Thanks Mike,

yes, you can simply do and find out. It's plausible that way, I think being wary about this issue if legacy software still should be able to use the data is a good choice. Another reason to actually first build up a new DBC for the SQL based GUI that will be used in the future.

What is that, actually, homersodyssey?

I think upgrading data to a SQL Server, usually MSSQL in a Windows environment, would be the way to go and have very many possibilities for both workstation, mobile devices and even android or iOS, as there are so many connectors for such an enterprise database product. This would also apply to other backends.

Are new DBFs just an intermediate step towards the long term goal? I mean, there also is Stonefield query to name one query tool for DBFs, the VFP OLEDB provider enables other query tools, too and the ODBC 6 driver (of course when you keep it at VFP6 features) would also work to use any tool that's capable to connect to any data via ODBC, and that's a lot.

Chriss