Visual FoxPro 9.0 Beta SQL Release Notes



This document contains details on new VFP9 SQL enhancements. Note: this feature is already documented in the Help file, however, we are providing these notes to better assist you in identifying the new enhancements.

VFP 9.0 Feature Description
Summary of Features
Feature
VFP8

Amount of Joins / Subqueries in SQL statement.
9 Joins

9 Subqueries

Amount of UNIONs in SQL SELECT statement.
9 Unions

Amount of Tables referenced by SQL statement.
30 tables

Increase IN () argument limit.
24 args







Allow Multiple Subquery Nesting.
1 Level

Allow UNION in INSERT INTO ... SELECT ... FROM ...
Not supported

Allow GROUP BY in correlated Subquery.
Not supported

Allow ORDER BY in conjunction with TOP N inside of non-correlated Subquery.
Not supported

Allow sub-SELECT in FROM clause.
Not supported

Allow subquery in SELECT list (projection).
Not supported

Allow ORDER BY using field name with UNION clause.
Not supported

Allow Subquery in UPDATE SET list.
Not supported

Allow Correlated UPDATE statements.
Not supported

Allow Correlated DELETE statements.
Not supported

Allow Aggregate functions in SELECT list of a Subquery using Comparison Operator.
Not supported

Allow complex expressions to be compared with correlated field for Correlated Subquerry.
Not supported







Optimize LIKE "sometext%" performance.
n/a

Improve TOP N performance.
n/a







New support for local buffered data.
n/a

SET ENGINEBEHAVIOR 70 ! 80 ! 90.
Supported


SQL - Amount of Joins / Subqueries in SQL Statement
In VFP9, there is no hard coded limit for amount of joins or amount of subqueries used in a SQL statement.

CLOSE DATABASES all

CREATE CURSOR foo (f1 I)

SELECT * FROM foo t1 JOIN foo t2 ON 1=2 JOIN foo t3 ON 1=2 JOIN;

foo t4 ON 1=2 JOIN foo t5 ON 1=2 JOIN foo t6 ON 1=2 JOIN ;

foo t7 ON 1=2 JOIN foo t8 ON 1=2 JOIN foo t9 ON 1=2 JOIN ;

foo t10 ON 1=2 JOIN foo t11 ON 1=2 JOIN foo t12 ON 1=2 JOIN;

foo t13 ON 1=2



SELECT * FROM foo t1 WHERE ;

f1 IN (SELECT f1 FROM foo);

AND f1 IN (SELECT f1 FROM foo);

AND f1 IN (SELECT f1 FROM foo);

AND f1 IN (SELECT f1 FROM foo);

AND f1 IN (SELECT f1 FROM foo);

AND f1 IN (SELECT f1 FROM foo);

AND f1 IN (SELECT f1 FROM foo);

AND f1 IN (SELECT f1 FROM foo);

AND f1 IN (SELECT f1 FROM foo);

AND f1 IN (SELECT f1 FROM foo)



Remarks

For VFP8, the limit for JOINs and Subqueries was 9.
In VFP8, we would generate an Error 1805 (SQL: Too many subqueiries).


SQL - Amount of UNIONs in SQL SELECT Statement
There is no hard coded limit for amount of UNIONs in a SQL SELECT statement in VFP9.

* Example:

CREATE CURSOR foo (f1 I)

INSERT INTO foo VALUES (1)



CREATE CURSOR foo1 (f1 I)

INSERT INTO foo1 VALUES (2)



SELECT f1 FROM foo ;

UNION ALL SELECT f1 FROM foo ; && 1

UNION ALL SELECT f1 FROM foo ;

UNION ALL SELECT f1 FROM foo ;

UNION ALL SELECT f1 FROM foo ;

UNION ALL SELECT f1 FROM foo ;

UNION ALL SELECT f1 FROM foo ;

UNION ALL SELECT f1 FROM foo ;

UNION ALL SELECT f1 FROM foo ;

UNION ALL SELECT f1 FROM foo ;

UNION ALL SELECT f1 FROM foo ; && 10

UNION ALL SELECT f1 FROM foo ;

UNION ALL SELECT f1 FROM foo ;

UNION ALL SELECT f1 FROM foo ;

UNION ALL SELECT f1 FROM foo ;

UNION ALL SELECT f1 FROM foo ;

UNION ALL SELECT f1 FROM foo ;

UNION ALL SELECT f1 FROM foo ;

UNION ALL SELECT f1 FROM foo ;

UNION ALL SELECT f1 FROM foo ;

UNION ALL SELECT f1 FROM foo && 20



Remarks

