Re: OCI question

In article <31fe3177.685147019_at_news.tncnet.com>, Simon Lee <simonl_at_dataworks.com> wrote:
>On 30 Jul 1996 00:32:02 GMT, surman_at_dlsun338.us.oracle.com (Scott
>Urman) wrote:
>
>Hi Scott,
>
>The problem I'm having is not how to get the info, but to figure out
>the most efficient way to allocate memory to store the info for a
>dynamic SQL query. If I get a statement like "select * from table", I
>don't know how many columns will be returned, so I don't know how much
>memory to allocate without doing a lot of little allocations. I could
>do allocate a description structure, call odescr(), allocate a define
>structure, call odefin(), then do it again until I run out of columns,
>but I have 2 problems then: 1. how do I keep track of these structures
>so I can delete them after all my fetching, and 2. it's inefficient
>having to allocate 2 chunks of memory for each column each time I do a
>fetch. It would be nice if the odescr() function returned how many
>columns are in the query.

I wrote a TCL extension which allows the user to connect to oracle and execute sql directly from the commandline. Each time the user creates a connection, I allocate all the memory I might need for a fetch. I calculate this in this way:

#define MAX_NUMBER_OF_SELECT_ROWS 250
#define MAX_ROW_SIZE 256
#define ROWS_PER_FETCH 10

char *columndata[MAX_NUMBER_OF_SELECT_ROWS];

for (row = 0; row < MAX_NUMBER_OF_SELECT_ROWS; row++) {

        columndata[row] = (char *)malloc(MAX_ROW_SIZE * ROWS_PER_FETCH); }

If you have some idea as to the size of the data you are working with, you can narrow down the above #defines. If you have no idea, just make them dynamic and create a routine which auto resizes your memory allocation. You allocate memory for indicator variables, return length and return code in this way also.

After every ROWS_PER_FETCH, I copy the data from my holding storage to where it finally rests. This means that the copy is the only inefficient part of the operation. Luckily this is extremely fast compared to the time it takes oracle to execute a SQL statement (the copy is probably faster than a context switch!).

>Also, when I do a ofetch(), does it only return those columns defined,
>and if so, what happens when I call ofetch again? Can I get the next
>column by call odefin() then ofetch() for each column, or does ofetch
>move on to the next row?

ofetch() works on rows only. You should odefin() all variables you need which odescr() tells you about.

Chris Hafey

PS - The pointers get really tricky, especially when you do multi row fetches. PPS - This scheme worked perfectly for Sybase and ODBC also.

-- 
chafey_at_ecst.csuchico.edu    	http://www.ecst.csuchico.edu/~chafey
Q: Does anyone have a C compiler test suite I can use? 
A: Yes. Unfortunately, it's probably broken. It's hard to tell.