We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

RJV
RJV asked
on
Medium Priority
1,522 Views
Last Modified: 2013-11-20
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)?
Comment
Watch Question

Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Author of the Year 2009

Commented:
>>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).

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 ....
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.