APPEND FROM problem 1

[Mike Lewis]

I feel a bit embarrassed posting this, but I have been staring at it for hours and I can't see what I am doing wrong.

What I am trying to achieve

To create a table and to populate it with certain character data.

What I have got so far

The following is the minimum code needed to demonstrate the problem.

CREATE TABLE CardPack ;
 (ID I, Ref I, English C(32), French C(32), Tested D)
  
CREATE CURSOR csrNew (TextE C(32), TextF C(32))

INSERT INTO csrNew (TextE, TextF) VALUES ("cosy", "douillet")
INSERT INTO csrNew (TextE, TextF) VALUES ("old-fashioned", "désuète")
INSERT INTO csrNew (TextE, TextF) VALUES ("flourishing", "florissant")

SELECT CardPack
APPEND FROM DBF("csrNew") FIELDS English, French 

BROWSE

Expected result

I expect the code to create the CardPack table, and to populate it with three records. The ID, Ref and Tested fields will all be blank. The English and French fields will contain the specified strings.

Actual result

The CardPack table is correctly created. The csrNew cursor is correctly created and populated with the specified strings. Three records are correctly added to the CardPack table. But all five fields in those records are blank. In other words, it is not picking up the English and French strings.

No doubt another pair of eyes will see what my stupid mistake is.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Tore Bleken]

The field names must match!

CREATE TABLE CardPack ;
 (ID I, Ref I, English C(32), French C(32), Tested D)
  
CREATE CURSOR csrNew (English C(32), French C(32))

INSERT INTO csrNew (English, French) VALUES ("cosy", "douillet")
INSERT INTO csrNew (English, French) VALUES ("old-fashioned", "désuète")
INSERT INTO csrNew (English, French) VALUES ("flourishing", "florissant")
SELECT CardPack
APPEND FROM DBF("csrNew") FIELDS English, French 

BROWSE

 

[Mike Lewis]

The field names must match!

How did I miss that? I must be finally losing my marbles.

Thanks, Tore.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Just a remark: That names have to match is not documented in the help topic about APPEND FROM.

If you don't have the field names in your hand, you could take a detour of selecting the data into an array and then APPEND FROM ARRAY, which matches by array column number = field number. It's obviously better to match the field names, so you can append the data directly.


Chriss

 

[Mike Lewis]

That names have to match is not documented in the help topic about APPEND FROM.

That's right. I read the Help topic very carefully. There was certainly no mention of field names having to match.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[german12]

What a nice expression! I never heard it before.
We have a similiar one in Germany
deutsch:"Ich glaube ich habe nicht alle Tassen im Schrank"
in english: "I think I don't have all my cups in the cupboard"

You are not alone, Mike - I have often said that to myself.
Stay calm - it's not final - it's just temporary

Klaus


Peace worldwide - it starts here...

 

[ManniB]

The same mistake has happened to me as before. I forgot about it because with SCATTER TO and GATHER FROM, it's only about the sequence, while with APPEND FROM, the field names have to match.

Manni

 

[Chris Miller]

Scatter/Gather also works on the basis of the same names. It's even more obvious than the normal APPEND FROM. You generate variables with the same name as the fields and you gather from them.

So if you SCATTER from Mikes csrNew (TextE, TextF) and GATHER into CardPack (English French), it would also fail to gather something, no variables English/French exist. Well, and if, they were not set by SCATTER.

When you SCATTER/GATHER from and into the same table you don't need to think about this, as the names are automatically matching as you generate them from the fields, but that only compares to self- appending from and into the same table, which is not what you usually do, unless you want to double data.

SCATTER GATHER is an outdated concept for buffering data, you'd scatter to variables, bind controls to variables and finally commit the buffer by GATHER or don't do changes to the original record by simply not gathering. This way of data binding is completely obsolete by using buffering. And that has advantages, as you can't suffer from name collisions, buffered data is buffered to the one workarea you buffer, not to variable names. And if you buffer 10 tables which each have a field name Id, that buffered Ids can't overwrite each other. Besides, you can buffer more than 1 record. So there are almost no good uses of SCATTER except with the new possibility to create one record variable with the NAME clause of SCATTER and to have one object to pass on to objects in another datasession, for example, by only passing one variable that has properties named as the fields.

Chriss

 

[ManniB]

Chriss, I think SCATTER/GATHER works differently:

CREATE CURSOR crsTest1 (name1 c(20), name2 C(20), name3 C(20))
INSERT INTO crsTest1 (name1, name2, name3) VALUES ("1", "2", "3")

CREATE CURSOR crsTest2 (name4 c(20), name5 C(20), name6 C(20))

SELECT crsTest1
SCATTER TO temp MEMO
SELECT crsTest2
APPEND BLANK
GATHER FROM temp MEMO

SELECT crsTest2
BROWSE

Manni

 

[GriffMG]

I think I would have created one cursor from the other (copy to stru style) so the field names would most likely (free table differences apart).

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Mike Lewis]

Griff,

I take your point. However, it is more complicated than that. I only showed the minimum code needed to reproduce the problem.

In fact, the data is originally imported from a text file. And the fields in the target table are memos rather than characters. You can't [tt]APPEND FROM .. DELIM WITH ...[/tt] into memo fields, hence the need for an intermediate cursor. And as an extra wrinkle, I need to do a bit of fiddling with code pages during the import.

I didn't show any of that in my original post because I didn't want to confuse the issue.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Sounds fun Mike, good luck with that then.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[atlopes]

Mike,

Regarding this particular issue, a different approach could be

INSERT INTO CardPack (English, French) SELECT TextE, TextF FROM csrNew

instead of [tt]APPEND FROM[/tt].

Regarding the other issues, you may have a look at the CSV processor class, which has some memo and codepage handling capabilities, along with field mapping.

 

[Mike Lewis]

Thanks for those suggestions, António. I am of course very aware of your CSV processor. In fact, the original data is in a tab-delimited rather than a comma-delimited file, although that wouldn't be a serious obstacle. I've now finished work on this application, so I won't go back and change anything, but this is all good stuff to keep in mind.

Similarly with your suggestion of using [tt]INSERT ... SELECT[/tt]. I would probably have done that eventually, but I really wanted to find out why my [tt]APPEND FROM[/tt] wasn't working.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Manni,

your example works based on an array, I didn't know that way of scatter, indeed. Something learned, thanks. I was assuming when you didn't have the NAME clause, you'd just have SCATTER MEMVAR, creating single variables.

Okay, so this way it becomes similar to GATHER FROM ARRAY, but the array only has one row.

Chriss