Mysql – Delphi 2010 and MySQL

delphidelphi-2010MySQL

Does anyone know of a simple tuturial for setting up and using Delphi 2010 with MySQL?

Best Answer

Use TAdoConnection and TAdoQuery:

implementation
uses adodb;
{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
var
  mAdoConnection: TAdoConnection;
  mAdoQuery: TAdoQuery;
begin
  mAdoConnection := TAdoConnection.Create(application);
  mAdoConnection.ConnectionString := 'Mode=ReadWrite;Extended Properties="DRIVER=MySQL ODBC 3.51 Driver;DATABASE=mydatabase;SERVER=myserver;UID=myusername;PASSWORD=mypassword;PORT=;OPTION=3;STMT=;"';
  mAdoConnection.LoginPrompt := False;
  mAdoConnection.Open;
  mAdoQuery:=TADOQuery.Create(application);

  with mAdoQuery do begin
    Connection:=mADOConnection;
    CursorType := ctStatic;
    ParamCheck := False;
    SQL.Add('SELECT * FROM SYSSET');
  end;
  mAdoQuery.Open;
  showmessage(mAdoQuery.fields[0].AsString);
  mAdoQuery.free;
  mAdoConnection.Free;


end;

Note: change the "My....." to your server, database, username and password. This also assumes you have the MySql ODBC driver installed on the computer.

Related Solutions

Mysql – How to quickly rename a MySQL database (change schema name)

For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:

RENAME TABLE old_db.table TO new_db.table;

You will need to adjust the permissions after that.

For scripting in a shell, you can use either of the following:

mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \ 
    do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done

for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;

Notes:

There is no space between the option -p and the password. If your database has no password, remove the -u username -ppassword part.
If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema error). If that is the case, use a traditional way to clone a database and then drop the old one:

mysqldump old_db | mysql new_db
If you have stored procedures, you can copy them afterwards:

mysqldump -R old_db | mysql new_db

Mysql – How to set a default value for a MySQL Datetime column

IMPORTANT EDIT: It is now possible to achieve this with DATETIME fields since MySQL 5.6.5, take a look at the other post below...

Previous versions can't do that with DATETIME...

But you can do it with TIMESTAMP:

mysql> create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql> desc test;
+-------+-------------+------+-----+-------------------+-------+
| Field | Type        | Null | Key | Default           | Extra |
+-------+-------------+------+-----+-------------------+-------+
| str   | varchar(32) | YES  |     | NULL              |       | 
| ts    | timestamp   | NO   |     | CURRENT_TIMESTAMP |       | 
+-------+-------------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)

mysql> insert into test (str) values ("demo");
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+---------------------+
| str  | ts                  |
+------+---------------------+
| demo | 2008-10-03 22:59:52 | 
+------+---------------------+
1 row in set (0.00 sec)

mysql>

CAVEAT: IF you define a column with CURRENT_TIMESTAMP ON as default, you will need to ALWAYS specify a value for this column or the value will automatically reset itself to "now()" on update. This means that if you do not want the value to change, your UPDATE statement must contain "[your column name] = [your column name]" (or some other value) or the value will become "now()". Weird, but true. I am using 5.5.56-MariaDB

Best Answer

Related Solutions

Mysql – How to quickly rename a MySQL database (change schema name)

Mysql – How to set a default value for a MySQL Datetime column

Related Topic