ruby - 使用 Ruby 和 SQL SMO 实现脚本自动化

Question

我需要在 ruby​​ 中创建一个脚本来获取所有数据库对象(表、 View 、sps、函数等)，并能够为每个数据库对象创建文件。

我希望能够在 ruby​​ 中实现这个解决方案并使用某种 Win32 类？

我正在使用 SQL Server 2008 R2。当然不是 ruby​​ on rails。

Answer 1

# == 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