For VFP8, the limit for Unions was 9.
In VFP8, we would generate an Error 1834 (SQL: Too many UNIONs.).
Note: parentheses are NOT completely supported with UNIONs.
o UNION: allow parenthesis around a single SQL SELECT statement.

o Note: if two or more SELECT statements are enclosed in parenthesis, a descriptive error is reported – this is regardless of current ENGINEBEHAVIOR level.

Here are some examples:

* Works OK:

SELECT * from Table1 ;

UNION ;

(SELECT * from Table2) ;

UNION ;

(SELECT * from Table3)



* Should fail:

SELECT * from Table1 ;

UNION ;

(SELECT * from Table2 ;

UNION ;

SELECT * from Table3)



SQL - Amount of Tables Referenced by SQL Statement
There is no hard coded limit for amount of tables referenced by a SQL statement in VFP9.

CLOSE DATABASES all

CREATE CURSOR foo (f1 I)



SELECT * FROM foo t1 WHERE ;

f1 IN (SELECT f1 FROM foo);

AND f1 IN (SELECT t1.f1 FROM foo t1,foo t2,foo t3,foo t4,;

foo t5,foo t6,foo t7,foo t8,foo t9,foo t10);

AND f1 IN (SELECT t1.f1 FROM foo t1,foo t2,foo t3,foo t4,;

foo t5,foo t6,foo t7,foo t8,foo t9,foo t10);

AND f1 IN (SELECT t1.f1 FROM foo t1,foo t2,foo t3,foo t4,;

foo t5,foo t6,foo t7,foo t8,foo t9,foo t10)



Remarks

In VFP8, we would generate an Error 1812 (SQL: Statement too long.).


SQL - Increase IN () Argument Limit Beyond 24
For VFP9, we will increate the number arguments allowed in a SQL IN statement. Currently, there is a limit of 24 arguments that can be passed.

CLOSE DATABASES all

CREATE CURSOR foo(f1 I,f2 I)

INSERT INTO foo values(1,6)

INSERT INTO foo values(2,5)

INSERT INTO foo values(3,4)



SELECT * FROM foo WHERE foo.f1 ;

IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,;

16,17,18,19,20,21,22,23,24,25,26,27,28,29,30)



Remarks

The amount of items is still indirectly limited by SYS(3055) setting. The more SYS(3055) settings, the more items can be used.
In VFP8 and earlier, SQL-IN is mapped to INLIST function, but this is not the case in VFP9. VFP9 may stop evaluating expressions from the list as soon as the match is found. Thus, if IN condition is not Rushmore optimized, the performance can be improved by placing values that more likely to match into very beginning of the list.
In VFP8 and earlier, the View/Query designer converts INLIST function calls into SQL-IN clauses. Due to the difference between those functions, the View/Query designer in VFP9 does not convert INLIST into SQL-IN.Since INLIST() is unchanged, it is still limited to 24 arguments.
In VFP8, we would generate an Error 1845 (SQL expression is too complex.).


SQL – Allow Multiple Subquery Nesting
VFP9 will allow for multiple subquery nesting. Correlation is allowed to the immediate parent. There is no hard coded limit for nesting depth.

Syntax:

SELECT … WHERE … (SELECT …. WHERE … (SELECT….)…) ….



The following queries which fails in VFP8, will now work in VFP9:

CREATE CURSOR foo (f1 I)

INSERT INTO foo VALUES (0)



CREATE CURSOR foo1 (f1 I)

INSERT INTO foo1 VALUES (1)



CREATE CURSOR foo2 (f1 I)

INSERT INTO foo2 VALUES (2)



SELECT * FROM foo T1 WHERE EXISTS ;

(SELECT * from foo1 T2 WHERE NOT EXISTS ;

(SELECT * FROM foo2 T3))



*** other example ***

SELECT * FROM table1 WHERE table1.iid IN ;

(SELECT table2.itable1id FROM table2 WHERE table2.iID IN ;

(SELECT table3.itable2id FROM table3 WHERE table3.cValue = ;

"value"))



Remarks

In VFP8, we would generate an Error 1842 (SQL: Subquery nesting is too deep.) if this situation occurred (more than 1 level of nesting).


SQL - Allow UNION in INSERT INTO ... SELECT ... FROM ...
We will allow for UNION clause in a SQL INSERT statement FROM clause.

Syntax:

INSERT INTO … SELECT …. FROM …[ UNION SELECT…[ UNION ….]]



The following scenario will now work in VFP9:

CLOSE DATABASES all

CREATE CURSOR foo (f1 I,f2 I)

CREATE CURSOR foo1 (f1 I,f2 I)

CREATE CURSOR foo2 (f1 I,f2 I)



INSERT INTO foo1 VALUES (1,1)

INSERT INTO foo2 VALUES (2,2)



INSERT INTO foo SELECT * FROM foo1 UNION SELECT * FROM foo2



SELECT foo

LIST



Remarks

In VFP8, we would generate an Error 36 (Command contains unrecognized phrase/keyword.).


SQL - Allow GROUP BY in Correlated Subquery
Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.

VFP8 does not allow using GROUP BY in correlated subquery. VFP9 removes this limitation.



Syntax:

SELECT … WHERE … (SELECT …. WHERE …GROUP BY…) ….



The following query fails in VFP8, but succeeds in VFP9:

CLOSE DATABASES all

CREATE CURSOR foo1 (f1 I, f2 I, f3 I)

INSERT INTO foo1 VALUES(1,2,3)

CREATE CURSOR foo2 (f1 I, f2 I, f3 I)

INSERT INTO foo2 VALUES(1,2,3)



SELECT * from foo1 T1 WHERE f1 IN (SELECT MAX(f1) FROM foo2 T2 ;

WHERE T2.f2=T1.F2 GROUP BY f3)



Remarks

In VFP8, we would generate an Error 1828 (SQL: Illegal GROUP BY in subquery.).


SQL - Allow ORDER BY in Conjunction with TOP N Inside of Non-Correlated Subquery
VFP9 will now allow for TOP N clause for non-correlated subquery. The ORDER BY clause should present if TOP N clause is used and this is the only scenario when it is allowed in subquery.

Syntax:

SELECT … WHERE … (SELECT … TOP nExpr … FROM … ORDER BY … ) …



The following query fails in VFP8, but succeeds in VFP9:

CLOSE DATABASES all

CREATE CURSOR foo1 (f1 I, f2 I, f3 I)

INSERT INTO foo1 VALUES(1,2,3)

CREATE CURSOR foo2 (f1 I, f2 I, f3 I)

INSERT INTO foo2 VALUES(1,2,3)



SELECT * FROM foo1 ;

WHERE f1 IN (SELECT TOP 5 f2 FROM foo2 order by f2)



Remarks

In VFP8, we would generate an Error 1221 (Command is missing required clause.).


SQL - Allow Sub-SELECT in FROM Clause
This is often referred to as a derived table. Derived tables are SELECT statements in the FROM clause referred to by an alias or a user-specified name. The result set of the SELECT in the FROM clause forms a table used by the outer SELECT statement.

VFP9 will allow one to use subquery in FROM clause.

Syntax:

SELECT … FROM (SELECT … ) …



For example:

SELECT * FROM ;

(SELECT * FROM foo T1 ;

WHERE f1 = (SELECT T2.f2 FROM foo1 T2 WHERE T2.f1=T1.f2) ;

UNION SELECT * FROM foo2 ;

ORDER BY 2 desc) AS subquery



Note: the following code will fail:

SELECT * FROM (SELECT TOP 5 f1 FROM foo) ORDER BY f1



Remarks

Sub-SELECT should be enclosed in parentheses, but alias is required. Correlation is not supported. Sub-SELECT has the same syntax limitation as SELECT command (not subquery syntax limitation). All sub-SELECTs are executed before the top most SELECT is evaluated.
In VFP8, we would generate an Error 10 (Syntax error.).


SQL - Allow Subquery in SELECT List (Projection)
VFP9 will allow subquery as a column or a part of expression in projection.

Syntax:

SELECT … (SELECT …. ) … FROM ….



The following query fails in VFP8, but succeeds in VFP9:

SELECT T1.f1, (SELECT f2 FROM foo2 T2 WHERE T2.f1=T1.f1) ;

FROM foo1 T1



Remarks

Subquery in projection has exactly the same requirements as subquery used in comparison operation. If subquery does not return any records, NULL value is returned.
In VFP8, we would generate an Error 1810 (SQL: Invalid use of subquery.).


SQL – Allow ORDER BY Using Field Name with UNION Clause
In VFP8, as soon as one uses the UNION clause, you need to use numeric references and can no longer use the field name for the ORDER BY clause. In VFP9, we will remove this restriction such as in following example:

CLOSE DATABASES all

CREATE CURSOR foo(f1 I,f2 I)

INSERT INTO foo values(1,6)

INSERT INTO foo values(2,5)

INSERT INTO foo values(3,4)



SELECT f1, f2, .T. AS FLAG,1 FROM foo;

WHERE f1=1;

UNION ;

SELECT f1, f2, .T. AS FLAG,1 FROM foo;

WHERE f1=3;

ORDER BY f2 ;

INTO CURSOR TEMP READWRITE



BROWSE NOWAIT



Remarks

