SET ENGINEBEHAVIOR 70 ¦ 80

[ShyFox]

It seems that in the Foxpro 8.0 - Professional release not Beta - they changed the sql engine behavior. Is this a way to aproach with SQL Server type of interpreting the commands? Could we just cut and paste from sql window into a foxpro method and vice-versa?
This will really be a interesting point of view!
Regards

As I go deeper the road seems to go further. We're just simply pasangers on the road of knowledge.

 

[rob444]

Just read the helpfile about this.

Rob.

Helpfile contents on SET ENGINEBEHAVIOUR:

Enables SQL data engine compatibility with Visual FoxPro 7.0 or Visual FoxPro 8.0.

SET ENGINEBEHAVIOR 70 | 80
Parameters
70
Specifies that Visual FoxPro treats SQL SELECT commands as it did in versions prior to Visual FoxPro 8.0
80
Specifies that Visual FoxPro treats SQL SELECT commands as follows. (Default) SQL SELECT clause Behavior
DISTINCT You cannot use the DISTINCT clause with Memo or General fields. Instead, wrap a Memo field inside a function such as PADR( ) or ALLTRIM( ).
For more information, see PADL( ) | PADR( ) | PADC( ) Functions and ALLTRIM( ) Function.

UNION The UNION clause does not support Memo fields unless the ALL clause is included in the SQL SELECT statement.
GROUP BY The GROUP BY clause does not support Memo or General fields.
The GROUP BY clause must list every field in the SELECT list except for fields contained in an aggregate function, such as the COUNT( ) function. In addition, the GROUP BY clause must also list every field in a HAVING clause except for fields contained in an aggregate function.

For example, the following code raises an error because the field company is not listed in the GROUP BY clause.

SELECT company, country FROM Customer GROUP BY country
You can include an aggregate function in the SELECT list without having it in the GROUP BY clause.

For example, the following code uses the COUNT( ) function on the field company without having the field company in the GROUP BY clause.

SELECT COUNT(company), country FROM Customer GROUP BY country

HAVING A SQL SELECT statement can contain the HAVING clause without the GROUP BY clause as long as the SQL SELECT statement does not contain any aggregate functions.
For example, the following code filters query results by specifying the country field must equal "Sweden".

SELECT customerid FROM customers HAVING country="Sweden"

LIKE SQL SELECT statements do not automatically remove trailing spaces from values compared with the LIKE operation. In versions prior to Visual FoxPro 8.0, both values in the LIKE operation were trimmed from the right prior to evaluation.
For example, the following code assumes that you have a table named table1, and the table has three rows that contain values of "1 ", "12 ", and "123", respectively.

SELECT * FROM table1 WHERE column1 LIKE "1__"
Visual FoxPro version 7.0 and earlier returns one row with the value of "123". Visual FoxPro 8.0 returns three rows with the values, "1 ", "12 ", and "123".

If the beginning of the filter condition matches the pattern of the expression in the LIKE operation, and the rest of the filter condition contains trailing spaces, the LIKE operation ignores the trailing spaces and returns True (.T.). Trailing spaces in the pattern are not ignored.


Remarks
Using SET ENGINEBEHAVIOR set to 70 might lead to ambiguous results from SQL SELECT commands when the DISTINCT and UNION clauses are used with memos and the GROUP BY clause, the HAVING clause without a GROUP BY clause, and when using the LIKE clause.

The scope of SET ENGINEBEHAVIOR is global.

 

[ramani]

Hi shyfox,

I believe, Microsoft attempts to unify the SQL commands so that the commands appear the same across the languages. But while doing so, different languages stay at differnt levels of official releases.

Now imagine, the modfied VFP8 way of SQL commands which differ from earlier versions. To face this problem, SET ENGINEBEHAVIOR 70 makes the commands get interpreted as they did in earlier versions. The default is SET ENGINEBEHAVIOR 80 which makes it follow the VFP8 way.

Now the question, if we can cut, copy and paste SQL server command?. It depends on that SQL versions command matching with the VFP8s command structure.

So while we can copy, we have to see if it matches the command syntax of VFP8. That is the only way to ensure the accuracy. There is no guarantee that future versions of SQL will not modify the command nor VFP8 can provide the same.

Going with Microsofts VFP team doing a good job in the job, I for one would try to follow the latest command sets as the versions keep going. Also, it is excellent that compatibility has been kept with older versions by an easy SET command, is to be appreciated and is a big help for developers.



ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[ShyFox]

ramani (Programmer)
This SQL engine thing I encountered when I compiled a 7.0 project with 8.0. It was strange first. It worked with 7.0 but not with 8.0. But when I looked deeply in the help file I found this "set enginebehavior" command.
I think that Microsofts VFP team indeed made a strong and performing environment. I did a great investment buying this release.
About the command sets I agree with you. I'll be watching.
Regards


As I go deeper the road seems to go further. We're just simply pasangers on the road of knowledge.

 

[Mike Lewis]

ShyFox,

Could we just cut and paste from sql window into a foxpro method and vice-versa?

Unfortunately, it is not that simple.

It's true that the VFP implementation of SQL is now closer to that of SQL Server. They both now conform to the ANSI 92 standard. So the basic syntax is the same.

However, if the query contains anything other than basic field names, operators and literals, they might well be incompatible. This is especially true where functions are involved. For example, to find all overdue payments in VFP, you would do this:

SELECT * FROM PAYMENTS WHERE Due_Date < DATE()

but in SQL Server, it would be:

SELECT * FROM PAYMENTS WHERE Due_Date < GETDATE()

There a hundreds (literally) of functions in VFP that do not exist or have different syntax in SQL Server.

There are also different data types, different treatment of memo/text fields, and differences caused by empty dates. Also, string delimiters are different: SQL Server requires single quotes, not double quotes or square brackets.

So, for very simple queries, you can indeed cut and paste as you suggested. But don't count on being able to do that all the time.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[ShyFox]

Mike,
Indeed you're right.But what I said is for the queries where functions are not implied but very simple queries. Still your remark VFP implementation of SQL is now closer to that of SQL Server makes me think that step by step Microsoft does a real hard work of improving VFP and who knows maybe one day we can talk about a full database software developing package built from VFP and SQL server.
Or maybe not ... ?
Regards

As I go deeper the road seems to go further. We're just simply pasangers on the road of knowledge.

 

[Mike Lewis]

Shufox,

... makes me think that step by step Microsoft does a real hard work of improving VFP ...

That's for sure. The fact that VFP now conforms more closely with ANSI 92 is an important step.

And you're right: for simple queries, without functions or data type considerations, you can indeed cut and paste between the two platforms (but do watch out for those string delimiters).

Mike

Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk