I googled over the weekend and found the real problem. I tried Tracy's terrific suggestions before posting this answer just to see if it would change anything.
The answer I found is the PostgreSQL ODBC driver does NOT support async mode!!! ARGH!! One of the primary reasons I dumped MySQL for PostgreSQL in my VFP app was due to the numerous bugs in the MySQL ODBC driver!!! This answer was not easy to find out as I never saw a direct NO answer by anyone to the question that was occasionally posted on their mailing list about support for async. Ultimately, I downloaded the source code and found out where the ODBC function to return the status of support for it simply returns the value which translates to not supported, plus a comment in the source code stating this, and a test to query the odbc driver interface for support to confirm I was understanding it right. I would have liked to see the driver inform VFP that async is not supported when you make the call to set that mode, instead of it returning true, but I'm not sure who's fault that is. Now my theory about Tracy's code and how it relates to VFP,as I find this part interesting. Knowing the driver cannot support async mode, it's no surprise that the code hangs/blocks at the SQLEXEC line as it must wait for the PostgreSQL server to finish executing the select statement. Since I'm pulling all records on a large table, this is simply a slow process. Once it completes, the first SQLEXEC returns 0 and we hit the DO LOOP, and I notice repeated SQLEXEC calls are executing in a linear fashion which seems to be dictated by the # of records returned. So my theory is this is kind of like partial async support, ie, the request to the server is synchrounous, but once the odbc driver is ready to return data to VFP, it can then act in an asnyc manner to a certain extent as the vfp cursor is populated, providing a small degree of control to the developer to update the UI somehow that the thing hasn't locked up entirely. Also forgot to mention that I tried my original code (with RECCOUNT() call) hitting an SQL Server 2000 backend, and it worked as I had expected it should, thus the call to RECCOUNT() has no bearing on this from what I witnessed. I was very bummed out about this discovery and was almost ready to write off using PostgreSQL with VFP (as my app has a lot of lengthy reporting summations on huge recordsets), until after extensive googling and reading the PostgreSQL docs, I found a way to talk to PostgreSQL directly using Fox and the dlls that were installed with the odbc driver which does support Async processing! Wahoo! I'll post more about this once I'm done experimenting. If I can get a decent solution working, I'll post the code up for the vfp community to share. Thanks for your efforts Tracy to assist, they are much appreciated. At 11:15 AM 12/01/2008, you wrote: >On Mon, December 1, 2008 10:58 am, Tracy Pearson wrote: > > Are you sure the hang-up was the first SQLEXEC call, or the RECCOUNT > > call? Did you see a 0 return on the end of ?"First Call:" then the record > > count after a bit? Or you get a 1 after it? > > > > Tracy > > >Good question...hope he sees this and responds! > > > [excessive quoting removed by server] _______________________________________________ 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.
