VC++ Database Connection

[Suamere]

SIMPLE Question.. I am using C++, and I want to connect to a database.

Additional (Maybe important):
Please do not ask me questions. I don't care what compiler you tell me to use (VC++, Mac-Based, DevC++, etc), I don't care what database (MS Access, MySQL, SQL Server, etc), I don't care about any of that. If you have a working database connection with a single SQL recordset, I'll take it.

Repeat: I don't CARE What setup your example is in.. I just want to see a working database connection so I at least know it exists. I'll get the compiler you used, on the platform you used it on, with all the setup you have, and I'll use that for the rest of my life if I have to.

I have read the Codeguru FAQ, I have searched online through forums and tutorials for nearly 3 weeks and have attempted every bit of sourcecode imaginable to me, including the MSDN Visual C++ Tutorial on how to create a database connection, which failed miserably because my VC++ 2008 express doesn't seem to have any of the features in that tutorial.

A lil background: I know what I'm doing in anything having to do with web development, and I am also pretty dam good at Visual Basic.

I am attempting C++ because I want the power and memory controls. I am about to throw my computer out the window, though, if I can't create a database connection.

In the 10 other languages I know, I type something like this:

dataConn = Connect kthx;

But for C++ it seems like I have to type a custom 100 module executable just to create a database connection, then the same for each SQL statement I want. I am about to break down.

As far as I can see, the only responses to this question, "How do you connect to a database?" are with more questions, and there are no answers in the FAQ's of any website... so maybe it is as complicated and perhaps even impossible.

[cilu]

Well, there are many articles on CG.
http://www.codeguru.com/cpp/data/mfc_database/

[carl666]

And you didn' find that on MSDN ?!!!
There are dozens of complete samples in MSDN (ODBC, ADO, etc) to connect to a database !!

[Arjay]

See reply #8 in this thread http://www.codeguru.com/forum/showthread.php?t=337819 for an example of using the ATL OLE DB Consumer classes inside an MFC app. The reply includes a sample app with the source and database.

[Suamere]

Great! I believe it's generally working. Thanks for the link to that tutorial. It was incomplete, which confused me a lot, but the zip'd source file had the complete code.

So it's running, but the run is printing an error:
Cannot start your application. The workgroup information file is missing or opened exclusively by another user.

I'm guessing that means it can't find my database? Because it surely isn't open. This is my connection string:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Suamere\\Documents\\Visual Studio 2008\\Projects\\Lordaeron\\Lordaeron.accdb;Persist Security Info=False

Is there a problem with it? I checked the directory using windows RUN, and it opened the DB, so the location IS correct. My database has a table named authors with two columns named au_id and au_fname.

I am assuming the problem might be that this script wasn't written to use MS Access?

Here is my full code:

test.cpp

#include <stdio.h>
#include <iostream>
#include <comdef.h>
#include <conio.h>
#include "Database.h"



//"Provider=SQLOLEDB.1; Persist Security Info=False; User ID=lab;Initial Catalog=pubs;Data Source=PRASUN@CSE";
char CnnStr[200]="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Suamere\\Documents\\Visual Studio 2008\\Projects\\Lordaeron\\Lordaeron.accdb;Persist Security Info=False";
char ErrStr[200];

int main()
{
::CoInitialize(NULL);
Database db;
Table tbl;
if(!db.Open("lab","cse-98",CnnStr))
{
db.GetErrorErrStr(ErrStr);
std::cout<<ErrStr<<"\n";
}
if(!db.Execute("select * from authors order by au_fname,au_id",tbl))
{
db.GetErrorErrStr(ErrStr);
std::cout<<ErrStr<<"\n";
}
/*if(!db.OpenTbl(ADODB::adCmdText,"select * from authors order by au_fname,au_id",tbl))
{
db.GetErrorErrStr(ErrStr);
cout<<ErrStr<<"\n";
}*/
char id[100];
if(!tbl.ISEOF())
tbl.MoveFirst();

while(!tbl.ISEOF())
{
if(tbl.Get("au_id",id))
std::cout<<"\nid:"<<id;
else
{
tbl.GetErrorErrStr(ErrStr);
std::cout<<"\n"<<ErrStr<<"\n";
break;
}
if(tbl.Get("au_fname",id))
std::cout<<" fname:"<<id;
else
{
tbl.GetErrorErrStr(ErrStr);
std::cout<<"\n"<<ErrStr<<"\n";
break;
}

tbl.MoveNext();
}
::CoUninitialize();
return 0;
}

