TMySQLDataset [TMySQLTable/TMySQLQuery] components

Previous  Top  Next

See Also Properties Methods Events Example
Unit
MySQLDataset

Description
The TMySQLDataset/TMySQLTable/TMySQLQuery objects are the real workhorses of the MyComponents suite. It is important to note that all three these objects are in fact just different flavours of the same thing. The TMySQLDataset should be used by advanced developers, while the TMySQLTable and TMySQLQuery objects hides some of the more powerful properties and more closely resemble the TQuery and TTable Borland classes for beginners. This means all the properties discussed here for the TMySQLDataset are also available for both the TMySQLTable and TMySQLQuery objects, some of the properties are just not published for the Object Inspector to view, which in turn makes the TMySQLQuery and TMySQLTable very easy to use, because you connect them both up just as you would a normal TTable and TQuery in delphi.

Understanding the TMySQLDataset

As the TMySQLDataset was developed since 1999 to work with MySQL servers 3.21.x to 5.x, it is very flexible and compatible. But before you start, you should know a little bit of how the component and MySQL works in the background. Currently MySQL still does not support server side cursors, which means for a client application there is no way to uniquely reference a single record in a table for updating and deleting purposes. For this reason your table design in MySQL is all important.

If your mysql table was designed without a primary key:
The components makes provision by trying to update the record you edited using the old values of the record, thus the components will try to send a sql statement like this for you:
   update accounts set accountname='NewName' where accountname='OldName' and accountsurname='OldSurname'....;
thus it will try to use all your fields you used in your select to uniquely pinpoint the record for mysql to update it. This method does not work if your table has either blob or float columns, because a float value is dependent on a precision which is dependent on the CPU of the server and client machines, which means after a certain precision, the value becomes random. For this reason will the dataset not use float or blob columns to form a ad hoc key for you. Bottomline, design your database tables with primary keys lest you want to be sure of the SQL you sent and also be sure MySQL server will find the record.

If your mysql table was designed using a primary key or unique key:
In this case the components will use your key to update the correct record. Again, floats and blob columns are skipped if they form part of your primary key. NOTE: If and when you customize your SQL property with your own select statements and which the resulting records to be updateble, make sure you include the primary key columns or the dataset will not have the primary key values available to update the records with, in which case the dataset will behave as if it has no primary key.

Suggestion
As a rule of thumb, try to design all your mysql tables in advance using a primary key defined on an AUTOINC column in your table. This key is unique, small and always allows your end users to enter and update data. There after you can add addition unique keys to restrict data entry into your tables.

How do I log and debug my application's SQL?

This is easy enough, just use the OnExecSQL event available on all dataset components.

How does the TMySQLDataset form the sql statements automatically?

The dataset uses a feature called macros, to change default sql statements at will. In other words each dataset has four properties, SQL, SQLUpdate, SQLInsert, SQLDelete (note the TMySQLTable hide these from the Object Inspector, as it works only on one table, but it still has these properties and uses them in the background) populated with default sql statements containing macros. Here is an example from a default TMySQLDataset's SQL property which is used each time you open your dataset or select your records with:
   select [$FIELDS] from [$TABLENAME] [JOIN] [WHERE] [GROUPBY] [ORDER] [LIMIT]
In the square brackets you can see all the macros defined, for example $TABLENAME is a special macro which gets replaced by your dataset's TableName property, while ORDER is a generic macro which you can change in your code, ex:
   MySQLDataset1.MacroByName('ORDER').AsString := 'order by myfield1 desc';

In general you don't have to pay much mind to these if you just want to open a table, but if you write custom sql selects or sub selects, you may find the macros very useful. Also note that these SQL* properties of the datasets can take complete SQL scripts, i.e. multiple sql statements separate by semicolons. Macros are also used to connect master-detail datasets and you may define your own macros if you please. So changing the SQL property to:
   select [$FIELDS] from [$TABLENAME] [JOIN] [WHERE] [MySpecialWhereMacro] [GROUPBY] [ORDER] [LIMIT]
is very valid. If you don't specify a value for the macro, the statement will just be generated without it, i.e. with a space where the macro was. See Macros/Params for more details

MySQLDataset and threads

Each dataset by default will share the connection to the mysql server already setup by the TMySQLServer selected on the Server property. But each dataset can also have its own thread/connection to the MySQL Server. Some sql commands, see your MySQL Docs, are specific to a thread or connection. A simple example of such a command is "USE DBName;" which sets the database to use for the connection, and thus all datasets using this connection will the MySQL server automatically assume they work on the same database. But with the TMySQLDataset, you can specify not to share the connection, see ShareConnection, and thus free the dataset to work completely on it's own thread and connection to the MySQL Server, i.e. on a completely different database than the one set in the TMySQLServer.DatabaseName for example. This is critical especially if you have a multi threaded application where each dataset can not use the same thread/connection to the server except if they want to block (clash) each others' read and write operations to the mysql server. So typically if you use a TMySQLDataset in a TThread, you must set the ShareConnection to False, so that this dataset will have it's own pipe/connection to send and receive data from the mysql server when it runs, without clashing with other threads/connections in your application. Another such scenario is when you use ResultType=rtIncremental, which means the dataset has a "live" connection to the MySQL server and pulls records as and when needed. In such a state the dataset can not share the connection because the connection is filled up with it's data and thus this dataset will block all other datasets while it has not finished reading all the records. For this reason the dataset will automatically set ShareConnection=False the moment you pick rtIncremental, thereby allocating itself it's own connection to the server leaving other datasets to operate as per usual on the MySQL server via the same TMySQLServer.

Besides these unique features, the TMySQLDataset supports all your normal properties and methods. From CachedUpdates, Master/Detailing, Filters to ResultType and lots of Options to set on how you which the dataset to behave and present your data.

Designing your database and tables

One of the many nice features of the MyComponent suite is that it allows you to design your whole database structure right in the Delphi/Kylix IDE. All you have to do is specify the soAutoCreate Option for TMySQLServer as well as a DatabaseName, the soAutoCreate Option for the TMySQLDataset/Tables/Query as well as a TableName you wish to autocreate. Then design your columns using the FieldDefs property and your indexes using the IndexDefs property. For more information see, CreateDatabase, CreateTable, TMySQLServer's soDropIfExists and doCreateTemp, TMySQLDataset's doDropIfExists for more information.