PDA

View Full Version : Foxpro/dBase ODBC update problem

GregS
03-11-03, 23:12
Hi all,

I have a one off (at this stage!!) need to update one dbf file 'test' from another 'mmdata'. I'm not sure what is possible using the Visual Foxpro ODBC driver, but I certainly don't understand the error message it is generating. SQL I'm running is:

update test set speed = mmdata.speed where
prodtype = mmdata.prodtype and
avediam = mmdata.diameter

Have tried several variations etc. Field names are correct etc.
Error produced is:

[Microsoft][ODBC Visual FoxPro Driver]SQL: Column ' ' is not found.

Have carried out simple updates without problems.
Is structure of above incorrect?
Driver version is 6.1.8629.1 running under Win2000.
Any ideas? I just don't get it!
Have tried Foxpro 5 command line as well as SQLedit and CuteSQL.
SQLedit and CuteSQL give same error .... Foxpro just doesnt do anything.

Would appreciate any advice!!
Thanks,
Greg Shearer
GregS
03-12-03, 01:07
Hi again,

Managed to work around my problem.
Steps required:
1. obtain trial alternative driver 'DataDirect 4.1 dBASEFile'
www.datadirect-technologies.com
2. restructure query using subselects as below:

update prod set speed =
(select mmdata.bestspee from mmdata
where mmdata.prodtype = prod.prodtype and
mmdata.diameter = prod.avediam and
pagenumber = 1),
tagname =
(select mmdata.tagname from mmdata
where mmdata.prodtype = prod.prodtype and
mmdata.diameter = prod.avediam and
pagenumber = 1)
where machine not like 'SL%'

Above worked fine. I assume the standard Visual Foxpro driver has bugs or just isn't that capable, as I still haven't had any success with it.
Unfortunately the DataDirect driver is US $625!!!

Hope someone finds this information useful.
ionut calin
03-12-03, 03:40
Sorry, I think that your problemm is not the VFP ODBC driver, but you SQL command:

update test set speed = mmdata.speed where
prodtype = mmdata.prodtype and
avediam = mmdata.diameter


the field mmdata.prodtype is from table mmdata, which is not added in your SQl command. The only table added is test "update test ....". You need the clause FROM in your update command, like this:

update test set speed = mmdata.speed
from test join mmdata on test.prodtype=mmdata.prodtype and test.avediam=mmdata.diameter
where .... additional conditions


in the clause above I assumed that the relation between the two tables test and mmdata is established through the pair of fields: (prodtype,avediam) -> (prodtype,diameter)


ionut
GregS
03-12-03, 17:57
Ionut,

Thanks for the suggestion. I had already tried using a 'from' clause, but it seems these aren't recognised by the Foxpro driver. The table to update from is implied instead by the 'table.field' naming convention. When a 'from' clause is inserted the driver responds with:

'Command contains unrecognised phrase/keyword'.

Even the DataDirect dBase driver doesn't seem recognise the 'from' clause. I'm most familiar with SQL under Ingres where a from clause is compulsory!! I did try the structure you suggested .... but no luck.

Thanks again,
Greg