In VFP8, we would generate an Error 1808 (SQL: ORDER BY clause is invalid.).
Referenced fields must be present in SELECT list (projection) for the last select in the UNION, that projection is used for ORDER BY operation.


SQL – Allow Subquery in UPDATE SET List
VFP9 will allow for a subquery in UPDATE SET clause.

Syntax:

UPDATE … SET …(SELECT …) …



Example:

CLOSE DATA

CREATE CURSOR foo1 (f1 I , f2 I NULL)



INSERT INTO foo1 VALUES (1,1)

INSERT INTO foo1 VALUES (2,2)

INSERT INTO foo1 VALUES (5,5)

INSERT INTO foo1 VALUES (6,6)

INSERT INTO foo1 VALUES (7,7)

INSERT INTO foo1 VALUES (8,8)

INSERT INTO foo1 VALUES (9,9)



CREATE CURSOR foo2 (f1 I , f2 I)



INSERT INTO foo2 VALUES (1,10)

INSERT INTO foo2 VALUES (2,20)

INSERT INTO foo2 VALUES (3,30)

INSERT INTO foo2 VALUES (4,40)

INSERT INTO foo2 VALUES (5,50)

INSERT INTO foo2 VALUES (6,60)

INSERT INTO foo2 VALUES (7,70)

INSERT INTO foo2 VALUES (8,80)



UPDATE foo1 SET f2=100+(SELECT f2 FROM foo2 ;

WHERE foo2.f1=foo1.f1) WHERE f1>5



SELECT foo1

LIST



Remarks

A subquery in a SET clause has exactly the same requirements as a subquery used in comparison operation. If the subquery does not return any records, NULL value is returned.
Only one subquery is allowed in a SET clause.
If there is a subquery in SET clause, subqueries in WHERE are not allowed.
In VFP8, we would generate an Error 1300 (Function name is missing).


SQL – Support for Correlated UPDATE
VFP9 will support correlated UPDATE.

Syntax



UPDATE … SET … FROM … WHERE…



Examples:

CLOSE DATABASES all



CREATE CURSOR foo1 (f1 I , f2 I NULL,f3 I NULL)

INSERT INTO foo1 VALUES (1,1,0)

INSERT INTO foo1 VALUES (2,2,0)

INSERT INTO foo1 VALUES (5,5,0)

INSERT INTO foo1 VALUES (6,6,0)

INSERT INTO foo1 VALUES (7,7,0)

INSERT INTO foo1 VALUES (8,8,0)

INSERT INTO foo1 VALUES (9,9,0)



CREATE CURSOR foo2 (f1 I , f2 I)

INSERT INTO foo2 VALUES (1,10)

INSERT INTO foo2 VALUES (2,20)

INSERT INTO foo2 VALUES (3,30)

INSERT INTO foo2 VALUES (4,40)

INSERT INTO foo2 VALUES (5,50)

INSERT INTO foo2 VALUES (6,60)

INSERT INTO foo2 VALUES (7,70)

INSERT INTO foo2 VALUES (8,80)



CREATE CURSOR foo3 (f1 I , f2 I)

INSERT INTO foo3 VALUES (6,600)

INSERT INTO foo3 VALUES (7,700)



UPDATE foo1 SET foo1.f2=foo2.f2, f3=foo2.f2*10 FROM foo2 ;

WHERE foo1.f1>5 AND foo2.f1=foo1.f1



SELECT foo1

LIST



UPDATE foo1 SET foo1.f2=foo3.f2 FROM foo2, foo3 ;

WHERE foo1.f1>5 AND foo2.f1=foo1.f1 AND foo2.f1=foo3.f1



SELECT foo1

LIST



Remarks

If a FROM clause is present then the name after UPDATE keyword defines the target for the update operation. This name can be a table name, an alias or a file name. The following logic is used to select the target table:
1) If the name matches implicit or explicit alias for a table in the FROM clause, then the table is used as a target for the update operation.

2) If the name matches alias for the cursor in the current data session, then the cursor is used as a target.

3) Table or file with the same name is used as a target.



The FROM clause has the same syntax as with the SELECT command with the following limitations:
o Target table/cursor can not be involved into OUTER JOIN as a secondary table.

o Target cursor can’t be a subquery result.

o It should be possible to evaluate all other JOINs before joining the target table.



In VFP8, we would generate an Error 36 (Command contains unrecognized phrase/keyword.).


SQL – Support for Correlated DELETE
VFP9 will support a correlated DELETE.

Syntax



DELETE [alias] FROM alias1 [, alias2, …] WHERE…



Examples:

CLOSE DATABASES



CREATE CURSOR foo1 (f1 I , f2 I NULL,f3 I NULL)

