Home » Developer & Programmer » Precompilers, OCI & OCCI » occi resultset row count
occi resultset row count [message #128474] Mon, 18 July 2005 17:01 Go to next message
ssalmehe
Messages: 5
Registered: July 2005
Junior Member
Hi,

How do you exactly find out how many rows have been returned by an execute of an sql select statement. I get the resulset. I would like to know then how many rows have been returned from the query. I want to throw an error if no rows or more than one row were retuned. I understand that I can have a while(resultset->next()) and have a counter but I would like to know before I scroll through. Can you help? Thanks!

Report message to a moderator

Re: occi resultset row count [message #128904 is a reply to message #128474] Thu, 21 July 2005 02:51 Go to previous messageGo to next message
palanivel_pk
Messages: 4
Registered: July 2005
Location: chennai
Junior Member


Use the cursor for the following sql

'select count(*) from <tablename> where <condition-if any>

and fetch the value to an integer variable


Hope this would solve your problem

Vel

Report message to a moderator

Re: occi resultset row count [message #133137 is a reply to message #128904] Wed, 17 August 2005 16:12 Go to previous messageGo to next message
ssalmehe
Messages: 5
Registered: July 2005
Junior Member
Thanks, but I don't want to do an extra query.

I want to do:
"select blah from tableA where <condition>"

then I get the recordset and I would like to know
how many rows have been returned before going through
each row. ADO, ODBC have such features. I am not sure
how to do it using occi.

can I also find out if it returned any rows at all
without doing while(rs->next())?

thanks!

Report message to a moderator

Re: occi resultset row count [message #134497 is a reply to message #133137] Thu, 25 August 2005 04:55 Go to previous messageGo to next message
Michael Hartley
Messages: 110
Registered: December 2004
Location: West Yorkshire, United Ki...
Senior Member
Hi

You don't necessarily get all rows returned when a select is executed.

The Oracle library fetches row results in buffer size gulps. This allows you to select an almost unlimited number of rows irrespective of how much memory you have on your client.

If you are hoping to allocate sufficient space in a local array for all returned arrays then you need to reconsider. I would suggest you either dynamically increase the size of the array in blocks of N rows at a time or process the returned database rows differently.

Kind regards
Michael Hartley
http://www.openfieldsolutions.co.uk

Report message to a moderator

Re: occi resultset row count [message #134739 is a reply to message #134497] Fri, 26 August 2005 10:04 Go to previous messageGo to next message
ssalmehe
Messages: 5
Registered: July 2005
Junior Member
Hi Michael,

Thanks for your reply. I understand what you explained, but I would like to know if some rows(zero or some) have returned or not. I need to know right away after I get the resultset if it's an empty resultset without going into a while loop and examining a counter's value. Is there a way to do that?

Thanks!!

Report message to a moderator

Re: occi resultset row count [message #134833 is a reply to message #134739] Sat, 27 August 2005 15:22 Go to previous messageGo to next message
Michael Hartley
Messages: 110
Registered: December 2004
Location: West Yorkshire, United Ki...
Senior Member
Hi

got you now.

see demo oci\samples\cdemo81.c

in particular

--cut
      if ((status = OCIStmtExecute(svchp, stmthp1, errhp, (ub4) 1, (ub4) 0,
               (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT))
          && (status != OCI_NO_DATA))
      {
        checkerr(errhp, status);
        cleanup();
        return OCI_ERROR;
      }
      if (status == OCI_NO_DATA)
        printf("The dept you entered doesn't exist.\n");
      } while (status == OCI_NO_DATA);

--cut

In particular you should consider status = OCIStmtExecute because OCI_NO_DATA is the key to your problem.
Laughing

Kind regards,
Michael Hartley, http://www.openfieldsolutions.co.uk

Report message to a moderator

Re: occi resultset row count [message #135443 is a reply to message #128474] Wed, 31 August 2005 10:27 Go to previous messageGo to next message
ssalmehe
Messages: 5
Registered: July 2005
Junior Member
Michael,

Thanks for your solution, but I am using occi instead of oci api. I need something with occi. Sad

Report message to a moderator

Re: occi resultset row count [message #136019 is a reply to message #135443] Mon, 05 September 2005 07:45 Go to previous messageGo to next message
kmohan
Messages: 28
Registered: July 2005
Junior Member
select count(*) is the only way (other than scrolling through all the records) to know how many records are there in a full resultset.
If all that you need is to find out one row's existence, why don't you have your logic something like this:
first call to rs->next() should not return END_OF_FETCH
second call to rs->next()should return END_OF_FETCH.

Report message to a moderator

Re: occi resultset row count [message #136203 is a reply to message #136019] Tue, 06 September 2005 13:04 Go to previous messageGo to next message
ssalmehe
Messages: 5
Registered: July 2005
Junior Member
I need to know if there is at least one row or not.. and then go through rs->next() and do the logic........ I guess there's not a straightforward way of doing this Sad

But, Thanks for all of your help!!

Report message to a moderator

Linking Problem :- OCCI/C++/linux(Redhat) please Help [message #150965 is a reply to message #128474] Mon, 12 December 2005 05:56 Go to previous messageGo to next message
manav.sah
Messages: 15
Registered: September 2005
Location: India
Junior Member
hi! i m new in OCI/OCCI...
i m trying to compilea simple occi/c++ program in redhat linux 9 with oracle 9i.
i m compiling the program as

g++ ./occi1.cc -I/opt/ora9/product/9.2/rdbms/demo/ -I/opt/ora9/product/9.2/rdbms/public/ -c

and linking as :-

g++ ./occi1.o /opt/ora9/product/9.2/lib/libocci9.a /opt/ora9/product/9.2/lib/libclntsh.so

[./occi1.o(.gnu.linkonce.t._ZN7occidmlC1ESsSsSs+0x20): In function `occidml::occidml[in-charge](std::basic_string<char, std::char_traits<char>, std::allocator<char> >, std::basic_string<char, std::char_traits<char>, std::allocator<char> >, std::basic_string<char, std::char_traits<char>, std::allocator<char> >)':
: undefined reference to `oracle::occi::Environment::createEnvironment(oracle::occi::Environment::Mode, void*, void* (*)(void*, unsigned), void* (*)(void*, void*, unsigned), void (*)(void*, void*))'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `cerr'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `__pure_virtual'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `exception type_info function'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `__cp_push_exception'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `endl(ostream &)'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `__uncatch_exception'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `__out_of_range(char const *)'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `__rtti_user'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `__ctype_toupper'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `__rtti_si'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `__check_eh_spec'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `__throw'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `ostream::operator<<(char const *)'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `terminate(void)'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `__start_cp_handler'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `__cp_pop_exception'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `exception type_info node'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `__builtin_vec_new'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `__eh_rtime_match'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `__length_error(char const *)'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `__builtin_vec_delete'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `exception virtual table'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `__rtti_class'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `__builtin_delete'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `__builtin_new'
/opt/ora9/product/9.2/lib/libocci.so: undefined reference to `__eh_alloc'
collect2: ld returned 1 exit status



pls help me to get over these error...
and guide me how to link the occi program...
is there any other file needed..
thanks
Best Regards
Manav Sah

Report message to a moderator

Re: occi resultset row count [message #150968 is a reply to message #150965] Mon, 12 December 2005 06:13 Go to previous message
kmohan
Messages: 28
Registered: July 2005
Junior Member
it is a bad idea to change the title of an existing thread. Please post your question as a new topic. I am reverting the title of this thread to what it was earlier.

Report message to a moderator

Previous Topic: OCCI Error !( ORA-24392 ERROR)
Next Topic: Linking problem in OCCI/C++ program
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 23 03:49:41 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.