Solved

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

Posted on 2009-07-02
3
1,123 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)?
0
Comment
Question by:RJV
3 Comments
 
LVL 39

Accepted Solution

by:
itsmeandnobodyelse earned 500 total points
ID: 24772515
>>>> 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
 
LVL 49

Expert Comment

by:DanRollins
ID: 24789650
>>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
 
LVL 2

Expert Comment

by:SamratAshok
ID: 25991946

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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction: The undo support, implementing a stack. Continuing from the eigth article about sudoku.   We need a mechanism to keep track of the digits entered so as to implement an undo mechanism.  This should be a ‘Last In First Out’ collec…
If you use Adobe Reader X it is possible you can't open OLE PDF documents in the standard. The reason is the 'save box mode' in adobe reader X. Many people think the protected Mode of adobe reader x is only to stop the write access. But this fe…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now