SQL Related Articles: MySQL and True page loading in ABC 2002-11-25 -- Dan Pressnell There is a little misconception about MySQL and Clarion's page loaded
browses. Some think that the problem is with the Clarion ODBC driver. But
it's not.
The problem is the lack of adequate "server side cursor" support in MySQL itself. Normally, a Clarion browse will send a SELECT statement to the server without any limits, except for a possible filter (WHERE clause). Then it FETCHES the rows that it needs to display in the browse. As you page up and down in the browse, then the appropriate rows are FETCHED and displayed. Clarion will FETCH the rows it needs, and only those rows. This works fine for many SQL systems, because the server will do some kind of caching on the entire result set, sending data to your application only when your app FETCHES the data. But MySQL doesn't work that way. It wants to send everything to your application on the SELECT statement, with no caching at the server. The caching appears to be taking place to Clarion, but it's not really happening. I've developed a browse class (ABC) that is derived from the ABC BrowseClass that you can use to get true page loaded browses from MySQL in your Clarion ABC applications. This class adds the LIMIT clause to the SELECT statements that are sent to MySQL for the browse. To use the class, it's a simple matter to select that class for your browses (overriding the default ABC BrowseClass). If this works as I expect it to, all your classes will have the same functionality that you are used to (icons, colors, filters, locators, etc.) but will be page loaded and very fast. One side effect is that scrolling will be a little slower, because rather than just FETCHING the next set of rows on PgDn, for example, a new query will have to be sent to the server. If you want to test the class, download it here: Class+Template for MySQL Follow the instructions in MySQLBr.inc. Let me know if there are any problems. Update 27-November-2002 -- Dan Pressnell I've updated the class to do page loaded MySQL browses. 1. The class now retrieves about half the records it did before for each page. On a slow connection, this might make a difference. 2. The list box is now refreshed when the window is resized. 3. Scrolling and paging are now more accurate when you have a non-unique key selected for the primary file, but you have "Additional sort fields" that make each record unique. Printed November 21, 2024, 11:01 am This article has been viewed/printed 35356 times. Google search has resulted in 98 hits on this article since January 25, 2004. |