__________________________________
database.h

#define CATCHERROR(ptr,a) catch(_com_error &e)\
{\
ErrorHandler(e,m_ErrStr);\
ptr=NULL;\
return a;\
}

#define CATCHERRGET catch(_com_error &e)\
{\
ErrorHandler(e,m_ErrStr);\
sprintf(m_ErrStr,"%s\n**For Field Name:%s",m_ErrStr,FieldName);\
return 0;\
}

#import "c:\Program Files\Common Files\System\ADO\msado15.dll" \
rename("EOF", "EndOfFile")

typedef ADODB::_RecordsetPtr RecPtr;
typedef ADODB::_ConnectionPtr CnnPtr;

class Database;
class Table;


class Database
{
public:
CnnPtr m_Cnn;
char m_ErrStr[500];
Database();
bool Open(char* UserName, char* Pwd,char* CnnStr);
bool OpenTbl(int Mode, char* CmdStr, Table& Tbl);
bool Execute(char* CmdStr);
bool Execute(char* CmdStr, Table& Tbl);
void GetErrorErrStr(char* ErrStr);
};

class Table{
public:
RecPtr m_Rec;
char m_ErrStr[500];
Table();
void GetErrorErrStr(char* ErrStr);
int ISEOF();
HRESULT MoveNext();
HRESULT MovePrevious();
HRESULT MoveFirst();
HRESULT MoveLast();
int AddNew();
int Update();
int Add(char* FieldName, char* FieldValue);
int Add(char* FieldName,int FieldValue);
int Add(char* FieldName,float FieldValue);
int Add(char* FieldName,double FieldValue);
int Add(char* FieldName,long FieldValue);
bool Get(char* FieldName, char* FieldValue);
bool Get(char* FieldName,int& FieldValue);
bool Get(char* FieldName,float& FieldValue);
bool Get(char* FieldName,double& FieldValue);
bool Get(char* FieldName,double& FieldValue,int Scale);
bool Get(char* FieldName,long& FieldValue);
};

__________________________________
database.cpp

#include <stdio.h>
#include <iostream>
#include <comdef.h>
#include <conio.h>
#include "Database.h"

#define _CRT_SECURE_NO_WARNINGS

ADODB::_RecordsetPtr rec1=NULL;

_variant_t vtMissing1(DISP_E_PARAMNOTFOUND, VT_ERROR);

void ErrorHandler(_com_error &e, char* ErrStr)
{

sprintf(ErrStr,"Error:\n");
sprintf(ErrStr,"%sCode = %08lx\n",ErrStr ,e.Error());
sprintf(ErrStr,"%sCode meaning = %s\n", ErrStr, (char*) e.ErrorMessage());
sprintf(ErrStr,"%sSource = %s\n", ErrStr, (char*) e.Source());
sprintf(ErrStr,"%sDescription = %s",ErrStr, (char*) e.Description());
}

Database:atabase()
{
m_Cnn=NULL;
sprintf(m_ErrStr,"NULL POINTER");
}

void Database::GetErrorErrStr(char* ErrStr)
{
sprintf(ErrStr,"%s",m_ErrStr);
}

void Table::GetErrorErrStr(char* ErrStr)
{
sprintf(ErrStr,"%s",m_ErrStr);
}

bool Database::Open(char* UserName, char* Pwd,char* CnnStr)
{
//cnn->Open(strCnn,"sa","sa",NULL);
try
{
HRESULT hr;
hr = m_Cnn.CreateInstance( __uuidof( ADODB::Connection ) );
m_Cnn->Open(CnnStr, UserName, Pwd, NULL);
}

CATCHERROR(m_Cnn,0)

sprintf(m_ErrStr,"Success");
return 1;
}

