Wednesday, June 9, 2010

Watir: Watir Scripting

 Ruby Database

equire 'win32ole'
         class SqlServer 
         # This class manages database connection and queries
        attr_accessor :connection, :data, :fields
         attr_writer :username, :password
               
        def initialize(sclient,script,pagecode,pagenum,host='', username='', password='',database='')
             puts "in init"
             #~ @dbtype=readINI("C:/config/DB.ini","DBtype")
                                 
                       @connection = nil
            @@sclient=sclient
            @@script=script
            @@pagecode=pagecode
            @@pagenum=pagenum
            @data = nil
           

      @timeout=60
      @sclient2=sclient

     puts "in init"
     
      end
     
        #end
           
        def open()
            # Open ADO connection to the SQL Server database
            connection_string =  "Provider=SQLOLEDB.1;"
            connection_string << "Persist Security Info=False;"
            connection_string << "User ID=#{$username};"
            connection_string << "password=#{$password};"
            connection_string << "Initial Catalog=#{$database};"
            connection_string << "Data Source=#{$host};"
            connection_string <<"Connect Timeout=#{@timeout};"
            connection_string << "Network Library=dbmssocn"
            puts connection_string
            @connection = WIN32OLE.new('ADODB.Connection')
           
            #~ if @dbtype="mssql"
            @connection.Open(connection_string)
            #~ else
              #~ $dbh = Mysql.real_connect( @host,@username,@password ,@database)
              #~ end
          end
         
          def query(sql)
            # Create an instance of an ADO Recordset
            recordset = WIN32OLE.new('ADODB.Recordset')
            #puts @connection
            # Open the recordset, using an SQL statement and the
            # existing ADO connection
            recordset.Open(sql, @connection)
            # Create and populate an array of field names
            @fields = []

            recordset.Fields.each do |field|
                @fields << field.Name
            end
            begin
                # Move to the first record/row, if any exist
                recordset.MoveFirst
                # Grab all records
                @data = recordset.GetRows
            rescue
                @data = []
           
            end
            #~ recordset.Close
            # An ADO Recordset's GetRows method returns an array
            # of columns, so we'll use the transpose method to
            # convert it to an array of rows
            @data = @data.transpose
        end
   
        def close
          #~ recordset.Close
            @connection.Close
        end
   
    end