Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1366
  • Last Modified:

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
0
Nilesh_Powar
Asked:
Nilesh_Powar
  • 8
  • 2
1 Solution
 
AxterCommented:
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!
0
 
AxterCommented:
Nilesh_Powar,
Your query should be something like the following:

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


David Maisonave :-}
0
 
AxterCommented:
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 :-}
0
Independent Software Vendors: 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!

 
Nilesh_PowarAuthor Commented:
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?
0
 
AxterCommented:
>>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.
0
 
Nilesh_PowarAuthor Commented:
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






0
 
AxterCommented:
I'll have to post it tomorrow, when I have access to some old code.
0
 
AxterCommented:
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
0
 
AxterCommented:
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);
            }
      }
}
0
 
AxterCommented:
This is a better version

void CDeletemeCRecordSetTestView::OnButton1()
{
      HSTMT     hstmt;

      SQLAllocStmt(m_pSet->m_pDatabase->m_hdbc,&hstmt);
      SQLCHAR svSQL[] = "Select TestField1, GroupName from TestTable1";
      int r = SQLExecDirect(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, hstmt, 1, Sqlstate, &NativeErrorPtr, MessageText, BufferLength, &TextLengthPtr);
      }
      else
      {
            CStringArray TestField1_Rows;
            CStringArray GroupName_Rows;
            while(SQLFetch(hstmt) == SQL_SUCCESS)
            {
                  UCHAR svData[8192]={0};
                  SDWORD cbDataLen;
                  SQLGetData(hstmt, 1, SQL_C_CHAR, &svData,8192,&cbDataLen);
                  TestField1_Rows.Add((LPCTSTR)svData);
                  SQLGetData(hstmt, 2, SQL_C_CHAR, &svData,8192,&cbDataLen);
                  GroupName_Rows.Add((LPCTSTR)svData);
            }
      }
}
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now