Daily Archives: March 4, 2014

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

logo
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;
}
Advertisements

C# to C++ (advanced), review of day 4, locks and operators

As promised I am going to explain the C# side of post C# to C++ (day 4) today.
Let’s start with a dummy method. We will need it in the examples to fill in arbitrary code at the right places.

public void DoSomething() {
   Console.WriteLine("Good night my dear thread " + Thread.CurrentThread.ManagedThreadId);
   Thread.Sleep(2000);
} //

Lock

This is the standard. The Lock keyword is used frequently. The syntax is simple and locks are easy to deal with. My speed tests also tell that the speed impact of locks is negligible. Thus you can use locks without hesitation when needed.

object lAnyObject2 = new object();
public void LockUsage() {
   lock (lAnyObject2) {
      DoSomething();
   }
} //

Monitor

Locks are like macros that use the more complex Monitor class, which has more specific methods. If you don’t use the complexity of the Monitor class, then you are better of by simply using locks, which provide much better code legibility. Microsoft describes it like this:
“The functionality provided by the Enter and Exit methods is identical to that provided by the C# lock statement, except that lock wraps the Enter(Object, Boolean) method overload and the Exit method in a try…finally block to ensure that the monitor is released.”

object lAnyObject1 = new object();
public void MonitorUsage() {
   bool lLocked = false;
   try {
      Monitor.Enter(lAnyObject1, ref lLocked);  // using System.Threading;
      DoSomething();
   }
   finally {
      if (lLocked) Monitor.Exit(lAnyObject1);
   }
} //

Semaphore

Semaphores are used to limit the number of threads that can access resources. Let’s say you want to limit the output of a loudspeaker, because you cannot listen to five different sounds simultaneously. Nevertheless you want to hear urgent incoming chat message sounds from your friends while listening to nice background sounds of a game. In that case you could limit the number of threads using a resource to two. Or you are writing to a hard disk, which can deal with some simultaneous write operations. Still you want to limit the number of threads writing at the same time.
There is a slim class of Semaphores as well. SemaphoreSlim provides a lightweight semaphore class that doesn’t use Windows kernel semaphores.

private static Semaphore _Semaphore = new Semaphore(3, 3); // three objects allowed to access concurrently
public void SemaphoreUsage() {
   bool lLocked = false;
   try {
      lLocked = _Semaphore.WaitOne();
      DoSomething();
   }
   finally {
      if (lLocked) _Semaphore.Release();
   }
} //

Mutex

Mutexes are used across the system. They are identified by strings. Make sure you use unique strings to avoid conflicts. Let’s say you are running an application, which sets a Mutex. By mistake the user tries to start the same application again. But you can check the Mutex now and close/not start the second application.

private const string cMutexName = "MyMutex"
private static Mutex _Mutex = new Mutex(false, cMutexName);  // known by every process
public void MutexUsage() {
   bool lLocked = false;

   try {
      // optional complexity: access rights
      //bool lNewCreation;
      //MutexSecurity lSecurity = new MutexSecurity();
      //SecurityIdentifier lId = new SecurityIdentifier(WellKnownSidType.WorldSid, null);
      //MutexRights lRights = MutexRights.Synchronize | MutexRights.Modify;
      //lSecurity.AddAccessRule(new MutexAccessRule(lId, lRights, AccessControlType.Allow));
      //_Mutex = new Mutex(false, cMutexName, out lNewCreation, lSecurity);
      //MutexSecurity lReverse = _Mutex.GetAccessControl();

      lLocked = _Mutex.WaitOne(2000); // You can the option to set a time limit. Here 2000 milliseconds.
      if (!lLocked) {
         Console.WriteLine("Try again later. Mutex is used by another process or thread.");
      }

      DoSomething();
   }
   finally {
      if (lLocked == true) _Mutex.ReleaseMutex();
   }
} //

Operator overloading

It does look easy, but can cause complex situations. All you have to do is to declare a method as static and replace the method name by the word operator followed by the operator itself. The return value can be of any type. The parameters should have at least one type that equals the class type. The order of the parameters is important.

Let’s have a look at the example source code of post C# to C++ (day 4) again:

public class myClass {
   // this class is not thread safe
 
   int[] Values = { 1, 2, 3 };
 
   public static myClass operator +(myClass a, myClass b) {
      int n = a.Values.Length;
      myClass lNewClass = new myClass();
      for (int i = 0; i < n; i++) lNewClass.Values[i] = a.Values[i] + b.Values[i];
      return lNewClass;
   } //      
 
