Briefcase Applications Explained - The Database Server
(Page 2 of 4 )
As explained above, the first thing we need to do is fill our dataset with data from the server. To do this, we need to use a tdataset component. A tdataset component is any component that is a descendant of the tdataset class. An example is TADOQuery, a table or ADOCommand.
For this application we will be using ADO components to interact with the server and XML file. More specifically, we will be using the ADOquery and ADOConnection components. Unfortunately, as with all Microsoft technologies, these components were designed to work only with other Microsoft technologies, and this is where our problem is. Because we cannot afford the scandalously expensive MS SQL Database Server, which is perfectly suitable to use with the ADO components, we need to look for an alternative.
Now, this article is not about database servers, and we will only be interacting with a database server twice, first to download data and second to upload changes. But I feel it is worthwhile to explain the server side of things to give you a complete picture of how this interaction takes place. Our choice of database server is the powerful and free MySQL database server. You can download it from http://www.mysql.org.
To connect to the MySQL server is not as straightforward as just dropping a database component on a form and then connecting to a database. You need a program called MySQL Connector/ODBC to connect to a MySQL database from your Delphi application. This program acts as a conduit between your Delphi application and the server. You can download this application from the MySQL website; I'm using version 3.51.
After you've installed the database server, run the mysql-connector.exe file and follow the instructions. The next thing you'll want to do is install a mysql client. A mysql client enables you to create, delete, view and update databases that is served by MYSQL. I use phpmyadmin which is available from www.phpmyadmin.net. It is very easy to install this and if you do encounter any problems doing so, then take a look at the website for some guidance.
Please note that you do not have to download this program; in fact, you do not have to download any mysql database admin applications at all. You could simply use the command line interface of the server to create and administer your databases. It is just that a mysql client makes it faster and easier to handle databases.
To summarize:
There are three things we need to do to use MySQL Server with our Delphi application:
- Download and install MYSQL Server from http://www.mysql.org.
- Download and install MySQL ODBC Connector, also from the above link.
- Finally, download and install php admin from http://www.phpmyadmin.net (optional).
Once you have them all installed, we need to create the database. Open up your phpmyadmin program and create a database called briefcase.
Here's what my mysql client looks like:
Once you've done that you should now be in the "briefcase" database. Click on the "sql" link and add the following SQL:
CREATE TABLE `contacts` (
`cID` int(4) NOT NULL auto_increment,
`name` varchar(100) NOT NULL default '',
`surname` varchar(100) NOT NULL default '',
`phone_no` varchar(100) NOT NULL default '',
PRIMARY KEY (`cID`)
) TYPE=MyISAM AUTO_INCREMENT=7 ;
INSERT INTO `contacts` VALUES (1, 'edit1.text', 'edit2.text',
'edit3.textt');
INSERT INTO `contacts` VALUES (2, 'Jack', 'Doe', '555-5467');
INSERT INTO `contacts` VALUES (3, 'Loozah', 'Dean', '555-000-
000');
INSERT INTO `contacts` VALUES (4, 'Don', 'Johnson', '555-6789');
INSERT INTO `contacts` VALUES (5, 'James', 'Bond', '555-879-
909');
INSERT INTO `contacts` VALUES (6, 'edit1.text', 'edit2.text',
'edit3.text');
Here's what the screen should look like:
Click on the go button and you should now have a table called contacts in your database. Now we need to create a connection between the database and the ODBC connector. Go to the control panel, click on "Performance and maintenance" then click on "Administration tools." You should now see the screen below:
Now, click on the link that says "Data Source/ODBC." You should now have the following screen:
Click on "add." This will start the process of creating a new database connection. You should see the following window:
This window lists all the drivers on your system. Find the ODBC connector driver and select "finish." You should see the following window:
Try to fill in the details of the screen as they are on the example above. There are four important fields that might differ from my details:
- Server - If you are on a network, your host name might be different so check with whoever controls the network.
- User - Usually the "root."
- Password - Usually empty, but you can create a username and password in the phpmyadmin interface.
- Database - Just select from the drop down list.
Finally, click the "test" button and check that everything is okay. If not, the problem is most likely to be the server, password or user name, so check them all. Then click "OK" and you should see the following screen with your new connection listed:
That's it for connecting the database to the connector. Now all the work that we are going to do with our Delphi application is going to be done through the connector to the database.
Please bear in mind that the most likely scenario in which you will use the briefcase application is where the database server will be located remotely. I've done the server aspect of the article for debugging purposes and also so that you know how the application interacts with the server.
Next: Building the Application >>
More Delphi-Kylix Articles
More By Leidago
