Solved

MySQLConnection  in Delphi

Posted on 2011-03-11
36
3,052 Views
Last Modified: 2012-05-11
How to connect to mysql in Delphi using  MySQLConnection componet
0
Comment
Question by:dabbiX
  • 12
  • 9
  • 7
  • +2
36 Comments
 
LVL 9

Expert Comment

by:Mahdi78
ID: 35108468
You mean MyDac component?
0
 
LVL 14

Expert Comment

by:systan
ID: 35108884
it depends on your Delphi version, it depends on your mySQL version.
You can try it by accessing dBExpress Component palette.
Drop down TSQLConnection
Drop down TSQLDataset
Drop down TSQLQuery
Get TDataSource in Data Access Component palette.

Select your TSQLConnection in TSQLConnection properties, select mySQL
Be sure the version of your mySQL server is the same with the mySQL driver used by Delphi.

And you can find many different dB connections in TSQLConnection Connection.
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35108966

You are not very clear.
Are you refering to MySQLConnection component from Devart or the one in Lazarus(Free Pascal)
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35109045

Using Devart
MySQL.bmp
0
 

Author Comment

by:dabbiX
ID: 35109659
I am using CodeGear 2009 with Delphi
dbexpress   TSQLConnection1
Key                                Value
DriverName
HostName
Database
User_Name
BlobSize
Password

Then I am using: xampp: Apache server and Mysql serer


0
 

Author Comment

by:dabbiX
ID: 35109683
Then I am using MySQL 5.1
0
 
LVL 9

Accepted Solution

by:
Mahdi78 earned 500 total points
ID: 35109876

Add the following value to property

ConnectionName: MySQLConnection

Params:
   HostName: your server
   Database: Your Database name
   User_Name : Username
   Password : Password

Then
   connected : True
0
 

Author Comment

by:dabbiX
ID: 35110376
It works
This was very god.

but a litle more problem

Then I use sqlTable and DataSource
Then I am traing to use DBGrid. and connected with DataSource
But I get this message.:  "Operation not allowed on a unidirectional dataset."
0
 
LVL 9

Expert Comment

by:Mahdi78
ID: 35110686
You will need then TSQLConnection, TDataSetProvider, TSQLTable, TClientDataSet, TDataSource and TDBGrid to connect
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35110696

Which one works?.

You cannot connect the SQLTable direct to a grid because it reads data only one way, first to last.

You have to use a ClientDataset and a provider

from Data Access tab
Drop a TDatasetProvider component on your form
Sete the DataSet property of the provider to TSQLTable

Drop a TClientDataset on the form, Set the ProviderName of the TClientDataset to the name of the Provider

Change the dataset of the TSQLTable to point to TClientDataset instead

0
 
LVL 9

Expert Comment

by:Mahdi78
ID: 35110832
Drop SQLTable1 with following property

   SQLConnection: SQLConnection1
   Tablename: Select table from your database

Drop DatasetProvider1 from Data Access pallet and select SqlTable1 in DataSet property

Drop ClientDataSet1 from Data Access pallet and select DatasetProvider1 in ProviderName property

Drop DataSource1 from Data Access pallet and select ClientDataSet1 in Dataset property

Drop DBGrid1 from Data controls pallet and select DataSource1 in DataSource property

Finally select true in SqlClientDataSet1 to activate it
0
 

Author Comment

by:dabbiX
ID: 35111064
This work fine.
Tanks to you gys

but litle more
I can not do enything in the DBGrid
In the sql server I make Name list. and put name in it.
But in the DBGrind I just see (MEMO)
Is it in the Database I make....
0
 
LVL 9

Expert Comment

by:Mahdi78
ID: 35111658
>> I can not do enything in the DBGrid

you mean, you can not update data?
0
 

Author Comment

by:dabbiX
ID: 35112052
I just see (memo) in the datafield.
it is nothing I can do in the DBGrid I just see (Memo)
I  can not update data?

0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35112127

TDBGrid does not handle memo fields well. You have to write your own GetText method.