   public static double operator *(myClass a, myClass b) {
      int n = a.Values.Length;
      int lSum = 0;
      for (int i = 0; i < n; i++) lSum += a.Values[i] * b.Values[i];
      return lSum;
   } //
 
   public static string operator +(string a, myClass b) {
      //return ">> " + a + b + "<<";  // WRONG! causes recursion
      return ">> " + a + b.ToString() + "<<";
   } //
 
   // I will explanation this in my post on Tuesday 4 February 2014
   // uncomment this and play with it (=>hardcore C#)
   //public static string operator +(myClass a, string b) {
   //   //return ">> " + a + b + "<<";  // WRONG! causes recursion
   //   return ">> " + a + b.ToString() + "<<";
   //} //
   // becomes even more hardcore when you have two conflicting overloads
 
   public override string ToString() {
      return "Values: " + Values[0] + " " + Values[1] + " " + Values[2] + " ";
   } //
 
} // class
 
public static void test() {
   myClass a = new myClass();
   myClass b = new myClass();
   myClass c = a + b;
   double d = a * b;
   Console.WriteLine("(Sum) " + c);  // ">> (Sum) Values: 2 4 6 <<"
   Console.WriteLine("(Sum) " + c.ToString());  // "(Sum) Values: 2 4 6"
   Console.WriteLine(c + " (Sum)");  // "Values: 2 4 6  (Sum)"
   Console.WriteLine(d);  // 14
   Console.ReadLine();
} //
source code line operator called output
myClass c = a + b; public static myClass operator +(myClass a, myClass b) {…}
double d = a * b; public static double operator *(myClass a, myClass b) {…}
Console.WriteLine(“(Sum) ” + c); public static string operator +(string a, myClass b) {…} >> (Sum) Values: 2 4 6 <<
Console.WriteLine(“(Sum) ” + c.ToString()); [not part of myClass]
operator overload “+” of the string class
(Sum) Values: 2 4 6
Console.WriteLine(c + ” (Sum)”); c.ToString() is called by the string class operator overload “+” for objects Values: 2 4 6  (Sum)

You are using operator overloading quite often without noticing it. A quick example is:

DateTime x = DateTime.Now;
DateTime y = x.AddMinutes(5);
TimeSpan t = y - x;  // operator "-" is overloaded and returns a TimeSpan.

Let’s make it more difficult now.
Uncomment public static string operator +(myClass a, string b) {…}. The statement Console.WriteLine(c + ” (Sum)”) now prints “>> >> Values: 2 4 6 << (Sum)<<“.
First the operator public static string operator +(myClass a, string b) {…} is called. This operator indirectly calls another operator public static string operator +(string a, myClass b) {…}, because the code return “>> ” + a + b.ToString() + “<<"; was called, which started with a string “>> ” + a class.

Implicit

This is used to avoid explicit cast operations and convert implicitly. But more precisely we talk about User-defined conversions.

public class ClassX {
   private readonly double _Value;
   public ClassX(double xInitValue) { _Value = xInitValue; } // constructor
   public override string ToString() { return "CLASS"; }

   // cast operation: (double)ClassX;
   public static implicit operator double(ClassX xClassX) { return xClassX._Value; }
   // cast operation: (ClassX)double;
   public static implicit operator ClassX(double xDouble) { return new ClassX(xDouble); }
}

public static void test() {
   int i = 10;
   double d1 = i;          // implicit cast operation
   double d2 = (double)i;  // explicit cast operation

   ClassX c = new ClassX(99.0);
   double d3 = c;          // implicit cast operation
   double d4 = (double)c;  // explicit cast operation
   c = 1.2;

   Console.WriteLine("class or double? " + c);  // "CLASS"
   Console.ReadLine();
}

Conflicts

So what happens when you have two classes with opposing declarations? Luckily the compiler is smart enough to detect these issues. The below source code does not compile at all. The statement Console.WriteLine(a + b) causes an error message like: “The call is ambiguous between the following methods or properties: ‘…A.operator +(…A, …B)’ and ‘B.operator +(…A, …B)'”.
The next statement Console.WriteLine(b + a) is not even a real problem. We did not define any operator overloading. Thus the compiler complains “Operator ‘+’ cannot be applied to operands of type ‘…B’ and ‘…A'”

public class A {
   public static string operator +(A a, B b) { return "class A"; }
} // class
public class B {
   public static string operator +(A a, B b) { return "class B"; }
} // class

public static void test() {
	A a = new A();
	B b = new B();
	Console.WriteLine(a + b);
	Console.WriteLine(b + a);
} //