wxAutomation and ADO work well together

If you have a cool piece of software to share, but you are not hosting it officially yet, please dump it in here. If you have code snippets that are useful, please donate!
Post Reply
RichardW
Earned a small fee
Earned a small fee
Posts: 14
Joined: Fri Apr 25, 2014 4:45 am

wxAutomation and ADO work well together

Post by RichardW »

I struggled and struggled with this and finally got it going. I'd like to share what I've done and hopefully it'll help someone avoid the hours of crashing code. (SetDispatchPtr(NULL)?!?!? ... Really?)

This set of classes references the ADODB automation objects. This sample uses an Access Database though any ADO backend should work. Maybe.

I'm using wxWidgets 3.0 and Mingw.

I encapsulated the calls in nice braindeadedly-written classes for ease of reading and because I'm not very good at it. You should be able to glean data thusly:

Code: Select all

Connection conn;
RecordSet r;
r.Open("select * from sometable",&conn);
// Equivalent to:
// r.Open("sometable",&conn);
while (!r.eof()) {
  // Get data by field name.
  wxMessageBox(r.GetValue("somefieldname");
  // or by field index...
  // wxMessageBox(r.GetValue(0));
  r.MoveNext();
}
Likewise, to update the record ADO-style, rather than an INSERT, you could do something like,

Code: Select all

Connection conn;
RecordSet r;
r.Open("select * from sometable",&conn,adOpenStatic,adLockOptimistic);  // Default is readonly.
r.NewData("somefieldname","Some Data");  // Why isn't this called SetValue?  I'll change that.
r.Update();
For those of you that have used ADO (or DAO, for that matter) can see that there's a bit to do still. .AddNew and .MoveFirst/Last are conspicuously missing. They should be easy to implement (he says without knowing any better) so I'll add them as they are required. For most of them, it shouldn't be much more than adding

Code: Select all

void RecordSet::MoveFirst() {
    r.CallMethod("MoveFirst");
}
wxWidgets makes it easy. If anyone has a use for this please post your improvements (Error checking?) here or provide a link to what you did.

Richard


db.h

Code: Select all

#ifndef _DB_H_
#define _DB_H_

#include "wx/wxprec.h"
#ifndef WX_PRECOMP
#include "wx/wx.h"
#endif
#include "wx/msw/ole/automtn.h"

//Cursor Types
const int adOpenForwardOnly=0;
const int adOpenDynamic=2;
const int adOpenKeyset=1;
const int adOpenStatic=3;

//Locks
const int adLockBatchOptimistic=4;
const int adLockOptimistic=3;
const int adLockPessimistic=2;
const int adLockReadOnly=1;

class Connection {
  private:
    wxString connstr;
    wxVariant Result;
  public:
    wxAutomationObject conn;
    Connection();
    ~Connection();
    void Open ();
    wxVariant Execute(wxString);
    void Close();
    bool isConnected();
    
};


class RecordSet {
    private:
        wxAutomationObject fld;
        wxVariant Result;
    public:
        wxAutomationObject r;
        
        RecordSet();
        bool eof();
        // Without parameters Open uses adOpenForwardOnly cursor and adLockReadOnly lock.
        void Open(wxString,Connection*);
        void Open(wxString,Connection*,int,int);
        wxString GetValue(int);
        wxString GetValue(wxString);
        void MoveNext();
        void Close();
        void Edit();
        void NewData(wxString,wxString);
        void Update();
};

#endif
db.cpp

Code: Select all

#include "db.h"

Connection::Connection() {
    conn.GetInstance("ADODB.Connection");
    conn.PutProperty("CursorLocation",wxVariant(2)); // 3=Client, 2=Server-Default
    connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\\temp\\inventory.accdb;Persist Security Info=False;";
}

void Connection::Open() {
    conn.PutProperty("ConnectionString",connstr);
    conn.CallMethod("Open");
}

Connection::~Connection() {
    conn.SetDispatchPtr(NULL);
}

void Connection::Close() {
    conn.CallMethod("Close");
}

bool Connection::isConnected() {
    Result=conn.GetProperty("State");
    if (Result.GetLong()==1) // Connected
        return true;
    else
        return false;
}

RecordSet::RecordSet() {
}

void RecordSet::Open(wxString qryStr,Connection *conn) {
    r.GetInstance("ADODB.Recordset");
    Result=r.CallMethod("Open",wxVariant(qryStr),wxVariant((IDispatch*)conn->conn.GetDispatchPtr()));
}

void RecordSet::Open(wxString qryStr,Connection *conn,int CursorType, int Lock) {
    r.GetInstance("ADODB.Recordset");
    Result=r.CallMethod("Open",wxVariant(qryStr),wxVariant((IDispatch*)conn->conn.GetDispatchPtr()),wxVariant(CursorType),wxVariant(Lock));
}

bool RecordSet::eof() {
    if (r.GetProperty("EOF").GetType()!="bool" || r.GetProperty("BOF").GetType()!="bool")
        return true;
    else if (r.GetProperty("EOF").GetLong()==0 && r.GetProperty("BOF").GetLong()==0) 
        return false;
    else
        return true;
}

wxString RecordSet::GetValue(int col) {
    Result=r.GetProperty("Fields",wxVariant(col));
    fld.SetDispatchPtr(Result.GetVoidPtr());
    Result=fld.GetProperty("Value");
    return Result.GetString();
}

wxString RecordSet::GetValue(wxString col) {
    Result=r.GetProperty("Fields",wxVariant(col));
    fld.SetDispatchPtr(Result.GetVoidPtr());
    Result=fld.GetProperty("Value");
    return Result.GetString();
}

void RecordSet::MoveNext() {
    r.CallMethod("MoveNext");
}

void RecordSet::Edit() {
    r.CallMethod("Edit");
}

void RecordSet::NewData(wxString fieldname,wxString value) {
    Result=r.GetProperty("Fields",wxVariant(fieldname));
    fld.SetDispatchPtr(Result.GetVoidPtr());
    fld.PutProperty("Value",wxVariant(value));
}

void RecordSet::Update() {
    r.CallMethod("Update");
}

void RecordSet::Close() {
    r.CallMethod("Close");
    r.SetDispatchPtr(NULL);
}
Post Reply