Delphi Unicode MySQL ADO Tutorial for Beginners
This tutorial shows you how to make a Delphi application which manages a unicode database.
We will use the new unicode enabled Delphi 2009 and the ADO components.
Steps:
- Install MySQL Express 2008
- Create a new database from code
- Create tables with unicode fields from code
- Fill table with unicode data from code
- Query the database and display the results in a TDBGrid and TMemo
Install MySQL Express 2008
Before starting writing any code first you have to install an SQL database server to which we can connect to from the Delphi application.
For example SQL Server 2008 R2 Express.
Create a new database from code
Start Delphi and create a new project.
Drop on the form a TADOConnection, TADOQuery from the dbGo components tab and a TDataSource from the Data Acces tab.
Double click on TADOConnection, the connection string dialog will show up. We have to specify a connection string that will be used for making a connection to the SQL server.
Select "Use connection string" and click "Build".
From the "Provider" tab select "SQL Server Native Client".
Switch to the "Connection" tab.
1. For "Select or enter a server name", select "(local)".
2. Select "Use Windows NT integrated security"
3. "Select the database": "master"
4. To test this connection click on "Test Connection". If everything is ok, a dialog will come up saying that the connection was successful.
Click OK to close the dialogs, we can now connect to the SQL server.
Back to the form, select the TADOQuery component and in the object inspector set it's Connection to ADOConnection1.
Select the TDataSource component and set it's DataSet to ADOQuery1.
We can now start coding. Put a TButton on the form and write the following code to it's OnClick event (download and see the tutorial for a complete example):
procedure TFormUnicodeMySQLTutorial.ButtonCreateDatabaseClick(Sender: TObject);
begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('CREATE DATABASE "' + EditDatabaseName.Text + '"');
ADOQuery1.SQL.Add('ON');
ADOQuery1.SQL.Add('(');
ADOQuery1.SQL.Add('NAME = "' + EditDatabaseName.Text + '_dat",');
ADOQuery1.SQL.Add('FILENAME = "' + EditDatabaseFilename.Text + '",');
ADOQuery1.SQL.Add('SIZE = ' + IntToStr(SpinEditDatabaseSize.Value));
ADOQuery1.SQL.Add(')');
ADOQuery1.ExecSQL;
end;
Create tables with unicode fields from code
procedure TFormUnicodeMySQLTutorial.ButtonCreateTableClick(Sender: TObject);
begin
ADOQuery1.SQL.Clear;
//* Set to use our new database (specified by name)
ADOQuery1.SQL.Add('USE ' + EditDatabaseName.Text + ';');
//* Create a new table
ADOQuery1.SQL.Add('CREATE TABLE "' + EditTableName.Text + '"');
ADOQuery1.SQL.Add('(');
//* On the form only 2 columns are specified but we create 4 columns
//* Add a unique identifier named "Id" with auto-increment
ADOQuery1.SQL.Add('Id INT IDENTITY(1,1) NOT NULL,');
//* Add the 2 specified columns as unicode type, max. 50 characters
ADOQuery1.SQL.Add(Edit1ColumnName.Text + ' NVARCHAR(50),');
ADOQuery1.SQL.Add(Edit2ColumnName.Text + ' NVARCHAR(50),');
//* Add a date & time column (type DATETIME)
ADOQuery1.SQL.Add('Date DATETIME,');
//* Id column as primary key
ADOQuery1.SQL.Add('PRIMARY KEY (Id)');
ADOQuery1.SQL.Add(')');
ADOQuery1.ExecSql;
end;
Fill table with unicode data from code
procedure TFormUnicodeMySQLTutorial.ButtonAddRecordClick(Sender: TObject);
begin
ADOQuery1.SQL.Clear;
//* Set to use our new database (specified by name)
ADOQuery1.SQL.Add('USE ' + EditDatabaseName.Text + ';');
//* Insert a new record
ADOQuery1.SQL.Add('INSERT INTO ' + EditTableName.Text);
ADOQuery1.SQL.Add('(' + Edit1ColumnName.Text + ', ' + Edit2ColumnName.Text + ', Date) VALUES (:a, :b, :c)');
//* Specify the values and their data type ftWideString = unicode
ADOQuery1.Parameters.ParamByName('a').DataType := ftWideString;
ADOQuery1.Parameters.ParamByName('a').Value := EditFirstName.Text;
ADOQuery1.Parameters.ParamByName('b').DataType := ftWideString;
ADOQuery1.Parameters.ParamByName('b').Value := EditLastName.Text;
ADOQuery1.Parameters.ParamByName('c').DataType := ftDateTime;
ADOQuery1.Parameters.ParamByName('c').Value := Now;
ADOQuery1.ExecSQL;
end;
Note that we specify the DataType as ftWideString that will put unicode strings in the database!
Query the database and display the results in a TDBGrid and TMemo
Drop on the form a TDBGrid component from the "Data Controls" tab, and a TMemo form the "Standard" tab.
Drop another TButton on the form and in the OnClick event handler write:
procedure TFormUnicodeMySQLTutorial.ButtonGetDataSetClick(Sender: TObject);
var
s: string;
begin
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
//* Set to use our new database (specified by name)
ADOQuery1.SQL.Add('USE ' + EditDatabaseName.Text + ';');
//* Get all data
ADOQuery1.SQL.Add('SELECT * FROM ' + EditTableName.Text);
ADOQuery1.Open;
//* This is how to get all the data resulted from the query and display it in a TMemo
Memo1.Clear;
DataSource1.DataSet.First;
while NOT DataSource1.DataSet.Eof do begin
s := '';
s := DataSource1.DataSet.FieldByName('Id').AsWideString;
s := s + ',' + DataSource1.DataSet.FieldByName(Edit1ColumnName.Text).AsWideString;
s := s + ',' + DataSource1.DataSet.FieldByName(Edit2ColumnName.Text).AsWideString;
s := s + ',' + DataSource1.DataSet.FieldByName('Date').AsWideString;
DataSource1.DataSet.Next;
Memo1.Lines.Append(s);
end;
end;
Note that after making the query (ADOQuery1.Open;) the TDBGrid will automatically show the results because it's connected to DataSource1.
Finally
For a complete solution download and see the complete tutorial:
If you have found this tutorial useful and want to award the author please consider buying a registration or a developer license for one of my programs.
See: www.3delite.hu
Or please click on one of the Google advertisements on the left or on the right! Thank you!
If you have any questions or enquiries please mail: 3delite@3delite.hu
Good coding! :)
3delite
|
