context: 
  /*
   * DVD Store 2 MySQL Functions - ds2mysqlfns.cs
   *
   * Copyright (C) 2005 Dell, Inc. <dave_jaffe@dell.com> and <tmuirhead@vmware.com>
   *
   * Provides interface functions for DVD Store driver program ds2xdriver.cs
   * See ds2xdriver.cs for compilation and syntax
   *
   * Last Updated 6/27/05
   * Last updated 6/14/2010 by GSK
   *  This program is free software; you can redistribute it and/or modify
   *  it under the terms of the GNU General Public License as published by
   *  the Free Software Foundation; either version 2 of the License, or
   *  (at your option) any later version.
   *
   *  This program is distributed in the hope that it will be useful,
   *  but WITHOUT ANY WARRANTY; without even the implied warranty of
   *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   *  GNU General Public License for more details.
   *
   *  You should have received a copy of the GNU General Public License
   *  along with this program; if not, write to the Free Software
   *  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA  */
  
  
  
  using System;
  using System.IO;
  using System.Data;
  using MySql.Data.MySqlClient;
  using System.Net;
  using System.Threading;
  using System.Runtime.InteropServices;
  
  
  namespace ds2xdriver
    {
    /// <summary>
    /// ds2mysqlfns.cs: DVD Store 2 MySql Functions
    /// </summary>
    public class ds2Interface
      {
  #if (USE_WIN32_TIMER)
      [DllImport("kernel32.dll")]
      extern static short QueryPerformanceCounter(ref long x);
      [DllImport("kernel32.dll")]
      extern static short QueryPerformanceFrequency(ref long x);  
  #endif
  
      int ds2Interfaceid;
      MySqlConnection objConn;
      MySqlCommand Login, New_Customer, Browse, Purchase;
      MySqlDataReader Rdr;
      MySqlParameter cust_out_param;
      string db_query;
      //string conn_str = "Server=" +  Controller.target + ";User ID=web;Password=web;Database=DS2";
      //Changed by GSK (connection string will be initialized in new Overloaded constructor )
      string conn_str = "";
      string target_server_name;
  //
  //-------------------------------------------------------------------------------------------------
  // 
      public ds2Interface(int ds2interfaceid)
        {
        ds2Interfaceid = ds2interfaceid;
        //Console.WriteLine("ds2Interface {0} created", ds2Interfaceid);
        }
  //
  //-------------------------------------------------------------------------------------------------
      //Added by GSK (Overloaded the constructor to handle scenario where Single instance of Driver program is driving load on multiple machines)
      public ds2Interface ( int ds2interfaceid , string target_name)
          {
          ds2Interfaceid = ds2interfaceid;
          target_server_name = target_name;
          conn_str = "Server=" + target_server_name + ";User ID=web;Password=web;Database=DS2";
          //Console.WriteLine("ds2Interface {0} created", ds2Interfaceid);
          }
  //
  //-------------------------------------------------------------------------------------------------
  // 
      public  bool ds2initialize()
        {
        return(true);
        } // end ds2initialize()
   
  //
  //-------------------------------------------------------------------------------------------------
  //  
      public bool ds2connect()
        {
        try
          {
          objConn = new MySqlConnection(conn_str);
          objConn.Open();
          }
        catch (MySqlException e)
          {
          //Changed by GSK
          //Console.WriteLine("Thread {0}: error in connecting to database {1}: {2}",  Thread.CurrentThread.Name,
          //  Controller.target, e.Message);
          Console.WriteLine("Thread {0}: error in connecting to database {1}: {2}",  Thread.CurrentThread.Name,
            target_server_name , e.Message );
          return(false);
          }
  
        // Set up MySql stored procedure calls and associated parameters
        New_Customer = new MySqlCommand("NEW_CUSTOMER", objConn);
        New_Customer.CommandType = CommandType.StoredProcedure; 
        New_Customer.Parameters.Add("username_in", MySqlDbType.VarChar, 50);
        New_Customer.Parameters.Add("password_in", MySqlDbType.VarChar, 50);    
        New_Customer.Parameters.Add("firstname_in", MySqlDbType.VarChar, 50);
        New_Customer.Parameters.Add("lastname_in", MySqlDbType.VarChar, 50);
        New_Customer.Parameters.Add("address1_in", MySqlDbType.VarChar, 50);
        New_Customer.Parameters.Add("address2_in", MySqlDbType.VarChar, 50);
        New_Customer.Parameters.Add("city_in", MySqlDbType.VarChar, 50);
        New_Customer.Parameters.Add("state_in", MySqlDbType.VarChar, 50);
        New_Customer.Parameters.Add("zip_in", MySqlDbType.Int32);
        New_Customer.Parameters.Add("country_in", MySqlDbType.VarChar, 50);
        New_Customer.Parameters.Add("region_in", MySqlDbType.Int32);
        New_Customer.Parameters.Add("email_in", MySqlDbType.VarChar, 50);
        New_Customer.Parameters.Add("phone_in", MySqlDbType.VarChar, 50);
        New_Customer.Parameters.Add("creditcardtype_in", MySqlDbType.Int32);
        New_Customer.Parameters.Add("creditcard_in", MySqlDbType.VarChar, 50);
        New_Customer.Parameters.Add("creditcardexpiration_in", MySqlDbType.VarChar, 50); 
        New_Customer.Parameters.Add("age_in", MySqlDbType.Byte);
        New_Customer.Parameters.Add("income_in", MySqlDbType.Int32);
        New_Customer.Parameters.Add("gender_in", MySqlDbType.VarChar, 1);
        cust_out_param = new MySqlParameter("customerid_out", MySqlDbType.Int32);
        cust_out_param.Direction = ParameterDirection.Output;
        cust_out_param.Value = 0;
        New_Customer.Parameters.Add(cust_out_param);      
        
        return(true);
        } // end ds2connect()
   
  //
  //-------------------------------------------------------------------------------------------------
  // 
      public bool ds2login(string username_in, string password_in, ref int customerid_out, ref int rows_returned, 
        ref string[] title_out, ref string[] actor_out, ref string[] related_title_out, ref double rt)
        {
        int i_row=0, prod_id;
  #if (USE_WIN32_TIMER)
        long ctr0 = 0, ctr = 0, freq = 0;
  #else
        TimeSpan TS = new TimeSpan();
        DateTime DT0;
  #endif     
        
        db_query="select CUSTOMERID FROM DS2.CUSTOMERS where USERNAME='" + username_in + "' and PASSWORD='" + password_in
          + "';";
        Login = new MySqlCommand(db_query, objConn);
        rows_returned = 0;
     
  #if (USE_WIN32_TIMER)
        QueryPerformanceFrequency(ref freq); // obtain system freq (ticks/sec)
        QueryPerformanceCounter(ref ctr0); // Start response time clock   
  #else
        DT0 = DateTime.Now;
  #endif
  
        try 
          {         
          Rdr = Login.ExecuteReader();
          if (!Rdr.HasRows)  // No customer found
            {
            customerid_out = 0;
            return(true);
            }
          Rdr.Read();
          customerid_out = Rdr.GetInt32(0);
          Rdr.Close();
          db_query = "select PROD_ID from DS2.CUST_HIST where CUSTOMERID =" + customerid_out + " ORDER BY ORDERID DESC LIMIT 10;"; 
          Login = new MySqlCommand(db_query, objConn);
     
          Rdr = Login.ExecuteReader();
          if (!Rdr.HasRows)  // No previous order
            {
            //Console.WriteLine("No previous orders");
            }
          else
            {
            i_row = 0;
            while(Rdr.Read())
              {
              prod_id = Rdr.GetInt32(0);            
              string db_query2 = "select TITLE, ACTOR from DS2.PRODUCTS where PROD_ID=" + prod_id + ";";
              MySqlConnection conn2 = new MySqlConnection(conn_str);
              conn2.Open();
              MySqlCommand Login2 = new MySqlCommand(db_query2, conn2);
              MySqlDataReader Rdr2 = Login2.ExecuteReader();
              Rdr2.Read();
              title_out[i_row] = Rdr2.GetString(0);
              actor_out[i_row] = Rdr2.GetString(1);                         
              Rdr2.Close();
              string db_query3 = "select TITLE from DS2.PRODUCTS where PROD_ID = " +
                "(select COMMON_PROD_ID from DS2.PRODUCTS where PROD_ID=" + prod_id + ");";
              MySqlCommand Login3 = new MySqlCommand(db_query3, conn2);
              related_title_out[i_row] = (string) Login3.ExecuteScalar();                        
              conn2.Close();
              ++i_row;
              }
            } // End else
          Rdr.Close();
          rows_returned = i_row;
          } // End try 
        catch (MySqlException e) 
          {
          Console.WriteLine("Thread {0}: Error in Login: {1}", Thread.CurrentThread.Name, e.Message);
          return (false);
          }
          
  #if (USE_WIN32_TIMER)
        QueryPerformanceCounter(ref ctr); // Stop response time clock
        rt = (ctr - ctr0)/(double) freq; // Calculate response time
  #else
        TS = DateTime.Now - DT0;
        rt = TS.TotalSeconds; // Calculate response time
  #endif            
  
        return(true);
        }  // end ds2login
  //
  //-------------------------------------------------------------------------------------------------
  // 
      public bool ds2newcustomer(string username_in, string password_in, string firstname_in, 
        string lastname_in, string address1_in, string address2_in, string city_in, string state_in, 
        string zip_in, string country_in, string email_in, string phone_in, int creditcardtype_in, 
        string creditcard_in, int ccexpmon_in, int ccexpyr_in, int age_in, int income_in, 
        string gender_in, ref int customerid_out, ref double rt) 
        {
        int region_in = (country_in == "US") ? 1:2;
        string creditcardexpiration_in = String.Format("{0:D4}/{1:D2}", ccexpyr_in, ccexpmon_in);
  #if (USE_WIN32_TIMER)
        long ctr0 = 0, ctr = 0, freq = 0;
  #else
        TimeSpan TS = new TimeSpan();
        DateTime DT0;
  #endif   
        New_Customer.Parameters["username_in"].Value = username_in;
        New_Customer.Parameters["password_in"].Value = password_in;
        New_Customer.Parameters["firstname_in"].Value = firstname_in;
        New_Customer.Parameters["lastname_in"].Value = lastname_in;
        New_Customer.Parameters["address1_in"].Value = address1_in;
        New_Customer.Parameters["address2_in"].Value = address2_in;
        New_Customer.Parameters["city_in"].Value = city_in;
        New_Customer.Parameters["state_in"].Value = state_in;
        New_Customer.Parameters["zip_in"].Value = (zip_in=="") ? 0 : Convert.ToInt32(zip_in);
        New_Customer.Parameters["country_in"].Value = country_in;
        New_Customer.Parameters["region_in"].Value = region_in;
        New_Customer.Parameters["email_in"].Value = email_in;
        New_Customer.Parameters["phone_in"].Value = phone_in;
        New_Customer.Parameters["creditcardtype_in"].Value = creditcardtype_in;
        New_Customer.Parameters["creditcard_in"].Value = creditcard_in;
        New_Customer.Parameters["creditcardexpiration_in"].Value = creditcardexpiration_in;
        New_Customer.Parameters["age_in"].Value = age_in;
        New_Customer.Parameters["income_in"].Value = income_in;
        New_Customer.Parameters["gender_in"].Value = gender_in;
      
  //    Console.WriteLine("Thread {0}: Calling New_Customer w/username_in= {1}  region={2}  ccexp={3}",
  //      Thread.CurrentThread.Name, username_in, region_in, creditcardexpiration_in);
  
  #if (USE_WIN32_TIMER)
        QueryPerformanceFrequency(ref freq); // obtain system freq (ticks/sec)
        QueryPerformanceCounter(ref ctr0); // Start response time clock   
  #else
        DT0 = DateTime.Now;
  #endif 
  
        bool deadlocked;      
        do
          {
          try 
            {
            deadlocked = false;
            New_Customer.ExecuteNonQuery();
            }
          catch (MySqlException e) 
            {
            if (e.Number == 1205)
              {
              deadlocked = true;
              Random r = new Random(DateTime.Now.Millisecond);
              int wait = r.Next(1000);
              Console.WriteLine("Thread {0}: New_Customer deadlocked...waiting {1} msec, then will retry",
                Thread.CurrentThread.Name, wait);
              Thread.Sleep(wait); // Wait up to 1 sec, then try again
              }
            else
              {           
              Console.WriteLine("Thread {0}: MySql Error {1} in New_Customer: {2}", 
                Thread.CurrentThread.Name, e.Number, e.Message);
              return(false);
              }
            }
          } while (deadlocked);
  
          customerid_out = (int) cust_out_param.Value;         
          
  #if (USE_WIN32_TIMER)
        QueryPerformanceCounter(ref ctr); // Stop response time clock
        rt = (ctr - ctr0)/(double) freq; // Calculate response time
  #else
        TS = DateTime.Now - DT0;
        rt = TS.TotalSeconds; // Calculate response time
  #endif        
        return(true);
        } // end ds2newcustomer()
      
  //
  //-------------------------------------------------------------------------------------------------
  // 
      public bool ds2browse(string browse_type_in, string browse_category_in, string browse_actor_in,
        string browse_title_in, int batch_size_in, int customerid_out, ref int rows_returned, 
        ref int[] prod_id_out, ref string[] title_out, ref string[] actor_out, ref decimal[] price_out, 
        ref int[] special_out, ref int[] common_prod_id_out, ref double rt)
        {
        // Products table: PROD_ID INT, CATEGORY TINYINT, TITLE VARCHAR(50), ACTOR VARCHAR(50), 
        //   PRICE DECIMAL(12,2), SPECIAL TINYINT, COMMON_PROD_ID INT
        int i_row;
        int[] category_out = new int[GlobalConstants.MAX_ROWS];
  
  #if (USE_WIN32_TIMER)
        long ctr0 = 0, ctr = 0, freq = 0;
  #else
        TimeSpan TS = new TimeSpan();
        DateTime DT0;
  #endif  
  
        switch(browse_type_in)
          {
          case "title":
            db_query = "select * from PRODUCTS where MATCH (TITLE) AGAINST ('" + browse_title_in + "') LIMIT " +
              batch_size_in + ";";
            break;
          case "actor":
            db_query = "select * from PRODUCTS where MATCH (ACTOR) AGAINST ('" + browse_actor_in + "') LIMIT " +
              batch_size_in + ";";
            break;
          case "category":
            db_query="select * from PRODUCTS where CATEGORY = " + Convert.ToInt32(browse_category_in) + 
              " and SPECIAL=1 LIMIT " + batch_size_in + ";";
            break;
          }
          
          Browse = new MySqlCommand(db_query, objConn);
  
  #if (USE_WIN32_TIMER)
        QueryPerformanceFrequency(ref freq); // obtain system freq (ticks/sec)
        QueryPerformanceCounter(ref ctr0); // Start response time clock   
  #else
        DT0 = DateTime.Now;
  #endif 
          
        try 
          {
          Rdr = Browse.ExecuteReader();
   
          i_row = 0;
          if (!Rdr.HasRows) // No rows returned
            {
            //Console.WriteLine("No DVDs Found");
            }
          else  // Rows returned
            {
            while (Rdr.Read())
              {
              prod_id_out[i_row] = Rdr.GetInt32(0);
              category_out[i_row] = Rdr.GetByte(1);
              title_out[i_row] = Rdr.GetString(2);
              actor_out[i_row] = Rdr.GetString(3);
              price_out[i_row] = Rdr.GetDecimal(4);
              special_out[i_row] = Rdr.GetByte(5);
              common_prod_id_out[i_row] = Rdr.GetInt32(6);
              ++i_row;
              }
            }
          Rdr.Close();
          rows_returned = i_row;
          }
        catch (MySqlException e) 
          {
          Console.WriteLine("Thread {0}: Error in Browse: {1}", Thread.CurrentThread.Name, e.Message);
          return(false);
          }
  
  //    Console.WriteLine("Thread {0}: Calling Browse w/ browse_type= {1} batch_size_in= {2}  category= {3}" +
  //      " title= {4}  actor= {5}", Thread.CurrentThread.Name, browse_type_in, batch_size_in, browse_category_in,
  //      browse_title_in, browse_actor_in); 
                       
  #if (USE_WIN32_TIMER)
  
gt:       QueryPerformanceCounter(ref ctr); // Stop response time clock