Retrieving and Modifying Data

Introducing

This tutorial describes how to use OraQuery component.

Requirements

This walkthrough supposes that you know how to connect to server, how to create the necessary objects on the server, and how to insert the data to the created tables.

Retrieving and Updating Data

In this sample we are using OraQuery to retrieve and manipulate data. For more information, refer to the description of this class in our documentation.
[Delphi]

program Project1;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  DB,
  DBAccess,
  Ora;

procedure PrintDept(OraSession: TOraSession);
var
  OraQuery:TOraQuery;
  i: integer;
begin
  OraQuery := TOraQuery.Create(nil);
  OraQuery.Session := OraSession;
  OraQuery.SQL.Text := 'SELECT * FROM dept';
  try
    OraQuery.Open;
    for i:= 0 to OraQuery.Fields.Count - 1 do
      Write(OraQuery.Fields[i].DisplayName+#09);
    WriteLn;
    while not OraQuery.Eof do
    begin
      for i:= 0 to OraQuery.Fields.Count - 1 do
        Write(OraQuery.Fields[i].AsString+#09);
      WriteLn;
      OraQuery.Next;
    end;
  finally
    OraQuery.close;
    OraQuery.Free;
  end;
end;

procedure ModifyDept(OraSession: TOraSession; SQLText: string; action: string);
var
  OraQuery:TOraQuery;
begin
  OraQuery := TOraQuery.Create(nil);
  OraQuery.Session := OraSession;
  OraQuery.SQL.Text := SQLText;
  try
    OraQuery.Execute;
    Writeln;
    write(format('Rows in DEPT %s: %d', [action,OraQuery.RowsAffected]));
  finally
    OraQuery.Free;
  end;
end;


var
  OraSession: TOraSession;

begin
  OraSession := TOraSession.Create(nil);
  OraSession.ConnectString := 'SCOTT/TIGER@ORCL1020';
  try
    OraSession.Connect;
    PrintDept(OraSession);
    ModifyDept(OraSession,'UPDATE DEPT SET LOC=''VEGAS'' WHERE DEPTNO > 20','updated');
    ModifyDept(OraSession, 'INSERT INTO dept (deptno, dname, loc) VALUES (50,''DEVELOPMENT'',''NEW YORK'')','insertted');
    ModifyDept(OraSession,'DELETE FROM dept WHERE deptno = 50','deleted');
    Readln;
  finally
    OraSession.Free;
  end;
end.

[C++ Builder]

#include <vcl.h>
#pragma hdrstop

#include <tchar.h>
#include <stdio.h>
#include <DBAccess.hpp>
#include <Ora.hpp>

#pragma argsused

void PrintDept(TOraSession *OraSession)
{
  TOraQuery *OraQuery = new TOraQuery(NULL);
  OraQuery->SQL->Text = "SELECT * FROM dept";
  OraQuery->Session = OraSession;
  int i;
  try
  {
	OraQuery->Open();
	for(i = 0; i< OraQuery->Fields->Count; i++)
	  printf("%s\t",OraQuery->Fields->operator [](i)->DisplayName.t_str());
	printf("\n");
	while (!OraQuery->Eof)
	{
		for(i = 0; i< OraQuery->Fields->Count; i++)
			printf("%s\t",OraQuery->Fields->operator [](i)->AsString.t_str());
		printf("\n");
		OraQuery->Next();
	}
  }
  __finally
  {
	OraQuery->Free();
  }
}

void ModifyDept(TOraSession *OraSession, String SQLText, String Action)
{
  TOraQuery *OraQuery = new TOraQuery(NULL);
  OraQuery->SQL->Text = SQLText;
  OraQuery->Session = OraSession;
  try
  {
	  OraQuery->Execute();
	  printf("Rows in DEPT %s: %d", Action.t_str(), OraQuery->RowsAffected);
	  printf("\n");
  }
  __finally
  {
	OraQuery->Free();
  }
}

int _tmain(int argc, _TCHAR* argv[])
{
	TOraSession *OraSession = new TOraSession(NULL);
	int i;
	try
	{
		OraSession->ConnectString = "SCOTT/TIGER@ORCL1020";
		OraSession->Connect();
		PrintDept(OraSession);
		ModifyDept(OraSession, "UPDATE DEPT SET LOC='VEGAS' WHERE DEPTNO > 20","updated");
		ModifyDept(OraSession, "INSERT INTO dept (deptno, dname, loc) VALUES (50,'DEVELOPMENT','NEW YORK')","insertted");
		ModifyDept(OraSession, "DELETE FROM dept WHERE deptno = 50","deleted");
		system("pause");
	}
	__finally
	{
		OraSession->Free();
	}
	return 0;
}