スクリプトの自動化にRubyとSQL SMOを使用する

ruby sql-server-2008
スクリプトの自動化にRubyとSQL SMOを使用する

rubyでスクリプトを作成して、すべてのデータベースオブジェクト(テーブル、ビュー、sps、関数など)を取得し、dbオブジェクトごとにファイルを作成できるようにする必要があります。

このソリューションをrubyで実装し、何らかのWin32クラスを使用できるようにしたいと思います。

SQL Server 2008 R2を使用しています。 もちろんレール上のルビーではありません。

  2  0


ベストアンサー

# == Name
# SQL Server Library
# == Author
# Maverick
# == Synopsis
# ADO SQL Server Library
# == Notes:
# Modify the following global variables in order to set up an execution environment
# sql_str: This is the SQL CMD command option and arguments -> Change the -U and -P arguments for -E to enable integrated security
# http://rubyonwindows.blogspot.com/2007/03/ruby-ado-and-sqlserver.html

Thread.abort_on_exception = true

require 'win32ole'
require 'win32api'
CoInitialize = Win32API.new('ole32', 'CoInitialize', 'P', 'L')

# This class manages database connection and queries
class SqlServer

    attr_accessor :connection, :data, :fields

    def initialize
        @connection   = nil
        @data         = nil
        @cmd_time_out = 900
    end

    #opens a database connection using integrated security
    def open(server,database)
        connection_string =  "Provider=SQLOLEDB.1;"
        connection_string << "Persist Security Info=False;"
        connection_string << "Integrated Security=SSPI;"
        connection_string << "Initial Catalog=#{database};"
        connection_string << "Data Source=#{server};"
        connection_string << "Network Library=dbmssocn"
        CoInitialize.call( 0 )

        if server.eql?(nil) or database.eql?(nil) or server.eql?('') or database.eql?('') then
          raise Exception, "Application Error: Server or Database parameters are missing"
        end

        begin
         @connection = WIN32OLE.new('ADODB.Connection')
          @connection.ConnectionString = connection_string
          @connection.open
        rescue Exception => e
          @connection.Errors.Count.times { |x|
            show_ado_error(@connection.Errors)
          }
          raise Exception, "Application Error: #{e.message} \n Can't open a connection with the server. Verify user credentials"
      end
    end

    def get_connection
     return @connection
    end

    #executes a query without returning any rows
    def execute_non_query(query)
      begin
        command = WIN32OLE.new('ADODB.Command')
        command.CommandType       = 1
        command.ActiveConnection  = @connection
        command.CommandText       = query
        command.CommandTimeOut    = @cmd_time_out
        result = command.Execute

        if @connection.Errors.Count > 1 then
          raise Exception,"ADODB Connection contains errors"
        end
      rescue Exception => e
        show_ado_error(@connection.Errors)
        raise Exception,  "Application Error: #{e.message} \n Can't execute query. Verify sql syntax"
      end
       return result
    end

    #prints ado db errors using ado connection error property
    def show_ado_error(obj)
      obj.Count.times { |x|
            puts "#{x}. ADODB Error Number: " + @connection.Errors(x).Number.to_s
            puts "#{x}. ADODB Generated By: " + @connection.Errors(x).Source
            puts "#{x}. ADODB SQL State: "    + @connection.Errors(x).SQLState
            puts "#{x}. ADODB Native Error: " + @connection.Errors(x).NativeError.to_s
            puts "#{x}. ADODB Description: "  + @connection.Errors(x).Description
          }
    end

    #executes a query returning an array of rows
    def execute_query(sql_query)
        # Create an instance of an ADO Record set
      begin

        record_set = WIN32OLE.new('ADODB.Recordset')
        # Open the record set, using an SQL statement and the
        # existing ADO connection
        record_set.open(sql_query, @connection)
        # Create and populate an array of field names
        @fields = []
        record_set.fields.each do |field|
            @fields << field.Name
        end
        begin
            # Move to the first record/row, if any exist
            record_set.movefirst
            # Grab all records
            @data = record_set.getrows
        rescue
            @data = []
        end
        record_set.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
      rescue
        raise Exception,  "Application Error: Can't execute query. Verify SQL Query syntax"
        end

      end

    def close
        @connection.Close
    end
end

0


タイトルとURLをコピーしました