Pro*C/C++ - How do I OPEN CURSOR and FETCH in different fucntions

[Don Chambers]

I am using Pro*C/C++ and need to have a function that sets up a cursor
and opens it but I do not need to fetch at this time. I need to fetch
from another function. How do I fetch in one function using a cursor
declared and opened in another function?
Is in possible to declare a cursor globally or as a member of a class?

This is what I am trying to do. If you have a better idea let me
know.
I am creating a class for other developers in which they issue a
select statement and then move through the rows with a MoveNext
function which sets variables to the data selected. I currently have
the open and fetch in the same function and I load up a linked list
with all the data. My MoveNext function iterates through the list and
sets variables to the current items in the list. On the next MoveNext
call I move to the next element in the list. This causes two
problems:
1) My Select fucntion is huge and hard to follow becasue all the
descriptor information (for dynamic SQL), the cursor logic, and the
linked list logic. If I keep this method I would at least like to
break it into multiple functions but am having problems becasue the
cursor can only be fetched in the function in which it is declared.
2) On selects with many columns and rows the linked list grows
huge and consumes much memory. As the user iterates thorugh the list
and stores the values in other ways (an Array, Container, or their own
list) this doubles the amount of memory used.

Thanks,
Don
cham...@inquiregroup.com

[Karl R.]

You can declate an global cursor in PRO*C when you put the
Cursor in GLOBAL C++ -Context. You have to define the cursor
outside of the function-body.
/Karl

In article <2kgtgsg93dq2g3884...@4ax.com>, Don

Karl Reitschuster
Senior Consultant CSC Ploenzke AG
Oracle Databases, Implementation, Performance-Tuning
<!Jesus is Lord!>
* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
The fastest and easiest way to search and participate in Usenet - Free!

[Don Chambers]

I need to set the select statement of the cursor at runtime. This
should be done in one function and the fetch in another function.
When I try it the way you describe I have to declare the cursor
outside of any function and I need its select statement at compile
time.
How can I change the select statement of a cursor after it has been
declared?

On Tue, 02 May 2000 07:19:29 -0700, Karl R. <krei...@zdnetonebox.com>
wrote:

[Karl R.]

You have to use dynamic method 3 with PRO*C ( Documentation )
This is like oo-latebinding. The Sql-statement is specified at
runtime.

Cursors and so on you have to declare c++-global!

Bye

[Mike Coldewey]

Cursors don't have to be fetched in the same function that they are declared
or opened. It's possible to have the following structure: (kinda
pseudocode):

int returnCode;
DeclareMyCursor();
OpenMyCursor();
returnCode = FetchMyCursor();
while (!returnCode)
{
DoSomethingWithTheData()
returnCode = FetchMyCursor();
}
CloseMyCursor;

void DeclareMyCursor()
{
exec SQL DECLARE MYCURSOR AS ...
}
void OpenMyCursor()
{
exec SQL OPEN MYCURSOR...
}
int FetchMyCursor()
{
exec SQL Fetch MYCURSOR into <the variables>, etc.
return <error from the fetch>
}
void CloseMyCursor()
{
exec SQL CLOSE MYCURSOR...
}

A cursor, not being a C/C++ variable, doesn't have to obey the same scoping
rules of a variable. It's part of the SQL session, as I understand it. I
don't think that there's a way to declare a cursor globally as a member of
the class for the same reason.

Mike
Don Chambers <dcha...@mindspring.com> wrote in message
news:2kgtgsg93dq2g3884...@4ax.com...

[John Ralph]

I'm not sure about ProC, but if you were doing this in PL/SQL, you would
just put the
functions in the same package and declare the cursor global to the
package.

There is probably an analogous way to do this in ProC

FWIW.

[matt mcConnell]

Yes, you can declare a cursor globally - all of Oracle's ProC example
programs do it, in fact I think ProC sort of treats them globally even if
they aren't declared as such.

I don't really understand what your two problems are - your SELECT is
complex and hard to follow? I don't think you can easily break up a single
select, though I guess you could make some smaller selects, store the data,
and combine it yourself, but it doesn't seem worth the effort. Or, you can
create the statement dynamically adding on several pieces building something
complex from simple parts.

The program uses a lot of memory? Well, you can always reduce the amount by
creating references to data instead of copying it but it all depends on your
application.

In my experience, ProC makes you use statically allocated memory in a lot of
situations and generally is pretty flaky about memory management. In some
cases it wouldn't let me split things (like OPENs and FETCHes) into separate
functions and I ended up with some pretty ugly stuff. I don't know what the
solution is - perhaps if developers complain enough, Oracle will start
testing its products before releasing them.

Good luck,

matt

---
The real problem is entropy.

"John Ralph" <cwrm...@erols.com> wrote in message
news:39306B61...@erols.com...