StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<pre><code># == 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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload