implement iterator layer around Pro*C embedded SQL (PL/SQL)

Background : I'm trying to implement a C++ STL iterator like layer around Pro*C embedded SQL (PL/SQL). Basically, I'll need something like these methods :

        int select_init(string sql_cmd) {
            EXEC SQL DECLARE cur1 CURSOR FOR ...;
            EXEC SQL OPEN cur1;
        }

        int select_get(ResultType &result) {
            EXEC SQL FETCH cur1 INTO ...;
        }

        int select_close() {
            EXEC SQL CLOSE cur1;
        }

which would then be called in order for a given SQL request.

Called like this, there's no problem :

        select_init(cmd);
        while (select_get(res)) {
            // do something with res
        }
        select_close();

But what if I call a second select_init, before the select_close was called for the first ?

        select_init(cmd1);
        select_init(cmd2);

        // some more code

        select_close();
        select_close();

The problem is that two cursors are now opened with the same identifier (cur1). That won't work like this afaik.


Now, finally on to my questions :

1) has something like this already been done, and where can I find it ?
2) is my approach good, or would you do it differently ?
3) if my approach is good, how do I solve the described problem ? Is there a way to get different cursor id's for subsequent calls to select_open ?


I tried to be as brief as possible in my question, so if I left anything important out, please let me know :)
LVL 53
Infinity08Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
the pro*c declare cursor statement can't have a dynamic cursor reference.

So, you'll have to process each cursor to completion sequentially, or create a separate cursor for each statement you want to process.  How many select_init's might you run in parallel?

Maybe do something like this, with a corresponding switch on the fetch and close procedures

int select_init(string sql_cmd, int cur_num) {
           switch (cur_num) {
                    case 1:
                             EXEC SQL DECLARE cur1 CURSOR FOR ...;
                             EXEC SQL OPEN cur1;
                             break;
                   case 2:
                             EXEC SQL DECLARE cur2 CURSOR FOR ...;
                             EXEC SQL OPEN cur2;
                             break;
                   case 3:
                             EXEC SQL DECLARE cur3 CURSOR FOR ...;
                             EXEC SQL OPEN cur3;
                             break;
                  // and so on for as many cursors as you would need to have open simultaneously

           }
        }
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Infinity08Author Commented:
>> the pro*c declare cursor statement can't have a dynamic cursor reference.

So, if I understand correctly, the chosen cursor identifier (cur1) has a 1-to-1 mapping with the actual cursor id ... Correct ?

        EXEC SQL OPEN cur1;

will always be replaced by the exact same code by Pro*C, no matter how many times I call it ... Yes ?


>> So, you'll have to process each cursor to completion sequentially

Yes, that's what I gathered.

Your alternative is indeed an option, but I was hoping to keep the code a bit nicer and more flexible (with regard to max. number of open cursors).

So, it seems EXEC SQL statements are not capable of doing what I want ... Fair enough, that's what I thought. Is there an alternative though, that would be able to do something like this ?
0
sdstuberCommented:
Yes, you are correct.

EXEC SQL is handy but also has its limits.

You can use occi to build any number of statement handles and pass those around to fetch in parallel.

occi isn't quite as easy to use but doesn't seem too bad.  I'll admit I'm more (much more) of an EXEC SQL guy though myself.  but the oci demo programs should help a lot.

here's an excerpt from the occidml.cpp sample app
{
string sqlStmt = "SELECT author_id, author_name FROM author_tab";
stmt = conn->createStatement (sqlStmt);
ResultSet *rset = stmt->executeQuery ();
try{
while (rset->next ())
{
cout << "author_id: " << rset->getInt (1) << " author_name: "
<< rset->getString (2) << endl;
}
}catch(SQLException ex)
{
cout<<"Exception thrown for displayAllRows"<<endl;
cout<<"Error number: "<< ex.getErrorCode() << endl;
cout<<ex.getMessage() << endl;
}
stmt->closeResultSet (rset);
conn->terminateStatement (stmt);
}

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Infinity08Author Commented:
I did find a glimmer of hope today : cursor variables :

    EXEC SQL BEGIN DECLARE SECTION;
        SQL_CURSOR   cur;
    EXEC SQL END DECLARE SECTION;

    EXEC SQL ALLOCATE :cur;

    EXEC SQL EXECUTE
        BEGIN
            OPEN :cur FOR ...;
        END;
    END-EXEC;

    etc.

They would allow me to declare cursors dynamically, which is what I wanted. Except that they won't let me use my sql string :

    EXEC SQL PREPARE S_SEL FROM :sql;

When I want to open the cursor, it only accepts precompiled SQL or a stored procedure.

So, the small glimmer of hope shot to hell heh.


I wonder why they haven't implemented this ... It would make life so much easier heh.


I'll take a look at occi, but I don't think that's an option for me, as the framework I'm doing this in is EXEC SQL based.


My hopeful mind wants me to keep this question open for a bit longer in case someone has a brilliant idea - I hope you don't mind ...
0
sdstuberCommented:
It's your question, if I haven't answered it for you satisfactorily, then definitely leave it open.  I don't mind.  And, if we're both wrong and there really is a way to create dynamic handlers for dynamic queries then we both learn something.  Off hand I don't know of a way.

Incidentally, are you sure you even need to process your data that way?
There are limits to how many open cursors you can have and each one does consume memory,  if you can process them sequentially, you probably should.
0
Infinity08Author Commented:
>> It's your question, if I haven't answered it for you satisfactorily, then definitely leave it open.

It's not a question of not satisfactorily answering ... I'm just hoping that something will come up ;) If that doesn't happen within the next 24 hours or so, then too bad ... :)


>> There are limits to how many open cursors you can have and each one does consume memory,

This is not intended to have more than 2, 3 (big) cursors open at the same time. I'm well aware of the limitations.

However, being able to implement this iterator like behavior would :

    1) make the rest of the code a LOT easier to read
    2) make it a LOT easier to maintain the code
    3) avoid bugs caused by repetitive coding

Sure, I can do everything the usual way (in fact I already did heh) ... but it would be much nicer the "iterator way".
0
Infinity08Author Commented:
I think it's safe to say I can abandon my hope now heh ;)

Thanks a lot for your help, sdstuber, and my apologies for the delay in closing this question.

Too bad that you confirmed what I feared, but that's the way it is unfortunately :(
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.