sql.cpp

#include <stdlib.h>
#include <iostream>
#include <sstream>
#include <stdexcept>
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>

#define EXAMPLE_HOST "localhost"
#define EXAMPLE_USER "root"
#define EXAMPLE_PASS "root"
#define EXAMPLE_DB "cs360"

using namespace std;

int main(int argc, const char **argv) {
    const string url = EXAMPLE_HOST;
    const string user = EXAMPLE_USER;
    const string pass = EXAMPLE_PASS;
    const string database = EXAMPLE_DB;
    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(url, user, pass);
        /* Connect to the MySQL test database */
        con->setSchema(database);

        stmt = con->createStatement();
        stmt->execute("DROP TABLE IF EXISTS Employee");
        stmt->execute("CREATE TABLE Employee ("
        "Id INT NOT NULL AUTO_INCREMENT,"
        "Name VARCHAR(200),"
        "Salary INT,"
        "DepartmentId INT,"
        "PRIMARY KEY (Id));");
        delete stmt;

        pstmt = con->prepareStatement("INSERT INTO Employee (Name, Salary, DepartmentId) VALUES (?, ?, ?),(?, ?, ?)");
        pstmt->setString(1, "Joe");
        pstmt->setInt(2, 70000);
        pstmt->setInt(3, 1);
        pstmt->setString(4, "Henry");
        pstmt->setInt(5, 80000);
        pstmt->setInt(6, 2);
        pstmt->executeUpdate();
        delete pstmt;

        /* Select in ascending order */
        pstmt = con->prepareStatement("select * from Employee");
        res = pstmt->executeQuery();

        /* Fetch in reverse = descending order! */
        res->afterLast();
        while (res->previous()) {
            cout << res->getInt("Id") << " " <<
                    res->getString("Name") << " " <<
                    res->getInt("Salary") << " " <<
                    res->getInt("DepartmentId") << 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 0;
}