using System;
using System.Data.Odbc;
using System.Collections.Generic;
using System.Diagnostics;


namespace DBTest
{
	class Program
	{

		// database varbintest has just one table:
		/*
			CREATE TABLE `test` (
				`id` int(11) default NULL,
				`varbin` varbinary(2000) default NULL
			) ENGINE=InnoDB DEFAULT CHARSET=latin1;
		*/

		internal const string ConnStr5 = @"DRIVER={MySQL Connector/ODBC v5}; SERVER=localhost; DATABASE=varbintest; UID=root; PASSWORD=;OPTION=3;";
		internal const string ConnStr3 = @"DRIVER=MySQL ODBC 3.51 Driver;SERVER=localhost;OPTION=3;DATABASE=varbintest;USER=root;PASSWORD=";

		private const string sGuid = "eb48a7bd-3da1-4a09-bfaf-8b35c7687ee7";

		static void Main(string[] args)
		{

			try
			{
				Reset();
				Guid g = new Guid(sGuid);
				byte[] data = g.ToByteArray();
				Save(data, ConnStr3, 3);
				Save(data, ConnStr5, 5);

				byte[] reload = Fetch(ConnStr3, 3);
				bool cmp33 = AreSame(data, reload);	// true

				reload = Fetch(ConnStr3, 5);
				bool cmp35 = AreSame(data, reload);	// false!!

				reload = Fetch(ConnStr5, 3);
				bool cmp53 = AreSame(data, reload);	// false!!

				reload = Fetch(ConnStr5, 5);
				bool cmp55 = AreSame(data, reload);	// true

				Console.WriteLine("Save3 == Fetch3: {0}", cmp33);
				Console.WriteLine("Save5 == Fetch3: {0}", cmp35);
				Console.WriteLine("Save3 == Fetch5: {0}", cmp53);
				Console.WriteLine("Save5 == Fetch5: {0}", cmp55);
			}
			catch (Exception ex)
			{
				Console.WriteLine(ex);
			}
		}

		private static void Reset()
		{
			using (OdbcConnection conn = new OdbcConnection(ConnStr3))
			{
				OdbcCommand cmd = conn.CreateCommand();
				cmd.CommandText = "truncate test";
				conn.Open();
				cmd.ExecuteNonQuery();
			}
		}

		private static void Save(byte[] data, string connStr, int index)
		{
			using (OdbcConnection conn = new OdbcConnection(connStr))
			{
				OdbcCommand cmd = conn.CreateCommand();
				cmd.CommandText = "insert into test values (?, ?)";
				cmd.Parameters.Add("id", OdbcType.Int).Value = index;
				cmd.Parameters.Add("varbin", OdbcType.VarBinary).Value = data;
				conn.Open();
				cmd.ExecuteNonQuery();
			}
		}

		private static byte[] Fetch(string connStr, int index)
		{
			byte[] r = null;
			using (OdbcConnection conn = new OdbcConnection(connStr))
			{
				OdbcCommand cmd = conn.CreateCommand();
				cmd.CommandText = String.Format("select varbin from test where id={0}", index);
				byte[] buffer = new byte[2000];
				conn.Open();
				using (OdbcDataReader rdr = cmd.ExecuteReader())
				{
					if (rdr.Read())
					{
						long len = rdr.GetBytes(0, 0, buffer, 0, buffer.Length);
						if (len > 0)
						{
							r = new byte[len];
							Array.Copy(buffer, r, r.Length);
						}
					}
				}
			}
			return r;
		}

		private static bool AreSame(byte[] original, byte[] reloaded)
		{
			if (original.Length != reloaded.Length) return false;
			for (int i = 0; i < original.Length; ++i)
			{
				if (original[i] != reloaded[i]) return false;
			}
			return true;
		}

	}
}