RAD Studio |  |

Creating and Modifying Server Metadata
See Also
Collapse All

Most of the commands that do not return data fall into two categories: those that you use to edit data (such as INSERT, DELETE, and UPDATE commands), and those that you use to create or modify entities on the server such as tables, indexes, and stored procedures.

If you don't want to use explicit SQL commands for editing, you can link your unidirectional dataset to a client dataset and let it handle all the generation of all SQL commands concerned with editing. In fact, this is the recommended approach because data-aware controls are designed to perform edits through a dataset such as TClientDataSet.

The only way your application can create or modify metadata on the server, however, is to send a command. Not all database drivers support the same SQL syntax. It is beyond the scope of this topic to describe the SQL syntax supported by each database type and the differences between the database types. For a comprehensive and up-to-date discussion of the SQL implementation for a given database system, see the documentation that comes with that system.

In general, use the CREATE TABLE statement to create tables in a database and CREATE INDEX to create new indexes for those tables. Where supported, use other CREATE statements for adding various metadata objects, such as CREATE DOMAIN, CREATE VIEW, CREATE SCHEMA, and CREATE PROCEDURE.

For each of the CREATE statements, there is a corresponding DROP statement to delete the metadata object. These statements include DROP TABLE, DROP VIEW, DROP DOMAIN, DROP SCHEMA, and DROP PROCEDURE.

To change the structure of a table, use the ALTER TABLE statement. ALTER TABLE has ADD and DROP clauses to create new elements in a table and to delete them. For example, use the ADD COLUMN clause to add a new column to the table and DROP CONSTRAINT to delete an existing constraint from the table.

For example, the following statement creates a stored procedure called GET_EMP_PROJ on an InterBase database:

Copy Code

    CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)
    RETURNS (PROJ_ID CHAR(5))
    AS
    BEGIN
      FOR SELECT PROJ_ID
      FROM EMPLOYEE_PROJECT
      WHERE EMP_NO = :EMP_NO
      INTO :PROJ_ID
      DO
        SUSPEND;
    END

The following code uses a TSQLDataSet to create this stored procedure. Note the use of the ParamCheck property to prevent the dataset from confusing the parameters in the stored procedure definition (:EMP_NO and :PROJ_ID) with a parameter of the query that creates the stored procedure.

Copy Code

    with SQLDataSet1 do
    begin
      ParamCheck := False;
      CommandType := ctQuery;
      CommandText := 'CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT) ' +
          'RETURNS (PROJ_ID CHAR(5)) AS ' +
          'BEGIN ' +
            'FOR SELECT PROJ_ID FROM EMPLOYEE_PROJECT ' +
            'WHERE EMP_NO = :EMP_NO ' +
            'INTO :PROJ_ID ' +
              'DO SUSPEND; ' +
           END';
      ExecSQL;
    end;

Copy Code

    SQLDataSet1->ParamCheck = false;
    SQLDataSet1->CommandType = ctQuery;
    SQLDataSet1->CommandText = "CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT) RETURNS (PROJ_ID CHAR(5)) AS BEGIN FOR SELECT PROJ_ID FROM EMPLOYEE_PROJECT WHERE EMP_NO = :EMP_NO INTO :PROJ_ID DO SUSPEND; END";
    SQLDataSet1->ExecSQL();

See Also

Specifying the Command to Execute

Executing the Command

Copyright(C) 2009 Embarcadero Technologies, Inc. All Rights Reserved.
What do you think about this topic? Send feedback!