Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

shortly DAO, ADO, ODBC, just do it

DataBase Access

shortly DAO, ADO, ODBC, just do it

by  Cagliostro  Posted    (Edited  )
The DAO and ADO codes are described with using ATL, because using OLE2 api directly is more routines than understandable code.

[color red]using ODBC:[/color]
Code: 
[color blue]#include[/color]<windows.h>
[color blue]#include[/color]<iostream>
[color blue]#include[/color]<string>
[color blue]#include[/color]<sql.h>
[color blue]#include[/color]<sqlext.h>
[color blue]using namespace[/color] std;
[color blue]int[/color] main()
{
  HENV hEnv;
  HDBC hDbc;
  RETCODE rc;
  [color blue]int[/color] iOut;
  [color blue]char[/color] strOut[256];
  [color blue]char[/color] szDSN[256] = "driver={Microsoft Access Driver (*.mdb)};dbq=[c:\\db1.mdb];";
  [color green]
  //dsn samples:
  //"driver={Microsoft Access Driver (*.mdb)};dbq=[f:\\db1.mdb];"
  //"driver={SQL Server};pwd={password there};Server={server name};Database={dbname there}"
  //driver names for different databases:
  //{SQL Server}
  //{Microsoft ODBC for Oracle}
  //{Oracle in oracle9}
  //{Microsoft Access Driver (*.mdb)}
  //{MySQL ODBC 3.51 Driver}
  [/color]

  [color blue]char[/color]* szSql = "select * from table1";
  rc = SQLAllocEnv(&hEnv);
  rc = SQLAllocConnect(hEnv, &hDbc);

  rc = SQLDriverConnect(hDbc, NULL, (unsigned char*)szDSN, 
      SQL_NTS, (unsigned char*)strOut, 
      255, (SQLSMALLINT*)&iOut, SQL_DRIVER_NOPROMPT);
  {
    [color blue]int[/color] ival;
    [color blue]char[/color] chval[128];
    [color blue]int[/color] ret1;
    [color blue]int[/color] ret2;
    HSTMT hStmt;
    rc = SQLAllocStmt(hDbc,&hStmt);
    rc = SQLPrepare(hStmt,(unsigned char*)szSql, SQL_NTS);[color green]//1
    //rc = SQLBindCol(hStmt, tab_column, tr_type, tr_value, tr_len, len_or_ind);[/color]
    rc = SQLBindCol(hStmt, 1, SQL_C_ULONG, &ival, 4, (SQLINTEGER*)& ret1);
    rc = SQLBindCol(hStmt, 2, SQL_C_CHAR, chval, 128, (SQLINTEGER*)&ret2);
    rc = SQLExecute(hStmt);[color green] //2[/color]
    [color green]
    //if you have queries like drop/create/many update... 
    //instead of //1 //2 and //3 you could use
    //rc=SQLExecDirectA(hStmt,(unsigned char*)szSql,SQL_NTS);
    [/color]
    cout<< ">table:"<< endl;
    [color blue]while[/color](1) [color green]//3[/color]
    {
      rc = SQLFetch(hStmt);
      [color blue]if[/color](rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)[color blue]break[/color];
      cout<< "{"<< ival<<"}{"<< chval<< "}"<< endl;
    }
    rc=SQLFreeStmt(hStmt, SQL_DROP);
  }
  rc = SQLDisconnect(hDbc);
  rc = SQLFreeEnv(hEnv);
  [color blue]return[/color] 0;
}
//using ADO
Code: 
[color blue]#import[/color] "G:\Program Files\Common Files\System\ADO\msado15.dll"
[color blue]#include[/color] "Debug\msado15.tlh"
[color blue]#include[/color]<atlbase.h>
[color blue]#include[/color]<iostream>

[color blue]using namespace [/color]std;

