?
Solved

Multiple Ref cursor as Out Parameter

Posted on 2008-06-17
14
Medium Priority
?
1,811 Views
Last Modified: 2013-12-07
Hi experts,
i've a question about using reference cursors into delphi.
i've a pl/sql procedure which has 2 out parameters which are reference cursor.
1) how do i access all the data. I want to print this data in quick report.
2) what if a cursor returns no value.

thanks
0
Comment
Question by:Asmitagoyal
  • 5
  • 3
  • 3
  • +1
12 Comments
 

Author Comment

by:Asmitagoyal
ID: 21801578
Dear all
suggestions required very fast. It is very urgent
thanks for all your help
0
 
LVL 14

Accepted Solution

by:
SteveBay earned 1000 total points
ID: 21801883
Let me suggest that you use as QuickAbstractRep and QRStringsBand in the report.
Here are some very general instructions. The code I include is meant as a rough example. I did not test it.

- Add form to to your applicaiton.
- Add QuickAbstractRep to the form.
- Place QRStringsBand on the QuickAbstractRep.
- Place a QRExpr on the QRStringsBand and set it's Expression to the QRSTRINGSBAND1 variable.  This will print the QRStringsBand1.Item.
-Place one or more QRLabels on the QRStringsBand.

Define an object into which you can place the values returned by your query,
type TMyObject = class(TObject)
  public
     value1 : String;
     value2 : String;
     value3 : String;
    {etc...}
end;

Then you can traverse your Cursor and load the values into the Items property of the QRStringsBand.

procedure TForm1.PrepareReport;
var MyObject : TMyObject;
begin
     While not Query1.eof do
          begin
          MyObject := TMyObject.Create;
        // if there is no value you can assign an empty string here
          MyObject.value1 := Query1.Parameters[0].Value;
          MyObject.value2 := Query1.Parameters[1].Value;
          MyObject.value3 := Query1.Parameters[2].Value;
        {etc...}
          // Assign the object as well as a string to the Items List.
          QRStringsBand1.Items.AddObject(MyObject.value1, MyObject)
          Query1.Next;
          end;
end;

In the BeforePrint Event of the QuickAbstractRep you can reference the values in MyObject

procedure TForm1.QRStringsBand1BeforePrint(Sender: TQRCustomBand;  var PrintBand: Boolean);
var MyObject : TMyObject;
begin
     //Get a refference to MyObject;
     MyObject := TMyObject(TQRStringsBand(Sender).Items.Objects[TQRStringsBand(Sender).Index]);
     // first Item is printed by QRExpr1
     QRLabel1.Caption := MyObject.value2;
     QRLabel2.Caption := MyObject.value3;
     {etc...}
end;

Make sure to free the Objects aftrer printing them.

procedure TForm1.QuickAbstractRep1AfterPrint(Sender: TObject);
begin
     TMyObject(TQRStringsBand(Sender).Items.Objects[TQRStringsBand(Sender).Index]).Free;
end;

I hope this helpful.
   
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 21802789
intersting problem

there is a sample for one ref-cursor

http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_20842305.html

SteveBay: guess you are off topic ;-)
Asmitagoyal: let me give a try

meikl ;-)
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:Asmitagoyal
ID: 21810028
hi
i've worked with one ref cursor as out parameter but do not know how to work with multiple ref cursor as out parameter.
i basically want to display the recordsets returned by ref cursors into quick report.
In quick reports work with one dataset is alright but how to proceed with multiple datasets?????
0
 
LVL 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 1000 total points
ID: 21810512
Ado has multiple datasets, haven't tried this yet.
But maybe this is the direction your looking for

As I remember there was a proc in the TAdoStoredProc or TAdoQuery
TAdoStoredProc.NextDataset

0
 

Author Comment

by:Asmitagoyal
ID: 21810686
But then how will i be using that in the quick reports
0
 
LVL 14

Expert Comment

by:SteveBay
ID: 21811800
Off topic or not, the method I described - using QuickAbstractRep and BeforePrint event is a way to handle multiple datasets.
0
 

Author Comment

by:Asmitagoyal
ID: 21812026
Hi
where can i find 'QuickAbstractRep '. I've just installed quickrep component and in that i can't find QuickAbstractRep
thanks
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 21812181
What exactly is it that you want to print on the report ?
maybe there is other easier way ?
0
 
LVL 14

Expert Comment

by:SteveBay
ID: 21812519
I use QuickReports Pro. It's posible that it's only available in that version
0
 

Author Comment

by:Asmitagoyal
ID: 21818912
I want to print data on report. and this data is coming from multiple queries. So i thought of writing a stored procedure which returns ref cursors as OUT parameters.
I've got an alternative solution to it by using multiple datasets (tquery component) on the form. I wanted that if only a single procedure could suffice my problem in which i could rite all my queries and then return the datasets.
If anyone finds a solution to this then kindly let me know.

Thanks
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 21863257
Nive idea, but i think you will end up with what you have.

If you look at the total result, it will be the same anyway.
X queries give X datasets

later if you get it to work ...
1 procedure gives X datasets

by the way, it will be simpler to find 1 error in the X queries than in the 1 procedure
only one of the X queries will give an error ...
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

599 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