MB Software Solutions General Account wrote:
> On Wed, February 25, 2009 7:05 am, Ricardo Aráoz wrote:
>
>> Hi all, I'm performing a script through odbc (sqlexec()) against an
>> SQLServer engine. Connection is ok, the script consists of 6 selects
>> with an output of 62000, 31000, 1350000, 20000, 27000, and 14 records.
>> Comes back from sqlexec with no errors and the cursors "mostly" ok, but
>> some records in the cursors come corrupted, this is aleatory, sometimes 5
>> cursors out of 6 are corrupted, sometimes only one. When I trace the
>> program and find a corrupted cursor, if I perform the query for that
>> cursor again it will come ok. I'm guessing this might be due to
>> multiselect queries and will be splitting the script into different
>> queries (one select each). Has anyone experienced this problem or can
>> think of a clue as to what's going on? Is it the SQL engine or VFP the
>> culprit?
>>
>> TIA
>>
>
>
> Sounds like a problem with the ODBC driver (although you'd think it would
> work fine since it's SQL Server...their own product). Your plan to split
> them up makes sense to me. It's the same amount of data regardless,
> whether you bring it back as one huge heap of 6 cursors, or 6 times of
> single cursors....I wouldn't expect there to be much performance
> difference?
>
Ok, found out what it was.
This is VFP 6.0 SP5.
I have about 30 files/cursors open in a form with private data session,
and some 20 more in the default session. My odbc call was generating 6
cursors, so I split it into 6 different queries, but corrupted cursors
remained. Then I had a hunch, got the third cursor which had 1,300,000
records and processed it first. Problem solved! So now I allow my system
to go on processing (I am tracing the program), it sends to an external
program a query (some 5 more selects), this program creates ascii files
with the results of the queries and my program reads those files and
places them in cursors with the following :
create cursor (lcAuxCrsr) &lcStructure
append from (ltInputFile) delimited with blank
And voilà, the first 6 cursors which where ok suddenly get corrupted
records.
So my conclusions so far are :
i) It is not a problem with ODBC as I had previously thought.
ii) It is a VFP6.0 cursors bug.
iii) This bug appears when you create huge cursors (don't know the
lower limit but 1,300,000 record will do it) after you have other
cursors created (have to check if it will only affect ODBC created cursors.
Now I created a cursor with "create cursor (.....)" filled it with some
data and perform the same test. The ODBC cursors got corrupted but not
the cursor I created. So :
iv) This bug only applies to cursors created from ODBC queries.
So how to fix it?
Created my 6 original cursors, for each one of them issued "select *
from MycursorNN into cursor OthercursorNN NOFILTER" where NN is the
number of the cursor. Then run the big query, everything ok, so run the
big query again and the 6 original cursors get corrupted as expected,
but the 6 copies of those cursors are ok.
So quick fix : a) split the queries b) after each query do a "select to
cursor NOFILTER" and close the original cursor, c) go on with my life.
Now I don't think M$ is going to care for a bug report on VFP6.0.
Could any of you guys test this in VFP9 and see how does it behave?
TIA
Ricardo
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
