ODAC

Inserting Data Into Tables

This tutorial describes how to use OraQuery component to insert data into tables by means of executing SQL queries.

Requirements

This walkthrough supposes that you know how to connect to server (tutorial Logging onto the server) and that necessary objects are already created on the server (tutorial Creating database objects).

General information

Data on server can be modified (inserted, changed or deleted) using Data Manipulation Language (DML), which is a part of SQL. The DML statements can be executed on server by account that has necessary privileges.

There are two ways to manipulate a database. You can build DML statements manually and run them within some component like OraQuery. Another way is to use design-time features that provide graphical user interface to manage database. We will discuss both ways.

The goal of this tutorial is to insert the following data into tables dept and emp:
Table dept

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

Table emp

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 data in run time

To insert the first row into table dept you can use the following statement:

INSERT INTO dept (deptno, dname, loc) VALUES (10,'ACCOUNTING','NEW YORK')

The following code fragment executes the query:
[Delphi]


var
  OraSession1: TOraSession;
  OraQuery1: TOraQuery;
begin
  OraSession1 := TOraSession.Create(nil);
  OraQuery1:= TOraQuery.Create(nil);
  OraSession1.ConnectString := 'SCOTT/[email protected]';
  OraQuery1.SQL.Text := 'INSERT INTO dept (deptno, dname, loc) VALUES (10,''ACCOUNTING'',''NEW YORK'')';
  OraQuery1.Session := OraSession1;
  OraSession1.LoginPrompt := False;
  try
    OraSession1.Connect;
    try
      OraQuery1.Execute;
      ShowMessage(IntToStr(OraQuery1.RowsAffected)+' rows were affected.');
    except
      ShowMessage('Error encountered during INSERT operation.');
    end;
  finally
    OraSession1.disconnect;
    OraQuery1.Free;
    OraSession1.Free;
  end;

[C++ Builder]


  TOraSession *OraSession1 = new TOraSession(NULL);
  TOraQuery *OraQuery1 = new TOraQuery(NULL);
  OraSession1->ConnectString = "SCOTT/[email protected]";
  OraQuery1->SQL->Text = "INSERT INTO dept (deptno, dname, loc) VALUES (10,'ACCOUNTING','NEW YORK')";
  OraQuery1->Session = OraSession1;
  OraSession1->LoginPrompt = false;
  try
  {
	OraSession1->Connect();
	try
	{
	  OraQuery1->Execute();
	  ShowMessage(IntToStr(OraQuery1->RowsAffected)+" rows were affected.");
	}
	catch(const Exception& e)
	{
	  ShowMessage("Error encountered during INSERT operation.");
	}
  }
  __finally
  {
	OraSession1->Disconnect();
	OraQuery1->Free();
	OraSession1->Free();
  }

The sample first creates a connection with hardcoded connection string. Then it creates OraQuery object, assigns the query text and connection to the OraQuery instance. Connection is opened then. The Execute method of OraQuery runs SQL statement in the Text property. The RowsAffected property stores the number of rows affected by the query. This method is not intended to run SELECT statements. We will discuss retrieving data in other tutorials.

If the query is executed successfully you are notified about number of affected rows. If some error occurs you get the error message. The connection is closed anyway. It is recommended that you use try ... finally  clauses to make sure the connections are closed properly.

Design time setup

Same operations in design time include following steps:

Place OraSession component on a designer.

  1. Setup its properties and open connection by changing the Connected property to True or double-click on the component, in the dialog window provide necessary logon information and press Connect button.
  2. Place OraQuery component on the designer.
  3. In its Session property select name of the OraSession instance on the designer.
  4. Click on the ellipsis in SQL property in Properties window double-click the component in the OraQuery editor on the SQL tab and enter the following query:
    INSERT INTO dept VALUES (20,'SALES','DALLAS')

    and press the Execute button.

Additional information

Actually there are lots of ways to insert data into tables. Any tool or component that is capable of running a SQL query, can be used to manage data. Some components are best for performing certain tasks. For example, OraLoader is the fastest way to insert data, OraScript is designed for executing series of statements. For more information on these components refer to ODAC reference.

See Also

Getting Started
OraQuery Class
OraLoader Class
OraScript Class

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