SQL Select & Inserts on Only DBFs

[stanlyn]

Hi,

Using VFP9sp2x I'm replacing my xbase commands with sql like commands and have read that some of this is not as straight forward as we would like to see. I'm doing this using the SQL commands built into VFP9sp2 and only on dbf local tables. There is NO SQL database in this mix. There is confusion with what works and what doesn't when using vfp's sql, mssql, mysql, and/or mixing them, so I'll give each question a number and listen to your advice...

Once again, NO SQL databases here, only VFP databases and tables...

Using VFP9sp2...
1. Combining insert and union commands within a select statement when result cursors are used. - All references I've read says it DOES NOT WORK. It looks as if the vfp sql statement can create the cursor, it should be able to use in it unions and inserts, right????

2. Vfp cursors cannot be referenced by the builtin VFP SQL engine. I can see why real SQL engines would have a problem with referencing the VfP cursor as they are totally local with no outside references. Is that still true with vfp's builtin sql engine?

3. What else can or cannot be done with vfp's sql engine in the vfp environment that cannot be done in a mssql or mysql environment?

Any thoughts and explanations greatly appreciated...
Stanley

 

[Olaf Doschke]

1. You want two totally different things here, a) insert and union within a single query, that's not part of SQL, unions are always part of SELECT queries, what can be combined is INSERT INTO fieldlist SELECT, so you can do a query, even with unions and insert the result into a destination table or cursor in one commend.

2. wrong, of cursors can be referenced, joined, etc. The only exception is, if they are just filters to DBFs, use the READWRITE or at least NOFILTER keyword when querying INTO CURSOR, and you can of course use such cursors in further queries. Without any doubt you can use cursors created with CREATE CURSOR in queries, as if they were tables. Only remote databases don't see VFP cursors at all, but VFP also only sees cursors of the same datasession. That's no reason to always use CREATE CURSOR, though. It's very nice to let queries INTO CURSOR infere the result schema at runtime.

3. That's a too broad question. One thing that's both positive and negative is, you can use any VFP and user defined functions within the SQL, which won't of course translate to other sql dialects. Every database has some string and type conversion functions, though. So you may use LEFT, SUBSTR or STRTRAN and have to translate them to t-sql, mysql or other dialects, but they are mostly also offered from these sql engines. Specific VFP functions like the new CAST are really almost part of SQL, even though you can also CAST outside of queries in VFP.

Overall, if you find some statement of anybody you question, reference it and we can discuss if there was something said wrong or you have a misunderstanding of the situation or the statement. We're also not always perfect in remembering how things are in details or in teaching something based on not knowing what others already know. There are many levels of difficulties besides misunderstandings.

Have a nice weekend.

Bye, Olaf.

 

[Mike Lewis]

Stanley,

You say "All references I've read says it DOES NOT WORK".

I don't know what these references are, but why don't you try it yourself to see what works and what doesn't?

In particular, you say "Vfp cursors cannot be referenced by the builtin VFP SQL engine.". I don't know where you got that idea, but I can assue you it is not true.

The possible reason for your confuion might be to do with so-called "filtered" cursors. Let me explain. In a limited number of cases, a SELECT .... INTO CURSOR statement doesn't really produce a cursor. It simply opens a second instance of the original table, with SET FILTER and/or SET FIELDS applied to it.

For example, if you issue this statement:

SELECT ID, FirstName FROM Customers INTO CURSOR csrCusts WHERE ID < 100

This does not produce a new cursor. Instead, VFP re-opens the Customers table, with an alias of csrCusts. It applies a filter for IDs less than 100, and it applies a SET FIELDS so that only the two specified fields are visible.

In those limited cases, the resulting "cursor" cannot itself be input into another SQL commands (or rather, it can, but the results will not be what you expect).

The solution is to add a NOFILTER clause to the original SELECT. That will force VFP to produce a cursor, which then behaves exactly as you would expect. I strees that this is only necessary for these particular cases, where you have a single table, no joins, no grouping, no ordering, and no calculated fields. If you are in doubt, there is no harm in simply adding NOFILTER to all your SELECTs.

Apart from the above, you should have no difficulty in using SQL commands within VFP. VFP is no more or less a "SQL database" than what you call a "real database". If you are unsure of this point, just try it and see. I'm sure you'll get along fine.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TamarGranor]

