[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 957
  • Last Modified:

How to use oracle cursor in Delphi 5?

Hello Experts,

  I have the requirement of using the cursor of oracle database (both forward and Backward movement is required). Also I am using BDE in my application. The query that is responsible for the resultset will be formed at run time.
can any one help me in implementing this. if I get any sample code will be helpful.

thanks in advance.

Regds
Shri
0
kShari
Asked:
kShari
  • 5
  • 4
1 Solution
 
kretzschmarCommented:
guessing a ref-cursor is meant

>both forward and Backward movement is required
this is not possible any cursor in oracle is unidirectional (only forward)

tell me, where comes the cursor from and what kind of cursor you have

meikl ;-)

0
 
kShariAuthor Commented:
meikl ,

  The cursor should be at client side. If I am not clear, pls let me know, so that i can explain clearly . Any alternative way to achieve the requirement.??

Regds
Shri
0
 
kretzschmarCommented:
?? The cursor should be at client side

if so, then just type your select into a tquery-object and open the query

usual oracle cursors are on server-side

meikl ;-)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
kShariAuthor Commented:
meikl

  U mean to say, a query object can be used for this purpose.? one point to be considered is that, every time a user select a new record from the result set, the result set has to be refreshed.

(let me explain in detail: many users will execute the same query and can see the results. from the result, the user(s) will select records one by one. is the record is already selected by another user, then any oher users should not be allowed to select the same record, and the second user should be allowed to select the next record and so on...) the Idea behind using the Cursor is every time we move in to the next record, the result set will be refreshed automatically.

Pls sugest me.

Regds
Shri
 
0
 
kretzschmarCommented:
hmm,
this kind of logic is not solveable with a query or a cursor

a schematic logic for this could be (the table is prepared with one additional field,
which can supplied by an trigger and a sequence):
1. get Resultset (a normal query)
2. if one record want to be edited, lookup specified record (second query) for same value prepared field
3. if value equal, update this recordfield
4. if not equal, next record

just as suggestion

meikl ;-)
0
 
kShariAuthor Commented:
meikl,

Just another clarification.
The term "second query" in point # 2, is to requery the result once we move to the next record??
0
 
kretzschmarCommented:
>The term "second query" in point # 2, is to requery the result once we move to the >next record??

the second query is just a specific select for the current record of the first query like

'select * from sametable where id = '+query1.fieldbyname('ID').AsString

after you get this single row, you can compare the values of the additional field,
if equal the record is editable, if not equal, next record

i could setup a small sample, how oracle should be prepared
and how then the flow is in delphi, but this takes a bit time, because
i'm currently a bit under pressure

meikl ;-)
0
 
kShariAuthor Commented:
meikl

 A sample code would be very helpful for me. Mean while, even I'l try to implement the logic suggested by you.

Shri.
0
 
kShariAuthor Commented:
Hi meikl
 
   Based on ur sugestion I am able to resolve the problem.
   Thank you for the help.
   
I am giving the  code also. if required some one can refer.. This code is as per my requirement.
still I would like to award points to meikl.


 unit PendingCheques;

interface
  uses dbtables,QueryLib,CommonLib,SysUtils,db;

  Const MAXPRIMARYKEY=16;

   { Private}
   Var CursorName:string;
   ResultSet,TableName,Condition,Order:string;
   TempQuery:TQuery;
   MainQuery:TQuery;

  {Public}
    AutoUnlockWhenChangeRecord:Boolean;
    Procedure createQuery(Query:TQuery); overload;
    procedure Create(Query:TQuery;LockFieldName,LockName:string;PrimaryKey:array of string); OverLoad;
    procedure OpenCursor(ResultSet,TableName,Condition,Order:string);
    Procedure OpenResultSet(ResultSet,TableName,Condition,Order:string);
    procedure ReOpenCursor;
    function Next:Boolean;
    function Prior:Boolean;
    function First:Boolean;
    function Last:Boolean;
    function Relative(RecordShift:Integer):Boolean;
    function Query:TQuery;
  {End;}

   {private}
    Var CountPrimaryKey:Integer;
    LockFieldName,LockName:string;
    PrimaryKey:array[0..MAXPRIMARYKEY-1] of string;

    //function LockCurrentRecord:Boolean; // Commented the Private declaration and made it public by shadakshari
    function PrimaryKeyCondition:String;
  {public}

    //procedure OpenCursor(CursorName,ResultSet,TableName,Condition,Order:string;Scrollable:Boolean);
    function NextLock:Boolean;
    function NextLockIncludeCurrentRecord:Boolean;
    procedure UnLockCurrentRecord;
    procedure UnLockAll;
    function LockCurrentRecord:Boolean;
    //Procedure deallocateCursor;
  {end;}

  Implementation

Procedure createQuery(Query:TQuery);
begin
  MainQuery:=Query;
  Query.SQL.Clear;
  Query.Close;
  TempQuery:=TQuery.Create(nil);
  TempQuery.SessionName :=Query.SessionName;
  TempQuery.DatabaseName:=Query.DatabaseName;
end;

procedure OpenResultSet(ResultSet,TableName,Condition,Order:string);
var Command:string;
begin
     Command :='select '+ResultSet+' from '+TableName+' where '+Condition+' order by '+Order;
     sqlopen(Query,Command);
