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
kShariAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.