RE: Async not working, what am I doing wrong?
From the VFP 9 help file: Speeding up Data Retrieval Controlling Cursor Fetching If you want to fetch the entire cursor, you can issue the GOTO BOTTOM command, or any command requiring access to the entire data set. I read this as RECCOUNT() being one of the other commands as it would require access to the entire data set. Try this: nHandle = SQLSTRINGCONNECT(lcCStr) ?Set Asycn Mode ??SQLSETPROP(nHandle,Asynchronous,.T.) lcSQL = SELECT * FROM email ORDER BY sent ?lcSQL ?First Call: ??SQLEXEC(nHandle,lcSQL,csrTest) + DO WHILE SQLEXEC(nHandle) = 0 ??. =INKEY(.25) ENDDO -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Steve Ellenoff Sent: Friday, November 28, 2008 12:07 PM To: [EMAIL PROTECTED] Subject: Async not working, what am I doing wrong? I cannot seem to get an asynchronous connection to work. Am I missing something obvious? Despite the call to SQLSETPROP(Asychrounous,.T.), the SQLEXEC() locks until the entire result set is returned, despite finishing with a 0 return value. I've tried in VFP8 9SP1 SP2 hitting a PostgreSQL database, with no luck. The code below hits a table with 16,000 records. The first SQLEXEC call takes about 5 minutes to finish executing and returns a 0. The next call returns a 1 and finishes immediately. Reccount() shows 16,000 for both calls. What's going on? Could it be a bug specific to PostgreSQL and/or their ODBC driver? CLOSE ALL CLEAR LOCAL lcDriver, lcServer, lcUserId, lcPassword, lcCStr, lnOption, lcOptionString, lcSQL lcDriver = PostgreSQL ANSI lcServer = VXP-POSTGRES lcUserId = xxx lcPassword = xxx lcDatabase = test lnOption = 0 lcOptionString = ;OPTION= + ALLTRIM(STR(lnOption)) lcCStr = DRIVER=+lcDriver+;SERVER=+lcServer+;DATABASE=+lcDatabase+;UID=+lcUse rID+;PWD=+lcPassword+;B9=0 lcCStr = lcCStr + lcOptionString ?lcCStr nHandle = SQLSTRINGCONNECT(lcCStr) ?Set Asycn Mode ??SQLSETPROP(nHandle,Asynchronous,.T.) lcSQL = SELECT * FROM email ORDER BY sent ?lcSQL ?First Call: ??SQLEXEC(nHandle,lcSQL,csrTest) ?Rec Count: ?RECCOUNT(csrTest) ?Second Call: ??SQLEXEC(nHandle,lcSQL,csrTest) ?Rec Count: ??RECCOUNT(csrTest) [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com 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.
RE: Async not working, what am I doing wrong?
On Mon, December 1, 2008 10:21 am, Tracy Pearson wrote: From the VFP 9 help file: Speeding up Data Retrieval Controlling Cursor Fetching If you want to fetch the entire cursor, you can issue the GOTO BOTTOM command, or any command requiring access to the entire data set. I read this as RECCOUNT() being one of the other commands as it would require access to the entire data set. Try this: nHandle = SQLSTRINGCONNECT(lcCStr) ?Set Asycn Mode ??SQLSETPROP(nHandle,Asynchronous,.T.) lcSQL = SELECT * FROM email ORDER BY sent ?lcSQL ?First Call: ??SQLEXEC(nHandle,lcSQL,csrTest) + DO WHILE SQLEXEC(nHandle) = 0 ??. =INKEY(.25) ENDDO So by choosing this approach it won't hang on the first SQLEXEC call? ___ Post Messages to: ProFox@leafe.com 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.
RE: Async not working, what am I doing wrong?
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 -Original Message- From: MB Software Solutions General Account Sent: Monday, December 01, 2008 11:02 AM On Mon, December 1, 2008 10:21 am, Tracy Pearson wrote: From the VFP 9 help file: Speeding up Data Retrieval Controlling Cursor Fetching If you want to fetch the entire cursor, you can issue the GOTO BOTTOM command, or any command requiring access to the entire data set. I read this as RECCOUNT() being one of the other commands as it would require access to the entire data set. Try this: nHandle = SQLSTRINGCONNECT(lcCStr) ?Set Asycn Mode ??SQLSETPROP(nHandle,Asynchronous,.T.) lcSQL = SELECT * FROM email ORDER BY sent ?lcSQL ?First Call: ??SQLEXEC(nHandle,lcSQL,csrTest) + DO WHILE SQLEXEC(nHandle) = 0 ??. =INKEY(.25) ENDDO So by choosing this approach it won't hang on the first SQLEXEC call? ___ Post Messages to: ProFox@leafe.com 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.
RE: Async not working, what am I doing wrong?
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! ___ Post Messages to: ProFox@leafe.com 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.
RE: Async not working, what am I doing wrong?
Yes it hung on the first SQLEXEC call. Using your code had some interesting results which I will theorize about in a follow up post, since I have found more info over the weekend. Your code acted as follows: First Call is printed.. Pause for (a long time) Then it prints 0 and goes through the loop and prints a bunch of until it finally finishes, this process goes fairly quick depending on the # of records returned. At 11:01 AM 12/01/2008, 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 -Original Message- From: MB Software Solutions General Account Sent: Monday, December 01, 2008 11:02 AM On Mon, December 1, 2008 10:21 am, Tracy Pearson wrote: From the VFP 9 help file: Speeding up Data Retrieval Controlling Cursor Fetching If you want to fetch the entire cursor, you can issue the GOTO BOTTOM command, or any command requiring access to the entire data set. I read this as RECCOUNT() being one of the other commands as it would require access to the entire data set. Try this: nHandle = SQLSTRINGCONNECT(lcCStr) ?Set Asycn Mode ??SQLSETPROP(nHandle,Asynchronous,.T.) lcSQL = SELECT * FROM email ORDER BY sent ?lcSQL ?First Call: ??SQLEXEC(nHandle,lcSQL,csrTest) + DO WHILE SQLEXEC(nHandle) = 0 ??. =INKEY(.25) ENDDO So by choosing this approach it won't hang on the first SQLEXEC call? [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com 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.
RE: Async not working, what am I doing wrong?
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: ProFox@leafe.com 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.
RE: Async not working, what am I doing wrong?
On Mon, December 1, 2008 12:45 pm, Steve Ellenoff wrote: 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!!! fwiw, I'm not experiencing problems with MySQL...which version were you using? I think I was still using the 3.23 ODBC driver version. Also, was it easy for you to switch the backend as you did? ___ Post Messages to: ProFox@leafe.com 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.
Re: Async not working, what am I doing wrong?
On Mon, Dec 1, 2008 at 11:45 AM, Steve Ellenoff [EMAIL PROTECTED] wrote: bla bal bal 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. - There lies the design problem. You are thinking in the local fox data mindset and have not considered that other engines do not work well for working with large data sets. Why not request the first reduction of data be done before the entire table is downloaded? Secondly do you need every column of data? The narrower your return table is the FASTER it comes back. I have found that if you use a go bott go top you will really raise the speed of your data returning. -- Stephen Russell Sr. Production Systems Programmer Mimeo.com Memphis TN 901.246-0159 ___ Post Messages to: ProFox@leafe.com 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.
RE: Async not working, what am I doing wrong?
I'm glad you found out why. It sounds like you are getting some asynchronous results from the connection. My curiosity is towards the initial connection. If you put a Select * From Table where 1=0 query first, to see if there is a pause, then get the large data and see the pause? -Original Message- From: Steve Ellenoff Sent: Monday, December 01, 2008 12:25 PM ___ Post Messages to: ProFox@leafe.com 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.
RE: Async not working, what am I doing wrong?
fwiw, I'm not experiencing problems with MySQL...which version were you using? I think I was still using the 3.23 ODBC driver version. I posted about it here about 3/4-1 year ago, I was using a 3.xx based version. The two things I recall from memory was that Medium Text fields were treated as C(255) in VFP, and many of the Numeric fields did not respect the sizing, ie, N(5,2) came back in VFP as N(13,11) or something crazy like that. I also tried drivers as old as MySQL5 would allow ( I think it went back to 3.11) and tried every new driver as they came out. During this time I think I tried 4 new revisions of the driver, none of which ever helped. The ODBC 5 series driver was also very problematic, far worse than the 3.xx series, but it was also flagged as beta (might still be?). I quickly lost confidence in MySQL which is unfortunate as the server itself is probably just fine. Kevin turned me on to PostgreSQL and it has not exhibited any oddities with the ODBC driver ( at least not yet ), with the exception of the async thing, which is not a bug, it's just missing functionality. I don't know if MySQL supports asycn either. Also, was it easy for you to switch the backend as you did? The upside of this whole mess was that it forced me to write an abstraction layer to the backend so that the switch over process was painless, and I can easily switch back by just changing a property (can also switch to VFP). The trick of course as you already know, is to never write your SQL using anything but strict ANSI compliant code, and trying to pick an approach that is backend agnostic (not always so easy). I also ended up writing code so as to allow me to write 1 SQL statement and let the class convert the syntax to the appropriate backend, rather than having to code specifically for any particular backend, ie, I tell it I want the equivalent of fox's NVL() for example, and it subs in the appropriate equivalent syntax for the selected backend. This makes reading any given SQL statement a bit yuckier, but I like the benefit of not having to maintain various SQL statements for any particular backend. Of course, I've really only focused on PostgreSQL support at this time (and even then, only covering functions as I find I need them).. so if I was going to do it right, it'd probably be a lot more work to flush out the functionality for other backends, which I don't have the time nor desire to do at the moment, but maybe someday.. :) ___ Post Messages to: ProFox@leafe.com 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.
Re: Async not working, what am I doing wrong?
Hi Stephen- Thanks for your reply. Yes, I'm very aware that select * is a foxpro mentality, I was only using it to test out the async functionality. In fact, my app runs just fine w/o async for the very reason that I only pull columns I need and I always use a fairly restrictive where clause. I've read all the how to design a c/s fox books, although I don't pretend to be perfect at eliminating the fox mindset yet either.. :) The issue for me is that for some of the existing reports, there's a ton of tabulation going on which means that often the entire recordset or a large part of it is included. In places where I can't keep the work on the backend, I wanted to implement asyc handling so the user wouldn't think the report locked up. I wasn't looking to try to make the report run faster via async, just to provide ui assistance to the user. Some of these reports are slow in VFP also.. Now, I don't mean 10 minutes slow (usually between 30 seconds and 3 minutes). At 02:00 PM 12/01/2008, Stephen Russell wrote: There lies the design problem. You are thinking in the local fox data mindset and have not considered that other engines do not work well for working with large data sets. Why not request the first reduction of data be done before the entire table is downloaded? What do you mean by this, can you give an example? Secondly do you need every column of data? The narrower your return table is the FASTER it comes back. I have found that if you use a go bott go top you will really raise the speed of your data returning. I don't understand what you mean by go bott, go top in relation to pulling from the backend. ___ Post Messages to: ProFox@leafe.com 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.
Re: Async not working, what am I doing wrong?
On Mon, Dec 1, 2008 at 12:40 PM, Steve Ellenoff [EMAIL PROTECTED] wrote: Hi Stephen- Thanks for your reply. Yes, I'm very aware that select * is a foxpro mentality, I was only using it to test out the async functionality. In fact, my app runs just fine w/o async for the very reason that I only pull columns I need and I always use a fairly restrictive where clause. I've read all the how to design a c/s fox books, although I don't pretend to be perfect at eliminating the fox mindset yet either.. :) The issue for me is that for some of the existing reports, there's a ton of tabulation going on which means that often the entire recordset or a large part of it is included. In places where I can't keep the work on the backend, I wanted to implement asyc handling so the user wouldn't think the report locked up. I wasn't looking to try to make the report run faster via async, just to provide ui assistance to the user. Some of these reports are slow in VFP also.. Now, I don't mean 10 minutes slow (usually between 30 seconds and 3 minutes). -- Sounds like a tough overhaul to push that functionality back on the server. Maybe you can't change your process because of all the other reporting requirements? Best of luck. -- Stephen Russell Sr. Production Systems Programmer Mimeo.com Memphis TN 901.246-0159 ___ Post Messages to: ProFox@leafe.com 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.
Async not working, what am I doing wrong?
I cannot seem to get an asynchronous connection to work. Am I missing something obvious? Despite the call to SQLSETPROP(Asychrounous,.T.), the SQLEXEC() locks until the entire result set is returned, despite finishing with a 0 return value. I've tried in VFP8 9SP1 SP2 hitting a PostgreSQL database, with no luck. The code below hits a table with 16,000 records. The first SQLEXEC call takes about 5 minutes to finish executing and returns a 0. The next call returns a 1 and finishes immediately. Reccount() shows 16,000 for both calls. What's going on? Could it be a bug specific to PostgreSQL and/or their ODBC driver? CLOSE ALL CLEAR LOCAL lcDriver, lcServer, lcUserId, lcPassword, lcCStr, lnOption, lcOptionString, lcSQL lcDriver = PostgreSQL ANSI lcServer = VXP-POSTGRES lcUserId = xxx lcPassword = xxx lcDatabase = test lnOption = 0 lcOptionString = ;OPTION= + ALLTRIM(STR(lnOption)) lcCStr = DRIVER=+lcDriver+;SERVER=+lcServer+;DATABASE=+lcDatabase+;UID=+lcUserID+;PWD=+lcPassword+;B9=0 lcCStr = lcCStr + lcOptionString ?lcCStr nHandle = SQLSTRINGCONNECT(lcCStr) ?Set Asycn Mode ??SQLSETPROP(nHandle,Asynchronous,.T.) lcSQL = SELECT * FROM email ORDER BY sent ?lcSQL ?First Call: ??SQLEXEC(nHandle,lcSQL,csrTest) ?Rec Count: ?RECCOUNT(csrTest) ?Second Call: ??SQLEXEC(nHandle,lcSQL,csrTest) ?Rec Count: ??RECCOUNT(csrTest) ___ Post Messages to: ProFox@leafe.com 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.
