Data base interaction programming 1

[IanStrange]

Hi
I am working on an app for work and I have done quite a bit of investigation into ADO in VC++ 6 and have got a record set with data in. However I can not seem to find the information to get this data out of the record set.
It is an ADORecordset with methods such as
Rs1->MoveNext();
Rs1->Open(Blah,blah,blah);
any help on this would be great.

Our aim is to populate structures with data. Manipulate that data then write it back over the top of the old data.
Am I going about things the right way.

Thanks for any help
Ian

No man is an island but 6 tied together make quite a good raft.

 

[Cagliostro]

There are three different methods of accessing databases, ODBC, DAO, ADO:

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

    char* szSql = &quot;select * from table1&quot;;
    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);
    {
        int ival;
        char chval[128];
        int ret1;
        int ret2;
        HSTMT hStmt;
        rc = SQLAllocStmt(hDbc,&hStmt);
        rc = SQLPrepare(hStmt,(unsigned char*)szSql, SQL_NTS);//1
        //rc = SQLBindCol(hStmt, tab_column, tr_type, tr_value, tr_len, len_or_ind);
        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); //2
        //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);
        cout<< &quot;>table:&quot;<< endl;
        while(1) //3
        {
            rc = SQLFetch(hStmt);
            if(rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)break;
            cout<< &quot;{&quot;<< ival<<&quot;}{&quot;<< chval<< &quot;}&quot;<< endl;
        }
        rc=SQLFreeStmt(hStmt, SQL_DROP);
    }
    rc = SQLDisconnect(hDbc);
    rc = SQLFreeEnv(hEnv);
    return 0;
}

//using ADO
#import &quot;G:\Program Files\Common Files\System\ADO\msado15.dll&quot;
#include &quot;Debug\msado15.tlh&quot;
#include<atlbase.h>
#include<iostream>

using namespace std;

int main()
{
    CoInitialize(0);
    {
        HRESULT hr = -1;
        CComPtr<IUnknown> cnnobj;
        CComPtr<IUnknown> rdsnobj;
        CComPtr<IUnknown> errobj;
        hr = cnnobj.CoCreateInstance(L&quot;ADODB.Connection.2.7&quot;);
        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&quot;driver={Microsoft Access Driver (*.mdb)};dbq=[c:\\db1.mdb];&quot;, L&quot;&quot;, L&quot;&quot;, -1);
        
        hr = rdsnobj.CoCreateInstance(L&quot;ADODB.Recordset.2.7&quot;);
        hr = rdsnobj->QueryInterface(&rds);

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

        hr = rds->put_ActiveConnection(_variant_t(cnn));
        hr = rds->Open(_variant_t(&quot;select * from Table1&quot;), _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&quot;X&quot;), &fd1);
        hr = fds->get_Item(_variant_t(L&quot;Y&quot;), &fd2);
        BSTR namex;
        BSTR namey;
        hr = fd1->get_Name(&namex);
        hr = fd2->get_Name(&namey);

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

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

    }
	CoUninitialize();
	
	return 0;
}
//using DAO
#import &quot;G:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll&quot;
#include &quot;Debug\dao360.tlh&quot;
#include<atlbase.h>
#include<iostream>

using namespace std;

int 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&quot;DAO.DBEngine.36&quot;);
        hr = app->QueryInterface(&eng);
        hr = eng->raw_CreateWorkspace(L&quot;TestWorkSpace1&quot;, L&quot;Admin&quot;, L&quot;&quot;, _variant_t(DAO::dbUseJet), &wr);
        //open dbname, ifexclusive, ifreadonly, connect, retdb
        hr = wr->raw_OpenDatabase(_bstr_t(L&quot;c:\\db1.mdb&quot;), _variant_t(0), _variant_t(0), _variant_t(L&quot;&quot;), &db);
        hr = db->raw_OpenRecordset(_bstr_t(L&quot;select * from Table1&quot;), 
            _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&quot;<&quot;<< (BSTR)f1->Name<< L&quot;><&quot; << f2->Name<< L&quot;>&quot;<< endl;
        VARIANT valx, valy;
        while(true)
        {
            hr = rs->get_EOF(&eof);
            hr = rs->get_BOF(&bof);
            if(eof)break;
            if(bof)break;
            f1->get_Value(&valx);
            f2->get_Value(&valy);
            wcout<< L&quot;{&quot;<< (wchar_t*)_bstr_t(_variant_t(valx))<< L&quot;}{&quot;;
            wcout<< (wchar_t*)_bstr_t(_variant_t(valy))<< L&quot;}&quot;<< endl;
            hr = rs->MoveNext();
        }

        hr = hr;
       
    }
    CoUninitialize();
	
	return 0;
}

Ion Filipski

ICQ: 95034075
AIM: IonFilipski
filipski@excite.com

 

[IanStrange]

Thank you very much. I have been searching for weeks in books and on microsofts web site for something in plain simple code like you have supplied and found nothing.


No man is an island but 6 tied together make quite a good raft.