migration C#, Java, C++ (day 6), MySQL

It is nearly midnight now. 7 minutes are left. Phew, I made it. Good night.
Discipline or stupidity? I guess both 😉
Was fun though. I am achieving my targets.
But now I need something soft, no more hardcore today. Maybe a cushion … Zzzzzzz

MySQL

private void ExecuteNonQuery(MySqlConnection xConnection, string xCommand) {
   MySqlCommand lCommand = xConnection.CreateCommand();
   lCommand.CommandText = xCommand;
   lCommand.ExecuteNonQuery();
} //

private void ExecuteNonQueryAddRow(MySqlConnection xConnection, string xName, DateTime xBirthday) {
   MySqlCommand lCommand = xConnection.CreateCommand();
   lCommand.CommandText = "INSERT INTO test (name, birthdate) values (@name, @birthdate)";
   MySqlParameterCollection lParams = lCommand.Parameters;        
   lParams.AddWithValue("@name", xName);
   lParams.AddWithValue("@birthdate", xBirthday);
   lCommand.ExecuteNonQuery();
} //

private DataTable ExecuteQuery(MySqlConnection xConnection, string xCommand) {
   MySqlCommand lCommand = xConnection.CreateCommand();
   lCommand.CommandText = xCommand;
   MySqlDataAdapter lAdapter = new MySqlDataAdapter(lCommand);
   DataSet lDataSet = new DataSet();
   lAdapter.Fill(lDataSet);
   return lDataSet.Tables[0];
} //

public void MySqlConnectionTest() {
   string lConnectionString = "Server=127.0.0.1;Database=ohta;Uid=root;Pwd=root;";

   try {
      using (MySqlConnection lConnection = new MySqlConnection(lConnectionString)) {
         lConnection.Open();

         //DataTable lSchema = lConnection.GetSchema(); // not required to get or set anything, see lConnectionString             

         // create a new table
         string lField1 = "id INT  NOT NULL AUTO_INCREMENT,";
         string lField2 = "name VARCHAR(40) NOT NULL,";
         string lField3 = "birthdate DATE,";
         string lKey = "PRIMARY KEY (id)";
         string lTableDefinition = lField1 + lField2 + lField3 + lKey;
         ExecuteNonQuery(lConnection, "DROP TABLE IF EXISTS test");               
         ExecuteNonQuery(lConnection, "CREATE TABLE test(" + lTableDefinition + ");");

         // fill the table with values
         ExecuteNonQueryAddRow(lConnection, "George Washington", new DateTime(1732, 2, 22));
         ExecuteNonQueryAddRow(lConnection, "Abraham Lincoln", new DateTime(1809, 2, 12));
         ExecuteNonQueryAddRow(lConnection, "Ronald Reagan", new DateTime(1911, 2, 6));
         ExecuteNonQueryAddRow(lConnection, "Bill Clinton", new DateTime(1946, 8, 19));

         // Query
         DataTable lTable = ExecuteQuery(lConnection, "SELECT * FROM test");
               
         // print column names
         foreach (DataColumn lColumn in lTable.Columns) Console.Write(lColumn.ColumnName + "; ");
         Console.WriteLine();

         // print rows
         int lColumnCount = lTable.Columns.Count;
         foreach (DataRow lRow in lTable.Rows) {
            for (int i = 0; i < lColumnCount; i++) {
               object o = lRow[i];
               if (o is DateTime) {
                  DateTime lDateTime = (DateTime)o;
                  Console.Write(lDateTime.ToString("dd MMM yyyy") + "; ");
                  continue;
               }                     
               Console.Write(o.ToString() + "; ");
            }
            Console.WriteLine();
         }

         // clean up
         ExecuteNonQuery(lConnection, "DROP TABLE IF EXISTS test");               
      }
   }
   catch (Exception ex) {
      Console.WriteLine(ex.Message + Environment.NewLine + ex.StackTrace);
   }
} //      

package Demos;

import java.sql.*;
import java.util.*;

public class MySqlDemo {

  public static Calendar getDate(int xYear, int xMonth, int xDay, int xHour, int xMinute, int xSecond) {
    Calendar lCalendar = Calendar.getInstance();
    lCalendar.clear();
    lCalendar.set(xYear, xMonth - 1, xDay, xHour, xMinute, xSecond);
    return lCalendar;
  } //

  private int ExecuteNonQuery(Connection xConnection, String xCommand) {
    Statement lStatement = null;
    try {
      lStatement = xConnection.createStatement();
      return lStatement.executeUpdate(xCommand);
    } catch (SQLException ex) {
      System.out.println("SqlException: " + ex.getMessage());
    } finally {
      CloseStatement(lStatement);
    }
    return -1;
  } //