INSERT INTO foo1 VALUES (1,1,0)

INSERT INTO foo1 VALUES (2,2,0)

INSERT INTO foo1 VALUES (5,5,0)

INSERT INTO foo1 VALUES (6,6,0)

INSERT INTO foo1 VALUES (7,7,0)

INSERT INTO foo1 VALUES (8,8,0)

INSERT INTO foo1 VALUES (9,9,0)



CREATE CURSOR foo2 (f1 I , f2 I)

INSERT INTO foo2 VALUES (1,10)

INSERT INTO foo2 VALUES (2,20)

INSERT INTO foo2 VALUES (3,30)

INSERT INTO foo2 VALUES (4,40)

INSERT INTO foo2 VALUES (5,50)

INSERT INTO foo2 VALUES (6,60)

INSERT INTO foo2 VALUES (7,70)

INSERT INTO foo2 VALUES (8,80)



CREATE CURSOR foo3 (f1 I , f2 I)

INSERT INTO foo3 VALUES (6,600)

INSERT INTO foo3 VALUES (7,700)



DELETE foo1 FROM foo2 ;

WHERE foo1.f1>5 AND foo2.f1=foo1.f1



SELECT foo1

LIST

RECALL ALL



DELETE foo1 FROM foo2, foo3 ;

WHERE foo1.f1>5 AND foo2.f1=foo1.f1 AND foo2.f1=foo3.f1



SELECT foo1

LIST

RECALL ALL



DELETE FROM foo1 WHERE foo1.f1>5



SELECT foo1

list

RECALL ALL



DELETE foo1 from foo1 WHERE foo1.f1>5



RECALL ALL IN foo1



DELETE T1 ;

FROM foo1 T1 JOIN foo2 ON T1.f1>5 AND foo2.f1=T1.f1, foo3 ;

WHERE foo2.f1=foo3.f1



RECALL ALL IN foo1



Remarks:

If a FROM clause has more than one table, then the name after the DELETE keyword is required, it defines the target for the delete operation. This name can be a table name, an alias or a file name. The following logic is used to select the target table:
1) If the name matches implicit or explicit alias for a table in the FROM clause, then the table is used as a target for the update operation.

2) If the name matches alias for the cursor in the current data session, then the cursor is used as a target.

3) Table or file with the same name is used as a target.



· The FROM clause has the same syntax as for SELECT command with the following limitations:

o Target table/cursor can not be involved into OUTER JOIN as a secondary table.

o Target cursor can’t be a subquery result.

o It should be possible to evaluate all other JOINs before joining the target table.



· In VFP8, we would generate an Error 36 (Command contains unrecognized phrase/keyword.).



SQL – Optimize LIKE "sometext%" Performance
In VFP, a SQL-SELECT statement with LIKE "sometext%" was not fully optimizable. While this can be worked around with local VFP data such as following expression, it is not supported with other back ends.

With VFP local data, to find records that have some text in the beginning of a field we can use following Rushmore optimizable query:

SELECT * FROM table1 WHERE somefield = "sometext"



However, this statement is not supported with many SQL back ends, but instead one would use a more common expression such as:

SELECT * FROM table1 WHERE somefield LIKE "sometext%"



Remarks

In VFP9, conditions like <field> [NOT] LIKE "<N non-wildcard symbols>[%[%[...]]] should be fully optimized. This is common with expressions such as -- LIKE “sometext%”.


SQL – Optimize TOP N Performance
In VFP8 and earlier, Top N [PERCENT] is applied as follows: all records are first sorted then Top N [PERCENT] are extracted.

For VFP9, we will improve performance by eliminating records that don't go into Top N from the sort process as early as possible. The performance is improved by decreasing amount of file I/O operations when operating on big result sets and/or in low memory situations.

Limitations

Top N PERCENT can’t be optimized unless the whole result set can be read into memory at once otherwise the real N is not known.


Remarks

Optimization requires that TOP N should return no more than N records (this is not the case for VFP8 and earlier) which is enforced if SET ENGINEBEHAVIOR is set to 90 .


SQL – New Support for Local Buffered Data
It would be very beneficial to be able to use SQL to select records out of a local buffered cursor which have not yet been table updated. Many times when building grids, list boxes, combo boxes, etc., it is necessary to consider newly added records which have not yet been committed. Currently, SQL statements are based on content that is already committed to disk.

We will add new language (SET) and new keywords to the SQL-SELECT statement to support this.

SELECT … WITH (BUFFERING=lExpr)
Syntax:

SELECT … FROM … Table [[AS] Local_Alias][ WITH (BUFFERING = lExpr) ]



lExpr – logical expression if .T., then Buffering is on, otherwise off.