Here is how to do that

http://delphi.about.com/library/weekly/aa030105a.htm
0
 
LVL 9

Expert Comment

by:Mahdi78
ID: 35112183
Oops this is other question, and you can not display memo data on DBGrid, except if you use OnSetText event of MemoField
I don't suggest you to use that event.

>> I  can not update data?

Read this is from Delphi help about TSQLTable component "you can only edit the data in an SQL table by explicitly creating an SQL UPDATE command or by connecting the table to a client dataset using a provider. Features that require buffering multiple records, such as filters or lookup fields, are not available";
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35112230

Alternatively, you can use the OnDrawColumn event of the grid

procedure TForm1.DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;
  DataCol: Integer; Column: TColumn; State: TGridDrawState);
begin
  if Column.Field.FieldName = 'MemoFieldName' then
  begin
    DBGrid1.Canvas.FillRect(Rect);
    DBGrid1.Canvas.TextRect(Rect, Rect.Left, Rect.Top, Column.Field.Value);
  end;
end;

or get a third party grid that handles memo fields
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:dabbiX
ID: 35116581
>> I  can not update data

I tray to insert og change text.

but it is not working.

I got this message
ClientDataSet1: Must apply updates before refreshing data.
I tray to update with this
SQLQuery1.Edit;
SQLQuery1.SQL.Add('update tabla_dabbi');
SQLQuery1.Post;



0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35116676

You need to call ApplyUpdates to save your changes

ClientDataset1.ApplyUpdates(0)
0
 

Author Comment

by:dabbiX
ID: 35117160
Thie work Tanks  to you
0
 

Author Comment

by:dabbiX
ID: 35120878
Can I use this  component to connect to mysql server  on  the internet.
 (DatasetProvider1,  SqlTable1 i, ClientDataSet1,DataSource1 )

Or shall I use another.

I am using CodeGer 2009
0
 
LVL 14

Expert Comment

by:systan
ID: 35121337
YES;
You connect like this;
SQLConnection1.SQLConnection.connect('ipaddress_or_hostname','theusername','thepassword');
eq.,
SQLConnection1.SQLConnection.connect('210.102.233.13','theusername','thepassword');
or as long as it is a valid domain name;
SQLConnection1.SQLConnection.connect('experts-exchange.com','theusername','thepassword');
0
 

Author Comment

by:dabbiX
ID: 35121423
I am not sure I have this componet.
SQLConnection1.SQLConnection.connect('');

But I have this  SQLConnection1.   no sql............
 
DriverName=MySQL
HostName= ip adress
Database=  db name
User_Name= xxxx
Password= xxxxxx
ServerCharSet=
BlobSize=-1
ErrorResourceFile=
LocaleCode=0000
Compressed=False
Encrypted=False
0
 
LVL 14

Expert Comment

by:systan
ID: 35122579
>>I am not sure I have this componet.
Yes, you have it in dBexpress component palette, including tSQLdataset, tSQLQuery, tSQLtable, tSQL_etc.,...others in dBexpress component palette.

Actually you understood it wrong, its not a component, it's a property of TSQLconnection

eq.,
is_component. is_property. is_function (parameters);
TSQLConnection1234_whatevernamed . SQLConnection . Connect();

Put that in you form Load, or what ever reasons when you want to connect to server.

About your post;
These are external connection settings, you've posted.  So, you misunderstood it, but you can override that inside Delphi form.
DriverName=MySQL
HostName= ip adress
Database=  db name
User_Name= xxxx
Password= xxxxxx
ServerCharSet=
BlobSize=-1
ErrorResourceFile=
LocaleCode=0000
Compressed=False
Encrypted=False
0
 
LVL 9

Expert Comment

by:Mahdi78
ID: 35122946
@dabbiX

I think you got full answer to your question designed above,
If you have other questions open another request
0
 

Author Comment

