reserved words as field names

[hanshasuro]

I am in the unfortunate position of having to deal with a Foxpro DB that has 'date', 'group', 'update' and other Foxpro/SQL reserved keywords as field names. I need to access it through a JDBC/ODBC connection in a Java application. This requires me to write SQL code to affect the DB. I think you might begin to see the problem with a statement like this:

e.g.
UPDATE customer SET update = d{'12/12/2002'} WHERE acctnum = 1234

The following error message is returned:
[Microsoft][ODBC Visual FoxPro Driver]Command contains unrecognized phrase/keyword.

When dealing with a SQL Server 2000 DB, it is possible to refer to a field which has been so unfortunately named by wrapping the field name in square brackets, like [date]. Is there a similar workaround for Foxpro? The square bracket approach is rejected. This database is part of an established back office application so changing the field names is not an option.

Thanks!

 

[skoko]

Maybe you can send table to the cursor with changed field names first and then run sql to the cursor.

 

[hanshasuro]

Can you elaborate a little? Perhaps with a code sample?

 

[hanshasuro]

Anyone else have an idea?

 

[GriffMG]

You MIGHT be able to put the field name in square brackets - like this:

UPDATE customer SET [update] = d{'12/12/2002'} WHERE acctnum = 1234

That assumes the d{...} is ok and the update bit isn't!

I use the above to order by (I have a field called DESC which is unpopular sometimes!

select * from company order by [DESC]

Is ok, but:

select * from company order by DESC

Isn't!

It can be difficult to avoid using the odd reserved word... I have a lot of 'private m.string' variables in my code

HTH



Regards

Griff
Keep [Smile]ing

 

[GriffMG]

Please ignore my rambling above...

UPDATE customer SET customer.update = ctod("12/12/2002") WHERE acctnum=1234

works well, I must be losing my marbles!

Martin

Regards

Griff
Keep [Smile]ing

 

[GriffMG]

I've worked it out, I had my MS Access head on - it uses the syntax I first wrote sorry to all for any confusion!

(So much for Standard Query Language)



Regards

Griff
Keep [Smile]ing