InformIT: Visual C++ 6 Unleashed > Data Definition Language

Table of Contents

Data Definition Language

Data Definition Language is the portion of SQL used for defining the structure of the database and creating objects within the database. DDL uses the following three different SQL commands to work with objects in the database:

CREATE: Creates a new object
ALTER: Modifies an existing object
DROP: Removes an object

These commands are used to work with several types of objects in the database, including tables, indexes, and views.

Tables

Tables are the most commonly used objects in relational databases. They hold the actual data in rows (records) and columns (fields). In many user applications, the tables that you need have already been created for you, whereas other situations require that you create your own tables.

SQL Data Types

Tables are constructed of columns that contain several types of data, so before moving on to the specifics of creating a table, let's look at the available data types for columns.

The SQL data types that you will look at here are grouped according to ODBC SQL conformance levels, although most implementations don't adhere strictly to the conformance-level specifications. Most drivers will support the minimum conformance level, but you should check your system's documentation to see if any others are supported.

The following types are supported by SQL at the minimum conformance level:

CHAR( n ): A fixed-length character string of n characters.
VARCHAR( n ): A variable length character string with a maximum length of n .
LONG VARCHAR: A variable length character string with no specified maximum length.
DECIMAL( p , s ) or NUMERIC( p , s ): These are equivalent and define a floating-point value with a precision of p decimal places and a scale of s places to the right of the decimal point.
SMALLINT: A 2-byte integer.
INTEGER: A 4-byte integer.
REAL: A 4-byte, floating-point value.
FLOAT or DOUBLE PRECISION: These are equivalent types that specify an 8-byte, floating-point value.

In addition, the extended conformance level specifies the following types, although support for these can vary widely between different databases:

BIT: A single bit.
TINYINT: A 1-byte integer.
BIGINT: An 8-byte integer.
BINARY( n ): A fixed-length binary field of n bytes.
VARBINARY( n ): A variable-length binary field with a maximum length of n bytes.
LONG VARBINARY: A variable-length binary field with no specified maximum length.
DATE: A date value.
TIME A time value.
TIMESTAMP: A value containing both time and date.

Procedures

Procedures are extremely useful functions that are stored inside a database. The general format for a procedure is:

CREATE PROC MyProcedure [optional variable list] AS
    SQL statements go here

For example, to create a routine that returns all the employees from a department, you would write the following MS SQL SERVER procedure:

CREATE PROC GetDepartment @department varchar (10) AS
    SELECT EmpId, EmpName, Salary
    FROM Employee
    WHERE Dept = @department

Procedures can perform a series of any Data Manipulation Language commands. In addition to standard SQL, procedures support decision construct equivalent to an if or switch statement, and looping constructs equivalent to a while statement. Procedures have several benefits over SQL code:

Procedures can be written to optimize SQL.
Procedures are pre-interpreted, which makes them faster than creating SQL and sending it to the database to be interpreted.
Procedures perform the work at the server, thereby reducing network traffic.
Procedures allow modular reuse of pretested SQL code. This enforces consistency, improves performance, and reduces programmer error.
Procedures can automate complex logic, allowing complex operations to be examined only once.

Procedures are executed using either the CALL or EXECUTE command, depending on the database. CALL and EXECUTE are Data Manipulation Language commands that allow the user to execute the procedure. The following command calls the GetDepartment procedure and passes it a string for the MIS department:

CALL GetDepartment 'MIS'

In addition, many database, such as Sybase and Microsoft SQL Server, allow execution with or without the EXECUTE statement:

EXECUTE GetDepartment 'MIS'

GetDepartment 'MIS'

Triggers

Triggers are special procedures that are executed automatically if an event occurs in the database. For example, if you want an audit trail to automatically generate an audit trail record every time payroll is updated, you could do this by using an MS SQL Server trigger:

CREATE TRIGGER auditPayroll
ON Employee
FOR Update
AS
INSERT INTO auditTable /* Audit table sets the date automatically */
    (EmpId,
    EmpName,
    Salary,
    Dept)
SELECT
    (EmpId,
    EmpName,
    Salary,
    Dept)
FROM deleted

In this code, deleted is a reserved word in MS SQL Server that indicates the record that existed before the SQL statement. Conversely, the inserted keyword is used for the record that exists after the SQL statement. Most databases allow you to capture both previous and new information.

This audit table example shows how useful triggers can be. Without any additional collaborative programming effort, every database call made to change the contents of a table is tracked. This information can be viewed by personnel to reduce fraudulent behavior, or can be used by system administrators to back up data on a row-by-row basis for important tables.

Database Projects

Visual Studio 6.0 includes the ability to create database projects. A database project is a type of CASE tool that allows easy administration of a database.

To start a database project, you need to define or create an ODBC database. Then, inside Visual Studio, you need to create a database project by clicking File, New and choosing Database Project, as shown in Figure 19.1

Figure 19.1 Database Projects are used to help administer a database from within Visual Studio.

Next, you must choose which ODBC database you want to administer (see Figure 19.2). Most of the time, you will use a Machine Data Source that has been previously defined through the ODBC setup in the control panel.

Figure 19.2 Visual Studio database projects can administer any ODBC database.

After you choose your database, a database project is created. You can use the database project like the one shown in Figure 19.3 to create tables, procedures (if supported), triggers (if supported), and to test SQL.

Figure 19.3 Database projects can perform a myriad of database tasks for database administration or database development.

Data Control Language | Next Section

Previous Section