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,138 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Change to event 1 100
Problem to open Excel file 15 95
x-bar in Google Sheets 2 25
Remove Password Protection from pdf (with password) using an command line executable 5 25
Introduction: Displaying information on the statusbar.   Continuing from the third article about sudoku.   Open the project in visual studio. Status bar – let’s display the timestamp there.  We need to get the timestamp from the document s…
Introduction: Dialogs (2) modeless dialog and a worker thread.  Handling data shared between threads.  Recursive functions. Continuing from the tenth article about sudoku.   Last article we worked with a modal dialog to help maintain informat…
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.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

912 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

22 Experts available now in Live!

Get 1:1 Help Now