How to interpret Foxpro syntax in SQL Server?

Question

I have a requirement where I need to convert Foxpro SQL syntax to SQL Server. What should be appropriate equivalent of below Foxpro code in SQL Server?

Here is complete statement:

Its a DBF Update statement in FoxPro:

REPLACE ALL Column1 WITH "N/A" FOR Column2="Some string value"=.f.

I want to understand what the Column2="Some string value"=.f. part means.

A per my assumption this code corresponds to below code in SQL Server:

columnName <> "String Value"

Please advise!

Answer 1

As per the handy unofficial backup mirror of the FoxPro documentation, (because Microsoft has started to copy Apple's strategy of pretending documentation for their older products doesn't exist) we can break it down:

REPLACE ALL Column1 WITH "N/A" FOR Column2="Some string value"=.f.

• REPLACE is like SQL's UPDATE or MERGE.

• The ALL in REPLACE ALL means that all records in the table should be processed.

• WITH "N/A" means "N/A" is the new string/text value to store in Column1.

• FOR denotes the start of the row predicate (which seems silly as ALL is also a row predicate, kinda).

• Column2 = "Some string value" = .f. is the actual predicate expression.

  • FoxPro has (at least) 2 comparison operators = and ==: the == operator is specifically for exact text equality, while = has looser rules for determining equivalence.
  • .F. (or .f.) is a literal false value, and .T. (or .t.) is a literal true value.
  • Annoyingly, the predicate is of the form x = y = false (rather than ( x = y ) = false or x = ( y = false ) - I couldn't find any authoritative documentation re: FoxPro's operator associativity, but from the context it's clear the intent is ( Column2 = "Some string value" ) = false - which could have just been written as FOR Column2 <> "Some string value".

• So this REPLACE ALL statement will:

  1. find all rows where Column2 is not equal to "Some string value"
  2. and only in those rows will it set Column1 = "N/A".

• The equivalent T-SQL for SQL Server would be:

  UPDATE
       tableNameGoesHere
  SET
       Column1 = 'N/A'
  WHERE
       Column2 <> N'Some string value';
  

  • Though keep-in-mind that SQL Server's UPDATE (and MERGE, if you dare) DML statements do have a lot more functionality than FoxPro had - for example, consider using the OUTPUT clause to return the actually changed data to the client or into a table without needing another SELECT - and MERGE lets you do UPDATE, INSERT, and DELETE at the same time in a single statement (with caveats) - so my point is that I think you should consider using this opportunity to explore how you can actively improve your product/project's codebase and functionality/features while you do this VFP-to-MSSQL porting work.

---

Update: Better docs

I found a copy of the Visual FoxPro documentation file (.chm) from the VFP9 CD and I've uploaded it to the Internet Archive if anyone would like unadulterated VFP docs, as the unofficial mirror I linked to has clearly been (at least) aesthetically compromised...

It's here:https://archive.org/details/dv_foxhelp the dv_foxhelp.chm file should have a SHA-256 hash of ABAA86E7623BB00E8BD9323CF2D8E162013598E35D9492557A3DDD1C2CF13E79.

On Windows 10 you'll need to fiddle with CHM/IE settings otherwise it'll display only a white page:

• https://community.spiceworks.com/topic/1961503-solved-windows-10-chm-help-files-showing-up-blank
• https://www.helpsmith.com/chm-cannot-be-displayed.php

Answer 2

Dai made quite a good explanation and was almost right on what it does.

In SQL Server, by default, it would translate as (assuming the table name is theTable):

Update theTable 
set Column1 = 'N/A' 
where Column2 not like 'Some string value%';

That might also mean, but very unlikely:

Update theTable 
set Column1 = 'N/A' 
where Column2 <> 'Some string value';

The reason for this is in Foxpro by default, with xBase commands, the comparison is done up to the right operand's length.

EDIT: Also in VFP, the comparison is case sensitive. In SQL Server, it would likely be case-insensitive. That means:

Column2: 'some string value'

would be updated in VFP but not in SQL Server.

Taking that into account. In SQL Server, a more appropriate translation would be:

Update theTable 
set Column1 = 'N/A' 
where Column2 not like 'Some string value%' COLLATE Latin1_General_CS_AS; 

EDIT: Here is some VFP code to show what it does (with default settings) - And BTW this is a very BAD use of VFP syntax:

Create Cursor crsSample (Id i, column1 c(10), column2 c(100))

Insert Into crsSample (Id, column2) Values (1, "Some string value")
Insert Into crsSample (Id, column2) Values (2, "some string value") && case sensitivity check
Insert Into crsSample (Id, column2) Values (3, "Some string value and some more")
Insert Into crsSample (Id, column2) Values (4, "some string value and some more") && case sensitivity check
Insert Into crsSample (Id, column2) Values (5, "Totally irrelevant")


Browse For column2="Some string value" Title [Column2="Some string value"]

Browse For column2="Some string value"=.F.  Title [Column2="Some string value"=.f.]