Solved

Setting an Oracle Rollback Segment from Delphi

Posted on 1998-02-23
12
357 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
Comment Utility
Edited text of question
0
 

Author Comment

by:jpussacq
Comment Utility
Edited text of question
0
 

Expert Comment

by:bijoyn
Comment Utility
Do u need Sample code ?.

Bijoy.
0
 

Author Comment

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

0
 

Expert Comment

by:bijoyn
Comment Utility
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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Expert Comment

by:bijoyn
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Tomorrow, I will try to do it.
Thank U.
0
 

Author Comment

by:jpussacq
Comment Utility
bijoyn:

Your solution is correct.
Thank you.
0
 

Accepted Solution

by:
bijoyn earned 100 total points
Comment Utility
I Have answered the question.

Bijoy
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now