SQLExecute and zero return - OO4O and Wizards(Archived)

Is it a known problem that when executing a delete statement the number of rows returned is always zero. I have tried with update and the number is correct. I know the delete is taking place.

Related

Row not getting updated, No errors thrown

Hi, I am using the Timesten ODBC interface for C to program an application which updates some rows in a timesten DB based on inputs received. It prepares the sql statement using SQLPrepare and then fires the query using SQLExecute. What is happening is sometimes the rows are NOT getting updated even though no errors where thrown. Does the function SQLExecute wait for update statement to complete before returning a success message or is it fire and forget. Is its possible that the queries my application is firing getting timed out , but since the function returned a success message the issue was not caught. What possible precautions can i take to avoid this kind of errors. Any help is appreciated. Thanks.
SQLExecute is completely synchronous; when it returns the target rows (if any) will have been updated. Of course those updates are not visible to queries from any other session until the UPDATE(s) have been committed. I see several possibilities for the behaviour that you report: 1.   You are UPDATEing via one connection and querying via a different connection and you have not committed the updates so the queries do not see the updated rows. 2.   There is some mistake in the UPDATE statement(s) such that it is not actually updating any rows. This could be a SQL issue or a programming issue e.g. related to bind variables etc. 3.   There is some mistake in the queries that you are issuing such that they are not properly finding the correct rows. This could be a SQL issue or a programming issue e.g. related to bind variables etc.What value is returned in the 'pcrow' argument when you call SQLRowCount() against the statement handle used for the UPDATE immediately after SQLExecute returns? When executing DML you should always check SQLRowCount() afterwards to see who many rows were affected. This might give you a clue as to where the problem lies.Chris

ORA - 01002  cursor out of sequence error

Hi,I am working on server application that does array fetches data from Oracle using OCI interface. Recently I faced an issue with the application where OCI return ORA - 01002 cursor out of sequence error on one of the cursor. However, none of the below conditions or cause is true for the cursor when this error hit // *Cause: This error means that a fetch has been attempted from a cursor//         which is no longer valid.  Note that a PL/SQL cursor loop//         implicitly does fetches, and thus may also cause this error.//         There are a number of possible causes for this error, including://         1) Fetching from a cursor after the last row has been retrieved//            and the ORA-1403 error returned.//         2) If the cursor has been opened with the FOR UPDATE clause,//            fetching after a COMMIT has been issued will return the error.//         3) Rebinding any placeholders in the SQL statement, then issuing//            a fetch before reexecuting the statement. It is expected that the server application might receive OCIRollback/OCICommit from the client application in between the array fetches. However. the SQL which is executed on the cursor is like below. There is no FOR UPDATE OF. So I am expecting there should be no impact of rollback on this cursor as per the above reasons for the ORA - 01002 error. Is this an expected behavior SELECT <collist> FROM <tab> WHERE <tab>.<col> < <value> One more thing I noticed is I am still able to call OCIAttrGet with statement attribute OCI_ATTR_NUM_ROWS to get how many rows fetched so far after this error and it returned me the correct no. of rows. Is it safe to call OCIAttrGet after ORA - 01002 error and rely on the result. Is this an expected behavior. Where can I find the documentation explaining this behavior?   Thanks,Sachin
Any chance to look into this. Thanks,Sachin

Cursor & large number of rows

hello !
i have an application wich interrogates my Oracle 8i Databse using OCI, my problem is that the number of rows returned is so large ( the totality of rows is 1 million) , how can i have rows returned by packets (as the same way as results returned by a search tool) 
Hi,
You must be using OCIDefineByPos to bind the buffer to get the result. Allocate the buffer for the number of rows you want and pass this count to OCIStmtFetch. OCI will return that only that many rows, afterwards you can issue again OCIStmtFetch call to get next chunk of result.
You can check how many rows has been fetched using OCIAttrGet call (use OCI_ATTR_ROW_COUNT attribute)
Pankaj 
Thanks !!
but will this allow my database server to respond quickly without scanning all the rows ? 
AFAIK, when you excute query database will know how many rows are there and then whenever you call OCIStmtFetch it will just keep count how many rows returned till this call. It won't scan all rows.

OCI Query: getting size of returned data

Dear OTN experts,
I am teaching myself OCI, and have successfully worked through several simple examples (connecting to database, executing query returning single value, etc.). I'm stumped though on whether it's possible to know the number of rows returned from a query following an OCIStmtExecute. Is there an example that, say, returns several scalar values from a database where the number of rows returned is not known when OCIStmtExecute is invoked? Of course, if I know the number of rows, I can dynamically allocate the correct amount of memory, and fetch the rows into an array of structs. (BTW, I'm using VC++ 6 on WinNT 4.)
I'd be much obliged for any suggestions.
David Jenkins 
I'm by no means an OCI expert. With that
caveat:
I don't believe you can determine the number
of rows directly after an OCIStmtExecute().
What I've been doing is after the execute
describing the result set, allocating a
buffer for each column, and then doing fetch
for each row. I keep doing this until
the fetch call returns a "no data" status.
It is possible to bind an
array for each column, so Oracle can fetch
multiple rows at one time, but even then
you don't know the total number of rows.
null

OCI8: retrieving rowcount after OCIStmtFetch()

I'm using OCI8 with VC++.
As a memory saver, OCIStmtFetch() automatically frees the statement handle after all rows are fetched. (I've verified that behavior in my code, and the Oracle OCI8 documentation for OCIStmtExecute() says that this happens with that call.)
But I am bulk record fetching (100 at a time), and the last iteration I need to know how many (37? 80?) were in that last batch.
Since the statement handle is freed after the last batch, I can't call OCIAttrGet to get the OCI_ATTR_ROW_COUNT attribute of the statement handle. So I have no idea how many of the retrieved rows are valid in the last batch. (For batches prior to the last, I will correctly get row count = 100, 200, 300, etc. What I'm trying to get is a last value of, say, 337. This is easy in OCI7.3, where the "rc" attribute of the Cda_Def structure held the row count even after the fetch was finished.)
Question:
1) How do I get the total row count after last batch of records, given that the statement handle is freed?
or:
2) How do I stop OCI's default behavior of freeing the statement handle when all rows have been retrieved, so I can retrieve the OCI_ATTR_ROW_COUNT attribute of the statement handle?
Any help would be appreciated.
Thanks,
Glen Mazza 
never mind. bug in my code---the handle is not deleted after all rows are fetched.
this posting may be deleted.
GM

Categories

Resources