ResultType

Previous  Top  Next

See Also Properties Methods Events Example
Unit
MySQLDataset

Applies to
TMySQLDataset and TMySQLQuery components

Declaration
property ResultType: TMySQLResultOptions = (rtLocal,rtIncremental,rtUnidirectional);

Description
The ResultType property is used to determine how the result will be fetched from the MySQL server:

rtLocal (default):
Using this method your dataset will (when activated):
·parse your sql (SQL property) replacing macros  
·insert any master-detail and filter conditions giving a Final SQL  
·sent your Final SQL query to the mysql server,  
·the mysql server will execute this query fetch the records (result set) for you  
·and start sending the records to the dataset one at a time.  

After this, the dataset will have all records available in local memory and full bidirectional scrolling and full updating is allowed. The dataset will optionally execute "show columns from ..." statements depending on your (doRetrieveFieldValues) and the number of tables contained in your query to determine things like field default values, etc. The RecordCount property will reflect the total number of records in the dataset's result.

rtIncremental:
This method will:
·parse your sql (SQL property) replacing macros  
·insert any master-detail and filter conditions giving a Final SQL  
·sent your Final SQL query to the mysql server,  
·the mysql server will execute this query fetch the records (result set) for you  
·and start sending the records to the dataset one at a time when prompted  
   
Thus the result from the mysql server is not fetch in total and then freed, it is fetched from the mysql server one at a time as you scroll through the dataset using methods like Next, LocateRecord, Last, etc. As an example: if you've got an application with a DBGrid (with a display length of 20 records at a time) with a MySQLDataset connected to a table with 1M records using the rtIncremental method, the dataset will initially only have to fetch 20 records. As you scroll through the dataset with a DBNavigator's Next button and you reach the 20th record displayed, if you click Next again the next record is only then fetched at that precise moment from the MySQL server and so on. If you click Last, all records will have to be fetched.

NOTE: You still will have full bidirectional scrolling and full update ability on the records you can see (have already fetched). Once all records have been fetched (EOF True) the result will be freed, all records are now in memory and the dataset will behave as if it was opened with rtLocal.

rtUnidirectional:
This method will:
·parse your sql (SQL property) replacing macros  
·insert any master-detail and filter conditions giving a Final SQL  
·sent your Final SQL query to the mysql server,  
·the mysql server will execute this query fetch the records (result set) for you  
·and start sending the records to the dataset one at a time when prompted  
   
Thus the result from the mysql server is not fetch in total and then freed, it is fetched from the mysql server one at a time as you scroll through the dataset using methods Next and Last only. The difference to rtIncremental though is that this method does not keep your previously fetched records in memory, it frees the previous record as the next is being fetched. Thus it only has one valid record in the client memory at a time, which makes your application very memory efficient. You can literally scroll through a million records containing 1 Gb of data without needing more memory than you will need for any one of those records. This method obviously only supports forward movement through the records, i.e. Next, Last methods. Except for this difference it behaves exactly like rtIncremental concerning record count etc.

Advantages of rtIncremental:
·You don't have to wait for large result sets or result sets with large blob fields to completely populate your dataset  
   
Disadvantages of rtIncremental or rtUnidirectional (until the last record was fetched):
·Your connection to your MySQL server must be live until the last record was fetched, if it was to disconnect your result will contain only records fetched until that moment.  
·Your RecordCount will always equal = Records actually fetched + 1  
·No "show columns from ..." statements are executed, thus you columns will not show the default values you setup in your database tables on the MySQL server when you insert a new record. Use OnNewRecord to correct this problem.  
·ShareConnection property = True and rtIncremental are mutually exclusive, thus this dataset must have its own thread to the mysql server as to not block your TMySQLServer or other TMySQLDatasets using the TMySQLServer of this dataset while this dataset has not finished collecting all the records.  
·Another disadvantage is that if you update a record while all the records have not been fetched your dataset will have to stop the fetching, sent the update and re-fetch all the records up to the point where you are. You can prevent this though using a combination of rtIncremental and CachedUpdates.  

To allow you to have full updating capability while all the records have not been fecthed from the server, the dataset will handle any updates you do, i.e. inserts, deletes and updates, in the following order until a condition is true:
1.Firstly if you have CachedUpdates enabled, the updating sql is just added to the cached statements and thus executed later, i.e. with the close or refresh of this dataset. Your record you are updating is in memory already and thus the changes will reflect immediately to the end-user.  
2.If caching is not enabled, but ThreadedUpdates is, then the dataset will sent the updating sql to the server using another thread and thus connection.  
3.If neither caching nor threadedupdates are enabled, then the dataset will load the rest of the dataset's records in the background, send the updating SQL and then refetch all the data upto the record which you just updated.  

You may interrupt or handle the updating sql on your own as well: before checking for ThreadedUpdates (step 2). The dataset passes the SQL to the OnExecSQL event handler. You may thus handle the SQL update yourself and set the Continue parameter to False.

Which method should I use?
Easy, if you have a mysql table which you will access using a MySQLDataset and it has many records or large blob fields or is accessed via a slow internet link you may consider using rtIncremental. If you use rtLocal you will probably have to wait at least a few seconds/minutes/hours for your dataset to open in such a scenario. Another case for rtIncremental is if you wish to pull a large read-only result from the mysql server, although you will still be able to update the dataset, you will have to consider that such an update will be subject to steps 1, 2 or 3 mentioned above. If you have no need to move backwards in your dataset and need no more than one record at a time then rtUnidirectional is just the method for you.