Maximum number of open Cursors?

[montypython1]

Greetings,

Is there a maximum number of cursors that I can have open at the same time?

I am doing some lengthy calculations and decided to break up the SQL into smaller bites, but after my third cursor, I receive an error message saying:
"csr01 must be created with SELECT ... INTO TABLE"

Each of my SQL statements are sequential (please see below):

***************************************************************************
SELECT st_src01.*, st_fi.* ;
FROM st_src01 ;
LEFT OUTER JOIN st_fi ;
ON stocknum = st_fi.stocknumfi ;
ORDER BY cust_state, cust_cnty, cust_city, taxcode1, prefix, typesale_a, stocknum, refnum ;
INTO CURSOR csr01_fi_a

SELECT dealer, cust_state, cust_cnty, cust_city, taxcode1, prefix, typesale_a, stocknum, refnum, ;
jrnl_date, rev_date, namelastfi, taxcode2, taxcode3, sale_amt, ;
IIF(sale_amt>0, NVL(CapCostRed,000000.00), NVL(CapCostRed,000000.00)*-1) AS CapCostRed_calc, ;
IIF(sale_amt>0, NVL(trd_acv,000000.00), NVL(trd_acv,000000.00)*-1) AS Trd_ACV_calc, ;
IIF(sale_amt>0, NVL(trd_oa,000000.00), NVL(trd_oa,000000.00)*-1) AS Trd_OA_calc, ;
IIF(sale_amt>0, NVL(trd_ua,000000.00), NVL(trd_ua,000000.00)*-1) AS Trd_UA_calc, ;
txbl_fe01 + txbl_fe02 + txbl_fe03 + txbl_fe04 + txbl_fe05 AS Amkt_FE, ;
txbl_be01 + txbl_be02 + txbl_be03 + txbl_be04 + txbl_be05 AS Amkt_BE, ;
IIF(sale_amt>0, NVL(docfee,000000.00), NVL(docfee,000000.00)*-1) AS DocFee_calc, ;
IIF(sale_amt>0, NVL(acqfee,000000.00), NVL(acqfee,000000.00)*-1) AS AcqFee_calc, ;
taxamt1, taxamt2, taxamt3, ct, taxrate ;
FROM csr01_fi_a ;
LEFT OUTER JOIN countytaxrates ;
ON cust_cnty = countyname ;
INTO CURSOR csr01_fi_b

SELECT * , ;
IIF(sale_amt>0, NVL(Amkt_FE,000000.00), NVL(Amkt_FE,000000.00)*-1) AS Amkt_FE_calc, ;
IIF(sale_amt>0, NVL(Amkt_BE,000000.00), NVL(Amkt_BE,000000.00)*-1) AS Amkt_BE_calc, ;
Trd_ACV_calc + Trd_OA_calc - Trd_UA_calc AS MaxDeduct ;
FROM csr01_fi_b ;
INTO CURSOR csr01_fi_c

SELECT dealer, cust_state, cust_cnty, cust_city, taxcode1, prefix, typesale_a, stocknum, refnum, ;
jrnl_date, rev_date, namelastfi, taxcode2, taxcode3, sale_amt, ;
CapCostRed_calc, ;
Trd_ACV_calc, ;
Trd_OA_calc, ;
Trd_UA_calc, ;
MaxDeduct, ;
Amkt_FE_calc, ;
Amkt_BE_calc, ;
DocFee_calc, ;
AcqFee_calc, ;
taxamt1, taxamt2, taxamt3, ct, taxrate ;
FROM csr01_fi_c ;
INTO CURSOR csr01_fi_d

BROWSE

*****************************************************************
** the following generates the error: "csr01_fi_d must be created with SELECT ... INTO TABLE"
SELECT * ;
FROM csr01_fi_d ;
INTO CURSOR csrSrc01_fi_e
*****************************************************************
***************************************************************************

Any help is greatly appreciated.

Dave Higgins

 

[bborissov]

Why do you need so many SELECTs?
Can this be done with one or two selects?
Help for this type of error is extremely helpful
From there:

You have tried to use a repackaged cursor as a source for another SELECT statement. 

Some cursors cannot be used in successive queries. 

Use the INTO TABLE clause of the SELECT - SQL statement instead.

What is repackaged cursor? I don't know.
What type of cursors exactly can't be used in successive queries? Again I don't know.

Try this and see if this happens SELECT ... INTO CURSOR csr01_fi_d NOFILTER


Borislav Borissov

 

[alvechurchdata]

Is there a maximum number of cursors that I can have open at the same time?

Yes - but it's 65,535 so that's not the problem<g>.

Borislav's suggestion of NOFILTER will force Fox to create the cursor as a temporary disk file so that it'll be available as the input for another query.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[montypython1]

Hi Borislav and Geoff,

Thanks for the suggestions. I will give them a try tomorrow and let you know how they turned out.

Thanks,
Dave

 

[montypython1]

Hi Borislav and Geoff,

I tested your suggestions and they worked perfectly.
Thank you again.

Dave Higgins

 

[Olaf Doschke]

Hi Geoff,

I can only SELECT 32767 and the help topic about capacities/limitations says you can only have up to 255 files open due to file handle limitations.

The thing is really NOFILTER. As certain SQL Selects may be done by just setting a filter to the underlying table, foxpro does so and the selects are faster, but you then can't use that cursor in further selects. That's a filter cursor. You find that DBF("cursoralias") will be equal to DBF("sourcetable"), if the select generated a filter rather than a "real" or "repackaged" cursor. If you have such a repackaged cursor it has it's own file.