  private void ExecuteNonQueryAddRow(Connection xConnection, String xName, Calendar xBirthday) {
    // old school
    //String lBirthday = String.format("%1$tY-%1$tm-%1$te", xBirthday);
    //String lCommand = String.format("INSERT INTO test (name, birthdate) values ('%s', '%s')", xName, lBirthday);
    //int lResult = ExecuteNonQuery(xConnection, lCommand);

    // modern school
    PreparedStatement lStatement = null;
    try {
      java.sql.Date lBirthday = new java.sql.Date(xBirthday.getTimeInMillis());
      lStatement = xConnection.prepareStatement("INSERT INTO test (name, birthdate) VALUES (?,?)");
      lStatement.setString(1, xName);
      lStatement.setDate(2, lBirthday, xBirthday);
      lStatement.executeUpdate();
    } catch (SQLException ex) {
      System.out.println("SqlException: " + ex.getMessage());
    } finally {
      CloseStatement(lStatement);
    }
  } //

  private void CloseStatement(Statement xStatement) {
    if (xStatement == null) return;

    try {
      xStatement.close();
    } catch (SQLException ex) {
      System.out.println("SqlException: " + ex.getMessage());
    }
  } //

  private void CloseStatement(PreparedStatement xStatement) {
    if (xStatement == null) return;

    try {
      xStatement.close();
    } catch (SQLException ex) {
      System.out.println("SqlException: " + ex.getMessage());
    }
  } //

  private void CloseResultSet(ResultSet xResultSet) {
    if (xResultSet == null) return;

    try {
      xResultSet.close();
    } catch (SQLException ex) {
      System.out.println("SqlException: " + ex.getMessage());
    }
  } //

  private void ExecuteQuery(Connection xConnection, String xCommand) {
    ResultSet lResultSet = null;
    Statement lStatement = null;

    try {
      lStatement = xConnection.createStatement();
      lResultSet = lStatement.executeQuery(xCommand);

      // print column headers
      ResultSetMetaData lMetaData = lResultSet.getMetaData();
      for (int i = 1, n = lMetaData.getColumnCount(); i <= n; i++)
        System.out.print(lMetaData.getColumnLabel(i) + " ");
      System.out.println();

      // print data
      while (lResultSet.next()) {
        long lId = lResultSet.getLong(1);
        String lName = lResultSet.getString(2);
        java.sql.Date lBirthday = lResultSet.getDate(3);
        System.out.println(lId + ": " + lName + " was born on " + lBirthday.toString());
      }
    } catch (SQLException ex) {
      System.out.println("SqlException: " + ex.getMessage());
    } finally {
      CloseStatement(lStatement);
      CloseResultSet(lResultSet);
    }
  } //

  public final void MySqlConnectionTest() {
    String lIpAddress = "127.0.0.1:3306";
    String lDatabase = "ohta";
    String lUser = "root";
    String lPassword = "root";

    Connection lConnection = null;
    try {
      Class.forName("com.mysql.jdbc.Driver");
      System.out.println("driver connection successful");

      String lUrl = "jdbc:mysql://" + lIpAddress + "/" + lDatabase;
      lConnection = DriverManager.getConnection(lUrl, lUser, lPassword);
      System.out.println("database connection successful");

      // create a new table
      String lField1 = "id INT  NOT NULL AUTO_INCREMENT,";
      String lField2 = "name VARCHAR(40) NOT NULL,";
      String lField3 = "birthdate DATE,";
      String lKey = "PRIMARY KEY (id)";
      String lTableDefinition = lField1 + lField2 + lField3 + lKey;

      ExecuteNonQuery(lConnection, "DROP TABLE IF EXISTS test");
      ExecuteNonQuery(lConnection, "CREATE TABLE test(" + lTableDefinition + ");");

      // fill the table with values
      ExecuteNonQueryAddRow(lConnection, "George Washington", getDate(1732, 2, 22, 0, 0, 0));
      ExecuteNonQueryAddRow(lConnection, "Abraham Lincoln", getDate(1809, 2, 12, 0, 0, 0));
      ExecuteNonQueryAddRow(lConnection, "Ronald Reagan", getDate(1911, 2, 6, 0, 0, 0));
      ExecuteNonQueryAddRow(lConnection, "Bill Clinton", getDate(1946, 8, 19, 0, 0, 0));

      // Query
      ExecuteQuery(lConnection, "SELECT * FROM test");

      // clean up
      ExecuteNonQuery(lConnection, "DROP TABLE IF EXISTS test");

    } catch (ClassNotFoundException ex) {
      System.out.println("ClassNotFoundException: " + ex.getMessage());
    } catch (SQLException ex) {
      System.out.println("SqlException: " + ex.getMessage());
    } finally {
      if (lConnection != null) try {
        lConnection.close();
      } catch (SQLException ex) {
        System.out.println("SqlException: " + ex.getMessage());
      }
    }
  } //

  public static void main(String[] args) {
    MySqlDemo lDemo = new MySqlDemo();
    lDemo.MySqlConnectionTest();
  } //

} // class

