PeeS290 wrote:
Quote:
How to connect and use SQL database from VC++ program in "MS preferred way"
?
|
I would suggest ADO and COM built-in support. Here is sample program to
get you started. Beware of broken lines.
B.
// This is sample program inserting data to SQL Server database
// using ADO and Visual C++ COM support
//
// The only required parameter is SQL connection string, for example
// "Data Source=.;Integrated Security=SSPI"
//
// I, Bronislaw Kozicki, hereby put this program in public domain
//
#define _CRT_SECURE_NO_WARNINGS
#define STRICT
#define _WIN32_WINNT 0x0500
#define WIN32_LEAN_AND_MEAN
#define NOMINMAX
#include <windows.h>
#include <comdef.h>
#include <iostream>
#include <vector>
#include <string>
#include <stdexcept>
#include <typeinfo>
#import "C:\Program Files\Common Files\System\ado\msado25.tlb" rename(
"EOF", "ADO_EOF" )
class ctrl
{
static volatile long break_;
static volatile long set_;
static BOOL WINAPI handler_(DWORD)
{
::InterlockedExchange(&break_, 1);
return TRUE; // Leave cleanup to me
}
public:
static void initialize()
{
if (::InterlockedExchange(&set_, 1) == 0)
{
::SetConsoleCtrlHandler(&handler_, TRUE);
}
}
static bool done()
{
return ::InterlockedCompareExchange(&break_, 1, 1) == 1;
}
struct interrupt : std::runtime_error
{
interrupt() : std::runtime_error("User interrupt")
{}
};
};
volatile long ctrl::break_ = 0;
volatile long ctrl::set_ = 0;
class data
{
bool commit_;
ADODB::_ConnectionPtr connection_;
ADODB::_CommandPtr command_;
ADODB::_ParameterPtr fields_[3];
enum Fields {id, name, time};
void execute(const std::string& sql)
{
_variant_t ignore;
connection_->Execute(sql.c_str(), &ignore, ADODB::adExecuteNoRecords
| ADODB::adCmdUnspecified);
}
public:
struct Row {
const int id;
const std::wstring name;
const std::wstring time; // yyyy-mm-dd hh:mi:ss.mmm(24h)
};
data(const std::string& connString)
: commit_(false)
{
_com_util::CheckError(connection_.CreateInstance(_ _uuidof(ADODB::Connection)));
_com_util::CheckError(command_.CreateInstance(__uu idof(ADODB::Command)));
connection_->Provider = L"SQLOLEDB";
connection_->Open(connString.c_str(), L"", L"",
ADODB::adConnectUnspecified);
command_->ActiveConnection = connection_;
execute("USE tempdb");
execute(
"CREATE TABLE #data ("
" [id] int NOT NULL PRIMARY KEY, "
" [name] nvarchar(200) NOT NULL, "
" [time] datetime NULL"
")");
command_->PutPrepared(true);
command_->PutCommandText(
"INSERT INTO #data ([id], [name], [time]) "
" SELECT ?, ?, CONVERT(datetime, ?, 121)" // yyyy-mm-dd
hh:mi:ss.mmm(24h)
);
fields_[id] = command_->CreateParameter(L"id", ADODB::adInteger,
ADODB::adParamInput, 0, _variant_t());
command_->Parameters->Append(fields_[id]);
fields_[name] = command_->CreateParameter(L"name", ADODB::adWChar,
ADODB::adParamInput, 200, _variant_t());
command_->Parameters->Append(fields_[name]);
fields_[time] = command_->CreateParameter(L"time", ADODB::adWChar,
ADODB::adParamInput, 24, _variant_t());
command_->Parameters->Append(fields_[time]);
connection_->BeginTrans();
}
void insert(const Row& r)
{
fields_[id]->PutValue(r.id);
fields_[name]->PutValue(r.name.c_str());
fields_[time]->PutValue(r.time.c_str());
command_->Execute(NULL,NULL,NULL);
}
void commit()
{
connection_->CommitTrans();
connection_->BeginTrans(); // Begin new transaction
}
~data()
{
try
{
connection_->RollbackTrans();
}
catch(_com_error)
{;} // We are in destructor, no exceptions are allowed to leak
}
};
int main(int argc, char* argv[])
{
int result = 13;
try
{
if (::CoInitializeEx(NULL, COINIT_MULTITHREADED |
COINIT_DISABLE_OLE1DDE) != S_OK)
throw std::runtime_error("Failed to initialize COM apartment");
std::vector<std::stringargs(argv, argv + argc);
if (args.size() < 2)
{
throw std::runtime_error(
"Please provide at least complete SQL connection string in
\"quotes\". "
"Optional second argument is number of rows to insert"
);
}
std::cout << "Connecting to \"" << args[1] << "\"" << std::endl;
data d(args[1]);
long rows = 10000;
if (args.size() == 3)
{
long r = 0;
if (sscanf(args[2].c_str(), "%lu", &r) == 1)
rows = r;
}
ctrl::initialize();
std::cout << "Inserting " << rows << " rows of data ..." << std::endl;
for (int i = 0; i < rows; )
{
if (ctrl::done())
{
std::cout << "Committing " << i << " rows" << std::endl;
d.commit();
throw ctrl::interrupt();
}
data::Row r = {i, L"blabla", L"2008-12-31 12:20:21.021"};
d.insert(r);
if (!(++i % 1000))
{
std::cout << i << std::endl;
}
}
std::cout << "Done, committing" << std::endl;
d.commit();
result = 0;
}
catch(ctrl::interrupt& e)
{
std::cerr << e.what() << "\n\n";
result = 0;
}
catch(std::exception& e)
{
std::cerr << typeid(e).name() << " : " << e.what() << "\n\n";
result = 1;
}
catch(_com_error& e)
{
_bstr_t descrB = e.Description(); // Keep the temporary
const char* descr = static_cast<const char*(descrB);
if (!descr)
descr = "(no error description available)";
std::cerr << typeid(e).name() << " : " << descr << "\n\n";
result = 2;
}
::CoUninitialize();
return result;
}
Using MS SQL from C++ guidelines 
