Solved

Multiple Ref cursor as Out Parameter

Posted on 2008-06-17
14
1,768 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 250 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 250 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month8 days, 9 hours left to enroll

617 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