Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Multiple Ref cursor as Out Parameter

Posted on 2008-06-17
14
Medium Priority
?
1,798 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
14 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
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 video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

971 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