1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. Donation with Paypal!!!

    Go to your paypal account and send directly donation to [email protected]

    1 month - 10 $ - Standart VIP

    6 months - 20 $- Standart VIP

    1 year - 30 $- Standart VIP

    2 years - 50 $- Standart VIP

    Gold member for life - 150 $- Standart VIP

    High Vip (Standart VIP include) group please send PM or email to [email protected] for info

    After Donation please send email to [email protected]

  3. Donation Ways 2020


    Paysend
  4. Telegram

  5. Delphifan Magazine
Dismiss Notice

Donation with Paypal!!!

Go to your paypal account and send directly donation to [email protected]

1 month - 10 $ - Standart VIP

6 months - 20 $- Standart VIP

1 year - 30 $- Standart VIP

2 years - 50 $- Standart VIP

Gold member for life - 150 $- Standart VIP

High Vip (Standart VIP include) group please send PM or email to [email protected] for info

After Donation please send email to [email protected]

Dismiss Notice
For open hidden message no need write thanks, thank etc. Enough is click to like button on right side of thread.

MySQL and Delphi

Discussion in 'Delphi Programming' started by AdminDF, Feb 1, 2014.

  1. AdminDF
    Online

    AdminDFAdminDF is a Verified Member Delphifan Staff Member DF Staff

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Problem/Question/Abstract:[/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]I've always wanted a better way to interface with my favorite (I would argue the best) database and Delphi - and after much searching I bring you an excellent and sensible way to do it.[/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Answer:[/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]This is based on the Open source MySQL connector "Objects".[/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]To start with you'll need Delphi of course - I believe this will work with 5 pro and higher although I've only used it with 7. Also I assume you have or have access to a properly configured and working MySQL server. If you don't there are plenty of excellent tutorials available.  I'll also assume you have moderate knowledge of Delphi and can navigate, add buttons and all that basic stuff.[/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]First also need a copy of the actual connector objects. Which can be found at:[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]http://sourceforge.net/projects/directsql/[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]http://prdownloads.sourceforge.net/directsql/DirectMysqlObjects.zip?download [/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]If your interested there is also a demo which shows off its capabilities which can be found:[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]http://prdownloads.sourceforge.net/directsql/DemoObjectsWin.zip?download [/font]


    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]To use the MySQL objects - simply unzip the contents of the zip you just downloaded into {Delphi}/lib/ folder.[/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Now to use them all you need to do is add a couple of things to the uses of your interface:[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]uMySqlVio, uMysqlCT, uMysqlClient, uMysqlHelpers [/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Its as easy as that![/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]I suggest trying to compile your application after adding the "uses" for the first time to make sure Delphi can find them okay. Now I'll run through a quick tutorial on how to use the library to get you started.[/font]


    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Connection Example[/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]First add "MySQLClient: TMySQLClient;" to your main form's public. This will make the actual client that you'll do all the work with.[/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Also add "MySQLResult: TMysqlResult;" to your main form's public as well. This will create an 'instance' of the MySQL result type for "catching" queries and other stuff that you'll want a result from.[/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Great, so now the naming is done we'll add some code to actually connect to your database. Add this code to your form's OnCreate procedure (double click on your form):[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]MySQLClient := TMySQLClient.Create;[/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Next add the following to the OnDestroy procedure:[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]MySQLClient.Free;[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]if MySQLResult <> nil then[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]MySQLResult.Free;[/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Okay, now make a new button on your form and give it the caption of "Connect". To get it to actually connect first we'll need to define a few things like the host and user and stuff. You can either "hard code" the values  (or read from your own config files / registry or whatever) or use edit boxes and such. Since this is a simple tutorial I'll leave the reading in values from cfg files up to you and use the easiest which is just a few edit boxes on your form.[/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Add 5 edit boxes to your form and 3 check boxes. For quick reference label (leave the names the same)  them[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Edit1 - Host[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Edit2 - Port[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Edit3 - User[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Edit4 - Password[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Edit5 - Db[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Check1 - Use named pipes[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Check2 - Use SSL[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Check3 - Compress[/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Now add the following code to your OnClick procedure for the connect button you added earlier:[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]MySQLClient.Host := Edit1.Text;[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]MySQLClient.port := StrToInt(Edit2.text);[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]MySQLClient.user := Edit3.text;[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]MySQLClient.password := Edit4.text;[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]MySQLClient.Db := Edit5.Text;[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]MySQLClient.UseNamedPipe := CheckBox1.Checked;[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]MySQLClient.UseSSL := CheckBox2.Checked;[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]MySQLClient.Compress := CheckBox3.Checked;[/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]if MySQLClient.Connect then ShowMessage('connected ok!')[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]else ShowMessage('Somthing went wrong!");[/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Or instead of the big chunk of text you can use:[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]if FMysql.Connect(Edit1.Text, Edit3.Text, Edit4.Text, 'db', StrToInt(Edit2.text), '', false, 0) then ShowMessage('connected ok!')[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]else ShowMessage('Somthing went wrong!");[/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]But its much easier for the second to go wrong, and harder to figure out what went wrong.[/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Now run your program, fill in the edit boxes and see if it works![/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]I'm assuming it did - so lets move along, almost there.[/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Now we come to actually making the query - which is just like a query in any other language or interface. When you make a new query you need to assign the result to MySQLResult and use MySQLClient to run the query. There are 3 parameters, the query, if you want it to save the result, a boolean to store if it executed ok:[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]MySQLResult := MySQLClient.Query('SELECT * FROM users WHERE username=''username'' and password=''pass''', True, OK); [/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif](just a quick note for the inexperienced - often you'll need to use a ' in a sql query (ie - select * from user where name = 'joe bloggs') - which also signifies to Delphi that the string you are making has ended and will make it "freak out"(TM) - so there thankfully is an easy way around it, which is simply to wherever you need a ' in a string put two together - so select * from user where name = 'joe bloggs' would be 'select * from user where name = ''joe bloggs''')[/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Now that you have the result of the query there's all sorts of things you can do with it. Have a go at browsing through the list of properties and procedures available. But to get you started - to get a field by using its name:[/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]MySQLResult.FieldValueByName('username'); [/font]

    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]Hint for a login type script - [/font]
    [font=Arial, Tahoma, Helvetica, FreeSans, sans-serif]if (MySQLResult.FieldValueByName('username') <> 'dummy_username') or (MySQLResult.FieldValueByName('password') <> 'dummy_pass') then ...[/font]
     

Share This Page