Problem with non native SQL Server ODBC

[Mark Burns]

Hello,

I was having a problem with my ODBC SQL Server connection (to SQL Server Express 2005).
I wasn't using a native ODBC connection and was finding that nvarchar columns were
being returned like this.
e.g.
text_field nvarchar(5)

>> Model.first.text_field
=>"1234\00\00"

with 2 null characters at the end and

>>text_field.length
=> 6
i.e. the length was one greater than the expected length

Anyway I found that switching to native drivers fixed the problem. So I thought I'd let people know
if they encounter this problem. And I don't know if this is any use to the maintainers?

BTW it's Win XP MINGW32, Ruby 1.9.1.

[Ken Collins]

I'd like to know more but I have to be treated like an ignorant fool
when you talk about windows. I just wasted a few hours of a nice
Friday evening playing around by installing 1.9.1 on XP in various
ways and kept failing. I cant even get a script/console session or a
gem installed with out tons of DLL issues. Ticks me off, I dont see
how anybody works on that platform.

That said, anything that you can provide and details would be greatly
appreciated. I get the gist of what your saying but would love to much
much much more. How did you get the ruby installed, connection stack
(just gems), what does native mean on windows, what does not native
mean. Details details details. Please share if you have the time.
Seriously, I'm a complete idiot when it comes to windows.


- Ken

[Mark Burns]

Tell me about it. It's a nightmare. I have to work with windows
for work (linux at home). Which means I don't have much understanding
of it either, or time to try things in my spare time.

I don't know why they don't bundle them with the install.
I don't know if there's some kind of licensing issues for
the dlls that means they can't bundle them or something
I spent around a week trying to get it up and running.
And probably around another week trying to get the
gems I needed running.

I almost got to the point of having to cancel the idea
of using ruby or ruby on rails for my project.
The loss of time has also meant I have less time for coding
and less time to try and help others.
But it's the weekend now, so I'll try my best to fill in the details.

Anyway, I'll look into it and try and figure out whether I can just
zip up my install and make that available.

Here's a rough summary of some of the things I needed to do
that I can remember:

Install the MinGW32 version which is compiled with the GNU
compiler rather than Microsoft Visual C++.
For any gems, choose the MINGW32 version.
I also had the fun of being btrehind a corporate firewall.
What this means is that you have to set the HTTP_PROXY
environment variable.
However, in practice I found that my proxy partially downloads
the file and saves it to disk with 0 bytes, (which triggers ruby into
action on a 0 byte file), my proxy then virus scans and after a
while sends the whole file, (but ruby has already given me an
error about an invalid gem format or something like that).
If I'm lucky the error message will contain the URL that
the gem was attempted to be downloaded from.
I can then wget this to a directory I created for downloaded gems
then do a
gem install -l gemname

When you try and start a console session.
You''ll get a bunch of missing DLL error messages.
These are clues, and you need to go treasure hunting
on those sites that provide downloads for missing DLLs.
I guess this is the MS equivalent of apt-get.

I spent perhaps a full day on downloading these alone.
That was really painful, and another reason why I'd be happy
to zip up my install.

I believe that at one point I had to build the ODBC from source.
Which is available here.
I'm not a C/C++ developer and so that stuff is a bit of a mystery to me.
But I'm pretty sure that the MSWin32 version didn't work and
so that's why I needed to compile for MinGW32.
I'm also not certain what combination of things resulted in
the magic of things working for me.

When I said native for windows what I meant is this.
If you are using ODBC then you can create an ODBC connection
file which stores connection information on your PC.
This is available in Control Panel>Administrative Tools>ODBC Data sources
(I think)

You can choose to create an ODBC data source and if you select the
type from the drop down then there's an option for each of the drivers
you have installed. E.g. MySQL and Oracle don't come as default.
I can't remember if SQL Server comes as default or not.
Anyway in this list, I had two types for SQL Server.
One was native, and the other non-native. I can't remember if that's
the exact naming or not.
I read somewhere else that the native driver only works for 2005 i.e.
not 2000. Luckily, for me I'm using 2005.

It was changing my ODBC connections to native that worked for me (with
this rails-sqlserver-adapter).
I'd guess that native means C++ based, and that non-native is
programmed using .Net. I guess the non-native is more flexible, and
the native version needs to be compiled for the processor architecture.
(I didn't have to do that, but perhaps whenever I installed the SQL
server tools it did this).

I think because of the barrier to entry for windows users, unfortunately
there's not much useful information available. Maybe those who have
entered Ruby-on-Rails world on a windows platform must be fewer in
number, and have much less free time as a result.

Anything that I say here could easily be outdated within a month.
In general I spent a lot of time googling irrelevant information and
finding out about other people's difficulties, which were 90% of the
time not useful for me.
I generally find that the chain of dependencies for the various gems
is often so complex (given that they may not work for ruby 1.9.1,
or for windows or for the MingW32 platform, or they may not be compatible
with the specific other gems you are using, etc), that sometimes
it seems that your specific requirements are not possible to be met.
Also there's not a great deal of crossover between people who understand
the linux build process/C++/ruby/windows/windows build process for MingW32.
So I may speak to C++ experts about what I need to do to get a native gem working,
who lose interest when I talk about anything Ruby (but may understand
the build process). Or there may be some really helpful linux/mac ruby experts who
have no idea about compiling things or windows.

My basic requirements were

Cucumber
Rails
Ruby 1.9.1 (for better unicode)
Oracle
SQL Server
MySQL
sqlite
ImageMagick/RMagick (I've still not cracked this yet)

I hope that any of this info helps someone in the future.

2009/11/7 Ken Collins <k...@metaskills.net>

[Ken Collins]

Mark,

> Maybe those who have entered Ruby-on-Rails world
> on a windows platform must be fewer in number, and
> have much less free time as a result.

I think this sums it up wonderfully! Every now and then I get
optimistic and think I can succeed at installing ruby on one of my
Windows VMWare images. I say to myself, it's going to be better this
time, this time – it will be different, this time – no one has to be
nailed to anything. Of course I'm horribly wrong!

So at this point, I'm gonna pass on your offer for a copy of your ruby
directory. I've got enough on my hands with my own side projects and
the upcoming rails 3 is going to be a large enough task when I find
time to move this project forward. Luckily the Windows issues from
where I sit are really not that large, the adapter is just ruby and if
you can get ruby "really honest to god running" on a Windows box, then
the true adapter issues that follow are solve-able edge cases. Mind
you, I think most people that publish blog articles on getting ruby
running on windows get to this point `ruby -e "puts 'itworks'"` and
think because that finally runs ruby is "working" on windows :(

Perhaps you can post to this ticket which might relate to your fix?

http://github.com/rails-sqlserver/2000-2005-adapter/issues#issue/17

- Thanks again,
Ken

[Mark Burns]

Yeah no worries
I will do that on Monday
Although I have no idea how successful that approach will be. :-)