How to Connect to SQL Server from Visual C++

Programming Articles on TechHowTos.com

In a previous how-to, we wrote a simple “Hello World” application using Visual C++.

In this article, we are going to talk about something a little bit more advanced.

We are going to see how we can connect to SQL Server from a Visual C++ program using an ODBC connection.

Step 1: Let’s start a new project in Visual Studio:

How to Connect to SQL Server from Visual C++ - Article on TechHowTos.com

Step 2: Create a new Visual C++ – Win32 Console Application.

How to Connect to SQL Server from Visual C++ - Article on TechHowTos.com

Step 3: Click on the “Finish” button to start the project.

How to Connect to SQL Server from Visual C++ - Article on TechHowTos.com

Step 4: Add the following code before the int main() function:

#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;

Note that  #include “stdafx.h” is automatically added by the project wizard.

 

Learn more about interesting technology topics in our eBook: “Tech How To’s Vol. 1

Tech How To's Vol. 1 - Technology eBook by Artemakis Artemiou

 

Step 5: Replace your int main() function with the below code, modify the connection string, T-SQL query, variables, etc. accordingly and then  build the solution (press F6 or click on Build–>Build solution).

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();
}

As you can see, in the above code, I have specified the connection string as below:

(SQLWCHAR*)L"DRIVER={SQL Server};SERVER=localhost, 1433;DATABASE=master;Trusted=true;",

With the above connection string, I’m attempting a trusted connection to SQL Server on localhost, port 1433. Also, note that by the time I am using SQLWCHAR I need to insert an L prior to the connection string value.

High Quality SQL Server eBooks by MVP Artemakis Artemiou

 

Furthermore, note that it doesn’t matter if you are trying to connect to a default or named instance of SQL Server. Just make sure you define the server name and the port number.

Also, if you want to connect to SQL Server using a username/password instead of a trusted connection, you can use a connection string like the below:

(SQLWCHAR*)L"DRIVER={SQL Server};SERVER=localhost, 1433;DATABASE=master;UID=username;PWD=password;",

*Note: It is recommended to prefer trusted connections to SQL Server for better security.

The last screenshot illustrates the output when running the application. As you can see in the code, I’m executing the query:

SELECT @@VERSION

Here’s the output:

How to Connect to SQL Server from Visual C++ - Article on TechHowTos.com

This was a simple example on how you can connect to SQL Server from Visual C++ and run a simple query.

You can easily modify this code in order to suit your needs.

If you want to see an example of retrieving more than one column using queries against SQL Server, you can find more information in this MSDN article.

Drop me a line if you have any feedback on this how-to!

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (18 votes, average: 5.00 out of 5)

Loading...

Reference: TechHowTos.com (http://www.techhowtos.com)

© TechHowTos.com

Share this!
About Artemakis Artemiou 30 Articles
Artemakis Artemiou is a Senior SQL Server and Software Architect, Author, and a former Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Artemakis's official website can be found at aartemiou.com.