Connecting To SQL Server Using C++ ODBC Example

Aim
The aim of this Windows C++ tutorial is to connect to an SQL Server database using native C++ and perform a simple query. We will use a connection string with a DRIVER and not a DSN . This is a fairly simple C++ example and we will use the Windows command line compiler cl.exe. While it is simple, it demonstrates what is required for connecting and retrieving data using C++ and SQL DB, with some error handling too. It is upto the user to furthere explore these functions. Please have a look at SQL Server ODBC C++ Connection Pool for an ODBC connection pool in C++. Leave any questions or problems as comments and I will endeavour to answer them.

Assumptions
This article assumes that you have Microsoft SQL Server 2008 Express edition installed and configured. Please see Installing and Configuring SQL Server 2008 for more details. Also note that SQL Server does not have port 1433 enabled by default. Please see the afore mentioned link on how to do this.


Versions used in this example
Sofware/ComponentImage
Windows XP SP2N/A
VC++ 2008 express EditionN/A
SQL Server 2008 Express EditionN/A
Links to these files can be found here


Write and Compile the Example
  1. Write the client code and save it as MicrosoftSQLExample.cpp
     1. #include <iostream>
     2. #include <windows.h>
     3. #include <sqltypes.h>
     4. #include <sql.h>
     5. #include <sqlext.h>
     6. 
     7. using namespace std;
     8. 
     9. void show_error(unsigned int handletype, const SQLHANDLE& handle){
    10.     SQLCHAR sqlstate[1024];
    11.     SQLCHAR message[1024];
    12.     if(SQL_SUCCESS == SQLGetDiagRec(handletype, handle, 1, sqlstate, NULL, message, 1024, NULL))
    13.         cout<<"Message: "<<message<<"\nSQLSTATE: "<<sqlstate<<endl;
    14. }
    15. 
    16. int main(){
    17. 
    18.     SQLHANDLE sqlenvhandle;    
    19.     SQLHANDLE sqlconnectionhandle;
    20.     SQLHANDLE sqlstatementhandle;
    21.     SQLRETURN retcode;
    22. 
    23.     if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sqlenvhandle))
    24.         goto FINISHED;
    25. 
    26.     if(SQL_SUCCESS!=SQLSetEnvAttr(sqlenvhandle,SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0)) 
    27.         goto FINISHED;
    28.     
    29.     if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_DBC, sqlenvhandle, &sqlconnectionhandle))
    30.         goto FINISHED;
    31. 
    32.     SQLCHAR retconstring[1024];
    33.     switch(SQLDriverConnect (sqlconnectionhandle, 
    34.                 NULL, 
    35.                 (SQLCHAR*)"DRIVER={SQL Server};SERVER=localhost, 1433;DATABASE=MyDatabase;UID=sa;PWD=Admin-123;", 
    36.                 SQL_NTS, 
    37.                 retconstring, 
    38.                 1024, 
    39.                 NULL,
    40.                 SQL_DRIVER_NOPROMPT)){
    41.         case SQL_SUCCESS_WITH_INFO:
    42.             show_error(SQL_HANDLE_DBC, sqlconnectionhandle);
    43.             break;
    44.         case SQL_INVALID_HANDLE:
    45.         case SQL_ERROR:
    46.             show_error(SQL_HANDLE_DBC, sqlconnectionhandle);
    47.             goto FINISHED;
    48.         default:
    49.             break;
    50.     }
    51.     
    52.     if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_STMT, sqlconnectionhandle, &sqlstatementhandle))
    53.         goto FINISHED;
    54. 
    55.     if(SQL_SUCCESS!=SQLExecDirect(sqlstatementhandle, (SQLCHAR*)"select * from testtable", SQL_NTS)){
    56.         show_error(SQL_HANDLE_STMT, sqlstatementhandle);
    57.         goto FINISHED;
    58.     }
    59.     else{
    60.         char name[64];
    61.         char address[64];
    62.         int id;
    63.         while(SQLFetch(sqlstatementhandle)==SQL_SUCCESS){
    64.             SQLGetData(sqlstatementhandle, 1, SQL_C_ULONG, &id, 0, NULL);
    65.             SQLGetData(sqlstatementhandle, 2, SQL_C_CHAR, name, 64, NULL);
    66.             SQLGetData(sqlstatementhandle, 3, SQL_C_CHAR, address, 64, NULL);
    67.             cout<<id<<" "<<name<<" "<<address<<endl;
    68.         }
    69.     }
    70. 
    71. FINISHED:
    72.     SQLFreeHandle(SQL_HANDLE_STMT, sqlstatementhandle );
    73.     SQLDisconnect(sqlconnectionhandle);
    74.     SQLFreeHandle(SQL_HANDLE_DBC, sqlconnectionhandle);
    75.     SQLFreeHandle(SQL_HANDLE_ENV, sqlenvhandle);
    76.     
    77. }
    Hide line numbers

  2. Now open a promt to where you saved the file and compile it. Just a reminder to make sure you have run vcvars32 beforehand or you will have all kinds of issues.

    MicrosoftSQLExample>cl MicrosoftSQLExample.cpp odbc32.lib