end;

function Next:Boolean;
var t: Boolean;
begin

  Query.Next;
  Result:=not Query.EOF;

end;


function Prior:Boolean;
begin
  Query.Prior;
  Result:=not Query.EOF;
end;

function First:Boolean;
begin
  Query.First;
  Result:=not Query.EOF;
end;

function Last:Boolean;
begin
   Query.Last;
   Result:=not Query.EOF;
end;

function Relative(RecordShift:Integer):Boolean;
begin
  Query.MoveBy(RecordShift);
  Result:=not Query.EOF;
end;

function Query:TQuery;
begin
  Result:=MainQuery;
end;

procedure ReOpenCursor;
begin
  OpenCursor(ResultSet,TableName,Condition,Order);
end;

Procedure Create(Query:TQuery;LockFieldName,LockName:string;PrimaryKey:array of string);
Var i:Integer;
begin
   createQuery(Query);

  AutoUnlockWhenChangeRecord:=False;

  TempQuery:=TQuery.Create(nil);
  TempQuery.DatabaseName:=Query.DatabaseName;

  {Self.}LockFieldName:=LockFieldName;
  {Self.}LockName:=LockName;
  CountPrimaryKey:=High(PrimaryKey)-Low(PrimaryKey);
  If CountPrimaryKey>MAXPRIMARYKEY then
    raise Exception.Create('LockSQLCursor support only 16 primary keys');
  for i:=0 to CountPrimaryKey do
    {Self.}PrimaryKey[i]:=PrimaryKey[Low(PrimaryKey)+i];
end;

procedure OpenCursor(ResultSet,TableName,Condition,Order:string);
begin
  AddStr(Condition,' and (ltrim('+LockFieldName+') is null or ltrim('+LockFieldName+') ='''' or '+Equal(LockFieldName,Quote(LockName))+')');
  {Inherited} OpenResultSet(ResultSet,TableName,Condition,Order);

end;

function NextLock:Boolean;
var HaveRecord:Boolean;

begin
  If AutoUnlockWhenChangeRecord and (not Query.EOF) then

    UnLockCurrentRecord;
  repeat
    HaveRecord:={inherited} Next;
    HaveRecord:=true;
  until (not HaveRecord) or LockCurrentRecord;
  Result:=HaveRecord;

end;

function NextLockIncludeCurrentRecord:Boolean;
begin
  If LockCurrentRecord then
  begin
    Result:=True;
    exit;
  end;
  Result:=NextLock;
end;

function PrimaryKeyCondition:String;
var i:Integer;
    FieldIndex:Integer;

begin
  Result:='';
  for i:=0 to CountPrimaryKey do
  begin
    If i<>0 then
      Result:=Result+' and ';
    With Query do
    begin
      FieldIndex:=FieldDefs.IndexOf(PrimaryKey[i]);
      If FieldDefs.Items[FieldIndex].DataType in
        [ftDate]
      then
        Result:=Result+PrimaryKey[i]+'='+Quote(FormatDateTime('MM/dd/yyyy',FieldByName(PrimaryKey[i]).AsDateTime))
      else if FieldDefs.Items[FieldIndex].DataType in
        [ftDateTime]
      then
        Result:=Result+PrimaryKey[i]+'=To_Date('+Quote(FormatDateTime('MM/dd/yyyy HH:SS',FieldByName(PrimaryKey[i]).AsDateTime))+',''MM/dd/yyyy HH24:SS'')'
      else if FieldDefs.Items[FieldIndex].DataType in
        [ftString,ftBoolean,ftBCD,ftBytes,ftVarBytes,ftTypedBinary,ftTime]
      then
        Result:=Result+PrimaryKey[i]+'='+Quote(FieldByName(PrimaryKey[i]).AsString)
      else
        Result:=Result+PrimaryKey[i]+'='+FieldByName(PrimaryKey[i]).AsString;
    end;
  end;

end;

function LockCurrentRecord:Boolean;
var Command:String;
begin
  Command:='Update '+TableName+' Set '+LockFieldName+'='+Quote(LockName)+' where '+Braces(Condition)+' and '+Braces(PrimaryKeyCondition);//+' and ltrim'+Braces(LockFieldName)+' is null';
  SQLExec(TempQuery,Command);
  CondSelect(Query,ResultSet,TableName,Braces(PrimaryKeyCondition)+' and '+Equal(LockFieldName,Quote(LockName)));
  If Query.RecordCount=0 then
  begin
    Query.Close;
    Result:=False;
    Exit;
  end;
  Result:=True;
end;

procedure UnLockCurrentRecord;

begin
  SQLExec(TempQuery,'Update '+TableName+' Set '+LockFieldName+'='+Quote('')+' where '+Braces(PrimaryKeyCondition)+' and '+Equal(LockFieldName,Quote(LockName)));
end;

procedure UnLockAll;
begin
  SQLExec(TempQuery,'Update '+TableName+' Set '+LockFieldName+'='+Quote('')+' where '+Equal(LockFieldName,Quote(LockName)));
end;

{Procedure deallocateCursor;
Begin
  SQLExec(TempQuery,'Delete from '+TableName+ 'where '+LockFieldName+'='+Equal(LockFieldName,Quote(LockName)));
end;}
end.


0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now