//###################################################################################
//#     name: none   (Visual C++ v6.0 code for Windows, using MFC for SQL)
//# modified: 2005-04-21 17:00
//#  assumes: myString set, other requirements shown in code
//#   output: finds "Description" for "Filename" specified in SQL line
//#    usage: part of Visual C++ program
//###################################################################################


// also needed are "USE MFC" choice selected from Preferences, General tab
#include <afx.h>
#include <odbcinst.h>
#include <afxdb.h>

{
  FILE *fp;
  char mySql[300];
  char myString[500], *p;
  int i=1;
  int altSet=0;

  WORD cbBufMax = 2000;
  WORD cbBufOut;
  char szBuf[2001];
  char *pszBuf = szBuf;

  CDatabase database;
  CString sSql, s1, s2;
  CString sDescription=_T("Text");
  CString sFieldname=_T("Text");
  CString sDsn;
  CString sDriver="";
  CString sFile = "c:\\robert\\db\\robertbody.mdb";   // or *.xls
  #define ACCESS_OR_EXCEL                  "Access"   // or "Excel"

  // myString below is a filename setup elsewhere in the code
  sprintf ( mySql, "SELECT * FROM Table1 WHERE Filename = '%s'", myString );
  sSql = mySql;
 
  // ---------------------- setup of database - start ---------------------
  // get names of installed drivers
  if ( !SQLGetInstalledDrivers (szBuf, cbBufMax, &cbBufOut) ) {
	  sDriver="";
  }
  else {
    do {
      if ( strstr(pszBuf, ACCESS_OR_EXCEL ) != 0 ) {  
        sDriver=CString(pszBuf);
        break;
      }
      pszBuf = strchr(pszBuf, '\0') +1;
    }
    while (pszBuf[1]!='\0')
	  ;
  }

  if ( sDriver.IsEmpty() ) {
    AfxMessageBox("NO ODBC driver found!");
    return;
  }
  // ---------------------- setup of database - end ---------------------

  sDsn.Format ("ODBC;DRIVER={%s};DSN='';DBQ=%s", sDriver, sFile );

  TRY {

    database.Open ( NULL, false, false, sDsn );
    CRecordset recset(&database);

    printf ("sSql=...%s...\n",sSql);
    recset.Open ( CRecordset::forwardOnly, sSql, CRecordset::readOnly );

    // loop through all records found
    while ( !recset.IsEOF() )  {
      recset.GetFieldValue("Description", s1 );
      recset.GetFieldValue("Filename", s2 );

      // conversion needed for strcpy() below
      // ... converting from (CString s1) to  (char altDescpription[])
      sDescription=s1;
      sFieldname=s2;

      altSet=1;
      break;
 
      //recset.MoveNext();          // if multiple records to consider, move to next

    }

    // match found, copy data
    if ( altSet )
      strcpy ( altDescription, sDescription.GetBuffer(0) );
        
      // AfxMessageBox (altDescription);    // debug showing of what was read

      // close database
      database.Close();
  }
  
  // problem with database .. eg reading a record though none was found
  //                          or reading a non-existant field (misspelled)
  CATCH (CDBException, e )  {
    AfxMessageBox (":) Database error: " + e->m_strError );
  }
  END_CATCH;

}