It just so happens that I stumbled upon this problem today.
So, I want to start off by saying that my experience with DataMapper
is only a few hours, but I think I might have found a sledgehammer
approach to your problem.
The issue seems to be all of the query variables that it's sending to
the server. To start, try doing this:
require 'rubygems'
require 'do_sqlserver'
@connection = DataObjects::Connection.new('sqlserver://user:pass@host/
database')
@connection.create_command('SELECT getdate()').execute_reader
You'll get output like:
#<DataObjects::SqlServer::Reader:0x7a80747 field_types=[DateTime],
field_count=1, opened=false, fields=[""]>
With that, you know you can connect and the problem is in how
DataMapper is setting up the connection.
My sledgehammer approach (not really recommended) was to edit a couple
of the data_objects files so that they don't pass query params when
making the connection.
In data_objects-0.10.3/lib/data_objects/uri.rb:
Find "if query". Basically, I commented out that whole if block so
that it wouldn't be adding the query params when making a string.
In data_objects-0.10.3/lib/data_objects/connection.rb:
Find "# Exceptions to how a driver class is determined for a given
URI" Right before that comment I added:
conn_uri = DataObjects::URI::parse(conn_uri.to_s)
Basically what that does (in a kludgy way) is reset the connection uri
based off the string without the query parameters.
Finally, in your config/database.yml, start your database name with a
"/". So, if you want to connect to "mydb", put in "database: /mydb".
Now, when I boot up my Rails console, I no longer get an error
message:
jruby-1.6.0 :002 > repository.adapter.select("select getdate()")
=> [Wed, 06 Apr 2011 15:54:43 -0400]
**NOTE** This isn't a wonderful thing I've done here. I've basically
yanked out a bit of functionality from DataObject's Connection class
(the ability to handle query parameters that might be used to set
encoding or something else). However, the problem is isolated and
hopefully someone with more than an afternoon's experience with
DataMapper can chime in on how this should be fixed. I might look
more into how it should be fixed, but I wanted to send this email out
sooner than later since you hit this same problem nearly a week ago
and it's always annoying when there's no one who has information on
your issue.
Digging a little deeper, it seems like you could override
"normalized_uri". It's defined in the DataObjectsAdapter (which
SqlserverAdapter inherits from). Actually, this could be a bug.
"query" is supposed to equal nil if there aren't any options other
than the standard ones (defined in "keys").
dm-do-adapter-1.1.0/lib/dm-do-adapter/adapter.rb:
keys = [
:adapter, :user, :password, :host, :port, :path, :fragment,
:scheme, :query, :username, :database ]
query = DataMapper::Ext::Hash.except(@options, keys)
query = nil if query.empty?
raise query.inspect
RuntimeError: {"adapter"=>"sqlserver", "database"=>"/my_database",
"path"=>"/my_database", "user"=>"username", "password"=>"sekret_kode",
"host"=>"myserver.com"}
query should be nil there since it should be the items that aren't in
keys. The problem is twofold. First, "keys" is an array of symbols
while @options has string keys. Second, DataMapper::Ext::Hash.except
takes a variable second parameter meaning that it has to be:
DataMapper::Ext::Hash.except(@options, *keys)
Anyway, I guess other servers don't complain about the non-removal of
the query params. SQLServer is sometimes a bit on the different side.
I haven't been able to test this second bit and it's now getting late,
but that seems like it's the culprit. I'll test it tomorrow and if
it's good I'll fork and make a pull request. Based on the code
written, it's clear that it wants "query" not to have the @options
that are in "keys" so it shouldn't break anything (unlike my earlier
kludge which you really shouldn't use in production). Because
"except" was expecting an expanded second param and the keys are in
the wrong format (string vs. symbol) it was just silently failing to
remove them and other databases weren't complaining so it seemed fine.
Sean.
P.S. Sorry for the fact that I wrote this email as I was figuring out
what was going on. tl;dr: dm-do-adapter-1.1.0/lib/dm-do-adapter/
adapter.rb looks like it has a bug where query is being populated with
items that are supposed to be removed from the hash, but aren't. I'll
test this tomorrow and if it works well, fork on github and submit a
pull request (or someone can tell me of a different workflow that the
DataMapper team uses for fixes).
On Apr 1, 12:14 pm, ladicha <[email protected]> wrote:
> I'm having troubles connecting to a sqlserver database and I was
> hoping other sqlserver users might be able to help me figure out what
> I'm doing wrong. I can connect via freetds/odbc and tinytds with
> activerecord, but I would like to use datamapper and dm-sqlserver-
> adapter. Any advice on how I might resolve my difficulties would be
> most appreciated.
>
> Lots of details below.
>
> Lonnie
>
> I'm trying to connect with this connection string:
>
> irb(main):003:0> DataMapper.setup(:default,'sqlserver://
> lsmith:b@dp@[email protected]:1433/
> AdventureWorks;instance=MSSQLSERVER')
> => #<DataMapper::Adapters::SqlserverAdapter:0x10e8647 @name=:default,
> @resource_naming_convention=DataMapper::NamingConventions::Resource::Unders
> coredAndPluralized,
> @normalized_uri=#<struct DataObjects::URI scheme="sqlserver",
> user="lsmith", password="b@dp@ss", host="192.168.1.21", port=1433,
> path="/AdventureWorks;instance=MSSQLSERVER",
> query={"scheme"=>"sqlserver", "user"=>"lsmith", "password"=>"b@dp@ss",
> "host"=>"192.168.1.21", "port"=>1433, "path"=>"/
> AdventureWorks;instance=MSSQLSERVER", "query"=>nil, "fragment"=>nil,
> "adapter"=>"sqlserver"}, fragment=nil>,
> @field_naming_convention=DataMapper::NamingConventions::Field::Underscored,
> @options={"scheme"=>"sqlserver", "user"=>"lsmith",
> "password"=>"b@dp@ss", "host"=>"192.168.1.21", "port"=>1433, "path"=>"/
> AdventureWorks;instance=MSSQLSERVER", "query"=>nil, "fragment"=>nil,
> "adapter"=>"sqlserver"}>
> irb(main):004:0> repository(:default).adapter.select('select
> GETDATE()')
> DataObjects::SQLError: Can't connect: jtds.sqlserver://lsmith:b%40dp
> %[email protected]:1433/AdventureWorks;instance=MSSQLSERVER?
> scheme=sqlserver&user=lsmith&password=b%2540dp
> %2540ss&host=192.168.1.21&port=1433&path=%252FAdventureWorks
> %253Binstance%253DMSSQLSERVER&query=&fragment=&adapter=sqlserver
> Server 192.168.1.21 has no instance named MSSQLSERVER?
> scheme=sqlserver&user=lsmith&password=b%2540dp
> %2540ss&host=192.168.1.21&port=1433&path=%252FAdventureWorks
> %253Binstance%253DMSSQLSERVER&query=&fragment=&adapter=sqlserver.
> (code: , sql state: , query: , uri: )
> from /home/lsmith/code/ruby/jruby/lib/ruby/gems/1.8/gems/
> data_objects-0.10.3/lib/data_objects/pooling.rb:177:in `new'
> from /home/lsmith/code/ruby/jruby/lib/ruby/gems/1.8/gems/
> data_objects-0.10.3/lib/data_objects/pooling.rb:172:in `new'
> from /home/lsmith/code/ruby/jruby/lib/ruby/gems/1.8/gems/
> data_objects-0.10.3/lib/data_objects/pooling.rb:119:in `new'
> from /home/lsmith/code/ruby/jruby/lib/ruby/gems/1.8/gems/
> data_objects-0.10.3/lib/data_objects/connection.rb:65:in `new'
> from /home/lsmith/code/ruby/jruby/lib/ruby/gems/1.8/gems/dm-do-
> adapter-1.1.0/lib/dm-do-adapter/adapter.rb:251:in `open_connection'
> from /home/lsmith/code/ruby/jruby/lib/ruby/gems/1.8/gems/dm-
> transactions-1.1.0/lib/dm-transactions/adapters/dm-do-adapter.rb:69:in
> `open_connection'
> from /home/lsmith/code/ruby/jruby/lib/ruby/gems/1.8/gems/dm-do-
> adapter-1.1.0/lib/dm-do-adapter/adapter.rb:276:in `with_connection'
> from /home/lsmith/code/ruby/jruby/lib/ruby/gems/1.8/gems/dm-do-
> adapter-1.1.0/lib/dm-do-adapter/adapter.rb:33:in `select'
> from (irb):4
> irb(main):005:0>
>
> It looks like the connection parameters are being appended to the end
> of the instance name, but I'm not sure if that's normal. I have also
> tried with an options hash, and I get the same results:
>
> DataMapper.setup(:default, {
> :adapter => 'sqlserver',
> :database => "AdventureWorks",
> :username => 'lsmith',
> :password => 'b@dp@ss,
> :host => '192.168.1.21',
> :path => "AdventureWorks;instance=MSSQLSERVER"
>
> })
>
> Here are my gem and jruby versions:
>
> data_objects (0.10.3)
> datamapper (1.1.0)
> dm-aggregates (1.1.0)
> dm-constraints (1.1.0)
> dm-core (1.1.0)
> dm-do-adapter (1.1.0)
> dm-migrations (1.1.0)
> dm-serializer (1.1.0)
> dm-sqlserver-adapter (1.1.0)
> dm-timestamps (1.1.0)
> dm-transactions (1.1.0)
> dm-types (1.1.0)
> dm-validations (1.1.0)
> do-jdbc_sqlserver (1.2.4)
> do_jdbc (0.10.3)
> do_sqlserver (0.10.3)
>
> jruby 1.6.0.dev (ruby 1.8.7 patchlevel 249) (2011-03-26 6586) (OpenJDK
> Client VM 1.6.0_20)
>
> On a windows box, I have verified that there is a MSSQLSERVER instance
> listening on port 1433 on 192.168.1.21
> udp port 1434 is also listening - which is the SQL Server Browser
> Service.
>
> C:\PortQryV2>PortQry.exe -n 192.168.1.21 -e 1434 -p UDP
>
> Querying target system called:
>
> 192.168.1.21
>
> querying...
>
> UDP port 1434 (ms-sql-m service): LISTENING or FILTERED
>
> Sending SQL Server query to UDP port 1434...
>
> Server's response:
>
> ServerName CASTDB
> InstanceName MSSQLSERVER
> IsClustered No
> Version 10.0.1600.22
> tcp 1433
>
> ==== End of SQL Server query response ====
>
> UDP port 1434 is LISTENING
>
> C:\PortQryV2>
>
> I can connect also via freetds/odbc:
>
> $ cat /etc/odbc.ini
>
> [CastitDB]
> Driver = FreeTDS
> Description = ODBC Connection via FreeTDS
> Trace = No
> Servername = CastDB
> Port = 1433
> Database = AdventureWorks
> $ isql -v CastDB lsmith b@dp@ss
> +---------------------------------------+
> | Connected! |
> | |
> | sql-statement |
> | help [tablename] |
> | quit |
> | |
> +---------------------------------------+
> SQL> select GETDATE()
> +------------------------+
> | |
> +------------------------+
> | 2011-04-01 09:05:23.403|
> +------------------------+
> SQLRowCount returns 1
> 1 rows fetched
> SQL>
--
You received this message because you are subscribed to the Google Groups
"DataMapper" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/datamapper?hl=en.
