MyDAC

Creating Database Objects

This tutorial describes how to create tables, stored procedures and other objects on MySQL.

  1. Requirements
  2. General information
  3. Creating tables
  4. Creating Stored Procedures
  5. Additional information

Requirements

In order to create database objects you have to connect to MySQL. This process is described in details in the tutorials "Connecting To MySQL" and "Connecting To MySQL Embedded".

General information

Database objects are created using Data Definition Language (DDL), which is a part of SQL. The DDL statements can be executed on server by an account that has the necessary privileges. There are two ways to create database objects. You can build DDL statements manually and execute them using a component like TMyCommand. Another way is to use visual database tools like dbForge Studio for MySQL. This topic covers the first way - using components.
There are two ways to execute DDL statements in components like TMyCommand: in design-time and in run-time. Both these ways are described below.

Note: The following assumes that you have the IDE running, you are currently focused on the form designer, and you have already set up the TMyConnection or TMyEmbConnection component on the form.

Creating tables

To create tables, the TMyCommand component is used here.

Design-time creation

Run-time creation

Same operations performed in runtime look as follows:

[Delphi]

var
  sql: TMyCommand;
begin
  sql := TMyCommand.Create(nil);
  try
    sql.Connection := con; // con is either TMyConnection or TMyEmbConnection already set up
    sql.SQL.Clear;
    sql.SQL.Add('CREATE TABLE DEPT (');
    sql.SQL.Add('DEPTNO INT PRIMARY KEY,');
    sql.SQL.Add('DNAME VARCHAR(14),');
    sql.SQL.Add('LOC VARCHAR(13)');
    sql.SQL.Add(');');
    sql.Execute;

    sql.SQL.Clear;
    sql.SQL.Add('CREATE TABLE EMP (');
    sql.SQL.Add('EMPNO INT PRIMARY KEY AUTO_INCREMENT,');
    sql.SQL.Add('ENAME VARCHAR(10),');
    sql.SQL.Add('JOB VARCHAR(9),');
    sql.SQL.Add('MGR INT,');
    sql.SQL.Add('HIREDATE DATETIME,');
    sql.SQL.Add('SAL FLOAT,');
    sql.SQL.Add('COMM FLOAT,');
    sql.SQL.Add('DEPTNO INT');
    sql.SQL.Add(');');
    sql.Execute;
  finally
    sql.Free;
  end;
end;

[C++Builder]

{
  TMyCommand* sql = new TMyCommand(NULL);
  try
  {
    sql->Connection = con; // con is either TMyConnection or TMyEmbConnection already set up
    sql->SQL->Clear();
    sql->SQL->Add("CREATE TABLE DEPT (");
    sql->SQL->Add("DEPTNO INT PRIMARY KEY,");
    sql->SQL->Add("DNAME VARCHAR(14),");
    sql->SQL->Add("LOC VARCHAR(13)");
    sql->SQL->Add(");");
    sql->Execute();

    sql->SQL->Clear();
    sql->SQL->Add("CREATE TABLE EMP (");
    sql->SQL->Add("EMPNO INT PRIMARY KEY AUTO_INCREMENT,");
    sql->SQL->Add("ENAME VARCHAR(10),");
    sql->SQL->Add("JOB VARCHAR(9),");
    sql->SQL->Add("MGR INT,");
    sql->SQL->Add("HIREDATE DATETIME,");
    sql->SQL->Add("SAL FLOAT,");
    sql->SQL->Add("COMM FLOAT,");
    sql->SQL->Add("DEPTNO INT");
    sql->SQL->Add(");");
    sql->Execute();
  }
  __finally
  {
    sql->Free();
  }
}

Creating Stored Procedures

Note: MySQL supports stored procedures since version 5.0.

To create tables, the TMyScript component is used here.

Design-time creation

Run-time creation

The same operations performed in runtime look as follows:

[Delphi]

var
  script: TMyScript;
begin
  script := TMyScript.Create(nil);
  try
    script.Connection := con; // con is either TMyConnection or TMyEmbConnection already set up
    script.SQL.Clear;
    script.SQL.Add('DELIMITER //');
    script.SQL.Add('CREATE PROCEDURE HighPaidEmployees()');
    script.SQL.Add('BEGIN');
    script.SQL.Add(' SELECT emp.ename AS TenMostHighPaidEmployees, emp.sal FROM emp ORDER BY emp.sal DESC LIMIT 10;');
    script.SQL.Add('END;');
    script.SQL.Add('//');
    script.SQL.Add('CREATE PROCEDURE GetEmpNumberInDept (IN deptno INT, OUT empnumb INT)');
    script.SQL.Add('BEGIN');
    script.SQL.Add(' SELECT COUNT(*) INTO empnumb FROM EMP WHERE EMP.deptno = deptno;');
    script.SQL.Add('END;');
    script.SQL.Add('//');
    script.Execute;
  finally
    script.Free;
  end;
end;

Note: To run this code, you have to add the MyScript unit to the USES clause of your unit.

[C++Builder]

{
  TMyScript* script = new TMyScript(NULL);
  try
  {
    script->Connection = con; // con is either TMyConnection or TMyEmbConnection already set up
    script->SQL->Clear();
    script->SQL->Add("DELIMITER //");
    script->SQL->Add("CREATE PROCEDURE HighPaidEmployees()");
    script->SQL->Add("BEGIN");
    script->SQL->Add(" SELECT emp.ename AS TenMostHighPaidEmployees, emp.sal FROM emp ORDER BY emp.sal DESC LIMIT 10;");
    script->SQL->Add("END;");
    script->SQL->Add("//");
    script->SQL->Add("CREATE PROCEDURE GetEmpNumberInDept (IN deptno INT, OUT empnumb INT)");
    script->SQL->Add("BEGIN");
    script->SQL->Add(" SELECT COUNT(*) INTO empnumb FROM EMP WHERE EMP.deptno = deptno;");
    script->SQL->Add("END;");
    script->SQL->Add("//");
    script->Execute();
  }
  __finally
  {
    script->Free();
  }
}

Note: To run this code, you have to include the MyScript.hpp header file to your unit.

Additional information

Actually, there are lots of ways to create database objects on server. Any tool or component that is capable of running a SQL query, can be used to manage database objects. For example, TMyCommand suits fine for creating objects one by one, while TMyScript is designed for executing series of DDL/DML statements. For information on DDL statements syntax refer to the MySQL documentation.

© 1997-2018 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback