Sunday, August 11, 2013

Using MariaDB with C++ on Debian Linux

I have started to learn more about MariaDB and C++. MariaDB is another version or improved version of MySQL, but it is supported by some other programmers.

Below are the steps for setting up MariaDB on Debian Linux:

$ aptitude install python-software-properties
apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db

$ add-apt-repository 'deb http://mirror2.hs-esslingen.de/mariadb/repo/5.5/debian wheezy main'

$ aptitude update
$ aptitude install mariadb-server

$ aptitude install libmariadbclient-dev

$ aptitude install libmariadbd-dev

$ aptitude install mariadb-client

$ aptitude install libmysqlcppconn-dev

$ aptitude install g++-mingw-w64

$ aptitude install mingw-w64



Add the iptables rules to use MariaDB locally:

iptables -A INPUT -p tcp --sport 3306 \
  --src \
  127.0.0.1 \
  --dst \
  127.0.0.1 \
  -j ACCEPT

iptables -A INPUT -p tcp --dport 3306 \
  --src \
  127.0.0.1 \
  --dst \
  127.0.0.1 \
  -j ACCEPT

iptables -A OUTPUT -p tcp --sport 3306 \
  --src \
  127.0.0.1 \
  --dst \
  127.0.0.1 \
  -j ACCEPT

iptables -A OUTPUT -p tcp --dport 3306 \
  --src \
  127.0.0.1 \
  --dst \
  127.0.0.1 \
  -j ACCEPT


iptables -A INPUT -p tcp \
  --dport 3306 \
  -j DROP
iptables -A INPUT -p tcp \
  --sport 3306 \
  -j DROP


If it is convenient to change 3306 to another number such as 3307, 3308, 4444 or any other valid number to avoid common attacks, then do

$ nano /etc/mysql/my.cnf

Change 3306 to 4444, for example.

Save the C++ code in p.c:

$ nano p.c

$ Ctrl + x

C++ Code:

/* Copyright 2008, 2010, Oracle and/or its affiliates. All rights reserved.

This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; version 2 of the License.

There are special exceptions to the terms and conditions of the GPL
as it is applied to this software. View the full text of the
exception in file EXCEPTIONS-CONNECTOR-C++ in the directory of this
software distribution.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
*/

/* Standard C++ includes */
#include
#include

/*
  Include directly the different
  headers from cppconn/ and mysql_driver.h + mysql_util.h
  (and mysql_connection.h). This will reduce your build time!
*/
#include "mysql_connection.h"

#include
#include
#include
#include
#include

using namespace std;

int main(void)
{
cout << endl;
cout << "Let's have MySQL count from 10 to 1..." << endl;

try {
  sql::Driver *driver;
  sql::Connection *con;
  sql::Statement *stmt;
  sql::ResultSet *res;
  sql::PreparedStatement *pstmt;

  /* Create a connection */
  driver = get_driver_instance();
  con = driver->connect("tcp://127.0.0.1:4444", "root", "whateverPassword");
  /* Connect to the MySQL test database */
  con->setSchema("test");

  stmt = con->createStatement();
  stmt->execute("DROP TABLE IF EXISTS test");
  stmt->execute("CREATE TABLE test(id INT)");
  delete stmt;

  /* '?' is the supported placeholder syntax */
  pstmt = con->prepareStatement("INSERT INTO test(id) VALUES (?)");
  for (int i = 1; i <= 10; i++) {
    pstmt->setInt(1, i);
    pstmt->executeUpdate();
  }
  delete pstmt;

  /* Select in ascending order */
  pstmt = con->prepareStatement("SELECT id FROM test ORDER BY id ASC");
  res = pstmt->executeQuery();

  /* Fetch in reverse = descending order! */
  res->afterLast();
  while (res->previous())
    cout << "\t... MySQL counts: " << res->getInt("id") << endl;
  delete res;

  delete pstmt;
  delete con;

} catch (sql::SQLException &e) {
  cout << "# ERR: SQLException in " << __FILE__;
  cout << "(" << __FUNCTION__ << ") on line "
     << __LINE__ << endl;
  cout << "# ERR: " << e.what();
  cout << " (MySQL error code: " << e.getErrorCode();
  cout << ", SQLState: " << e.getSQLState() <<
     " )" << endl;
}

cout << endl;

return EXIT_SUCCESS;
}



Please make sure that you use the same port number in iptables and C++.

To use g++, do

$ g++ \
-finput-charset=utf-8 \
-fexec-charset=utf-8 \
-fwide-exec-charset=utf-8 \
-lmysqlcppconn \
-g p.c -o p \
`mysql_config --cflags --libs`




To run the program, do

$ ./p


I can use MariaDB with C++. Great!


References:

https://downloads.mariadb.org/mariadb/repositories/

http://dev.mysql.com/doc/refman/5.1/en/connector-cpp-examples-complete-example-2.html

No comments:

RSS