bool Database::OpenTbl(int Mode, char* CmdStr, Table &Tbl)
{
if(m_Cnn==NULL)
{
Tbl.m_Rec=NULL;
sprintf(m_ErrStr,"Invalid Connection");
return 0;
}
RecPtr t_Rec=NULL;
try
{
//t_Rec->putref_ActiveConnection(m_Cnn);
//vtMissing<<-->>_variant_t((IDispatch *) m_Cnn, true)
t_Rec.CreateInstance( __uuidof( ADODB::Recordset ) );
t_Rec->Open(CmdStr,_variant_t((IDispatch *) m_Cnn, true),ADODB::adOpenStatic,ADODB::adLockOptimistic,Mode);
}

CATCHERROR(Tbl.m_Rec,0)

Tbl.m_Rec=t_Rec;
sprintf(m_ErrStr,"Success");
return 1;
}

bool Database::Execute(char* CmdStr)
{
try
{
m_Cnn->Execute(CmdStr,NULL,1);
}
catch(_com_error &e)
{
ErrorHandler(e,m_ErrStr);
return 0;
}
sprintf(m_ErrStr,"Success");
return 1;
}

bool Database::Execute(char* CmdStr, Table& Tbl)
{
RecPtr t_Rec=NULL;
try
{
t_Rec=m_Cnn->Execute(CmdStr,NULL,1);
}

CATCHERROR(Tbl.m_Rec,0)

sprintf(m_ErrStr,"Success");
Tbl.m_Rec=t_Rec;
sprintf(m_ErrStr,"Success");
return 1;
}

Table::Table()
{
m_Rec=NULL;
}

int Table::ISEOF()
{
int rs;
if(m_Rec==NULL)
{
sprintf(m_ErrStr,"Invalid Record");
return -1;
}
try{
rs=m_Rec->EndOfFile;
}

CATCHERROR(m_Rec,-2)

sprintf(m_ErrStr,"Success");
return rs;
}

bool Table::Get(char* FieldName, char* FieldValue)
{
try
{
_variant_t vtValue;
vtValue = m_Rec->Fields->GetItem(FieldName)->GetValue();
sprintf(FieldValue,"%s",(LPCSTR)((_bstr_t)vtValue.bstrVal));
}

CATCHERRGET

sprintf(m_ErrStr,"Success");
return 1;
}

bool Table::Get(char* FieldName,int& FieldValue)
{
try
{
_variant_t vtValue;
vtValue = m_Rec->Fields->GetItem(FieldName)->GetValue();
FieldValue=vtValue.intVal;
}

CATCHERRGET

sprintf(m_ErrStr,"Success");
return 1;
}

bool Table::Get(char* FieldName,float& FieldValue)
{
try
{
_variant_t vtValue;
vtValue = m_Rec->Fields->GetItem(FieldName)->GetValue();
FieldValue=vtValue.fltVal;
}

CATCHERRGET

sprintf(m_ErrStr,"Success");
return 1;
}

bool Table::Get(char* FieldName,double& FieldValue)
{
try
{
_variant_t vtValue;
vtValue = m_Rec->Fields->GetItem(FieldName)->GetValue();
FieldValue=vtValue.dblVal;
//GetDec(vtValue,FieldValue,3);
}

CATCHERRGET

sprintf(m_ErrStr,"Success");
return 1;
}

HRESULT Table::MoveNext()
{
HRESULT hr;
try
{
hr=m_Rec->MoveNext();
}
catch(_com_error &e)
{
ErrorHandler(e,m_ErrStr);
//m_Rec=NULL;
return -2;
}
sprintf(m_ErrStr,"Success");
return hr;
}

HRESULT Table::MovePrevious()
{
HRESULT hr;
try
{
hr=m_Rec->MovePrevious();
}
catch(_com_error &e)
{
ErrorHandler(e,m_ErrStr);
//m_Rec=NULL;
return -2;
}
sprintf(m_ErrStr,"Success");
return hr;
}