// .h file
#pragma once

#include <stdlib.h>
#include <iostream>
#include "mysql_connection.h"
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>

using namespace std;
using namespace sql;

class Day6 {
private:
  void ExecuteNonQuery(Statement * const xStatement, const string &xCommand);
  void ExecuteNonQueryAddRow(PreparedStatement *xPreparedStatement, const string &xName, const int &xYear, const int &xMonth, const int &xDay); 
  ResultSet *ExecuteQuery(Connection *xConnection, const string &xCommand);  
public:
  int test(void);
};

// .cpp file
#include "stdafx.h"
#include "Day6.h"

void Day6::ExecuteNonQuery(Statement * const xStatement, const string &xCommand) {
  SQLString lCommand(xCommand);
  xStatement->execute(lCommand);
} //

void Day6::ExecuteNonQueryAddRow(PreparedStatement *xPreparedStatement, const string &xName, const int &xYear, const int &xMonth, const int &xDay) {
  SQLString lName(xName);
  xPreparedStatement->setString(1, lName);
  int64_t lBirthday = xYear * 10000 + xMonth * 100 + xDay;
  xPreparedStatement->setInt64(2, lBirthday);
  xPreparedStatement->executeUpdate();
} //

ResultSet *Day6::ExecuteQuery(Connection *xConnection, const string &xCommand) {
  SQLString lCommand(xCommand);
  PreparedStatement *lPreparedStatement = xConnection->prepareStatement(lCommand);
  ResultSet *lResultSet = lPreparedStatement->executeQuery();
  delete lPreparedStatement;
  return lResultSet;
} //

int Day6::test(void) {
  try {
    // connect
    Driver *lDriver = get_driver_instance();
    Connection *lConnection = lDriver->connect("tcp://127.0.0.1:3306", "root", "root");

    // lConnection->setAutoCommit(0); // turn off auto commit        
    lConnection->setSchema("ohta"); // replace it with your database name

    // create a new table
    string lField1 = "id INT NOT NULL AUTO_INCREMENT,";
    string lField2 = "name VARCHAR(40) NOT NULL,";
    string lField3 = "birthday INT,";  // YYYYMMDD
    string lKey = "PRIMARY KEY (id)";
    string lTableDefinition = lField1 + lField2 + lField3 + lKey;
    string lCommand = "CREATE TABLE test(" + lTableDefinition; lCommand += ")";

    Statement *lStatement = lConnection->createStatement();

    ExecuteNonQuery(lStatement, "DROP TABLE IF EXISTS test");    
    ExecuteNonQuery(lStatement, lCommand);
    delete lStatement;

    // fill the table with values
    PreparedStatement *lPreparedStatement = lConnection->prepareStatement("INSERT INTO test (name, birthday) values (?, ?)");
    ExecuteNonQueryAddRow(lPreparedStatement, "George Washington", 1732, 2, 22);
    ExecuteNonQueryAddRow(lPreparedStatement, "Abraham Lincoln", 1809, 2, 12);
    ExecuteNonQueryAddRow(lPreparedStatement, "Ronald Reagan", 1911, 2, 6);
    ExecuteNonQueryAddRow(lPreparedStatement, "Bill Clinton", 1946, 8, 19);
    delete lPreparedStatement;

    // Query
    ResultSet *lResultSet = ExecuteQuery(lConnection, "SELECT * FROM test;");

    // print column names    
    ResultSetMetaData *lMeta = lResultSet->getMetaData();
    unsigned int lColumnCount = lMeta->getColumnCount();
    for (unsigned int i = 1; i <= lColumnCount; i++) {
     SQLString lColumnName = lMeta->getColumnName(i);  // http://bugs.mysql.com/bug.php?id=70006
     SQLString lTypeName = lMeta->getColumnTypeName(i);
     cout << lColumnName.asStdString() << ": " << lTypeName.asStdString() << endl;
    }    

    // print rows    
    lResultSet->beforeFirst();    
    while (lResultSet->next()) {
      string lId = to_string(lResultSet->getInt("id"));
      SQLString h = lResultSet->getString("name"); // http://bugs.mysql.com/bug.php?id=70006      
      const string lName = h.asStdString();
      int64_t lBirthday = lResultSet->getInt("birthday");
      int lYear = (int)( lBirthday / 10000);
      int lMonth = (int)(lBirthday % 10000) / 100;
      int lDay = (int)(lBirthday % 100);

      cout << lId << ", " << lName << ", " << lYear << "/" << lMonth << "/" << lDay << endl;      
    }

    // clean up
    lStatement = lConnection->createStatement();
    ExecuteNonQuery(lStatement, "DROP TABLE IF EXISTS test");

    delete lStatement;
    delete lResultSet;
    lConnection->close();
    delete lConnection;
  }
  catch (SQLException &e) {
    cout << "exception" << e.getSQLState() << endl;
  }

  return EXIT_SUCCESS;
}