Stanlyn - you might find the SQL papers on my website at

http://www.tomorrowssolutionsllc.com/publications/conferencepapers

useful.

Tamar

 

[Olaf Doschke]

>replacing my xbase commands with sql like commands

In regard to that aspect there is one essential difference in xbase vs SQL in that SQL queries copy data into a new alias, while xbase let's it stay where it is. SQL makes a snapshot, xbase can give you a live view. Tha advantage of sql is it scales better and it translates to usage of other databases.

A simple example is about the filter cursor already mentioned. Let's simply do that manually and compare SET FILTER with a WHERE clause. In the following example I don't index, as it's not about perfomrance optimisation but about the live vs snapshot aspect of xbase vs SQL:

Create Cursor curTest (cText C(20))
Insert Into curTest Values ("aaa")
Insert Into curTest Values ("aab")
Insert Into curTest Values ("xyz")

Select * From curTest Where cText="a" Into Cursor curResult
Browse && will show curResult
Select curTest
Set Filter To cText = "a"
Browse && will show the same result within the original table

Insert Into curTest Values ("aac")
Insert Into curTest Values ("zzz")
Select curResult && still only aaa and aab, no aac
Browse
Select curTest && includes aac
Browse

Of course you can redo the sql query to get aac into curResult. The point is, this is not live viewing of data.

This also works with Joins vs Set Relation and Set Skip, if you ever used that xbase commands to do a live join of tables.

The advantage of sql of course is to offer much more complicated queries and ways to compose a result with eg subqueries or unions. You can't do all kind of subqueries, you may do something like setting relation from table1 to table2 and filtering table1 by EOF("table2"), you can also "union" with APPEND, but it that's really adding data to the original tables or to a secondary cursor and not an aspect of live view of data anymore.

If your goal is to migrate data to an sql server or mysql server and still let it have a foxpro user interface your goal does not need to be to turn all xbase into sql, you just need new ways to get at data. If it's queried into cursors in a scalable way (eg not fetching full tables) you can still use xbase ways to relate cursors instead of tables and much of your application logic can stay as is. This doesn't even depend on deciding for remote view vs sqlpassthrough vs cursoradapter. In all cases you can create cursors, which can store back to the sql server and for your VFP layer are still like DBFs with all the xbase options you have to work on them.

If you then turn to .Net to reimplement the user interface, you will need to go new ways with the data in memory, bound to controls etc and "querying" from a dataset or array or collection, which are a .Net languages in memory data object. Even if you concentrate on datasets to be quite similar to data sessions in VFP and datatables as analogon to workareas there is no way to use sql to query from one datatable into another or union two datatables, instead you can address them quite like arrays and you may use LINQ to query them in a way more or less similar to sql. But that means you have two query languages in dotNet towards the database and towards your query results. One way to cope with this is to use LINQ all the way, as there also is LINQ to SQL, meaning LINQ to SQL Server not LINQ to the sql language. Indeed using LINQ in .Net is a bit like using xbase commands in VFP, as it's proprietary for .Net like xbase commands are proprietary in VFP. That also means it's not really the best strategy to not get stuck again, but like xbase commands LINQ can be quite convenient to use within in .Net, even if use other means to query data from the database via sql or entity framework.

It's about creating the right mix and use the right tool for a job, not about turning away from all old tools and only using new tools, even just within VFP. So don't be too strict about your xbase to sql conversion, if you move your userinterface later you'd be surprised you can't reuse the sql within another languages or platforms fontend, so you mainly have to categorize parts of you code querying the database and parts of your code working on data you already queried and wok on in memory to change display, going in the direction of the user interface. That's things you'll later do different anyway. That'll be datatables in a .Net application or JSON objects in a web/javascript frontend or html form elements in HTML or arrays in PHP.

Bye, Olaf.

 

[stanlyn]

Hi Mike,

Sorry to take so long to get back, but I'm back now...

For example, if you issue this statement:
SELECT ID, FirstName FROM Customers INTO CURSOR csrCusts WHERE ID < 100
This does not produce a new cursor. Instead, VFP re-opens the Customers table, with an alias of csrCusts. It applies a filter for IDs less than 100, and it applies a SET FIELDS so that only the two specified fields are visible.

