Retrieving an entire table row for row? [message #2472] |
Thu, 18 July 2002 11:53 |
Martin Joncker
Messages: 2 Registered: July 2002
|
Junior Member |
|
|
I am writing a c++ program required to access an oracle db, I am using the proc precompiler, I need to go through a table on row at a time, from first to last, how do I go through a table one row at a time and how do I know when the entire table has been scanned?
this is the last thing I have tried:
(10 being used because I do not know how to find the last row in the table)
while (number<=10)
{
EXEC SQL SELECT Field1, Field2, Field3, Field4, etc
INTO :extract_rec INDICATOR :extract_rec_ind
FROM COMBINED7
WHERE rownum = :number;
cout << "SELECT statement: successfully excecuted" << endl;
{
extract xs(extract_rec, extract_rec_ind);
cout << number <<" "<< xs;
}
Thanks
Martin
|
|
|
Re: Retrieving an entire table row for row? [message #2490 is a reply to message #2472] |
Fri, 19 July 2002 10:40 |
ctg
Messages: 146 Registered: July 2002
|
Senior Member |
|
|
Your current way will not work because rownum deals with the # of the row in the output. as soon as you say where rownum = 2, you will get no rows returned.
You need to use a cursor. 4 steps to cursor processing:
1) declare the select stmt you want to execute (not sure the exact syntax for C++, but this works in C)
EXEC SQL DECLARE cursorname CURSOR FOR
select stmt;
2) execute the select stmt. this will build a result set that you can then process through.
EXEC SQL OPEN cursorname;
3) retrieve the next row into your ws variables. this is done in a loop. continue the loop while SQLCODE=0.
EXEC SQL FETCH cursorname
INTO :extract_rec INDICATOR :extract_rec_ind;
4) close the result set
EXEC SQL CLOSE cursorname;
|
|
|