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/Component	Image
Windows XP SP2	N/A
VC++ 2008 express Edition	N/A
SQL Server 2008 Express Edition	N/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