How would I know that its a cursor or an alias?, as I've always known that a "into cursor" always produces a cursor and not an alias... So, why would this NOT produce a cursor and what way would? I understand a cursor is an table like object independent of an table allowing you to close the tables used in building the cursor without affecting the cursor's content. In Olaf's code above, his curResults can stand alone with the data inserted into it and this behavior is exactly my understanding of it. If I want to refresh the cursor to get the aac record, then reissue the "select ......... into cursor" command which replaces the old cursor with one of the same name and a new snapshot matching the where clause.

This is some of the confusion that I'm seeking to understand. Olaf maybe saying that if you're in xbase it does what Mike says and if you're in native SQL Server it produces the cursor. Is this what is being said? But what if I'm in VFP issuing vfp's sql commands? Will vfp's sql engine allow me to insert data into a cursor from the result of another vfp's sql select? This is where I'm reading that will not work because a vfp cursor is local and sql server cannot see it. I see that completely if using sql server, but I'm asking if vfp's sql engine running in vfp on dbfs is cursor aware all from the vfp side and not from native sql.

Thanks, Stanley

 

[stanlyn]

Hi Mike,

be to do with so-called "filtered" cursors. Let me explain. In a limited number of cases, a SELECT .... INTO CURSOR statement doesn't really produce a cursor. It simply opens a second instance of the original table, with SET FILTER and/or SET FIELDS applied to it.

So, is it the "filtered cursor" that creates the alias version instead of a real standalone cursor? as this is what I'm hearing you say.

Thanks, Stanley

 

[stanlyn]

Hi Olaf,

After reading your 1st message's #2 comment, you are saying that from vfp's sql dialect, both cursors and tables can be used and referenced as long as they are part of the same datasession, and will work, while if using native sql server it will not.

The difficulty in explaining this and reading other's comments is about the perspective, vfp's sql vs sql's sql.

Thanks, Stanley

 

[stanlyn]

Hi Mike,

You say "All references I've read says it DOES NOT WORK".

It now appears that the references were coming from a sql's sql perspective instead of vfp's sql perspective.

Thanks, Stanley

 

[stanlyn]

Hi Mike,

VFP is no more or less a "SQL database" than what you call a "real database".

To me, a VFP database contains dbc and dbf files, while a "real SQL database" contains mdf and ldf files. Do I have this wrong?

Thanks, Stanley

 

[stanlyn]

Hi Mike,

The solution is to add a NOFILTER clause to the original SELECT.

Now you just took the magic away, or would I be using the where clause to effectivly filter the results into a new cursor? I see not using a "where" clause as being equal to "NOFILTER" therfore fetching all data and not scalable, so what is the best practice to limit the data?

Thanks, Stanley

 

[Olaf Doschke]

Ok, you still ahve a lot of misunderstandings. Let's see, if we can lift some of the more heavy ones.

The filter cursor is a very special case, so we could put away with it, bu I'll get to it a bit later.

First more easier things:

Terminology
===========

An alias is just a synonym for a workarea, an alias name is a workarea name and so all cursors or tables open with an alias. An alias is not just a different name for the table or cursor, as the natural language term tells you. I assume in the first place workareas just had the workarea number they still have. In old foxpro versions there only were only 1 workareas you could also refer to with the letters a to o, or the number 1 to 15. I imagine workarea names came later and are called aliases. If you don't specify an ALIAS clause when USEing a table the alias name equals the file name. Most probably people therefore think this is not an alias, but it still is. Everything from table to view cursor and any other cursor is.

Seperation of SQL Server and VFP
================================

It should be clear without much thinking that SQL Server in the first place only has access to SQL Server databases and tables, not to any client concept. You can use VFP from very different languages with very different concepts for data loaded into it's process memory. In VFP we have cursor, in PHP we have arrays or objects/colllections. In .NET we have datasets. SQL Server is where the data of a cursor came from, but like sending out something physical somewhere else, after it's been sent it didn't stay there. If SQL Server has finished a query that yielded a result, which was transferred to the client requesting it, it has done with this result and memory it's freed and there is no temp table or alias name remaining in SQL Server, which could be used in further queries. If you want to use a result in an SQL Server T-SQL script you can query into a temp table or use a SQL Server cursor. As a side not that T-SQL cursor only has the same name, it's not what VFP creates as a query result cursor. Cursors are slow and bad in T-SQL. The best is to make a more complex query and have your temp result via subquery you integrate into the final result right away. In the end SQL Server forgets, what is not needed anymore after a skript ended. It persist only things stored in tables, it temporarily persists some things per connection and per session, but when a result was given to a client as a response, SQL Server is finished with this. SQL Server internally does not have the same concept of workareas and opened tables in it, that stay open, this is onl a VFP concept.

