Ruby connect to SQL Server native Client

After trying for a day, I finally found a way to connect to SQL Server.

First, use win32ole to connect.

Pay special attention to the red part. The errors reported by many people on the network are due to a problem with the provider of the connected string. Change it to the red value, you can connect to the native client of SQL Server.

Require 'win32ole'

Class sqlserver
# This class manages database connection and queries
Attr_accessor: connection,: data,: Fields
Attr_writer: username,: Password

Def initialize (host, username = 'sa ', password = '')
@ Connection = Nil
@ DATA = Nil
@ Host = Host
@ Username = Username
@ Password = Password
End

Def open (database)
# Open ADO connection to the SQL Server database
Connection_string = "Provider = sqlncli ;"
Connection_string <"Persist Security info = true ;"
Connection_string <"User ID =#{@ username };"
Connection_string <"password =#{@ password };"
Connection_string <"Initial catalog =# {database };"
Connection_string <"Data Source =#{@ Host };"
Connection_string <"network library = dbmssocn"
@ Connection = win32ole. New ('ADODB. connection ')
@ Connection. Open (connection_string)
End

Def query (SQL)
# Create an instance of An ADO recordset
Recordset = win32ole. New ('ADODB. recordset ')
# Open the recordset, using an SQL statement and
# 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
# Convert it to an array of rows
@ DATA = @ data. Transpose
End

Def close
@ Connection. Close
End
End

DB = sqlserver. New ('hostip', 'username', 'Password ')
DB. Open ('databasename ')
Warmlead_url = "select * from table '"
DB. Query (warmlead_url)

Puts field_names = dB. Fields

Cust = dB. Data

Puts Cust. Size

Puts Cust [0]. Inspect

DB. Close

 

Method 2: Use DBI to connect.

Follow these steps to configure the ruby environment and then run the script.

1. First download ruby-dbi的dbi-0.4.3.gemand dbi-0.1.0.tar.gz, download URL for http://rubyforge.org/frs? Group_id = 234 & release_id = 4323

2. Install dbi-0.4.3.gem

Gem install dbi-0.4.3.gem

3. Decompress dbi-0.1.0.tar.gz to find the ADO. RB file. Dbi-0.1.0.tar.gz package after decompression path to find (Bdi-0.1.0/lib/DBD/ADO. Rb)

4. manually create the ADO folder. After the folder is created, the path is C:/Ruby/lib/Ruby/site_ruby/1.8/DBD/ADO.

5. Copy the ADO. RB file to the folder created in step 4.

6. Use the following RubyCodeConnect to the SQL Server native client. Pay special attention to the red part.

Require 'dbi'

Class Server
Attr_reader: Name
Def initialize (name,Username, password, database)
@ SERVER_NAME = Name
@ Username = Username
@ Password = Password
@ Database = Database
@ DBH = DBI. Connect ("DBI: Ado: provider = sqlncli; Data Source =#{ name };Persist Security info = false; User ID =#{@ username}; password =#{@ password}; initial catalog =#{ database};")
End
Def Databases
DB = array. New
@ DBH. select_all ('select name from Master. SYS. Databases order by 1') Do | row |
DB. <database. New (@ DBH, row [0])
End
DB
End
End

Class Database
Attr_reader: Name
Def initialize (DBH, name)
@ DBH = DBH
@ Name = Name
End
End

Server = server. new ("hostname", "username", "password", "database_name")
server. databases. each {| x | puts X. name}