Creating Database Objects

This tutorial describes how to create database objects in MySQL using the TMyCommand and TMyScript components.

1. Requirements
2. General Information
3. Creating Tables
   • 3.1 Design-Time
   • 3.2 Runtime
4. Creating Stored Procedures
   • 4.1 Design-Time
   • 4.2 Runtime
5. Additional Information

Requirements

This tutorial assumes that you have already connected to the server (see Connecting to MySQL and Connecting to MySQL Embedded). To create database objects at runtime, add the MyAccess and MyScript units to the uses clause for Delphi or include the MyAccess.hpp and MyScript.hpp header files for C++ Builder.

General Information

Database objects are created using Data Definition Language (DDL), which is part of the SQL language. The user must have the appropriate privileges to execute DDL statements on the server. There are two ways to create database objects: build DDL statements manually and execute them with a component like TMyCommand, or use GUI tools for databases like dbForge Studio for MySQL. This tutorial uses the data access components to create tables and stored procedures.

Creating Tables

To create tables, the TMyCommand component is used in this tutorial.

Design-Time

• Find the TMyCommand component in the MyDAC category on the Tool Palette.
• Double-click the component. A new object will appear on the form. If this is the first TMyCommand object in this project, it will be named MyCommand1. Note that the Connection property is automatically set to an existing connection.
• Double-click the MyCommand1 object.
• Enter the following statements:
  

  CREATE TABLE dept (
      deptno INT NOT null PRIMARY KEY,
      dname VARCHAR(14),
      loc VARCHAR(13)
  );
  CREATE TABLE emp (
      empno INT NOT null PRIMARY KEY,
      ename VARCHAR(10),
      job VARCHAR(9),
      mgr INT,
  	hiredate TIMESTAMP,
  	sal INT,
  	comm INT,
  	deptno INT REFERENCES dept (deptno)
  );
  

• Click the Execute button to create two tables.

Runtime

The same tables created at runtime:

Delphi

var
  MyCommand1: TMyCommand;
begin
  MyCommand := TMyCommand.Create(nil);
  try
    // MyConnection1 (or MyEmbConnection1) was set up earlier
    MyCommand1.Connection := MyConnection1; 
    //adds statements to create tables
    MyCommand1.SQL.Add('CREATE TABLE dept (');
    MyCommand1.SQL.Add('   deptno INT NOT NULL PRIMARY KEY,');
    MyCommand1.Add('  dname VARCHAR(14),');
    MyCommand1.SQL.Add('  loc VARCHAR(13)');
    MyCommand1.SQL.Add(');');
    MyCommand1.SQL.Add('CREATE TABLE emp (');
    MyCommand1.SQL.Add('  empno INT NOT NULL PRIMARY KEY,');
    MyCommand1.SQL.Add('  ename VARCHAR(10),');
    MyCommand1.SQL.Add('  job VARCHAR(9),');
    MyCommand1.SQL.Add('  mgr INT,');
    MyCommand1.SQL.Add('  hiredate TIMESTAMP,');
    MyCommand1.SQL.Add('  sal INT,');
    MyCommand1.SQL.Add('  comm INT,');
    MyCommand1.SQL.Add('  deptno INT REFERENCES dept (deptno)');    
    MyCommand1.SQL.Add(');');
    // executes the statements
    MyCommand1.Execute;
  finally
    MyCommand1.Free;
  end;
end;

C++Builder

TMyCommand* MyCommand1 = new TMyCommand(NULL);
try {
    // MyConnection1 (or MyEmbConnection1) was set up earlier
	MyCommand1->Connection = MyConnection1; 
	//adds statements to create tables
	MyCommand1->SQL->Add("CREATE TABLE dept (");
	MyCommand1->SQL->Add("  deptno INT NOT NULL PRIMARY KEY,");
	MyCommand1->SQL->Add("  dname VARCHAR(14),");
	MyCommand1->SQL->Add("  loc VARCHAR(13)");
	MyCommand1->SQL->Add(");");
	MyCommand1->SQL->Add("CREATE TABLE emp (");
	MyCommand1->SQL->Add("  empno INT NOT NULL PRIMARY KEY,");
	MyCommand1->SQL->Add("  ename VARCHAR(10),");
	MyCommand1->SQL->Add("  job VARCHAR(9),");
	MyCommand1->SQL->Add("  mgr INT,");
	MyCommand1->SQL->Add("  hiredate TIMESTAMP,");
	MyCommand1->SQL->Add("  sal INT,");
	MyCommand1->SQL->Add("  comm INT,");
	MyCommand1->SQL->Add("  deptno INT REFERENCES dept (deptno)");
	MyCommand1->SQL->Add(");");
	// executes the statements
	MyCommand1->Execute();
}
__finally {
	MyCommand1->Free();
}

