Using MS SQL from C++ guidelines

  • Thread starter Thread starter PeeS290
  • Start date Start date
P

PeeS290

Hi all,

Where can I find guidelines about programming SQL based applications in VC++
?
Since MS SQL Server 2008 is strongly promoted, I cannot understand why there
is no help for developers.
There is so many technologies using SQL, but no clear guidelines which one
to use.
Last one I tried is ODBC, but I don't know if I should stick to it.
How to connect and use SQL database from VC++ program in "MS preferred way"
?
Last time when I put such a question to discussion long time ago, everyone
had his own way, and nobody was really happy about it.
Has anything changed in this matter ?

Regards
Pawel
 
PeeS290 said:
How to connect and use SQL database from VC++ program in "MS preferred way"
?
Click to expand...


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(__uuidof(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::string> args(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;
}
 
Back
Top