HRESULT Table::MoveFirst()
{
HRESULT hr;
try
{
hr=m_Rec->MoveFirst();
}
catch(_com_error &e)
{
ErrorHandler(e,m_ErrStr);
//m_Rec=NULL;
return -2;
}
sprintf(m_ErrStr,"Success");
return hr;
}

HRESULT Table::MoveLast()
{
HRESULT hr;
try
{
hr=m_Rec->MoveLast();
}
catch(_com_error &e)
{
ErrorHandler(e,m_ErrStr);
//m_Rec=NULL;
return -2;
}
sprintf(m_ErrStr,"Success");
return hr;
}

[GCDEF]

That is way more code than you need. Way, way, way more.

If you can use MFC, look at the CRecordset class.

[Arjay]

Great! I believe it's generally working. Thanks for the link to that tutorial. It was incomplete, which confused me a lot, but the zip'd source file had the complete code.

I take it you are not referring to my link?

[Suamere]

I take it you are not referring to my link?

There was a link before yours. If this one doesn't work tonight I'll try your link... it does look simpler, lol.

[MJBritton25]

a CDatabase connection can be use to create the ODBC Connection very easily. The default option when creating this will bring up a dialog box allowing you to select an existing ODBC connection already setup as a data source on your PC/Server (depending on how you're deploying the app) - or you can store the DSN (datasource name) as a #def, extract it from a field or hardcode it (just for testing of course :-)

You can use the wizards to create a CRecordset class based on an extant SQL database (SQL Server express will do just fine) using your existing tables - and it will create a class with all the member variables you need (representing the table as presented in SQL).

Once you get it working you'll be amazed at how simple this method is.

[Suamere]

That is way more code than you need. Way, way, way more.
If you can use MFC, look at the CRecordset class.

I'm running into some similar problems again. I tried "Post Number 8" in the thread linked above, and it keeps talking about going to Add Class, then to choose a selection I don't have (In both post 8 and in post 4). The only options I have when I am in the Add Class wizard are:
C++ Class
Windows Form
Component Class

These options are NOTHING like what I am being told to select. If I select C++ Class, it is simply a "type in a name and finish" scree, no options.

So... I did a search for CRecordset, and just got some general syntax pages, I don't know how to implement it or use it. Do you know of a good tutorial or code sample?

[Arjay]

Did you try to load the AccessWOLEDb.zip solution in the link provided?

What version and edition of Visual C++ are you using? (i.e Visual Studio 2008 Professional).

[Suamere]

I'm using VC++ 2008 Express
Microsoft Visual Studio 9.0.30729.1 SP
.NET Framework 3.5 SP1

When using the provided code, None of the includes in stdafx.h can be found.

[Arjay]

I'm using VC++ 2008 Express
Microsoft Visual Studio 9.0.30729.1 SP
.NET Framework 3.5 SP1

When using the provided code, None of the includes in stdafx.h can be found.

In your first post, you mentioned..

Repeat: I don't CARE What setup your example is in..

So I sent you the code for opening an Access database using the ATL Consumer classes in MFC.

Unfortunately the VC++ 2008 Express edition that you have doesn't support MFC. Use a non-Express VC++ edition from 2003 onwards and the code should work fine.

This explains why you don't see the options with regard to inserting an ATL OLEDB consumer class.

[Suamere]

Ah... That makes sense. And makes me mad again, lol. I'll try to get one. Thanks.

[Suamere]

Turns out it is impossible to use a Database with C++ unless you pay for Microsoft's VC++ 2008. Previous versions are no longer available for download from what I've read, and the express editions don't support MFC.

In addition, it appears there is no way to connect to a database using another compiler such as Dev C++, since MFC is apparently the ONLY way to connect to a database.

So sorry to all you C++ Wannabe developers who wanna use a database (the backbone of any application). You can't unless you buy VC++ 2008 from Microsoft.