IBDAC

Inserting Data Into Tables

This tutorial describes how to insert data into tables using the TIBCQuery and TIBCTable components.

  1. Requirements
  2. General Information
  3. Design-Time
  4. Runtime
  5. Additional Information

Requirements

This tutorial assumes that you already know how to connect to the server (see Connecting to InterBase and Firebird) and that the necessary objects have already been created on the server (see Creating Database Objects). To insert data at runtime, add IBC unit to the uses clause for Delphi or include the IBC.hpp header file for C++ Builder.

General Information

You can insert data into a table using the Data Manipulation Language (DML), which is part of SQL. The user must have the appropriate privileges to execute DML statements on the server. There are two ways to manipulate data in a table: you can build DML statements manually and run them with a component like TIBCQuery, or you can use the dataset functionality (the Insert, Append, and Post methods) of the TIBCQuery and TIBCTable components. This tutorial shows you how to insert data into the dept and emp tables.

The dept table definition:

deptno dname loc
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

The emp table definition:

empno ename job mgr hiredate sal comm deptno
7369 SMITH CLERK 7902 17-12-1980 800 NULL 20
7499 ALLEN SALESMAN 7698 20-02-1981 1600 300 30
7521 WARD SALESMAN 7698 22-02-1981 1250 500 30
7566 JONES MANAGER 7839 02-04-1981 2975 NULL 20
7654 MARTIN SALESMAN 7698 28-09-1981 1250 1400 30
7698 BLAKE MANAGER 7839 01-05-1981 2850 NULL 30
7782 CLARK MANAGER 7839 09-06-1981 2450 NULL 10
7788 SCOTT ANALYST 7566 13-07-1987 3000 NULL 20
7839 KING PRESIDENT NULL 17-11-1981 5000 NULL 10
7844 TURNER SALESMAN 7698 08-09-1981 1500 0 30
7876 ADAMS CLERK 7788 13-07-1987 1100 NULL 20
7900 JAMES CLERK 7698 03-12-1981 950 NULL 30
7902 FORD ANALYST 7566 03-12-1981 3000 NULL 20
7934 MILLER CLERK 7782 23-01-1982 1300 NULL 10

Inserting at Design-Time

Inserting at Runtime

Using the DataSet Functionality

The Insert, Append, and Post methods of the TIBCQuery and TIBCTable components allow you to insert data without having to manually construct a DML statement — it is generated by IBDAC components internally. Insert adds a new empty record in the current cursor position, while Append adds a new empty record at the end of the dataset. The code below demonstrates the use of these methods:

Delphi

var
  IBCQuery1: TIBCQuery;
begin
  IBCQuery1 := TIBCQuery.Create(nil);
  try
	// IBCConnection1 was set up earlier
    IBCQuery1.Connection := IBCConnection1; 
    // adds a statement to retrieve data
    IBCQuery1.SQL.Text := 'SELECT * FROM dept';
    // opens the dataset
    IBCQuery1.Open;
    // adds a new empty record at the end of the dataset
    IBCQuery1.Append;
    // searches fields by their names and assigns new values
    IBCQuery1.FieldByName('deptno').AsInteger := 10;
    IBCQuery1.FieldByName('dname').AsString := 'ACCOUNTING';
    IBCQuery1.FieldByName('loc').AsString := 'NEW YORK';
    // writes the modified record
    IBCQuery1.Post;
  finally
    IBCQuery1.Free;
  end;
end;

C++Builder

TIBCQuery* IBCQuery1 = new TIBCQuery(NULL);
try {
	// IBCConnection1 was set up earlier
    IBCQuery1->Connection = IBCConnection1;
    // adds a statement to retrieve data
    IBCQuery1->SQL->Text = "SELECT * FROM dept";
    // opens the dataset
    IBCQuery1->Open();
    // adds a new empty record at the end of the dataset
    IBCQuery1->Append();
    // searches fields by their names and assigns new values
    IBCQuery1->FieldByName("deptno")->AsInteger = 10;
    IBCQuery1->FieldByName("dname")->AsString = "ACCOUNTING";
    IBCQuery1->FieldByName("loc")->AsString = "NEW YORK";
    // writes the modified record
    IBCQuery1->Post();
}
__finally {
    IBCQuery1->Free();
}

Building DML Statements Manually

DML statements can be constucted with or without parameters. The code below demonstrates both ways.

Parameterized DML Statements

Delphi

var
  IBCQuery1: TIBCQuery;
begin
  IBCQuery1 := TIBCQuery.Create(nil);
  try
	// IBCConnection1 was set up earlier
    IBCQuery1.Connection := IBCConnection1;
    // adds a parameterized statement to insert data
    IBCQuery1.SQL.Add('INSERT INTO dept(deptno, dname, loc) VALUES (:deptno, :dname, :loc);');
    // searches parameters by their names and assigns new values
    IBCQuery1.ParamByName('deptno').AsInteger := 10;
    IBCQuery1.ParamByName('dname').AsString := 'ACCOUNTING';
    IBCQuery1.ParamByName('loc').AsString := 'NEW YORK';
    // executes the statement
    IBCQuery1.Execute;
  finally
    IBCQuery1.Free;
  end;
end;

C++Builder

TIBCQuery* IBCQuery1 = new TIBCQuery(NULL);
try {
	// IBCConnection1 was set up earlier
    IBCQuery1->Connection = IBCConnection1;
    // adds a parameterized statement to insert data 
    IBCQuery1->SQL->Add("INSERT INTO dept(deptno, dname, loc) VALUES (:deptno, :dname, :loc);");
    // searches parameters by their names and assigns new values
    IBCQuery1->ParamByName("deptno")->AsInteger = 10;
    IBCQuery1->ParamByName("dname")->AsString = "ACCOUNTING";
    IBCQuery1->ParamByName("loc")->AsString = "NEW YORK";
    // executes the statement
    IBCQuery1->Execute();
}
__finally {
    IBCQuery1->Free();
}

Non-Parameterized DML Statements

Delphi

var
  IBCQuery1: TIBCQuery;
begin
  IBCQuery1 := TIBCQuery.Create(nil);
  try
	// IBCConnection1 was set up earlier
	IBCQuery1.Connection := IBCConnection1; 
    // adds a statement to insert a record
    IBCQuery1.SQL.Add('INSERT INTO dept(deptno, dname, loc) VALUES (10,''ACCOUNTING'',''NEW YORK'');');
    // executes the statement
    IBCQuery1.Execute;
  finally
    IBCQuery1.Free;
  end;
end;

C++Builder

TIBCQuery* IBCQuery1 = new TIBCQuery(NULL);
try {
	// IBCConnection1 was set up earlier
    IBCQuery1->Connection = IBCConnection1;
    //adds the statement to insert a record 
    IBCQuery1->SQL->Add("INSERT INTO dept(deptno, dname, loc) VALUES (10,'ACCOUNTING','NEW YORK');");
    // executes the statement
    IBCQuery1->Execute();
}
__finally {
    IBCQuery1->Free();
}

Additional Information

There are many ways to insert data into tables. Any tool or component that is capable of running an SQL query can be used to manage data. For example, TIBCSQL can be used to insert records one by one, while TIBCScript is designed to execute multiple DDL/DML statements as a single SQL script. TIBCLoader is the fastest way to insert data into InterBase/Firebird tables.

It is also possible to use stored procedures to insert data, in which case all data manipulation logic is defined on the server. See Using Stored Procedures for more information.

© 1997-2022 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback