asked on

Using MFC, CRecordset class running a select query for a particular table in the Database.

I am using Crecordset class wizard & I have recordset class for every table in the database. for eg: I have a table named "SPECIMEN" & by using the wizrad i have a class named CSPECIMEN

consider my recordset class as follows:CSPECIMEN* pSpecimen=new CSPECIMEN(m_db);
So I have already opened the database with m_db & then
try
      {
            //pSpecimen->m_strFilter="Part_Number= '"+ m_Part_Number +"' ";
            pSpecimen->m_strFilter="Part_Number ='" + m_strPart_Number +"' AND Serial_Number= '"+ m_strSerial_Number +"'";
            pSpecimen->m_strSort="Specimen_ID DESC";
            pSpecimen->Open( CRecordset::snapshot,_T( "Select * from Specimen " ));
            while(!pSpecimen->IsEOF())
            {

            pSpecimen->MoveNext();

            }

            pSpecimen->Close();
      }
This query works great with all the results, Now I want to run this query _T("Select DISTINCT Serial_Number from Specimen");
it fails, error in the query,,,so I just want to run a simple query _T("Select Serial_Number from Specimen");that fails.
I think the CSPECIMEN::GetDefaultSQL() & void CSPECIMEN::DoFieldExchange(CFieldExchange* pFX) in the class needs to be altered so that it runs, presently the function is as follows:
CString CSPECIMEN::GetDefaultSQL()
{
      return _T("[dbo].[SPECIMEN]");

}
void CSPECIMEN::DoFieldExchange(CFieldExchange* pFX)
{
      pFX->SetFieldType(CFieldExchange::outputColumn);
// Macros such as RFX_Text() and RFX_Int() are dependent on the
// type of the member variable, not the type of the field in the database.
// ODBC will try to automatically convert the column value to the requested type
      RFX_Date(pFX, _T("[Specimen_ID]"), m_Specimen_ID);
      RFX_Text(pFX, _T("[Part_Number]"), m_Part_Number);
      RFX_Text(pFX, _T("[Serial_Number]"), m_Serial_Number);
      RFX_Text(pFX, _T("[Cycle_Number]"), m_Cycle_Number);
      RFX_Long(pFX, _T("[UT_History_Count]"), m_UT_History_Count);

}

So what should I change so that I can run any select queries?
Nilesh

Axter

Hi Nilesh_Powar,
> >So what should I change so that I can run any select queries?
Please post the code for your select query.

David Maisonave :-)
Cheers!

Axter

Nilesh_Powar,
Your query should be something like the following:

sigmesstb_set.m_strFilter.Format("Message = '%s'", Message);
sigmesstb_set.Requery();

David Maisonave :-}

Axter

Nilesh_Powar,
> >Now I want to run this query _T("Select DISTINCT Serial_Number from Specimen");
If you use a select that limits the quantity of fields, your query is going to fail, because your derive CRecordset class is still looking for all the required fields.

David Maisonave :-}

Nilesh_Powar

ASKER

It makes sense since the CRecordset class is looking for all the fields it fails, does that mean that for every custom select query that I write I would have to make a new Crecordset class with that particular column ?
I did make another Crecordset class for the query & it works, however i didn't like the idea of just making classes for every query, Is there a better way to handle the queries?

Axter

>>does that mean that for every custom select query that I write I would have to make a new Crecordset class with that particular column ?

Yes, if you want to use normal CRecordset logic to retrieve the values.

You could use SQL?? API functions for specific query requirement.
See MSDN info on the following API functions for more specifics:
SQLExecDirect
SQLGetData

FYI:
The SQL API functions take a HSTMT variable for the first argument.
One of CRecordset's data members is m_hstmt, which is of type HSTMT.
You can use an open connection made by a CRecordset class with SQL API functions.

Nilesh_Powar

ASKER

I do agree with ur answer, but I want to try the SQLExecDirect
SQLGetData of the Crecordset to just make sure that it works, there is lot of explanation about it on the MSDN ...

MSDN SAYS:
"The example shows executing a SELECT statement by using SQLExecDirect. It has been simplified by removing all error checking.

The complete sample code is in this file: SELECTWithSQLExecDirect.cpp. You can download a file containing this sample from the Microsoft® SQL Server™ Downloads page at this Microsoft Web site.

This sample was developed for ODBC version 3.0 or later. It was developed with Microsoft Visual C++® version 6.0, and may expose properties of the Microsoft Foundation Classes."

I couldn't find any example nor could I find the SELECTWithSQLExecDirect.cpp, Could U provide a little sample code on running a query using SQLExecDirect &SQLGetData of the Crecordset ? I would definitely appreciate it....

That was a very good suggestion but I want to see it work....

Thanks again,
Nilesh

Axter

I'll have to post it tomorrow, when I have access to some old code.

Axter

Check out the following links:
http://code.axter.com/SQL_Win.h
http://code.axter.com/SQL_Win.cpp

The above files contains a non-MFC database class that I started working on last month, but I haven't had a chance to finish it.

It's basically a wrapper class for the SQL API functions.

I'll post an example usage for SQLExecDirect shortly

Axter

Here's an example function:

void CDeletemeCRecordSetTestView::OnButton1()
{
      int r;
      r = SQLFreeStmt(m_pSet->m_hstmt, SQL_CLOSE); //Close out previous query
      SQLCHAR svSQL[] = "Select TestField1, GroupName from TestTable1";
      r = SQLExecDirect(m_pSet->m_hstmt, svSQL, strlen((char*)svSQL));
      if (r != SQL_SUCCESS)
      {
            SQLCHAR Sqlstate[SQL_MAX_MESSAGE_LENGTH];
            SQLINTEGER NativeErrorPtr;
            SQLCHAR MessageText[SQL_MAX_MESSAGE_LENGTH];
            SQLSMALLINT BufferLength=SQL_MAX_MESSAGE_LENGTH;
            SQLSMALLINT TextLengthPtr=SQL_MAX_MESSAGE_LENGTH;
            SQLGetDiagRec(SQL_HANDLE_STMT, m_pSet->m_hstmt, 1, Sqlstate, &NativeErrorPtr, MessageText, BufferLength, &TextLengthPtr);
      }
      else
      {
            CStringArray TestField1_Rows;
            CStringArray GroupName_Rows;
            while(SQLFetch(m_pSet->m_hstmt) == SQL_SUCCESS)
            {
                  UCHAR svData[8192]={0};
                  SDWORD cbDataLen;
                  SQLGetData(m_pSet->m_hstmt, 1, SQL_C_CHAR, &svData,8192,&cbDataLen);
                  TestField1_Rows.Add((LPCTSTR)svData);
                  SQLGetData(m_pSet->m_hstmt, 2, SQL_C_CHAR, &svData,8192,&cbDataLen);
                  GroupName_Rows.Add((LPCTSTR)svData);
            }
      }
}

ASKER CERTIFIED SOLUTION

Axter

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial