Dismiss Notice

REGISTRATION IS AFTER ADMIN ACTIVATION

DONATIONS WITH PAYPAL CLICK TO BUTTON

6 MONTHS VIP - 20$; 1 YEAR VIP - 30$; 2 YEARS VIP - 50$; GOLD USER FOR LIFE VIP - 150$

DONATIONS WITH Bitcoin Address:3NRs3CK3fhXifrNYxHEZKpETDd9vNLMsMD

Dismiss Notice
The registration is approved by the Administrator. It takes about 1 day to approve your registration
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 Administrator 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

Laws
Our website, law, laws, copyright and aims to be respectful of individual rights. Our site, as defined in the law of 5651 serves as a provider of space. According to the law, contrary to the law of site management has no obligation to monitor content. Therefore, our site has adopted the principle of fit and remove. Works are subject to copyright laws and is shared in a manner that violated their legal rights, or professional associations, rights holders who, adsdelphi@gmail.com I can reach us at e-mail address. Complaints considered to be infringing on the examination of the content will be removed from our site.
Donate Help To Us and Be VIP
DONATIONS WITH PAYPAL CLICK TO BUTTON
6 MONTHS VIP - 20$; 1 YEAR VIP - 30$; 2 YEARS VIP - 50$; GOLD USER FOR LIFE VIP - 150$
Social Block