Create cursor curSource (cText c(3))
Insert into curSource values ("aaa")
Insert into curSource values ("bbb")
Insert into curSource values ("ccc")
Insert into curSource values ("zzz")
Select * from curSource where cText<"m" into cursor curRepackaged
? "Filter?",Dbf("curRepackaged") = Dbf("curSource")
Select curSource
Index On cText tag xText
Select * from curSource where cText<"m" into cursor curFilter
? "Filter?", Dbf("curFilter") = Dbf("curSource")

It's the same select, but with an index on cText it can be optimized to a filter, therefore you will have the results F for curRepackaged and T for curFilter. Select * from curFilter will result in an error, Select * from curRepackaged not. And if you set NOFILTER in the select into curFilter, it will be no filter and you can use it in subsequent selects.

Bye, Olaf.

 

[alvechurchdata]

I can only SELECT 32767 and the help topic about capacities/limitations says you can only have up to 255 files open due to file handle limitations

VFP 9 help does say 65,535 but when I tried USE ... IN 32768 Fox failed. It being the end of the day I've just written a loop:

for n = 1 to 32767
  c=alltrim(str(n,0))
  use customer again in 0 alias x&c
next

and after 15 minutes it had opened the Customer table 32767 times in 32767 different work areas - X1 to X32767. I couldn't call it "usable" though. I've written this post whilst waiting for it to open two Browse windows on areas 1 and 32767 and there's still no action.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Olaf Doschke]

Hi Geoff,

Well, then the help is wrong both in 65,535 and 255.

Maybe it's no limitation but obviously at least a recommendation to have less tables open ;-).

Hmm, maybe the other 32768 workareas have negative numbers )? Well, SELECT -1 doesn't work, USE foxuser in -1 also not.

At least 4 or 5 tables are no problem whatsoever, Dave. The error is very misleading. Selecting into a table is of course a solution to have a source for the next SQL select, but a simple NOFILTER is enough. It's a good sign, that you have a filter cursor, because that means VFP can optimize it very good. And it's sometimes easier to write two or three selects than one, that does it in a single step.

Bye, Olaf.

 

[montypython1]

Hi Olaf and Geoff,

Sorry for the delay in responding to your posts.

Based on your tests, it appears that VFP will give me more than enough open cursors for my needs (I can't imagine ever needing more than 10 open cursors at once).

And you are correct about breaking a complex SQL into multiple small SQL's .... It is easier to debug (at least for me). The NOFILTER suggestion is working perfectly .... Thank you.

Dave

 

[alvechurchdata]

I can't imagine ever needing more than 10 open cursors at once

Once upon a time a very long time ago Fox only had ten work areas. They were labelled "A" to "J" and even to this day the command "SELECT B" will select work area 2 for you.

Just a bit of history - please don't start writing code like that.

Thank you

You're very welcome. Glad it's working for you

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Mike Lewis]

Geoff,

Once upon a time a very long time ago Fox only had ten work areas.

You had ten work areas? You don't know you were born.

When I was your age, my lad, we only had primary and secondary. P and S we used to call them. None of this arty A to J business. But we got the job done .. oh, yes.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Olaf Doschke]

Hi all,

Talking about history...

640 kB ought to be enough for anybody

Well, I found an entry in Wikiquote, a german wiki encyclopedia especially about quotes, which states it's wrong, he never said that... He also disclaimed this himself, see here:

http://www.usnews.com/usnews/biztech/gatesivu.htm

I began with commodore VC-20 with 16 kb, if I don't count my programmable calculator. At that times I couldn't imagine the development of hard- and software lying ahead.

On the other side predictions of artificial intelligence have been too optimistic and are still...

Bill Gates said another thing (checked):

Microsoft has had clear competitors in the past. It's a good thing we have museums to document that.

October 1st 2004 in a speech at the museum for computer history.



Bye, Olaf.

 

[alvechurchdata]

When I was your age, my lad, we only had primary and secondary.

Just fired up FoxBase and reminded myself we did have A..J. Perhaps you're thinking of dBase II.

Can't check dBase - the 8" CP/M disks won't fit in the drive.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[alvechurchdata]

I began with commodore VC-20 with 16 kb

I think I began with 12 kb. It was in a GEC 2050 which controlled Peterhead power station. The memory was all ferrite core with every bit wound by hand. Twenty years' later I'm sitting in front of a Tosh laptop with a gigabyte of RAM.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Mike Lewis]

Geoff,

Perhaps you're thinking of dBase II.

Yes, I was thinking of dBASE II. It was dBASE III that introduced ten work areas, circa 1982.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Mike Lewis]

Olaf,

I don't know why people think it was Bill Gates who said that. I remember reading at the time that it was an interview with someone from IBM. It's just not the sort of thing Bill would have said.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[alvechurchdata]

I remember reading at the time that it was an interview with someone from IBM.

Sounds plausible - 640k was a hardware limit and IBM (used to) make the hardware.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Dan Freeman]

Guys, don't forget the 32k workareas are multiplied by 32k datasessions. ;-)

 

[alvechurchdata]

32k workareas are multiplied by 32k datasessions

OK, I'll bite. We're off shopping for the day so I've wrapped this:

for n = 1 to 32767
  c=alltrim(str(n,0))
  use customer again in 0 alias x&c
next

in an outer loop which creates 32767 data session objects and we'll see if it's still running this evening.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[alvechurchdata]

we'll see if it's still running this evening.

After six hours' shopping I came back to find that Fox had managed to open 32767 work areas in 8 data sessions and was chugging along opening areas in session 9. It's now taking seconds to open each new work area so I've put it out of its misery.

Geoff Franklin

http://www.alvechurchdata.co.uk