OCI problem in getting back results from SQL query

From: Cooper <cooper_at_linuxfan.com>
Date: Fri, 03 Dec 1999 11:22:15 +0100
Message-ID: <384799D7.97DC2704_at_linuxfan.com>

People, I'm trying to get OCI to process an SQL statement and to get the results of that statement back into the program. Seemingly basic stuff, but as it turns out, I can't get it to work.

Let me start by saying that if anybody would be willing to send me some sample OCI code that performs a SELECT statement and puts the resulting dataset in some sort of variable, I'd be highly appreciative.

The systems I'm testing this on are a Sun Ultra5 with Oracle 8.0.5 and RedHat Linux with Oracle 8.0.5.

My code, simplified, looks like this:
OCIInitialise
OCIEnvInit

OCIHandleAlloc(Error)
OCIHandleAlloc(Server)
OCIHandleAlloc(ServerContext)

OCIServerAttach
OCIAttrSet(ServerContext,Server)
OCIHandleAlloc(Session)
OCIAttrSet(Session,Username)
OCIAttrSet(Session,Passwd)
OCISessionBegin
OCIAttrSet(ServerContext,Session)
OCIHandleAlloc(Statement)
OCIStmtPrepare
OCIStmtExecute(iter=0)
OCIAttrGet(PARAM_COUNT)

Now to this point everything is fine and dandy and the paramcount I'm getting back is correct for each query so I assume the query is in fact correctly executed on the server...

i=1;
while (i<=PARAM_COUNT) {

OCIDefineByPos(stmt,&def,errh,i,store->data,BUF_MAX,SQLT_STR,&indicator,

                       NULL,NULL,OCI_DEFAULT);
        }

That stuff also doesn't produce any errors. Note that the store->data has been allocated as char* at BUF_MAX size and is different for each loop, but the OCIDefine (def) is the same variable each time as is the indicator.
The database only has tables with columns of type number(X) or type varchar2(X).
Since I don't know in advance what the statement actually is, I need to define after performing the query. I can't find sample code for such situations. If anybody has any...?
According to the documentation the store->data variable should hold the contents of column number i in string form after performing a Fetch.

OCIStmtFetch(stmt,errh,1,OCI_FETCH_NEXT,OCI_DEFAULT)

Now the store->data of each column should hold the data of the first row, right?
This statement executes without producing an error. The query should produce an OCI_SUCCESS_WITH_INFO to indicate that only one row was selected which it does. However when I printf("%s",store->data) I get a bit of garbage (when I'm lucky) and the thing segfaults. Does anybody have any insights as to what I'm doing wrong?

Also, for future development, does it matter if you're doing an insert, update, delete or select in the way that you process the statement? Naturally only the select results in a table that I need to process, but aside from that?

Thanks for whatever help you can provide.

Tom Wirschell

-- 
Life is unfair, but the root password helps...
    - Taken from The Bastard Operator From Hell -

Received on Fri Dec 03 1999 - 11:22:15 CET