From this seperation it should be very clear why SQL Server can't join a VFP cursor. To make it even more clear a VFP cursor is in memory of the VFP client and in it's VFP process. This process alone is sperated from the SQL Server process. You may have in mind that you can use SQL Server via shared memory, but for one this is just one concept aside of pipes and tcp/ip and it's for transfer from SQL Server to client processes only. There are 3 things involved in general: The VFP SQL Passthrough layer is talking to the client side SQL ODBC Driver or OLEDB Provider. This is talking to SQL Server via network, pipes or shared memory. SQL SErver creates a query result and forwards it to the ODBC layer, which gives this data to the VFP layer, which converts the ODBC data types coming back into a VFP cursor. Only VFP can create a VFP cursor, it uses the single field data coming from ODBC to create a cursor from it.

For that reason a cursor you got from a SQLEXEC never can be used in further queries inside SQL Server, it doesn't have any handle on the VFP process or it's memory or it's workarea and cursor structures and no idea of it. The query result also can't be a filter.

So let's go back to the filter topic:

What is a filter cursor then? Mike has explained it quite extensively. To make it clear, from this point on we're doing SQL inside VFP towards DBFs again. In case a VFP query is only querying a single table and the where clause can be done with a single index, VFP can simply USE the table and SET FILTER TO the where clause expression. It does so, because this is simply faster, no data has to be copied. The steps needed are opening a DBF, which needs to be done anyway, then setting a FILTER. The table is opened with an alias in that case, so from that point you can't detect the table is opened. But you can of course simply detect a filter cursor by DBF("alias") being the file name of the table instead of a TMP file in the TEMP dir and SET("Filter") is set on the result alias.

There goes the disadvantage of such filter cursors: SQL (we are on SQL to DBFs here still) doesn't factor in a filter set o some alias. That's not foreseen in the VFP sql engine. It simply is that way, we have to swallow that pill.

But the solution is simple: You specify NOFILTER and VFP will not set filter instead of copying a query result into it's own cursor. You're simply done with this and won't need to detect, whether VFP created a filter cursor or not. Simply do so and you're finished with the error 1815. And forget about it's message being misleading. It says you need to SELECT INTO TABLE. Maybe once that was the only slution, you now can query INTO CURSOR ... NOFILTER. That's all to it.

The separation of VFP memory/process and SQL Server memory/process is one of the big reasons VFP cursors and SQL Server Tables don't mix. This has nothing to do with filters or NOFILTER. What is seperate is not only the memory and process, VFP does not use it' sql engine to read from mdf/ldf files as it does with DBF files, VFP requests data from the seperate SQL Process by sending in a SQL Query command, which in't at all executed by VFP, it's executed by the sql engine of the SQL Server, totally seperate in very many ways. It just looks to you SELECT * FROM Table is the same in VFP and SQL Server, but that's just because the VFP language dialects of VFP SQL and SQL Server SQL T-SQL (that's what it's called there). This does not execute inside VFP.

If you understand this seperations now, is it clear why you would can't join VFP cursors with SQL Server tables? Also how I explained filter cursor, is it clear, that you can't join them with DBFs, even inside VFP? Even if not, is it clear the resons for that are totally separate and different reasons compared to why you can't combine VFP cursors with remote data?

It shouldn't be too hard to unserstand how VFP and SQL Server are very seperate entities on very many levels, just communicating with each other. The reasoning you can't use a filter cursor in further queries is very weak one, in the end it's just the incapability of the VFP language to incorporate SET("FILTER") into a query on a DBF. Other prerequisites as memory access are not a problem at all. It's just a design decision, but you can overcome it alway using NOFILTER, so there's a very simple solution to that problem, isn't there?

Bye, Olaf.

 

[Olaf Doschke]

One more detail: NOFILTER is not telling VFP to drop the WHERE clause. NOFILTER is telling VFP to not create the result by a SET FILTER. VFP always will filter the data and create the same result with or without NOFILTER. A result of an sql query without NOFITLER may just not be usable in successive queries, if it is a filter cursor.

