Re: OCI question

Simon Lee (simonl_at_dataworks.com) wrote:
: On 30 Jul 1996 00:32:02 GMT, surman_at_dlsun338.us.oracle.com (Scott
: Urman) wrote:
:
: :In article <31fd4110.623604205_at_news.tncnet.com>, simonl_at_dataworks.com (Simon Lee) writes:
: :|>
: :|> Is it just me or does Oracle's OCI library suck? I'm writing an
: :|> application, and OCI doesn't even return the number of columns from a
: :|> query. Does anyone know how I can write a "fetch" function that after
: :|> given a SQL statement, I can call fetch(), which will retrieve any
: :|> results pending? The problem I have is trying to figure out how many
: :|> columns there will be, and to allocate memory for the define structure
: :|> per column, without losing performance.
: :|>
: :
: :Use odescr(). It will tell you exactly what Oracle is planning on returning -
: :each column, its datatype, and its size. You then allocate memory to hold this
: :info and point to it using odefin(). Then fetch using ofen() or ofetch().
:
: 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.

It can't. At the time odescr is called the number of rows matching the query is not known yet since the query has not executed yet.

: 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() only returns one column at a time. You typically call it in a loop until ofetch() returns an error when it is out of rows. To do an array fetch use ofen(). You can also combine the oexec() and ofetch() by calling oexfet() which might give you better performance(?).

dave

-- 
David-Michael Lincke
Research Assistant
Institute for Information Management IWI-HSG, University of St. Gallen
EMail:	David-Michael.Lincke_at_iwi.unisg.ch, dlincke_at_sgcl1.unisg.ch
URL:	http://www-iwi.unisg.ch/about/team/dal.html