Examples:

SELECT * FROM customers WITH (BUFFERING=.T.)

lBuff = .F.

SELECT * FROM customers WITH (BUFFERING=m.lBuff)



Remarks

If the BUFFERING=.T. clause is used, then the SELECT statement pulls data from the local buffered cursor. IF BUFFERING=.F., then the SELECT statement pulls data from cursor on disk (which may be different to what is in buffer). If no BUFFERING statement is included, then VFP uses the SET SQLBUFFERING setting. By default, VFP will use the VFP8 setting of pulling data from disk (SET SQLBUFFERING=OFF). A SELECT…WITH BUFFERING setting always overrides the SET SQLBUFFERING setting.
Support for BUFFERING is only supported on local Fox data. It is not supported on data from other SQL databases so one should not use these clauses for SQL pass through. If you intend to write generic code that could apply to local and remote data, then consider using the SET SQLBUFFERING command.
A syntax error will occur if invalid use of WITH BUFFERING clause is used in statement.
The SELECT … WITH BUFFERING option is respected only if alias of existing cursor is used. Consider the following examples:
Example 1:
SELECT * FROM customers WITH (BUFFERING=.T.)

This statement respects the WITH… option only if there is a work area with alias “customers”.


Example 2:
SELECT * FROM Northwind!customers WITH (BUFFERING=.T.)

This statement never respects the option.

· The WITH … option is also ignored for non-buffered cursors and cursors that act as a target for UPDATE/DELETE operation.

· When BUFFERING=.T. is used for row buffered cursor, the current record will be commited before the statement is executed. In case of table buffered cursor, the “dirty” current record will be saved into the buffer.

· THE WITH BUFFERING clause is needed for each table referenced. The following example shows this:

SELECT t1.*, t2.* FROM FORCE (cFoo) t1 WITH (BUFFERING=.T.) JOIN;

(cBar) t2 WITH (BUFFERING=.T.) ON f1>=f3 AND f2>0 AND ;

f4<100 INTO CURSOR (cFoo+cBar+'_1')



SET SQLBUFFERING
We will add a new SET command to control whether data in a SQL-SELECT statement is based on buffered or committed-to-disk data. This gives customers ability to use a single command to apply to existing SELECT statements without needing to modify those statements. In addition, it allows for flexibility with generic SELECT code that is used against both VFP local and remote back end data.

Syntax

SET SQLBUFFERING ON | OFF



Remarks

If this setting is ON, then SELECT statements use local buffered data (if available). If data is not buffered, we simply grab from disk.
The default for this setting will be OFF to maintain compatibility with VFP8 and earlier versions.
This SET command is scoped to the current data session.
One can use SET(“SQLBUFFERING”) to query the current setting.
If the WITH (BUFFERING=ON | OFF) clause is used with a SELECT statement, then it overrides this SET statement. The WITH clause gives more granular control at the table level whereas the SET statement applies at the session level.


SQL – Allow Aggregate Functions in SELECT List of a Subquery Compared Using {< | <= | > | >=} {ALL | ANY | SOME}
For VFP9, we will support aggregate functions in SELECT list of a subquery compared using {< | <= | > | >=} {ALL | ANY | SOME}.



SELECT * FROM Foo WHERE ;

f1 > ALL (SELECT COUNT(*) FROM Bar GROUP BY f2) ;

INTO CURSOR Res1



SQL – Correlated Subquery - Allow Complex Expressions to be Compared with Correlated Field
Currently (VFP8), correlated fields can be referenced only in the following form:

<correlated field><Comparison><local field>

-or-

<local field><Comparison><correlated field>



For VFP9. we will alter this to support:

<correlated field><Comparison><local expression>

-or-

<local expression><Comparison><correlated field>



Where <local expression> must use at least one local field and can not reference any outer (correlated) field. For example:



SELECT * FROM Foo ;

WHERE EXISTS(SELECT * FROM Bar ;

WHERE Bar.f2 / 2 > Foo.f1)



Changes for Expressions Compared with Subqueries.
In VFP8, the left part of a comparison ([NOT] IN, {< | <= | = | == | <> | != | >= | >} [{ALL | ANY | SOME}]) with subquery must reference one and only one table from the FROM clause. In case of a comparison with correlated subquery, the table must also be the correlated table.

In VFP9 this restriction is replaced with:

The expression on the left side of IN comparison must reference at least one table from the FROM clause. For example, the following query fails:

SELECT * FROM foo WHERE 4 IN (SELECT f2 FROM bar)


The left part for the condition …{= | == | <> | != } {ALL | SOME |ANY} must reference at least one table from the FROM clause. For example, the following queries fail:

