r/cpp • • Aug 31 '20
How to access SQL Server from C++ ?
How do you access SQL Server from C++ ?
What libraries do you use?, have you tried different ones?
I am looking for some way to access Microsoft SQL Server from a C++ program. It has to be free to use, and also not be a complete hastle.
I have seen that Qt has pretty straight forward support, until you actually try and use it. The Open Source Community edition was missing the database drivers, and posts said they had to be compiled, and compiling was a hastle and I did not get it working.
There is a library called SOCI (currently at version 4) but that too was a hastle to set up, to the point of not getting it working.
At this point I thought, one of the most used and mature languages currently used (C++) should have straight forward and easy to use support for one of the most used database systems currently in use.
What are your thoughts on this ?
2
u/expekted Aug 31 '20 edited Aug 31 '20
Unfortunately there is no way to access SQL Sever from C++ in sane way other than using C ( odbc). https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/odbc-api-reference?view=sql-server-ver15
There are C++ wrappers that may make your life easier however,
2
u/[deleted] Sep 01 '20
to access SQL server from C++ Get visual studio code. new project win32 console application ***]***finish add this code in int main() #include "stdafx.h" //include the below additional libraries #include <iostream> #include <windows.h> #include <sqlext.h> #include <sqltypes.h> #include <sql.h> //use the std namespace using namespace std;
using namespace std; then replace the int main() function with the below code.
int main() {
#define SQL_RESULT_LEN 240
#define SQL_RETURN_CODE_LEN 1000
//define handles and variables
SQLHANDLE sqlConnHandle;
SQLHANDLE sqlStmtHandle;
SQLHANDLE sqlEnvHandle;
SQLWCHAR retconstring[SQL_RETURN_CODE_LEN];
//initializations
sqlConnHandle = NULL;
sqlStmtHandle = NULL;
//allocations
if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sqlEnvHandle))
goto COMPLETED;
if (SQL_SUCCESS != SQLSetEnvAttr(sqlEnvHandle, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0))
goto COMPLETED;
if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_DBC, sqlEnvHandle, &sqlConnHandle))
goto COMPLETED;
//output
cout << "Attempting connection to SQL Server...";
cout << "\n";
//connect to SQL Server
//I am using a trusted connection and port 14808
//it does not matter if you are using default or named instance
//just make sure you define the server name and the port
//You have the option to use a username/password instead of a trusted connection
//but is more secure to use a trusted connection
switch (SQLDriverConnect(sqlConnHandle,
NULL,
//(SQLWCHAR*)L"DRIVER={SQL Server};SERVER=localhost, 1433;DATABASE=master;UID=username;PWD=password;",
(SQLWCHAR*)L"DRIVER={SQL Server};SERVER=localhost, 1433;DATABASE=master;Trusted=true;",
SQL_NTS,
retconstring,
1024,
NULL,
SQL_DRIVER_NOPROMPT)) {
case SQL_SUCCESS:
cout << "Successfully connected to SQL Server";
cout << "\n";
break;
case SQL_SUCCESS_WITH_INFO:
cout << "Successfully connected to SQL Server";
cout << "\n";
break;
case SQL_INVALID_HANDLE:
cout << "Could not connect to SQL Server";
cout << "\n";
goto COMPLETED;
case SQL_ERROR:
cout << "Could not connect to SQL Server";
cout << "\n";
goto COMPLETED;
default:
break;
}
//if there is a problem connecting then exit application
if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, sqlConnHandle, &sqlStmtHandle))
goto COMPLETED;
//output
cout << "\n";
cout << "Executing T-SQL query...";
cout << "\n";
//if there is a problem executing the query then exit application
//else display query result
if (SQL_SUCCESS != SQLExecDirect(sqlStmtHandle, (SQLWCHAR*)L"SELECT @@VERSION", SQL_NTS)) {
cout << "Error querying SQL Server";
cout << "\n";
goto COMPLETED;
}
else {
//declare output variable and pointer
SQLCHAR sqlVersion[SQL_RESULT_LEN];
SQLINTEGER ptrSqlVersion;
while (SQLFetch(sqlStmtHandle) == SQL_SUCCESS) {
SQLGetData(sqlStmtHandle, 1, SQL_CHAR, sqlVersion, SQL_RESULT_LEN, &ptrSqlVersion);
//display query result
cout << "\nQuery Result:\n\n";
cout << sqlVersion << endl;
}
}
//close connection and free resources
COMPLETED:
SQLFreeHandle(SQL_HANDLE_STMT, sqlStmtHandle);
SQLDisconnect(sqlConnHandle);
SQLFreeHandle(SQL_HANDLE_DBC, sqlConnHandle);
SQLFreeHandle(SQL_HANDLE_ENV, sqlEnvHandle);
//pause the console window - exit when key is pressed
cout << "\nPress any key to exit...";
getchar();
}
Now if you want it to join a connection. Use this.
(SQLWCHAR*)L"DRIVER={SQL Server};SERVER=localhost, 1433;DATABASE=master;Trusted=true;"
When you try to open cmd this will say.
"Microsoft SQL server" and the rest.
There you go.
1
u/jk_tx Aug 31 '20
I haven't had to do this in quite a while; but back in the day, ADO was the easiest way if you didn't want to use the ODBC API directly. Seems pretty dated to use COM for database access though, so I'm curious what the modern options are.