Creating Stored Procedures

To create stored procedures, the TMyScript component is used in this tutorial. MySQL supports stored procedures since version 5.6.

Design-Time

• Find the TMyScript component in the MyDAC category on the Tool Palette.
• Double-click the component. A new object will appear on the form. If this is the first TMyScript object in this project, it will be named MyScript1. Note that the Connection property is already set to an existing connection.
• Double-click the MyScript1 object.
• Enter the following statement:
  

  DELIMITER $$
  CREATE PROCEDURE TenMostHighPaidEmployees()
  BEGIN
  	SELECT emp.ename, emp.sal FROM emp ORDER BY emp.sal DESC LIMIT 10;
  END $$
  CREATE PROCEDURE GetEmpNumberInDept (IN deptno INT, OUT empnumb INT)
  BEGIN
  SELECT COUNT(*) INTO empnumb FROM EMP WHERE EMP.deptno = deptno;
  END $$
  DELIMITER ;
  

• Click the Execute button to create two stored procedures.

Runtime

The same stored procedures created at runtime:

Delphi

var
  MyScript1: TMyScript;
begin
  MyScript1 := TMyScript.Create(nil);
  try
    // MyConnection1 (or MyEmbConnection1) was set up earlier
    IBCScript1.Connection := IBCConnection1; 
    // adds statements to create procedures
    MyScript1.SQL.Add('DELIMITER $$');
    MyScript1.SQL.Add('BEGIN');
    MyScript1.SQL.Add('  SELECT emp.ename, emp.sal FROM emp ORDER BY emp.sal DESC LIMIT 10;');
    MyScript1.SQL.Add('END $$');
	MyScript1.SQL.Add('CREATE PROCEDURE GetEmpNumberInDept (IN deptno INT, OUT empnumb INT)');
    MyScript1.SQL.Add('BEGIN');
    MyScript1.SQL.Add('  SELECT COUNT(*) INTO empnumb FROM EMP WHERE EMP.deptno = deptno;');
    MyScript1.SQL.Add('END $$');
    MyScript1.SQL.Add('DELIMITER ;');
    // executes the statements
    MyScript1.Execute;
  finally
    MyScript1.Free;
  end;
end;

TMyScript* MyScript1 = new TIBCScript(NULL);
try {
    // MyConnection1 (or MyEmbConnection1) was set up earlier
	MyScript1->Connection = IBCConnection1; 
	// adds statements to create procedures	
    MyScript1->SQL->Add("DELIMITER $$");
    MyScript1->SQL->Add("BEGIN");
    MyScript1->SQL->Add("	SELECT emp.ename, emp.sal FROM emp ORDER BY emp.sal DESC LIMIT 10;");
    MyScript1->SQL->Add("END $$");
    MyScript1->SQL->Add("  FOR");
    MyScript1->SQL->Add("CREATE PROCEDURE GetEmpNumberInDept (IN deptno INT, OUT empnumb INT)");
	MyScript1->SQL->Add("BEGIN");
    MyScript1->SQL->Add("  SELECT COUNT(*) INTO empnumb FROM EMP WHERE EMP.deptno = deptno;");
    MyScript1->SQL->Add("END $$");
    MyScript1->SQL->Add("DELIMITER ;");
    // executes the statements
    MyScript1->Execute;
}
__finally {
	MyScript1->Free();

Additional Information

There are many ways to create database objects on the server. Any tool or component that is capable of running an SQL query can be used to manage database objects. For example, TMyCommand can be used to insert statements one by one, while TMyScript is intended to execute multiple DDL/DML statements as a single SQL script. For more information on DDL statements, refer to the MySQL documentation.