If You SCAN ENDSCAN through a filter cursor, which just is the USEd table with SET FILTER, you also only get at the records matching the WHERE clause, because SET FILTER does filter with the where clause expression.

SELECT * FROM some.dbf INTO CURSOR curFilter without any where clause and without NOFILTER also results in a filter cursor, as that can be done by USE some.dbf alias curFilter, and VFP then does so. Even though this doesn't make use of SET FILTER, it's called a filter cursor, as it's not a separate cursor, DBF("curFilter") will be some.dbf.

Bye, Olaf.

 

[Mike Lewis]

Stanley,

You're in danger of making too big a deal of this whole thing. It's really not that big an issue.

The bottom line is this. Just add NOFILTER to every SELECT ... INTO CURSOR statement, and everything will be fine.

Really, that's all you need to know.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Mike is right, you seldom will have use for a filter cursor, especially if you want to use it in successive queries. It only makes sense to skip the NOFILTER or READWRITE clause, when you know you will only scan..endscan through the result or display it in a grid or bind it to a control as is, if it's the final result.

It even makes sense to always query with READWRITE, as you can then index on cursor fields and add records, change data, etc.

Cursor you create via SQLExec always will be writable, too. In both cases this does not mean writing back to DBF or remote table, just changing the cursor data. But in case of SQLExec you can make a cursor updatable, meaning more than writable, updating the remote table data initially came from via TABLEUPDATE. That's a seperate topic, but the final thought about Mikes final thought is, you can also always add the READWRITE clause without any disadvantage.

One thing can make a filter cursor still worse than a dedicated result cursor is result data is sparsely scattered in the dbf file. Even a fully optimizable filter is skipping in the dbf file any time you skip through the result and a reqadwrite cursor will pull togehter all result data into a local temp file, which after being created is the fastest thing to work on anyway.

You should never query full tables or worse multiple jonins of data leading to huge amounts of result data, of which you display the first 10 in a grid. What is huge depends on network bandwidth, number of users, frequence of queries etc. but normally a user will need data for one product, one customer, the last month, there always is some limiting factor you can make use of in your queries.

This is of essence even more so, once you go for remote databases.

Bye, Olaf.

 

[Dan Freeman]

While I agree with both Mike and Olaf that filtered cursors are just not that big of a deal, I disagree that the best way around it is to always use NOFILTER.

The reason VFP sometimes presents a filtered view of the data is that the query engine determines that it is the fastest route to the result set. If you turn that off at all times by using nofilter, you are turning off the fastest route to the data. In other words, you are permanently settling for second best in performance.

I think I've encountered a need for nofilter twice since the clause was added to the language. We got along just fine without it for many years.

If you bump into a situation where you need nofilter, either the compiler will tell you (or the runtime engine will tell you) or the result set will tell you. Until that time, don't over-think it lest you risk paralysis by analysis.

 

[stanlyn]

Hi Olaf,

There goes the disadvantage of such filter cursors: SQL (we are on SQL to DBFs here still) doesn't factor in a filter set o some alias. That's not foreseen in the VFP sql engine. It simply is that way, we have to swallow that pill.

Not sure what you mean here.

So Mike is saying to always use a NoFilter or the readwrite clause? If so, then selecting records without a where clause is basically useless, unless we want all the data which is what NoFilter would do, right?

Also Olaf, thanks for the detailed insight on how vfp and sql works, however there is still confusion on this statement:

The separation of VFP memory/process and SQL Server memory/process is one of the big reasons VFP cursors and SQL Server Tables don't mix. This has nothing to do with filters or NOFILTER. What is seperate is not only the memory and process, VFP does not use it' sql engine to read from mdf/ldf files as it does with DBF files, VFP requests data from the seperate SQL Process by sending in a SQL Query command, which in't at all executed by VFP, it's executed by the sql engine of the SQL Server, totally seperate in very many ways. It just looks to you SELECT * FROM Table is the same in VFP and SQL Server, but that's just because the VFP language dialects of VFP SQL and SQL Server SQL T-SQL (that's what it's called there).

I have been asking questions from the perspective of doing SQL inside VFP towards DBFs, so in the quote above, why couldn't vfp do it all, especially if there is no sql server installed and we are using vfp's sql pointing to dbfs? The whole nature of my questions is regarding this "SQL inside VFP towards DBFs" scenerio. I understand the vfp vs mssql server differences and why they cannot be intermixed, but if vfp is using its internal sql engine, and vfp knows about cursors, tables, views, joins, unions, sql, then why can't it (vfp) bring them all together.

Now, I would interpret "select f1, f2, f3 from vendors where f1='Olaf' into cursor curResults nofilter" as nullifying the where clause. If the nofilter clause does NOT negate the where clause, then great and I'll continue using the readwrite clause.

Sorry for reading too much into this, but behavioral questions remained, thus I kept digging...

And yes Dan, I AM paralyzed by this analysis, as this is eaxctly where I'm at.

Thanks again for taking the time to spell out the details,
Stanley

 

[Mike Lewis]

While I agree with both Mike and Olaf that filtered cursors are just not that big of a deal, I disagree that the best way around it is to always use NOFILTER.

Dan, what you are saying is perfectly correct. However, I was trying to make this as simple as possible for Stanley. He is clearly having some difficulty in grasping the issue, and I wanted to cut through the verbiage, and give him the simplest rule that he can apply.

If forced to, I can modify my previous statement. Stanley, I said you can solve the problem by always putting NOFILTER on every SELECT ... INTO CURSOR. Let me modify that to say that you should put NOFILTER on every SELECT ... INTO CURSOR whenever you plan to use the cursor as an input to another SELECT. That will answer Dan's valid point about performance, and still give you a simple rule that you can live by.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

> selecting records without a where clause is basically useless, unless we want all the data which is what NoFilter would do, right?
No

> I would interpret "select f1, f2, f3 from vendors where f1='Olaf' into cursor curResults nofilter" as nullifying the where clause.
NO

Again, I already told you in my seperate answer NOFILTER has not the meaning of nullifying a where clause.

I said

NOFILTER is not telling VFP to drop the WHERE clause. NOFILTER is telling VFP to not create the result by a SET FILTER. VFP always will filter the data and create the same result with or without NOFILTER. A result of an sql query without NOFILTER may just not be usable in successive queries, if it is a filter cursor.

Please - plaese - read the explanations of filter cursors. NOFILTER is just telling foxpro to NOT create a FILTER cursor. That doesn't mean not to filter. A non filter cursor is filtered to the same result records as a filter cursor. The filtering just is done different. In a filter cursor VFP simply uses SET FILTER, it doesn't copy the result records into a separate new cursor, instead it just filters the table itself under the given alias name. A non filter cursor is a real cursor that is not filtered via SET FILTER, but it IS filtered in respect of all data, as it only contains result records, which are copied into it.

>why couldn't vfp do it all, especially if there is no sql server installed and we are using vfp's sql pointing to dbfs?
Well, there only is the Error 1815 problem, and it's solved. It's solved, there is no need to complain about this weakness. You just put a damn NOFILTER or READWRITE clause to all your selects INTO CURSORS, where you want to use that cursor in successive sql and that's it. No problem remains then. Not.

And the only difference between NOFILTER and READWRITE is, NOFILTER cursors are readonly. They could also have called it READ and READWRITE.

Just let me explain one last thing about the error 1815: It happens, if you use a filter cursor inside a query. And it's not telling you you need to add INTO TABLE to this erroring query. It is complaining about a certain cursor alias name you used as source data in the query and the query createing that cursor, the PREVIOUS query is what needs to be modified by either INTO TABLE or INTO CURSOR NOFILTER.

If you really still don't get how a filter cursor differs from a non filter cursor, forget about it. I can't explain it any further. Then just make use of the clauses you have NOFILTER or READWRITE, and you're done.

You can join views with tables and cursors, whatever you like. If views are involved, it still would be more permorfmant to parameterize them and then use them with prepared parameters instead of selecting from them, but that's a totally different story....

Bye, Olaf.

 

[Olaf Doschke]

Olaf>There goes the disadvantage of such filter cursors: SQL (we are on SQL to DBFs here still) doesn't factor in a filter set o some alias. That's not foreseen in the VFP sql engine. It simply is that way, we have to swallow that pill.

Stanley>Not sure what you mean here.

In short: There is error 1815, live with it.

Easy way to circumvent it: use NOFILTER or READWRITE in all queries INTO CURSOR. That'll also catch the queries done previously to the failing one.

Bye, Olaf.