Re: CDatabase CRecordset - How to get the number of rows?

Roy,

You are an absolute genius!

I replaced this line - rs.GetFieldValue((long)0,vtval);

with this line - rs.GetFieldValue((short)0,vtval,SQL_C_SLONG);

And now it works great! I think in my naivety I was trying to get the
GetFieldValue() to return a long, which obviously isnt the right way to do it
(can you tell I'm new to this), however your method works flawlessly! I
breathe a big sigh of relief over here.

Many many thanks for saving my bacon once again! Your a swell guy!

All the very best,

Best Regards,
David



"Roy Fine" wrote:

> David,
>
> pardon the top posting, but:
>
> What is the value of dataStr in the CRecordset::Open call. If dataStr is
> not "Select count(*) from Table" then you will not get the correct results.
>
>
> Have alook at the MSDN docs at the CRecordset::GetFieldValue. there is no
> method that takes a long as the first argument, so the following makes no
> sense :
> > rs.GetFieldValue((long)0,vtval);
> I would recommend changing to something like this:
> rs.GetFieldValue((short)0,vtval,SQL_C_SLONG);
>
> regards
> Roy Fine
>
> "David++" <David@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:3C9091C6-AEFD-47BB-8FF9-A6FDD2878CB5@xxxxxxxxxxxxxxxx
> > Hi Roy,
> >
> > Thanks for your continued help, Here is my code -
> >
> >
> > // Declare our CDatabase object
> > CDatabase dbSQL;
> > dbSQL.SetQueryTimeout(60);
> > BOOL bSuccess;
> >
> > // Declare a long to hold the row count for the fast method
> > long rowcountSQL = 0;
> > // Declare a long to hold the row count for the slow method
> > long count = 0;
> >
> > try
> > {
> > bSuccess = dbSQL.Open(connect);
> > }
> >
> > catch(CDBException)
> > {
> > // DB Connection Failure
> > //SetFailureBoolean(0);
> > //SetMessageString(0);
> > }
> >
> > if(bSuccess)
> > {
> > try
> > {
> > // Set failure bool to false
> > // This is used to control user messages i.e. success / fail
> > //SetFailureBoolean(1);
> >
> > // Declare and open our Recordset
> > CRecordset rs(&dbSQL);
> > rs.Open(CRecordset::forwardOnly,dataStr,CRecordset::readOnly);
> > CDBVariant vtval;
> > rs.GetFieldValue((long)0,vtval);
> > rowcountSQL = vtval.m_lVal;
> >
> > /*
> > //This can be used for debugging to a file
> > char buffer[10];
> > _ltoa(rowcountSQL, buffer, 10);
> >
> > FILE *f;
> > f = fopen("Debug.txt", "w");
> > fprintf(f, buffer);
> > fclose(f);
> > */
> >
> >
> > // This is the slow method to count the rows
> > // This works but is too slow
> > /*
> > count = 0;
> > while(!rs.IsEOF())
> > {
> > count +=1;
> > rs.MoveNext();
> > }
> > */
> >
> > // Close Recordset
> > rs.Close();
> >
> > }
> > catch(CDBException)
> > {
> > // SQL Error
> > //SetFailureBoolean(0);
> > //SetMessageString(1);
> > }
> > }
> >
> > dbSQL.Close();
> > // Here I pass back the row count to the calling function
> > return(rowcountSQL);
> > }
> >
> > Many thanks for any advice,
> >
> > Best Regards,
> > David
> >
> > "Roy Fine" wrote:
> >
> > > David,
> > >
> > > show the connection (CDatabase object) and the CRecordset open and fetch
> > > code. I have a couple of Oracle instances that I can test against -
> > >
> > > One Note - we might be well advised to get the snippet working in a
> minimal
> > > MFC app, then move it to a DLL when all is working. Let's debug one
> step at
> > > a time.
> > >
> > >
> > > regards
> > > Roy Fine
> > >
> > >
> > > "David++" <David@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > news:ACDC8E0B-E87F-4682-881B-E9B362285E09@xxxxxxxxxxxxxxxx
> > > > Hello Again,
> > > >
> > > > Damn, I thought I had nailed this one. Looks like I spoke too soon as
> > > usual.
> > > > I'm now testing my DLL with the real database. Its an Oracle SQL
> Database.
> > > > I'm able to connect to it and perform my slow algorithm (yes, back to
> that
> > > > one) and it returns around 87,000 rows which is correct. However, as
> you
> > > are
> > > > aware it takes too long to fetch all those rows one at a time. For
> some
> > > > reason the fast algorithm doesnt work on this new database and returns
> 0
> > > > rows. Everything is the same as before and I've only changed the
> > > connection
> > > > string to suit this new database. I know I can connect as the slow
> > > algorithm
> > > > eventually returns the result. But how come the fast algorithm returns
> > > zero
> > > > again? I'm pretty sure the ODBC Driver for Oracle is configured etc..
> > > Sorry
> > > > to bother you again, I've been picking away at this all day with no
> > > progress.
> > > >
> > > > Any opinions welcome,
> > > >
> > > > Many Thanks,
> > > > David
> > > >
> > > > "Roy Fine" wrote:
> > > >
> > > > > david,
> > > > >
> > > > > glad to be of assistance....
> > > > >
> > > > > rlf
> > > > >
> > > > >
> > > > > "David++" <David@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > > > news:483AFE5A-6416-40EB-ABEA-C4F84B22F877@xxxxxxxxxxxxxxxx
> > > > > > Hi Roy :-)
> > > > > >
> > > > > > It works now! Thankyou very, very much for your help! If I could I
> > > woyuld
> > > > > > buy you a beer,
> > > > > >
> > > > > > All the best,
> > > > > > David
> > > > > >
> > > > > > "Roy Fine" wrote:
> > > > > >
> > > > > > >
> > > > > > > "David++" <David@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > > > > > news:63FB861B-E7A4-408C-A964-A4DD36A98B16@xxxxxxxxxxxxxxxx
> > > > > > > > Hi Roy,
> > > > > > > >
> > > > > > > > This is the code I have so far -
> > > > > > > >
> > > > > > > > CRecordset rs(&db);
> > > > > > > >
> > > > > > > > rs.Open(CRecordset::forwardOnly,"SELECT * FROM
> > > > > > > Records",CRecordset::readOnly);
> > > > > > > >
> > > > > > >
> > > > > > > NO! -- the SQL command should be "Select Count(*) FROM Recrds"
> > > > > > >
> > > > > > > regards
> > > > > > > Roy
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>
.

Relevant Pages