• C++ Programming for Financial Engineering
    Highly recommended by thousands of MFE students. Covers essential C++ topics with applications to financial engineering. Learn more Join!
    Python for Finance with Intro to Data Science
    Gain practical understanding of Python to read, understand, and write professional Python code for your first day on the job. Learn more Join!
    An Intuition-Based Options Primer for FE
    Ideal for entry level positions interviews and graduate studies, specializing in options trading arbitrage and options valuation models. Learn more Join!

connecting C++ and SQL Server

hi guys,

I did a research on quantnet but I didn't find much.

I'd like to write a class in C++ that can obtain data from a SQL server. I found some info on the internet but some of the resources are not free...

Other info I found is to use C# to download data from the server, but then I don't know how to deliver the data to my c++ code

any idea/links or resources?

Thanks!
 

DominiConnor

Quant Headhunter
Back in the day I had a column in PC Mag about this stuff and then I used ODBC which is a C style library, powerful but not easy to use until you get the hang of the way it does memory, the gold standard in debugging nasty SQL client/server problems but no concept of C++ classes.

I lean towards RDO (Remote data objects) since you get most of OO plus you can still easily drop down to ODBC when you need to diagnose, optimise or make it do something less ordinary.
Once you know their names, the web is full of examples for anything you'd be likely to need.

I'd warn against using MFC, they are really bad and old, really really bad and really really old and bad, can you tell I don't like them ?
MS has disavowed MFC as well.

I would also counsel against the C#/C++ model because what you'd have in effect is:
C# talking to ADO to ODBC to Windows named pipes to sockets to Windows named pipes to SQL Server to SQL to SQL Server to Win64

Every additional layer will cost you a few grey hairs and from my photo you can see that I only have seven black hairs left so unless you want to end up like me, don't add C# to where it isn't needed.
 
In Java you would simply use the corresponding JDBC driver for SQL Server.

While looking for a JDBC equivalent for C++, I came across http://www.sqlapi.com/. If you're just looking to run SQL queries on the database this library would be the simplest solution for you. It is portable across different RDBMSes, but also allows you to access SQL Server-specific features if you need them.

I'm not sure about the performance of this library, but IMO the design of your SQL invocations will always have a greater impact on performance than the way you're connecting to the database.
 
Thank you so much DominiConnor! I appreciate your view very much!

I've been doing some research on the internet but got no result except some microsoft websites that explain what a rdo is, difference between an ado and rdo... do you have any resource I could have?

dazzwater, thanks for your view as well! I also found sqlapi but it seems it's under commercial license

Regards
 
Answer to original question:

include <sqltypes.h>
#include <sql.h>
#include <sqlext.h>

and the procedure call:

SQLHANDLE sql_event;
SQLHANDLE sql_connection;
SQLHANDLE sql_statement;

if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sql_event))
goto FINISHED;

if(SQL_SUCCESS!=SQLSetEnvAttr(sql_event,SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0))
goto FINISHED;

if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_DBC, sql_event, &sql_connection))
goto FINISHED;

SQLCHAR retconstring[1024];
switch(SQLDriverConnect (sql_connection,
NULL,
(SQLCHAR*)connection_string.c_str(),
SQL_NTS,
retconstring,
1024,
NULL,
SQL_DRIVER_NOPROMPT)){
case SQL_SUCCESS_WITH_INFO:
show_error(SQL_HANDLE_DBC, sql_connection);
break;
case SQL_INVALID_HANDLE:
case SQL_ERROR:
show_error(SQL_HANDLE_DBC, sql_connection);
goto FINISHED;
default:
break;
}

if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_STMT, sql_connection, &sql_statement))
goto FINISHED;

// Add SQL SQLBindParameter to pass in variables to SP

if(SQL_SUCCESS!=SQLExecDirect(sql_statement, (SQLCHAR*)"{? = CALL spCloseScenario (?)}", SQL_NTS)){
show_error(SQL_HANDLE_STMT, sql_statement);
goto FINISHED;
}
else{
while(SQLFetch(sql_statement)==SQL_SUCCESS){}

if (SQLMoreResults(sql_statement) != SQL_NO_DATA_FOUND){} // SQL Server does not send back the values for the return code or output parameters until the end of all result sets for the procedure. The program variables for a return statement do not hold the output values until SQLMoreResults returns SQL_NO_DATA_FOUND.
}

FINISHED:
SQLFreeHandle(SQL_HANDLE_STMT, sql_statement );
SQLDisconnect(sql_connection);
SQLFreeHandle(SQL_HANDLE_DBC, sql_connection);
SQLFreeHandle(SQL_HANDLE_ENV, sql_event);
 
Hi all

Thank for your comments and info!! I solved the problem partially with C# but I prefer to use only C++.

I'll have a look at OTL, it looks nice
 
Top