How to use CDatabase's ExecuteSQL to find the record or row and then get the result, such as in CRecordSet

I have used ExecuteSQL to find a record or row in a table. Then I'd like to get the result such as in CRecordSet. As such I passed the CDatabase pointer after finding the record but when I read the data in CRecordSet I did not get the right record number (or table row number). What must I do to get the correct record number, such as using ExecuteSQL (which overall is much faster)?
RJVAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

itsmeandnobodyelseCommented:
>>>> as using ExecuteSQL (which overall is much faster)?
ExecuteSql is much faster because it runs asynchronously. That doesn't help if you want to fetch data.

If you want to fetch data you should call CRecordSet::Open using a table name (if you want to real all records of a table) or supplying a select statement into the m_strSql member of the recordset. After the Open was successful you could use the MoveXXX functions to navigate thru the recordset.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DanRollinsCommented:
>>What must I do to get the correct record number, such as using ExecuteSQL...
You need to make sure that your SELECT statement includes a valid WHERE clause, typically specifying a primary index key.
I agree that there is no performance hit to using CRecordset objects.  One exception is for simple ad-hoc queries that get a single value like is
   SELECT sText FROM CodeToTextLookupTable WHERE sCode='8F3'
For such one-shot queries, the overhead of setting up a CRecordset is not needed (though even there, the time lost is negligible).
0
SamratAshokCommented:

I will give you a solution that I'd use (assuming that db is oracle). For other Databases, you'd have to
make corresponding changes.

In ExecuteSQL, rewrite to obtain a return val or returnable OUT parameter in which your executed query will return relevant ROWID (Oracle)... (If this is difficult or you have more than one row, consider
a temp table where all rowids will be stored for sometime.) Either way, ExecuteSQL has to identify
the target rows with their rowid and allow rowids to remain persistent.

Once you have rowid, prepare a recordset with correct m_strSQL to go straight to the desired record(s).

I'd also advice you to dig into CRecordset class source (at leisure!) to see how it access database ... You might learn a new way to use ExecuteSQL deep inside somewhere ....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.