SELECT * FROM foo WHERE 4 = ALL (SELECT f2 FROM bar)
SELECT * FROM foo WHERE 4 <> SOME (SELECT f2 FROM bar)


The following should work:

SELECT * FROM foo WHERE 4 = (SELECT f2 FROM bar)

SELECT * FROM foo WHERE 4 <> (SELECT f2 FROM bar)

SELECT * FROM foo WHERE f1 = ALL (SELECT f2 FROM bar)

SELECT * FROM foo WHERE f1 <> SOME (SELECT f2 FROM bar)



The left part for the condition …{> | >= | < | <=} {ALL | SOME |ANY} (SELECT TOP …) must reference at least one table from the FROM clause. For example, the following queries fail:

SELECT * FROM foo;
WHERE 4 > ALL (SELECT TOP 5 f2 FROM bar ORDER BY 1)
SELECT * FROM foo;
WHERE 4 > SOME (SELECT TOP 5 f2 FROM bar ORDER BY 1)


The left part for the condition …{> | >= | < | <=} {ALL | SOME |ANY} (SELECT <aggregate function> …) must reference at least one table from the FROM clause. For example, the following query fail:

SELECT * FROM foo;
WHERE 4 > ALL (SELECT MAX(f2) FROM bar)



The left part for the condition …{> | >= | < | <=} {ALL | SOME |ANY} (subquery with GROUP BY and/or HAVING) must reference at least one table from the FROM clause. For example, the following query fail:


SELECT * FROM foo;
WHERE 4 > ALL (SELECT f2 FROM bar GROUP BY f2;

HAVING MAX(f3)>100)



Remarks

The left part for a comparison that does not not come from the list (for example, {ALL | SOME |ANY} are not used, etc.) is allowed to not reference any table from the FROM. In all cases, the left part is allowed to reference more than one table from the FROM. And in case of a correlated subquery, the left part does not have to reference correlated table.


SET ENGINEBEHAVIOR
We will update the SET ENGINEBEHAVIOR command that we first introduced in VFP8 to include some additional SQL behavior.

Syntax

SET ENGINEBEHAVIOR 70 ! 80 ! 90



Remarks



Specifies that Visual FoxPro treats SQL commands with the standard Visual FoxPro 9.0 behavior.
For VFP9, we will update SET ENGINEBEHAVIOR for following behavior changes with 90 setting:
Top N performance optimization.
Top N never returns more than N records – because of new TOP N optimizations, the result set will never contain more than the N requested records. This scenario would occur where you have multiple records of the same value for N, N+1, N+…. records.
Aggregate function without GROUP BYalways returns a record – in VFP8, if an aggregate function such as MAX() was used in a SQL statement that resulted in no matches meeting criteria, we would return 0 records (_TALLY=0). This is often caused by not having an associated GROUP BY clause. For VFP9, the behavior will adhere more with SQL standards where we return a single record resultset.
Note: SET ENGINEBEHAVIOR changes also apply to SYS(3099):
SYS(3099 [, 70 | 80 | 90])



© 2004 Microsoft Corporation. All rights reserved.

Вот это уже на что-то похоже, если будет работать как задекларировано

Updates records in a table with new values.

Note:
The SQL UPDATE command can update records only for a single table.


