Creating Database Objects

This tutorial describes how to create database objects in InterBase and Firebird using the TIBCSQL and TIBCScript 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 InterBase and Firebird). To create database objects at runtime, add the IBC and IBCScript units to the uses clause for Delphi or include the IBC.hpp and IBCScript.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 TIBCSQL, or use GUI tools for databases. This tutorial uses the data access components to create tables and stored procedures.

Creating Tables

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

Design-Time

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

  CREATE TABLE dept (
  	deptno integer not null primary key,
  	dname varchar(14),
  	loc varchar(13),
  	primary key (deptno)
  );
  CREATE TABLE emp (
  	empno integer not null primary key,
  	ename varchar(10),
  	job varchar(9),
  	mgr integer,
  	hiredate timestamp,
  	sal integer,
  	comm integer,
  	deptno integer references dept (deptno)
  );
  

• Click the Execute button to create two tables.

Runtime

The same tables created at runtime:

Delphi

var
  IBCSQL1: TIBCSQL;
begin
  IBCSQL1 := TIBCSQL.Create(nil);
  try
    // IBCConnection1 was set up earlier
    IBCSQL1.Connection := IBCConnection1; 
    //adds statements to create tables
    IBCSQL1.SQL.Add('CREATE TABLE dept (');
    IBCSQL1.SQL.Add('  deptno integer not null primary key,');
    IBCSQL1.SQL.Add('  dname varchar(14),');
    IBCSQL1.SQL.Add('  loc varchar(13)');
    IBCSQL1.SQL.Add(');');
    IBCSQL1.SQL.Add('CREATE TABLE emp (');
    IBCSQL1.SQL.Add('  empno integer not null primary key,');
    IBCSQL1.SQL.Add('  ename varchar(10),');
    IBCSQL1.SQL.Add('  job varchar(9),');
    IBCSQL1.SQL.Add('  mgr integer,');
    IBCSQL1.SQL.Add('  hiredate timestamp,');
    IBCSQL1.SQL.Add('  sal integer,');
    IBCSQL1.SQL.Add('  comm integer,');
    IBCSQL1.SQL.Add('  deptno int references dept (deptno)');    
    IBCSQL1.SQL.Add(');');
    // executes the statements
    IBCSQL1.Execute;
  finally
    IBCSQL1.Free;
  end;
end;

C++Builder

TIBCSQL* IBCSQL1 = new TIBCSQL(NULL);
try {
    // IBCConnection1 was set up earlier
	IBCSQL1->Connection = IBCConnection1; 
	//adds statements to create tables
	IBCSQL1->SQL->Add("CREATE TABLE dept (");
	IBCSQL1->SQL->Add("  deptno integer not null primary key,");
	IBCSQL1->SQL->Add("  dname varchar(14),");
	IBCSQL1->SQL->Add("  loc varchar(13)");
	IBCSQL1->SQL->Add(");");
	IBCSQL1->SQL->Add("CREATE TABLE emp (");
	IBCSQL1->SQL->Add("  empno integer not null primary key,");
	IBCSQL1->SQL->Add("  ename varchar(10),");
	IBCSQL1->SQL->Add("  job varchar(9),");
	IBCSQL1->SQL->Add("  mgr integer,");
	IBCSQL1->SQL->Add("  hiredate timestamp,");
	IBCSQL1->SQL->Add("  sal integer,");
	IBCSQL1->SQL->Add("  comm integer,");
	IBCSQL1->SQL->Add("  deptno integer references dept (deptno)");
	IBCSQL1->SQL->Add(");");
	// executes the statements
	IBCSQL1->Execute();
}
__finally {
	IBCSQL1->Free();
}

Creating Stored Procedures

To create stored procedures, the TIBCScript component is used in this tutorial.

Design-Time

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

  CREATE PROCEDURE TenMostHighPaidEmployees
  RETURNS (salary integer)
  AS
  BEGIN
    FOR
    SELECT FIRST 10 emp.sal FROM emp ORDER BY emp.sal DESC INTO salary DO
      suspend;
  END;
  CREATE FUNCTION GetEmpNumberInDept (
      pdeptno integer)
  RETURNS integer
  AS
  BEGIN
    RETURN (SELECT COUNT(*) FROM emp WHERE deptno = :pdeptno);
  END;
  

• Click the Execute button to create two stored procedures.

Runtime

The same stored procedures created at runtime:

Delphi

var
  IBCScript1: TIBCScript;
begin
  IBCScript1 := TIBCScript.Create(nil);
  try
    // IBCConnection1 was set up earlier
    IBCScript1.Connection := IBCConnection1; 
    // adds statements to create procedures
    IBCScript1.SQL.Add('CREATE PROCEDURE TenMostHighPaidEmployees');
    IBCScript1.SQL.Add('RETURNS (salary integer)');
    IBCScript1.SQL.Add('AS');
    IBCScript1.SQL.Add('BEGIN');
    IBCScript1.SQL.Add('  FOR');
    IBCScript1.SQL.Add('  SELECT FIRST 10 emp.sal FROM emp ORDER BY emp.sal DESC INTO salary DO');
	IBCScript1.SQL.Add('    suspend;');
    IBCScript1.SQL.Add('END');
    IBCScript1.SQL.Add('');
    IBCScript1.SQL.Add('CREATE FUNCTION GetEmpNumberInDept (');
    IBCScript1.SQL.Add('  pdeptno integer)');
    IBCScript1.SQL.Add('RETURNS integer');
    IBCScript1.SQL.Add('AS');
    IBCScript1.SQL.Add('BEGIN');
    IBCScript1.SQL.Add('  RETURN (SELECT COUNT(*) FROM emp WHERE deptno = :pdeptno);');
    IBCScript1.SQL.Add('END');
    // executes the statements
    IBCScript1.Execute;
  finally
    IBCScript1.Free;
  end;
end;

TIBCScript* IBCScript1 = new TIBCScript(NULL);
try {
    // IBCConnection1 was set up earlier
	IBCScript1->Connection = IBCConnection1; 
	// adds statements to create procedures	
    IBCScript1->SQL->Add("CREATE PROCEDURE TenMostHighPaidEmployees");
    IBCScript1->SQL->Add("RETURNS (salary integer)");
    IBCScript1->SQL->Add("AS");
    IBCScript1->SQL->Add("BEGIN");
    IBCScript1->SQL->Add("  FOR");
    IBCScript1->SQL->Add("  SELECT FIRST 10 emp.sal FROM emp ORDER BY emp.sal DESC INTO salary DO");
	IBCScript1->SQL->Add("    suspend;");
    IBCScript1->SQL->Add("END");
    IBCScript1->SQL->Add("");
    IBCScript1->SQL->Add("CREATE FUNCTION GetEmpNumberInDept (");
    IBCScript1->SQL->Add("  pdeptno integer)");
    IBCScript1->SQL->Add("RETURNS integer");
    IBCScript1->SQL->Add("AS");
    IBCScript1->SQL->Add("BEGIN");
    IBCScript1->SQL->Add("  RETURN (SELECT COUNT(*) FROM emp WHERE deptno = :pdeptno);");
    IBCScript1->SQL->Add("END");
    // executes the statements
    IBCScript1->Execute;
}
__finally {
	IBCScript1->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, TIBCSQL can be used to insert statements one by one, while TIBCScript is intended to execute multiple DDL/DML statements as a single SQL script. For more information on DDL statements, refer to the InterBase/Firebird documentation.