Solved

Multiple Ref cursor as Out Parameter

Posted on 2008-06-17
14
1,752 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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 37

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 37

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 37

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

786 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