TinyTDS - 使用 DB-Library 为 Ruby 编写简单快速的 FreeTDS 绑定。
The TinyTDS gem is meant to serve the extremely common use-case of connecting, querying and iterating over results to Microsoft SQL Server from Ruby using the FreeTDS's DB-Library API.
TinyTDS offers automatic casting to Ruby primitives along with proper encoding support. It converts all SQL Server datatypes to native Ruby primitives while supporting :utc or :local time zones for time-like types. To date it is the only Ruby client library that allows client encoding options, defaulting to UTF-8, while connecting to SQL Server. It also properly encodes all string and binary data.
The API is simple and consists of these classes:
tiny_tds is tested with Ruby v2.7 and upwards.
We precompile tiny_tds with FreeTDS and supporting libraries, which are dynamically linked at runtime. Therefore, you can run:
gem install tiny_tds
It should find the platform-specific gem.
You can also avoid getting the platform-specific gem if you want to compile FreeTDS and supporting libraries yourself:
gem install tiny_tds --platform ruby
Install FreeTDS via Homebrew:
brew install openssl@3 libiconv
brew install freetds
Then you can install tiny_tds:
gem install tiny_tds
tiny_tds will find FreeTDS and other libraries based on your compiler paths. Below you can see an example on how to install FreeTDS on a Debian system.
$ apt-get install wget
$ apt-get install build-essential
$ apt-get install libc6-dev
$ wget http://www.freetds.org/files/stable/freetds-1.4.23.tar.gz
$ tar -xzf freetds-1.4.23.tar.gz
$ cd freetds-1.4.23
$ ./configure --prefix=/usr/local --with-tdsver=7.4 --disable-odbc
$ make
$ make install
You can also tell tiny_tds where to find your FreeTDS installation.
gem install tiny_tds -- --with-freetds-dir=/opt/freetds
Optionally, Microsoft has done a great job writing an article on how to get started with SQL Server and Ruby using TinyTDS, however, the articles are using outdated versions.
Our goal is to support every SQL Server data type and convert it to a logical Ruby object. When dates or times are returned, they are instantiated to either :utc or :local time depending on the query options. Only [datetimeoffset] types are excluded. All strings are associated to the connection's encoding and all binary data types are associated to Ruby's ASCII-8BIT/BINARY encoding.
Below is a list of the data types we support when using the 7.3 TDS protocol version. Using a lower protocol version will result in these types being returned as strings.
Connect to a database.
client = TinyTds::Client.new username: 'sa', password: 'secret', host: 'mydb.host.net'
Creating a new client takes a hash of options. For valid iconv encoding options, see the output of iconv -l. Only a few have been tested, and are highly recommended to leave blank for the UTF-8 default.
call-able object such as a Proc or a method to receive info messages from the database. It should have a single parameter, which will be a TinyTds::Error object representing the message. For example:opts = ... # host, username, password, etc
opts[:message_handler] = Proc.new { |m| puts m.message }
client = TinyTds::Client.new opts
# => Changed database context to 'master'.
# => Changed language setting to us_english.
client.execute("print 'hello world!'").do
# => hello world!
Use the #active? method to determine if a connection is good. The implementation of this method may change but it should always guarantee that a connection is good. Current it checks for either a closed or dead connection.
client.dead? # => false
client.closed? # => false
client.active? # => true
client.execute("SQL TO A DEAD SERVER")
client.dead? # => true
client.closed? # => false
client.active? # => false
client.close
client.closed? # => true
client.active? # => false
Escape strings.
client.escape("How's It Going'") # => "How''s It Going''"
Send a SQL string to the database and return a TinyTds::Result object.
result = client.execute("SELECT * FROM [datatypes]")
A result object is returned by the client's execute command. It is important that you either return the data from the query, most likely with the #each method, or that you cancel the results before asking the client to execute another SQL batch. Failing to do so will yield an error.
Calling #each on the result will lazily load each row from the database.
result.each do |row|
# By default each row is a hash.
# The keys are the fields, as you'd expect.
# The values are pre-built Ruby primitives mapped from their corresponding types.
end
A result object has a #fields accessor. It can be called before the result rows are iterated over. Even if no rows are returned, #fields will still return the column names you expected. Any SQL that does not return columned data will always return an empty array for #fields. It is important to remember that if you access the #fields before iterating over the results, the columns will always follow the default query option's :symbolize_keys setting at the client's level and will ignore the query options passed to each.
result = client.execute("USE [tinytdstest]")
result.fields # => []
result.do
result = client.execute("SELECT [id] FROM [datatypes]")
result.fields # => ["id"]
result.cancel
result = client.execute("SELECT [id] FROM [datatypes]")
result.each(:symbolize_keys => true)
result.fields # => [:id]
You can cancel a result object's data from being loading by the server.
result = client.execute("SELECT * FROM [super_big_table]")
result.cancel
You can use results cancelation in conjunction with results lazy loading, no problem.
result = client.execute("SELECT * FROM [super_big_table]")
result.each_with_index do |row, i|
break if row > 10
end
result.cancel
If the SQL executed by the client returns affected rows, you can easily find out how many.
result.each
result.affected_rows # => 24
This pattern is so common for UPDATE and DELETE statements that the #do method cancels any need for loading the result data and returns the #affected_rows.
result = client.execute("DELETE FROM [datatypes]")
result.do # => 72
Likewise for INSERT statements, the #insert method cancels any need for loading the result data and executes a SCOPE_IDENTITY() for the primary key.
result = client.execute("INSERT INTO [datatypes] ([xml]) VALUES ('
')")
result.insert # => 420
The result object can handle multiple result sets form batched SQL or stored procedures. It is critical to remember that when calling each with a block for the first time will return each "row" of each result set. Calling each a second time with a block will yield each "set".
…
Use the #sqlsent? and #canceled? query methods on the client to determine if an active SQL batch still needs to be processed and or if data results were canceled from the last result object. These values reset to true and false respectively for the client at the start of each #execute and new result object. Or if all rows are processed normally, #sqlsent? will return false. To demonstrate, lets assume we have 100 rows in the result object.
client.sqlsent? # = false
client.canceled? # = false
result = client.execute("SELECT * FROM [super_big_table]")
client.sqlsent? # = true
client.canceled? # = false
result.each do |row|
# Assume we break after 20 rows with 80 still pending.
break if row["id"] > 20
end
client.sqlsent? # = true
client.canceled? # = false
result.cancel
client.sqlsent? # = false
client.canceled? # = true
It is possible to get the return code after executing a stored procedure from either the result or client object.
client.return_code # => nil
result = client.execute("EXEC tinytds_TestReturnCodes")
result.do
result.return_code # => 420
client.return_code # => 420
Every TinyTds::Result object can pass query options to the #each method. The defaults are defined and configurable by setting options in the TinyTds::Client.default_query_options hash. The default values are:
Each result gets a copy of the default options you specify at the client level and can be overridden by passing an options hash to the #each method. For example
result.each(:as => :array, :cache_rows => false) do |row|
# Each row is now an array of values ordered by #fields.
# Rows are yielded and forgotten about, freeing memory.
end
Besides the standard query options, the result object can take one additional option. Using :first => true will only load the first row of data and cancel all remaining results.
result = client.execute("SELECT * FROM [super_big_table]")
result.each(:first => true) # => [{'id' => 24}]
By default row caching is turned on because the SQL Server adapter for ActiveRecord would not work without it. I hope to find some time to create some performance patches for ActiveRecord that would allow it to take advantages of lazily created yielded rows from result objects. Currently only TinyTDS and the Mysql2 gem allow such a performance gain.
暂无开放 Issues，或尚未同步最近议题。