Daily Archives: March 4, 2014
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;
}
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);
} //
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();
}
} //
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);
}
} //
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();
}
} //
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.
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);
} //
C# Hardcore Programming 