UPDATE Target
SET Column_Name1 = eExpression1 [, Column_Name2 = eExpression2 ...]
[FROM [FORCE] Table_List_Item [[, ...] | [JOIN [ Table_List_Item]]]
WHERE FilterCondition1 [AND | OR FilterCondition2 ...]


Parameters
UPDATE Target


Specifies a target table, cursor, table or cursor alias, or file to update. You can include multiple sources of data for the update operation in the FROM clause. Target can have the following syntaxes:
[DatabaseName!]TableName DatabaseName! specifies the name of a database containing the table if the table is in a noncurrent database. If the table is in a noncurrent database, you must include the name of the database. Use an exclamation point (!) as a delimiter immediately following the database name and preceding the table name. TableName specifies the name of a table for the update operation.
Alias Alias specifies an alias that matches a table in the FROM clause or a cursor in the current data session for the update operation.
FileName FileName specifies the name of a file for the update operation.
SET Column_Name1= eExpression1[, Column_Name2= eExpression2...]


Specifies the columns in the table to update and their new values. If you omit the WHERE clause, every row in the table is updated with the same value. When specifying expressions in the SET clause, you can include one subquery per SET clause. If the subquery does not return any results, it returns NULL. For syntax and information about subqueries, see SELECT - SQL Command.
Note:
If you use a subquery in the SET clause, you cannot use subqueries in the WHERE clause. A subquery in the SET clause must meet exactly the same requirements as subqueries used in comparison operations.

Мерси JS!...

Цитата:

INSERT INTO dbf_name [(FieldName1 [, FieldName2, ...])]
SELECT SELECTClauses [UNION UnionClause SELECT SELECTClauses ...]

Вот это - дело !... Это действительно новая фишка(т.е. в 8-ке её не было?)?

Давайте уже на новую ветку, а то не дай бог сервер завалим...

Смотрю этот VFP 9 Reporting system и не пойму - толи в бете отрезали Target Alias для Detail band толи еще не докрутили - потому как описывается

Цитата:

Automatic one-to-many behavior
Having validated the targetAliasExpressions, VFP sets up one-to-many behavior for the duration of the report if you have not already done so explicitly. Your previous environment is restored after the report runs. Refer to the chart below for a more complete reference to the cases in which VFP takes this action.

А вот этого самого targetAliasExpressions что-то не нашел

Плохо только то, что у меня бета уже 3 раза упала при дизайне формы

2 WiRuc

Цитата:

Плохо только то, что у меня бета уже 3 раза упала при дизайне формы

У меня два раза подряд при первом запуске.
Убрал foxuser.dbf - (брался старый от vfp8) - и вроде полегчало.

Да Db Engine нормально навернули.

А вот в гриде что самое обидное - изменений ровно одно - Выбор Иконки,
а ведь есть поле для деятельности.
В EditBox'e Горизонтальную прокрутку так и не прикрутили.
Меню.... - обидно.

Блин - мимо кассы рапорт - не пользую его.

Блин опять руский рантайм кривой
В конструкторе таблиц - в табличной части объекты понатыканы как попало

Цитата:

А вот в гриде что самое обидное - изменений ровно одно - Выбор Иконки,

Ну не совсем одно... В column и header теперь можно указывать свой курсор мыши. Хотя конечно этого маловато

Цитата:

Блин - мимо кассы рапорт - не пользую его

Мож теперь запользуешь

2 WiRuc

Цитата:

Ну не совсем одно... В column и header теперь можно указывать свой курсор мыши. Хотя конечно этого маловато

ОписАлся Курсор, а не Иконку и имел в виду

Anchor - Absolute - все понятно, Relative - на кой? По какому принципу ресайз.

Смотря что привязывать. Если поставить для якоря ззначение к примеру 16 то контрол будет зафиксирован относительно Left и при "растяжке" формы начнет смещаться по вертикали.



[i][small][color=Gray]Отредактировано (04.06.04 15:10)

Тоже качаю, пока 38,5мб - 6 часов ожидания и 300р. ;)
Спасибо за ссылку!

2 Syberex

Цитата:

Тоже качаю, пока 38,5мб - 6 часов ожидания и 300р. ;)
Спасибо за ссылку!

У меня жопорез быстрей фурычит, чет плохо у вас диалапом.

Скачал!
Ну на скорости 46,6 примерно по 5,1кб в секунду,
а первую половину утром качал чуть медленней 3,8-4,6 в сек.

Да ну? У меня ещё в VFP8 перестало работать Ибо правильнее писать WHERE ?&lcWhere т.е. указывать что это параметр. Но я знаю как минимум 2 манёвра:
1) Делать такие View в VFP7 или младше
2) Заниматься прямой правкой DBC-файла - т.е. во View писать WHERE 1=?cWhere и потом в поле Property DBC делать замену этой подстроки на ?&lcWhere - т.к. число символов не меняется, то всё будет хорошо - не надо править "счётчик длинны" который имеется в этом поле на каждую Property.
Или ты обнаружил что такое View в принципе не работает? Т.е. даже если взять базу от VFP7 то View не откроется?

Я так понимаю это сильно поможет для курсоров получаемых с реальных SQL серверов - там Varchar в большом почёте - без такого родного типа в VFP приходилось извращаться чтобы при записи обратно на SQL не добавлялись лишние пробелы - или ручками INSERT/UPDATE ПИСАТЬ, или в CursorAdapter ConversionFunc пользовать... Теперь наверное можно позабыть о таких мучениях

Они еще и управление разметкой добавили.

Здравствуйте, Владимир!

Цитата:

Если интересно, оказывается VarChar хранится также, как и CHAR, т.е. физически занимает столько байт, сколько указано в его размерности + один скрытый байт, содержащий реально заполненное количество символов.

На самом деле, один служебный байт может обслуживать восемь Varchar полей и его использование ничем не отличается от служебного байта для NULL полей. Более того, NULL и Varchar поля могут использовать тот же самый служебный байт. Если в таблице 7 NULL полей, то изменение одного Char поля на Varchar не отразится на размере записи.

Алексей.