Executing a variety of sql statements??

What I need to be able to do is execute a number of queries across a relational database ( held in sql server 7 ). I can create projects and dialogs for these projects that connect to a single table / view or use a stored procedure and get the results back. I have been modifying the ::GetDefaultSQL() method to attach say a stored procedure, but this is very limiting. Do I have to create new classes for each query/ table or stored procedure that I need data from?? or is there a neat way to specify from which source your data is required and return possibly just a Recordset/ResultSet?? I have read a number of books on databases using VC++ but all they contain is a number of basic examples and no indepth real-world examples.
simloxAsked:
Who is Participating?
 
vachoohoConnect With a Mentor Commented:
You need dynamic binding of fields or as simple decision
you need GetFieldValue() function of CRecordset (suppose).

Overwrite GetDefaultSQL() as you needed:
{
   return _T("EXEC sp_some_stored_procedure");
}
or
{
   return _T("SELECT * FROM TableName");
}
or use full sql statement in Open() method. In latter case you do not need to overwrite GetDefaultSQL()

This code works even on non-derived CRecordset - thus you will not need class wizard.

CRecordset rs(&DataBase);
rs.Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT Field1, Field2 FROM Table");

CString strValue1, strValue2;
rs.GetFieldValue("Field1", strValue1);
rs.GetFieldValue("Field2", strValue2)

rs.Close();

If you need recordset data in non-string form you can use
the other version of GetFieldValue() with CDBVariant & as argument.

Also this way of recordset use allow you to specify complex queries for recordset which is not always possible using class wizard architecture



0
 
PinTailCommented:
just CUSTOMIZE DoFieldExchange:

example:

switch(m_eCurrentQry)
{
      
      case eSPSelectItemOneOnly
      {
            //{{AFX_FIELD_MAP(YourDataClass)
            pFX->SetFieldTypeCFieldExchange::outputColumn);
            RFX_Long(pFX, "ItemOne", m_itemOne);
            //}}AFX_FIELD_MAP
      }
      break;
      case eSPSelectItemTwoOnly
      {
            //{{AFX_FIELD_MAP(YourDataClass)
            pFX->SetFieldType(CFieldExchange::outputColumn);
            RFX_Long(pFX, "ItemTwo", m_itemTwo);
            //}}AFX_FIELD_MAP
      }
      break;
}

0
 
simloxAuthor Commented:
Could somebody expand on this answer and possibly clarify some of the other points in the question..
0
 
PinTailCommented:
>>Could somebody expand on this answer

What sort of expansion do you want ??

>>and possibly clarify some of the other points in the question..

You only asked two closely related questions, which in fact is a single question

>>Do I have to create new classes for each query/ table or stored procedure that I need data from??

No

>>or is there a neat way to specify from which source your data is required and return possibly just a Recordset/ResultSet??

I am unclear exactly what this part means, but I refer you to my previous answer.

In that answer I though it was pretty clear than a single CRecordSet derived class can actually manipulate any number of result set formats by simply defining multiple FieldMaps.

In that example I presumed that you would notice that on some occassions I bind to a return column called "ItemOne", and on other occasions I bind to "ItemTwo".

Presumably this would be the result of issuing a different SQL statement.

Note that I use a data member to 'switch' between the two result set types.  This data member would presumably be set before you issued the differing SQL statement.

>>I have been modifying the ::GetDefaultSQL() method to attach say a stored procedure, but this is very limiting.

In what way is this limiting, this corresponds directly to what you are trying to acheive, namely having a single CRecordSet derived class manipulate differing result sets.

I would add that the whole point of a CRecordSet is that it is tailored to a specific result set, not the way you appear to be wanting to use it.  I would also say; however, that I do exactly this, since I have many SPs which, for various reasons, return slight variations in the result set.

Let me know if you need more help
0
 
simloxAuthor Commented:
vachooho thanks.. and thanks to pintail for his expanded answer.. both have helped
0
All Courses

From novice to tech pro — start learning today.