how to use setDataBuffer from OCCI for array fetches

Question

I have a query that I'm executing on a database that returns an array of records, I read in the Oracle OCCI documentation you have to use the ResultSet::setDataBuffer() function to fetch array's of data from the db.

I just don't get what I'm supposed to give as the first two args when a database row contains multiple columns of different data? What type should I give as my buffer type?

//example, does not contain all parts, just enough to demonstrate my point
    SELECT AGE, BIRTHDATE, NAME FROM PEOPLE;
    int i[10];  // Type of buffer??? Age is int, but name is a string?
    ResultSet* res;
    res->setDataBuffer(1 /*col index of first col in select statement*/, &i[0], OCCIINT, 10 * sizeof(int));

while(res->next()) { //Fetch data...}

I have searched Google for examples in vain so far. I'm hoping that someone here could help?

Is there a reason why you don't use the OCCI::ResultSet functions getString(), getInt(),... and so on? What are the datatypes of your database columns AGE, BIRTHDAY or NAME? I guess they are simple number, vchars and so on, or not? I could provide an example using the above mentioned methods, if it is any help. — nabulke, Apr 06 '11 at 13:24
@nabulke, that is what I did originally, however it seems it's not fetching all records, it goes through the loop a number of times, each time fetching the same record. But yea, if you provide an example, maybe I've done something wrong. — Tony The Lion, Apr 06 '11 at 13:48

score 2 · Accepted Answer · answered Apr 06 '11 at 14:08

2

As per our comments, I provide a simple example using the getString(),... functions:

// Statement -> stmt
// ResultSet -> res
// Connection -> con

// pseudo code ahead !

stmt = con->createStatement(MY_SELECT, MY_TAG);
stmt->setPrefetchRowCount(NUMBER_OF_PREFETCHES); // enable bulk collect
res = stmt->executeQuery();

while(res->next())
{
    date = res->getDate(INDEX_OF_DATE);
    age = res->getInt(INDEX_OF_AGE);
    name = res->getString(INDEX_OF_NAME);

    // do something with date, age, name...
}

stmt->closeResultSet(res);
con->terminateStatement(stmt);

But I guess this is exactly what you were originally doing?

answered Apr 06 '11 at 14:08

nabulke

11,025
13
65
114

what if the rowcount to be fetched is unknown prior to fetching it? – Tony The Lion Apr 06 '11 at 14:28
the rowcount is almost always unknown before fetching. you just iterate over all returned rows. Why do you need the row count before querying? You can allocate necessary resources inside the while loop. – nabulke Apr 06 '11 at 14:35
so what is NUMBER_OF_PREFETCHES supposed to be then? – Tony The Lion Apr 06 '11 at 14:39
1

That is just the number of rows occi should fetch in one bulk from the database, to reduce roundtrips. So it's kind of an optimization which has nothing to do with the actual number of rows returned in summary. The example should work without the setPrefetchStatement. – nabulke Apr 06 '11 at 14:41

how to use setDataBuffer from OCCI for array fetches

1 Answers1