by:dabbiX
ID: 35123622
I try this but is it not working
I got this message
--------------------------
Debugger Exception Notification
Project ProjectSqlData.exe raised exception class TDBXError with message 'Can't connect to MySQL server on '93.95.225.179' (10060)'.
---------------------------
Break   Continue   Help  
---------------------------
I use this connection
------------------------------------------------
DriverName=MySQL
HostName=93.95.225.179
Database=21_delphi_no1
User_Name=21_delphi
Password=dabbi1
ServerCharSet=
BlobSize=-1
ErrorResourceFile=
LocaleCode=0000
Compressed=False
Encrypted=False




0
 
LVL 14

Expert Comment

by:systan
ID: 35124113
You connect like this;
SQLConnection1.SQLConnection.connect('ipaddress_or_hostname','theusername','thepassword');
0
 
LVL 14

Expert Comment

by:systan
ID: 35124120
Put that in you form Load, or what ever reasons when you want to connect to server.
0
 

Author Comment

by:dabbiX
ID: 35124297
I dont have componet with this property.

SQLConnection1.SQLConnection.connect('ipaddress_or_hostname','theusername','thepassword');

Tanks
0
 
LVL 14

Expert Comment

by:systan
ID: 35125628
>>I dont have componet with this property.
You meant, you don't have this property in your TSQLConnection

That's impossible,
try to drop TSQLConnection in the Form  from dBexpress component palette, without renaming it.
Double Click the Form, which loads the Form_Load;
Type;
SQLConnection1.   //when you type the period, it automates the property, next then try to type again SQLConnection.  //when you type the period, it automates the FUNCTION, next then try to type again
Connect

Almost every Delphi Versions have this; dBexpress component palette.

Or just try to put it in your Form_Load;
procedure TForm1.FormCreate(Sender: TObject);
begin
SQLConnection1.SQLConnection.connect('93.95.225.179','21_delphi','dabbi1');
...
...
//Be sure TSQLConnection named is SQLConnection1,  unless you have it already named it.
end;
0
 

Author Comment

by:dabbiX
ID: 35125669
OK I do that
I drop TSQLConnection in the Form  from dBexpress component palette
I make this...........
procedure TForm3.FormCreate(Sender: TObject);
begin
   SQLConnection1.SQLConnection.connect('93.95.225.179','21_delphi','dabbi1');
end;

But I got syntax error for this

.SQLConnection.connect

This property is not in my SQLConnection1.

What is the problem..
0
 
LVL 14

Expert Comment

by:systan
ID: 35126681
I doubt about the mySQLdriver version you have; because I have mySQLdriver for mySQL4.0.xx
0
 
LVL 14

Expert Comment

by:systan
ID: 35128094
Ok; Here's the alternative for that; My last post;
This is when your Form_Loads, or an event if you would like to Connect;

SQLConnection1 := TSQLConnection.Create(nil);
SQLConnection1.DriverName := 'MYSQL';
SQLConnection1.GetDriverFunc := 'getSQLDriverMYSQL';
SQLConnection1.LibraryName := 'dbexpmysql.dll';
SQLConnection1.VendorLib := 'LIBMYSQL.dll';
SQLConnection1.Params.Append('Database=__NAME_OF_DATABASE__);
SQLConnection1.Params.Append('User_Name=21_delphi');
SQLConnection1.Params.Append('Password=dabbi1');
SQLConnection1.Params.Append('HostName=93.95.225.179');
SQLConnection1.Open;
....
.... SQLCommands and Others, that you can add, delete, update.
....
.... Close before program exits.
//change the values if must,  careful.
0
 
LVL 14

Expert Comment

by:systan
ID: 35128138
Found an interesting link, refer to this if your dbExpress_mySQL_version is obsolete.
http://www.justsoftwaresolutions.co.uk/delphi/dbexpress_and_mysql_5.html

Use same version as your remote mySQL Server has.


Good Luck
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35399287
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Navigation is an important part of web design from a usability perspective. But it is often a pain when it comes to a developer’s perspective. By navigation, it often means menuing. This is less theory and more practical of how to get a specific gro…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now