SDAC Creating Database Objects

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

1. Requirements
2. General information
3. Creating tables
   • 3.1 Design-time creation
   • 3.2 Run-time creation
4. Creating Stored Procedures
   • 4.1 Design Time Creation
   • 4.2 Run Time Creation
5. Additional information

Requirements

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

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 TMSSQL. Another way is to use visual database tools like dbForge Studio for SQL Server or Microsoft SQL Server Management Studio. This topic covers the first way - using components.
There are two ways to execute DDL statements in components like TMSSQL: 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 TMSConnection or TMSCompactConnection component on the form.

Creating tables

To create tables, the TMSSQL component is used here.

Design-time creation

• Open the Component palette and find the TMSSQL component in the SQL Server Access category.
• Double-click on the component. Note that a new object appears on the form. If this is the first time you create TMSSQL in this application, it is named MSSQL1. Note that the MSSQL1.Connection property is already set to an existent (on the form) connection.
• Double-click on the MSSQL1 object.
• Type the following lines:
  

  CREATE TABLE dept (
    deptno INT PRIMARY KEY,
    dname VARCHAR(14),
    loc VARCHAR(13)
  );
  CREATE TABLE emp (
    empno INT IDENTITY(1,1) PRIMARY KEY,
    ename VARCHAR(10),
    job VARCHAR(9),
    mgr INT,
    hiredate DATE,
    sal FLOAT,
    comm FLOAT,
    deptno INT
  );

• Click on the Execute button. This will create two tables that we will use for tutorial purposes.

Run-time creation

Same operations performed in runtime look as follows:

[Delphi]

var
  sql: TMSSQL;
begin
  sql := TMSSQL.Create(nil);
  try
    sql.Connection := con; // con is either TMSConnection or TMSCompactConnection 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.SQL.Add('CREATE TABLE emp (');
    sql.SQL.Add('  empno INT IDENTITY(1,1) PRIMARY KEY,');
    sql.SQL.Add('  ename VARCHAR(10),');
    sql.SQL.Add('  job VARCHAR(9),');
    sql.SQL.Add('  mgr INT,');
    sql.SQL.Add('  hiredate DATE,');
    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]

{
  TMSSQL* sql = new TMSSQL(NULL);
  try
  {
    sql->Connection = con; // con is either TMSConnection or TMSCompactConnection 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->SQL->Add("CREATE TABLE emp (");
    sql->SQL->Add("  empno INT IDENTITY(1,1) PRIMARY KEY,");
    sql->SQL->Add("  ename VARCHAR(10),");
    sql->SQL->Add("  job VARCHAR(9),");
    sql->SQL->Add("  mgr INT,");
    sql->SQL->Add("  hiredate DATE,");
    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

To create tables, the TMSScript component is used here.

Design-time creation

• Open the Component palette and find the TMSScript component in the SQL Server Access category.
• Double-click on the component. Note that a new object appears on the form. If this is the first time you create TMSScript in this application, it is named MSScript1. Note that the MSScript1.Connection property is already set to existent (on the form) connection.
• Double-click on the MSScript1 object.
• Type the following lines:
  

  CREATE PROCEDURE [Ten Most High-Paid Employees]
  AS
  BEGIN
    SET ROWCOUNT 10
    SELECT emp.ename AS TenMostHighPaidEmployees, emp.sal FROM emp ORDER BY emp.sal DESC
    SET ROWCOUNT 0
  END;
  /
  CREATE PROCEDURE GetEmpNumberInDept
    @deptno  INT,
    @empnumb INT OUT
  AS
  BEGIN
    SELECT @empnumb = count(*) FROM emp WHERE deptno = @deptno;
  END
  /

• Click on the Execute button. This will create five stored procedures that we will use for tutorial purposes.

Run-time creation

The same operations performed in runtime look as follows:

[Delphi]

var
  script: TMSScript;
begin
  script := TMSScript.Create(nil);
  try
    script.Connection := con; // con is either TMSConnection or TMSCompactConnection already set up
    script.SQL.Clear;
    script.SQL.Add('CREATE PROCEDURE [Ten Most High-Paid Employees]');
    script.SQL.Add('AS');
    script.SQL.Add('BEGIN');
    script.SQL.Add('  SET ROWCOUNT 10');
    script.SQL.Add('  SELECT emp.ename AS TenMostHighPaidEmployees, emp.sal FROM emp ORDER BY emp.sal DESC');
    script.SQL.Add('  SET ROWCOUNT 0');
    script.SQL.Add('END');
    script.SQL.Add('/');
    script.SQL.Add('CREATE PROCEDURE GetEmpNumberInDept');
    script.SQL.Add('@deptno INT,');
    script.SQL.Add('@empnumb INT OUT');
    script.SQL.Add('AS');
    script.SQL.Add('BEGIN');
    script.SQL.Add('  SELECT @empnumb = count(*) FROM emp WHERE 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 MSScript unit to the USES clause of your unit.

[C++Builder]

{
  TMSScript* script = new TMSScript(NULL);
  try
  {
    script->Connection = con; // con is either TMSConnection or TMSCompactConnection already set up
    script->SQL->Clear();
    script->SQL->Add("CREATE PROCEDURE [Ten Most High-Paid Employees]");
    script->SQL->Add("AS");
    script->SQL->Add("BEGIN");
    script->SQL->Add("  SET ROWCOUNT 10");
    script->SQL->Add("  SELECT emp.ename AS TenMostHighPaidEmployees, emp.sal FROM emp ORDER BY emp.sal DESC");
    script->SQL->Add("  SET ROWCOUNT 0");
    script->SQL->Add("END");
    script->SQL->Add("/");
    script->SQL->Add("CREATE PROCEDURE GetEmpNumberInDept");
    script->SQL->Add("@deptno INT,");
    script->SQL->Add("@empnumb INT OUT");
    script->SQL->Add("AS");
    script->SQL->Add("BEGIN");
    script->SQL->Add("  SELECT @empnumb = count(*) FROM emp WHERE deptno = @deptno;");
    script->SQL->Add("END");
    script->SQL->Add("/");
    script->Execute();
  }
  __finally
  {
    script->Free();
  }
}

Note: To run this code, you have to include the MSScript.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, TMSSQL suits fine for creating objects one by one, while TMSScript is designed for executing series of DDL/DML statements. For information on DDL statements syntax refer to the SQL Server documentation.