Ruby Methods - MSSQL Queries

midacts · January 19, 2017, 3:49pm

I am looking to populate dynamic dropdowns using ruby to run select queries on MSSQL servers.
Do any of you know how to get somethig like this working?

This probably isn’t the best path, but i looked into getting tiny_tds to work which appears to be an option for getting ruby to talk to MSSQL databases.

I installed freetds (yum install freetds) and tried to install tiny_tds (gem install tiny_tds), but it throws an error when trying to do the install.

I posted my error on this github page (https://github.com/github/pages-gem/issues/133) and they said:
It looks like you’re using a custom version of Ruby installed to /opt/rh/rh-ruby22/root/usr. Installing ruby-devel won’t install the headers into this location, but into /usr/share/include/ruby.h instead. You will need to install the headers for the binary located at /opt/rh/rh-ruby22/root/usr/bin/ruby
(ruby-devel is a pre req for tiny_tds)

Basically, I don’t know what the best way to get this working. Maybe it is a fools errand?

hayesr · January 22, 2017, 4:53am

I’ve worked with the tiny_tds gem and connecting to MSSQL from Linux before, it’s totally doable. Tiny_tds needs to compile some C code with the headers from your Ruby version, but it looks like you have a Ruby install that didn’t put the headers in the expected place. You can fix the Ruby install or point the gem install process at the location of your headers. I believe it would look something like gem install tiny_tds -- --with-ruby-include="path/to/your/ruby/headers"

How did you install Ruby? I personally install Ruby from source when using RHEL/CentOS7 (easier than it sounds). Otherwise I’d recommend rbenv/ruby-build.

midacts · January 22, 2017, 8:06am

We are using Cloudforms (ManageIQ) from Red Hat. It is deployed from an OVA
file. So ruby already came pre installed that way.

I wasn’t aware that it installed to a different location until you
mentioned it.
I’ll try using the switch to install pointing to a specific ruby path.
Maybe that will work.

We already got tiny_tds to work and run queries on another machine so it
will do what we need it to.

midacts · January 23, 2017, 4:43pm

This is ruby from the default cloudforms appliance. it still complains about not seeing the header file (because it does not exist) here:
/opt/rh/rh-ruby22/root/usr/share/include/ruby.h
(there is not an include folder under /opt/rh/rh-ruby22/root/usr/share)

[root@cfdb1 ~]# find / -name ruby.h
/usr/include/ruby/ruby.h
/usr/include/ruby.h

[root@cfdb1 ~]# gem install tiny_tds --source http://rubygems.org/ – --with-ruby-include="/usr/include/ruby.h"
Building native extensions with: '–with-ruby-include=/usr/include/ruby.h’
This could take a while…
ERROR: Error installing tiny_tds:
ERROR: Failed to build gem native extension.

/opt/rh/rh-ruby22/root/usr/bin/ruby -r ./siteconf20170123-15603-1ynqd5y.rb extconf.rb --with-ruby-include=/usr/include/ruby.h

mkmf.rb can’t find header files for ruby at /opt/rh/rh-ruby22/root/usr/share/include/ruby.h

extconf failed, exit code 1

Gem files will remain installed in /opt/rh/rh-ruby22/root/usr/local/share/gems/gems/tiny_tds-1.1.0 for inspection.
Results logged to /opt/rh/rh-ruby22/root/usr/local/lib64/gems/ruby/tiny_tds-1.1.0/gem_make.out

[root@cfdb1 ~]# gem install tiny_tds --source http://rubygems.org/ – --with-ruby-include="/usr/include/ruby/ruby.h"
Building native extensions with: '–with-ruby-include=/usr/include/ruby/ruby.h’
This could take a while…
ERROR: Error installing tiny_tds:
ERROR: Failed to build gem native extension.

/opt/rh/rh-ruby22/root/usr/bin/ruby -r ./siteconf20170123-15610-1hnze6p.rb extconf.rb --with-ruby-include=/usr/include/ruby/ruby.h

mkmf.rb can’t find header files for ruby at /opt/rh/rh-ruby22/root/usr/share/include/ruby.h

extconf failed, exit code 1

Gem files will remain installed in /opt/rh/rh-ruby22/root/usr/local/share/gems/gems/tiny_tds-1.1.0 for inspection.

jrafanie · January 24, 2017, 4:13am

@midacts I believe ruby.h is provided by the rh-ruby22-ruby-devel rpm package in software collections. Try installing that one and see if it helps. You can browse the similarly named but yet different centos versions here.

midacts · January 24, 2017, 2:02pm

I think maybe I am not setting up the server to talk to the repo where
rh-ruby22-ruby-devel lives?

[root@sev1 ~]# yum-config-manager --enable rhel-server-rhscl-7-rpms
Loaded plugins: product-id

[root@sev1 ~]# yum install rh-ruby22-ruby-devel
Loaded plugins: product-id, search-disabled-repos, subscription-manager
Unix_EPEL_EPEL_7 | 2.1 kB 00:00:00
rhel-7-server-extras-rpms | 2.0 kB 00:00:00
rhel-7-server-optional-rpms | 2.0 kB 00:00:00
rhel-7-server-rh-common-rpms | 2.1 kB 00:00:00
rhel-7-server-rpms | 2.0 kB 00:00:00
rhel-7-server-satellite-tools-6.2-rpms | 2.1 kB 00:00:00
rhel-7-server-supplementary-rpms | 2.0 kB 00:00:00
rhel-ha-for-rhel-7-server-rpms | 2.0 kB 00:00:00
No package rh-ruby22-ruby-devel available.
Error: Nothing to do

geertj · January 24, 2017, 3:24pm

@jrafanie wouldn’t rh-ruby22-ruby-devel bring in rh-ruby22-ruby (or whatever the basic runtime is called) as a dependency, which would likely conflict with our system Ruby?

If we need to support compiled extensions then I think the right approach would be to install the headers when we install Ruby I didn’t check that this isn’t done but from this thread I assume it is not. The installer is called from here:

github.com

ManageIQ/manageiq-appliance-build/blob/master/kickstarts/partials/post/ruby_install.ks.erb

# The ruby-install will result in about 350MiB of files in /usr/local/src.
# These do not need to be in the image, so put them on a tmpfs.
mkdir -p /usr/local/src
mount -t tmpfs tmpfs /usr/local/src

# Note, we hardcode the ruby patch level for now.
# Once the --update option is available, we could ask
# ruby-install to "learn" what new rubies are available
# See: https://github.com/postmodern/ruby-install/issues/175
ruby-install --system ruby 2.3.1 -- --disable-install-doc --enable-shared

# ensure /usr/local/bin is on the path as this is where ruby, gem, and bundle will be installed
export PATH=$PATH:/usr/local/bin

# Free up tmpfs memory.
umount /usr/local/src

# Add the git based gem bin path to $PATH
ruby_gem_path="$(gem env gemdir)/bin"
echo "export PATH=\$PATH:$ruby_gem_path" >> /etc/default/evm

This file has been truncated. show original

Also ping @simaishi

midacts · January 24, 2017, 3:29pm

I don’t see any header files under the include folder under the custom ruby install location Cloudforms seems to use:
/opt/rh/rh-ruby22/root/usr/share/include/ruby.h

-Is is supported to install additional gems on the Cloudforms appliances?
-What would you suggest as next steps (I’ve been working on this for over a week and havent made any progress)?

simaishi · January 24, 2017, 4:45pm

@geertj Since we’re talking about CloudForms (not ManageIQ), the process is a bit different… The CF appliances don’t come with packages needed to compile extensions, as whatever we need is pre-built.

@midacts Can you double-check your repo settings? rh-ruby22-ruby-devel is available in the SCL repo, so you should be able to install the package.

jrafanie · January 24, 2017, 4:49pm

I don’t believe the *-ruby package brings in the *-ruby-devel since all rpms are already compiled and don’t need ruby.h at runtime. Upstream uses ruby-install which installs ruby from source and probably includes ruby.h. Downstream cfme uses SCL ruby 2.2 or 2.3 rpm for cfme 5.7+ which follows rpm guidelines and has ruby.h as a separate (-devel) rpm.

I don’t think it would be a problem to add ruby-devel rpm as a rpm dependency for the packaged cfme. I would need @simaishi’s expertise but imagine we could install it by default if that’s the request.

Joe

midacts · January 24, 2017, 5:10pm

Our satellite server didnt have those repos so we added the Centos repo and we were able to install the ‘rh-ruby22-ruby-devel’ package.

From there, we were able to install tiny_tds successfully.

Thank you guys for your help on this!! (We had been beating our head against the wall on this for several days).

midacts · January 25, 2017, 12:58pm

After installing tiny_tds and doing some testing it looks like it does not support WIndows Authentication…
Do you guys know of any alternatives to being able to do MSSQL queries with Ruby (specificially with WIndows Authentication)?

midacts · January 25, 2017, 4:31pm

We were able to get tiny_tds installed…but it doesn’t look like tiny_tds supports Windows Authentication to SQL database? : <