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,191 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Introduction: Dialogs (1) modal - maintaining the database. Continuing from the ninth article about sudoku.   You might have heard of modal and modeless dialogs.  Here with this Sudoku application will we use one of each type: a modal dialog …
In this post we will learn different types of Android Layout and some basics of an Android App.
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.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

730 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