Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Setting an Oracle Rollback Segment from Delphi

Posted on 1998-02-23
12
361 Views
Last Modified: 2010-08-05
I am working with Oracle from Delphi.
I need to set a specific "Oracle Rollback Segment" before the transaction start.
How can I do it?
0
Comment
Question by:jpussacq
  • 7
  • 5
12 Comments
 

Author Comment

by:jpussacq
ID: 1359700
Edited text of question
0
 

Author Comment

by:jpussacq
ID: 1359701
Edited text of question
0
 

Expert Comment

by:bijoyn
ID: 1359702
Do u need Sample code ?.

Bijoy.
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:jpussacq
ID: 1359703
I need sample code or an explanation of how can I do this operation.

0
 

Expert Comment

by:bijoyn
ID: 1359704
Hi Again,

I am sending u a sample code for this. I have dropped two buttons on a form and on the click of one, i create the rollback segment and on the click of the other i drop the rollback segment. U also can create a private rollback segment for the tablespace that u are working on in the same way as demonstrated in the program just by putting the keyword 'PRIVATE' instead of 'PUBLIC'. A Public rollback segment is available to everyone using the tablespace.

----------

unit Testing;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Grids, StdCtrls, ExtCtrls, Buttons, DBTables, comctrls;


type
  TForm1 = class(TForm)
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    procedure FormShow(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
  public
    { Public declarations }
      FQuery         :  TQuery;
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.FormShow(Sender: TObject);
begin
   FQuery := TQuery.Create;
end;

procedure TForm1.BitBtn1Click(Sender: TObject);
begin
  FQuery.DatabaseName  := 'ObjMaint'; { You Can give your database name/ Alias name over here }
   with FQuery do
   begin
      SQL.Clear;
      SQL.Add('CREATE PUBLIC ROLLBACK SEGMENT BIJOY1'); { You can give any name as u wish instead of 'Bijoy1' }
      Prepare; { Does not matter if u do not prepare it }
      ExecSQL;
   end;
end;

procedure TForm1.BitBtn2Click(Sender: TObject);
begin
   FQuery.DatabaseName := 'ObjMaint'; { You Can give your database name/ Alias name over here }
   with FQuery do
   begin
      SQL.Clear;
      SQL.Add('DROP ROLLBACK SEGMENT BIJOY1'); { You can give any name as u wish instead of 'Bijoy1' }
      Prepare; { Does not matter if u do not prepare it }
      ExecSQL;
   end;
end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
   FQuery.Free;
end;

end.
-----------------

I hope it solves the problem.
0
 

Author Comment

by:jpussacq
ID: 1359705
Thank you four your code.
But the problem is not resolved yet.

Supose that my database have five Rollback Segments, named ROLL_SEGM_1, ROLL_SEGM_2, ROLL_SEGM_3, ROLL_SEGM_4 and ROLL_SEGM_5, and I want to use one of this five to process and specific transaction.

How can I do it from Delphi.

In oracle PL-SQL, the solution woul be:

  SET TRANSACTION USE ROLLBACK SEGMENT ROLL_SEGM_5;
  INSERT INTO Table VALUES ('xxx','yyy');
  COMMIT;

How can I write this code in Delphi???
0
 

Expert Comment

by:bijoyn
ID: 1359706
Hi There,

It is no different if u have existing rollback segments. If u know the rollback segments in prior then the following code should work. I am assuming that the rollback segment already exists. The table name and the fields will be what u want.

-----------
procedure TForm1.BitBtn3Click(Sender: TObject);
var
   str_Field1, str_Field2  :  String;
begin
   Qry_Temp.DatabaseName := 'ObjMaint'; { You Can give your database name/ Alias name over here }
   str_Field1  := 'TDefaultDBEdit';
   str_Field2  := 'XXX';
   with Qry_Temp do
   begin
      SQL.Clear;
      SQL.Add('SET TRANSACTION USE ROLLBACK SEGMENT BIJOY1'); { You can give any name as u wish instead of 'Bijoy1' }
      ExecSQL;
      SQL.Clear;
      SQL.Add('INSERT INTO OBJ_COMPPROPS(COMPONENTNAME,PROPERTYNAME) VALUES');
      SQL.Add('(');
      SQL.Add('''' + str_Field1 + '''' + ',' + '''' + str_Field2 + '''');
      SQL.Add(')');
      ExecSQL;
   end;
   Qry_Temp.SQL.Clear;
   Qry_Temp.SQL.Add('COMMIT');
   Qry_Temp.ExecSQL;
end;

-----------------

U can define a query statement and pass parameter values to it if u do not want to construct the query at runtime. That way saying post will commit the transaction on the database.

If u don't know which rollback segment exists then the code will change slightly to find out all the available rollback segments.

Bijoy.
0
 

Author Comment

by:jpussacq
ID: 1359707
All my programs are like this:

  try
   Database1.StartTransaction
   Query1.SQL.Clear;
   Query1.SQL.Add ('INSERT INTO table VALUES ('xxx','yyy');
   Query1.ExecSQL;
   Database1.Commit
  except
   Database1.Rollback;
  end;

Can I add in this code your solution (the following):

  Query1.SQL.Clear;
  Query1.SQL.Add('SET TRANSACTION USE ROLLBACK SEGMENT BIJOY1');
  Query1.ExecSQL;


0
 

Expert Comment

by:bijoyn
ID: 1359708
Yes U definitely can add my code into your programs. In fact instead of adding the whole thing into all programs, put this code into a library procedure and call it from the programs that need it.  Tell me if it solves the problem.

Bijoy.

P.S Do it after u start the transaction. i.e. Database.StartTransaction statement.
0
 

Author Comment

by:jpussacq
ID: 1359709
Tomorrow, I will try to do it.
Thank U.
0
 

Author Comment

by:jpussacq
ID: 1359710
bijoyn:

Your solution is correct.
Thank you.
0
 

Accepted Solution

by:
bijoyn earned 100 total points
ID: 1359711
I Have answered the question.

Bijoy
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Path  to current project in Delphi. 2 90
Delphi XE10, MySQL Query 4 164
scroll down TListBox component in Delphi 1 19
FMX TEdit KeyUp handler detecting  "enter" key 4 11
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question