[color blue]int[/color] main()
{
    CoInitialize(0);
    {
        HRESULT hr = -1;
        CComPtr<IUnknown> cnnobj;
        CComPtr<IUnknown> rdsnobj;
        CComPtr<IUnknown> errobj;
        hr = cnnobj.CoCreateInstance(L"ADODB.Connection.2.7");
        ADODB::_Connection* cnn;
        ADODB::_Recordset* rds;
        ADODB::Error* err;
        BSTR berr;
        ADODB::Fields* fds;
        ADODB::Field* fd1;
        ADODB::Field* fd2;

        
        hr = cnnobj->QueryInterface(&cnn);
        hr = cnn->Open(L"driver={Microsoft Access Driver (*.mdb)};dbq=[c:\\db1.mdb];", L"", L"", -1);
        
        hr = rdsnobj.CoCreateInstance(L"ADODB.Recordset.2.7");
        hr = rdsnobj->QueryInterface(&rds);

        hr = errobj.CoCreateInstance(L"ADODB.Error.2.7");
        hr = errobj->QueryInterface(&err);

        hr = rds->put_ActiveConnection(_variant_t(cnn));
        hr = rds->Open(_variant_t("select * from Table1"), _variant_t(cnn),  ADODB::adOpenUnspecified, ADODB::adLockUnspecified, -1);

        VARIANT_BOOL eof;
        VARIANT_BOOL bof;
        hr = rds->MoveFirst();
        hr = rds->get_Fields(&fds);
        hr = fds->get_Item(_variant_t(L"X"), &fd1);
        hr = fds->get_Item(_variant_t(L"Y"), &fd2);
        BSTR namex;
        BSTR namey;
        hr = fd1->get_Name(&namex);
        hr = fd2->get_Name(&namey);

        cout<< "filends count: "<< fds->GetCount()<< endl;
        wcout<< L"<"<< namex<< L"><"<< namey<< L">"<< endl;
        VARIANT valx, valy;
        [color blue]while[/color]([color blue]true[/color])
        {
            hr = rds->get_EOF(&eof);
            hr = rds->get_BOF(&bof);
            [color blue]if[/color](eof)[color blue]break[/color];
            [color blue]if[/color blue](bof)[color blue]break[/color];
            fd1->get_Value(&valx);
            fd2->get_Value(&valy);
            wcout<< L"{"<< ([color blue]wchar_t[/color]*)_bstr_t(_variant_t(valx))<< L"}{";
            wcout<< ([color blue]wchar_t[/color]*)_bstr_t(_variant_t(valy))<< L"}"<< endl;

            hr = rds->MoveNext();
        }
        hr = rds->Close();
        hr = cnn->Close();

    }
    CoUninitialize();
    [color blue]return[/color] 0;
}
[color red]using DAO[/color]
Code: 
[color blue]#import[/color] "G:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"
[color blue]#include[/color] "Debug\dao360.tlh"
[color blue]#include[/color]<atlbase.h>
[color blue]#include[/color]<iostream>

[color blue]using namespace[/color] std;

[color blue]int[/color] main()
{
    CoInitialize(0);
    {
        HRESULT hr = -1;
        CComPtr<IUnknown> app;
        DAO::_DBEngine* eng;
        DAO::Workspace* wr;
        DAO::Database* db;
        DAO::Recordset* rs;
        VARIANT_BOOL eof;
        VARIANT_BOOL bof;
        DAO::Fields* fs;
        DAO::_Field* f1;
        DAO::_Field* f2;
        hr = app.CoCreateInstance(L"DAO.DBEngine.36");
        hr = app->QueryInterface(&eng);
        hr = eng->raw_CreateWorkspace(L"TestWorkSpace1", L"Admin", L"", _variant_t(DAO::dbUseJet), &wr);
        [color green]//open dbname, ifexclusive, ifreadonly, connect, retdb[/color]
        hr = wr->raw_OpenDatabase(_bstr_t(L"c:\\db1.mdb"), _variant_t(0), _variant_t(0), _variant_t(L""), &db);
        hr = db->raw_OpenRecordset(_bstr_t(L"select * from Table1"), 
            _variant_t(DAO::dbOpenDynaset), _variant_t(0), _variant_t(2), &rs);
        hr = rs->get_Fields(&fs);
        hr = fs->get_Item(_variant_t(0), &f1);
        hr = fs->get_Item(_variant_t(1), &f2);
        wcout<< L"<"<< (BSTR)f1->Name<< L"><" << f2->Name<< L">"<< endl;
        VARIANT valx, valy;
        [color blue]while[/color]([color blue]true[/color])
        {
            hr = rs->get_EOF(&eof);
            hr = rs->get_BOF(&bof);
            [color blue]if[/color](eof)[color blue]break[/color];
            [color blue]if[/color](bof)[color blue]break[/color];
            f1->get_Value(&valx);
            f2->get_Value(&valy);
            wcout<< L"{"<< (wchar_t*)_bstr_t(_variant_t(valx))<< L"}{";
            wcout<< (wchar_t*)_bstr_t(_variant_t(valy))<< L"}"<< endl;
            hr = rs->MoveNext();
        }

        hr = hr;
       
    }
    CoUninitialize();
    
    [color blue]return[/color] 0;
}
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top