24 comments:

Anonymous said...

Can we do same with Sql Server 2000?

Anonymous said...

can we do same with 2005 and 2008

righteous said...

Don't see why not!!

Anonymous said...

Got error when build
" error C2664: 'SQLGetDiagRecW' : cannot convert parameter 4 from 'SQLCHAR [1024]' to 'SQLWCHAR *'
1> Types pointed to are unrelated; conversion requires reinterpret_cast, C-style cast or function-style cast"

grgisme said...

@Anonymous I struggled with the same for a while. You have to set the project to use the Multi-byte character set rather than Unicode. That will resolve your issue.

RyanF said...
This comment has been removed by the author.
Einar Jón said...

Works for me, but can crash/corrupt memory because the SQL handles are not initialized.

Would recommend starting with:
SQLHANDLE sqlenvhandle = SQL_NULL_HANDLE;
SQLHANDLE sqlconnectionhandle = SQL_NULL_HANDLE;
SQLHANDLE sqlstatementhandle = SQL_NULL_HANDLE;

righteous said...

I don't think you have to set SQL_NULL_HNADLE, because they are initialized at the start and then freed at the end using SQLFreeHandle

Wormy said...

@righteous: No they are NOT necessarily initialized... Imagine what happens if the very first alloc fails - the code jumps to FINISHED and there tries to free ALL handles although none of them has been initialized!

righteous said...

I don't think SQLFreeHandle is like 'free', where if it's not allocated it causes a seg fault. It will just return some error like invalid handle and return without any drama.

Anonymous said...

Thanks for the code but....
Using goto statements in C++? What the hell were you thinking? :)

Anonymous said...

Thanks a lot, your code really helped be very much.

righteous said...

I love my BASIC goto statements!

Anonymous said...

A very helpful post. Helped me a lot as I am new to VC++ programming

Anonymous said...

@grgisme Thank's a lot man. You really saved me a lot of time.

Anonymous said...

this line:
if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sqlenvhandle))


fails on my machine ??

Anonymous said...

Ah the common "the reader knows this" problem. Never assume knowledge on part of the reader when making a tutorial. It just makes the newbies frustrated. Including me.

samaneh heidari said...

Hi there,

Thanks for the example.

I tried to run the code, but it returns SQL_ERROR. I tried different connection strings. Here is my code:
SQLDriverConnect (sqlconnectionhandle,
NULL,
(SQLWCHAR*)TEXT("DRIVER={SQL Server};SERVER=localhost, 1433;DATABASE=Northwind;UID=Developer;PWD=;"),
//(SQLWCHAR*)TEXT("Driver={SQL Server Native Client 10.0};Server=localhost;Database=Northwind;Trusted_Connection=yes;"),
//(SQLWCHAR*)TEXT("Data Source=WIN-TVOQMTOTG6J\SQLSERVER;Initial Catalog=Northwind;Integrated Security=True;Pooling=False"),
SQL_NTS,
NULL,
0,
NULL,
SQL_DRIVER_NOPROMPT);

what is wrong with it? I can connect to my DB from management studio. I also can connect to the DB from "visual studio"->server Explorer. If it is helpful, I use visual studio 2010 and SQL server 2008 R2.

I will appreciate your help.

Thanks in advance,

Anonymous said...

This program works but not properly for me. "Select *" statements are limited to only 3 columns, and "Select x" (where x is a column name) returns gibberish. Anyone have a fix?

Anonymous said...

Great information, my thanks to author.

emad al den said...

show message
Message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error.Check your network documentation.
SQLSTATE: 08S01

please solution this problem
Note that I used appserv program

Rich Stevenson said...

emad al den, I get exactly the same error and no matter what I so, I keep getting the same error.

Unknown said...

IS there a change for 2012 SQL Server

Deepti Puri said...

does not take the connection string - run to the case SQLERROR. Do I need to configure something on machine. I can see db registered in ODBC registry. Also for sure, my queries will return more than 3 records. I am not there yet but resolution for the no of queries limited to 3? Please advise. Thanks