MySQL Connector C++ Example - Windows cl.exe (VC++)

Aim
The aim of this tutorial is to create a simple client that uses MySQL Connector C++ to connect to a MySQL database. We will be using the windows command line compiler cl.exe that comes with Visual Studio C++ for this example.Leave any questions or problems as comments and I will endeavour to answer them.

Assumptions
This article assumes that you have the MySQL database installed and running. purposes of this example the the project directory is MySQLConnectorCPPExample. Also you have VC++ installed and configured. See Problems and Solutions Installing VC++ Express Edition, to install and run vcvars32.bat.

Versions used in this example
Sofware/ComponentImage
Windows XP SP2N/A
MySQL Databasemysql-noinstall-5.1.32-win32.zip
MySQL Connector C++mysql-connector-c++-noinstall-1.0.5-win32.zip
Visual Studio Express Editions 2008 VC++N/A
Links to these files can be found here

MySQL is installed (expanded) in the "D:\downloads\mysql\mysql-5.1.32-win32" for this example. Also MySQL Connector C++ is expanded in the "D:\downloads\mysql-connector-c++-noinstall-1.0.5-win32" directory. Please remember to substitute your own directories for these. You can have a look at the  MySQL Connector C++ Database Pool once you become familiar with this.


Download and unzip or install the MySQL Connector C++
  1. Download and install or unzip MySQL Connector C++
  2. `
  3. For the purposes of this example I have a 'test' database running with a table called 'example' in MySQL. I have included an excerpt of the dump below for completeness.
     1. CREATE TABLE `example` (
     2.   `id` int(11) DEFAULT NULL,
     3.   `data` varchar(100) DEFAULT NULL
     4. ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
     5. SET character_set_client = @saved_cs_client;
     6. 
     7. --
     8. -- Dumping data for table `example`
     9. --
    10. 
    11. LOCK TABLES `example` WRITE;
    12. /*!40000 ALTER TABLE `example` DISABLE KEYS */;
    13. INSERT INTO `example` VALUES (1,'one'),(2,'two'),(3,'three');
    14. /*!40000 ALTER TABLE `example` ENABLE KEYS */;
    15. UNLOCK TABLES;
    Hide line numbers

Write and compile the Client
  1. Write the client and save it as mysqlexample.cpp in your working directory.
     1. #include <stdlib.h>
     2. #include <iostream>
     3. 
     4. #include <cppconn/driver.h>
     5. #include <cppconn/exception.h>
     6. #include <cppconn/resultset.h>
     7. #include <cppconn/statement.h>
     8. #include <cppconn/prepared_statement.h>
     9. 
    10. int main(){
    11. 
    12.     sql::Driver *driver;
    13.     sql::Connection *con;
    14.     sql::Statement *stmt;
    15.     sql::ResultSet *res;
    16.     sql::PreparedStatement *pstmt;
    17.     
    18.     try{
    19.         driver = get_driver_instance();
    20.         con = driver->connect("tcp://127.0.0.1:3306", "root", "root");
    21.         con->setSchema("test");
    22.         
    23.         stmt = con->createStatement();
    24.         stmt->execute("insert into example values(4,'four'),(5, 'five')");
    25.         delete stmt;
    26. 
    27.         pstmt = con->prepareStatement("select * from example");
    28.         res = pstmt->executeQuery();
    29.         while (res->next())
    30.             std::cout<<res->getInt("id")<<"  "<<res->getString("data")<<std::endl;
    31.         delete res;
    32.         delete pstmt;
    33. 
    34.         pstmt = con->prepareStatement("delete from example where id=?");
    35.         pstmt->setInt(1,4);
    36.         pstmt->executeUpdate();
    37.         pstmt->setInt(1,5);
    38.         pstmt->executeUpdate();
    39. 
    40.         delete pstmt;
    41. 
    42.         delete con;    
    43.     }catch(sql::SQLException &e){
    44.         std::cout<<e.what();
    45.     }
    46. }
    Hide line numbers
  2. `
  3. Open a prompt to the working directory and compile the code using the windows cl.exe compiler. Make sure to point '-I' (capital i) option to the include files and to include mysqlcppconn.lib

    ..workspace\MySQLConnectorCPPExample>cl mysqlexample.cpp D:\downloads\mysql-connector-c++-noinstall-1.0.5-win32\lib\mysqlcppconn.lib -I D:\downloads\mysql-connector-c++-noinstall-1.0.5-win32\include
    Substitue your local directories.
  4. `
  5. This should now create the mysqlexample.exe file. However if you run it now you might get some errors saying that some DLLs are missing.
  6. `
  7. Add these paths to your existing path so that the executable can find the required libraries. Again, don't forget to replace the directories with the ones on your local machine. Basically you need to give the 'bin' diretory of your MySQL database installation and the 'lib' directory in your MySQL Connector C++ directory.


    ..\MySQLConnectorCPPExample>set path=%path%;D:\downloads\mysql\mysql-5.1.32-win32\bin

    ..\MySQLConnectorCPPExample>set path=%path%;D:\downloads\mysql-connector-c++-noinstall-1.0.5-win32\lib
  8. `
  9. Now run the mysqlexample.exe and you should see the result. If there are any 'database not found' or 'credential' errors, you need to sort out your database. Make sure the tables are there and the username you are using has access to it.

Back to the tutorial trail | Home

5 comments:

Spyfree Admin said...

Dood .. nice work .. i just wanna exhange the backlinks with u !!
i have added urs .. expecting the same from u !!

awais kamran said...

You have done an excellent job, thank you so much for sharing. C++ in Urdu

Anonymous said...

Nice tut, does it happen to know how to you get the num_fields in this executeQuery style ?

while (res->next())
std::cout<getInt("id")<<" "<getString("data")<<std::endl;

getString() has to know what field in order to get you result if i dont know the name of the field how do i create a loop to get all the results of the database query ?

Silvia Jacinto said...

Life is full of many challenges. Challenges that will make you or break you depending on how you handle it. Visit my site for more updates. God Bless to your site.

n8fan.net

www.n8fan.net

Shravan Kumar said...

db_connection.cpp:35:20: error: invalid use of incomplete type ‘class sql::PreparedStatement’
res = pstmt->executeQuery();
^
In file included from mysql_connection.h:15:0,
from db_connection.cpp:11,
from main.cpp:2:
cppconn/connection.h:39:7: error: forward declaration of ‘class sql::PreparedStatement’
class PreparedStatement;