sharagoz.com

Rails + SQL Anywhere 10 on Linux

Published October 20th 2011

I recently had to make a rails app talk to an sqlanywhere database. The app was to be deployed on a linux server that runs nginx + passenger. This is a fairly simple process, but there arent a lot of information on it on the internet so I'm writing a quick article.

Setting up a development environment on (arch) linux:

Here's how to make a rails app talk to a local database server. If you wanna talk to an external DB then see the section on deployment.

(The paths used here assumes that you install sql anywhere to /opt/sqlanywhere10 and that you use the 64-bit version)

The first step is to download and install the sql anywhere 10 developer edition for linux and get it patched to the latest version. I cant link to the tarball as it requires registration.
Extract the files and run the setup file. The installation is straight forward. I installed all the modules to be able to set up a local database server and use the Sybase Central. This requires a developer key, which my company already had. I think you can get one by registering on the sybase site.

Right out of the gates you will have a couple of issues. Try to check which version you have:



/opt/sqlanywhere10/bin64/dbsrv10 -v

You will probably get this error:



error while loading shared libraries: libdbserv10_r.so: cannot open shared object file: No such file or directory

This is because sql anywhere requires some environment variables to be set up
You can set these up by sourcing a .sh script like so:



source /opt/sqlanywhere10/bin64/sa_config.sh

Try checking the version again. It should show something like:



10.0.1.4239

If your version is below 10.0.1.4000 you'll most likely run into issues down the road. Get a hold of the update patch (again I cant link, as it requires login). Do not continue until you get this resolved.

Having to source that sa_config.sh file every time you open a new shell gets annoying, so I recommend you add the command to /etc/profile or ~/.bashrc

Btw, you may want to check out Sybase Central. It can be started with this command (if you have sourced that config file):



scjview

Sybase central enables you to do administer databases through a GUI, which is helpfull.

Right, once you have a patched version of sqlanywhere 10 it is time to start a database server. Most likely you have an existing database you can use, otherwise you can create one through Sybase Central.

This command launches the server:



/opt/sqlanywhere10/bin64/dbsrv10 -n your_sa_server_name /path/to/database/file
<code>

If everything is set up correctly you should get a stream of output that ends with these lines:

<code>
Starting on port 2638
TCPIP link started successfully
Now accepting requests


Now we need to configuring Rails to talk to this database server and get the active record adapter installed.

Add the activerecord-sqlanywhere-adapter to the gemfile:



gem 'activerecord-sqlanywhere-adapter'

And run bundle install

Set up database.yml



development:
adapter: sqlanywhere
server: your_sa_server_name # This must match what you set as the -n directive when you started the database server
database: filename # This must match the filename of your database file. E.G: If the filename is my_database.db then use "my_database"
host: localhost
encoding: utf-8

Start a console with rails c and execute a command to check that everything is OK:



ActiveRecord::Base.connection.execute("SELECT * FROM my_table")

At the point of writing there are some issues with the sqlanywhere adapter for active record when used with Rails 3.0 and 3.1. I have a fork on Github that fixes some of these issues. Hopefully the maintainer will look into it soon and get it fixed properly in the main repo.

Setting up the production environment on (arch) linux:

When installing on the server you only need the module called "Network Client" (no licence key required) if you are going to talk to a database on a different server.

Make sure you apply the patch on the server too.

When you are talking to an external database server you will have to specify its IP in database.yml



production:
adapter: sqlanywhere
database: external_db_name
server: external_server_name
commlinks: TCPIP(IP=80.80.80.80:2638)
encoding: utf-8

When you deploy the app you'll most likely get this error "Could not load SQLAnywhere DBCAPI library". That is because those environment variables are missing again. On the server you cant simply source the script through /etc/profile or ~/.bashrc because the web server is not launched through a shell. Find a way to make the application server source the file.
I am using Nginx + passenger, and the way to do it here is to create a custom ruby wrapper that launches passenger. Here's how:

In nginx.conf there is a line that looks like this:



passenger_ruby /usr/local/rvm/wrappers/ruby-1.9.2-p180/ruby

Edit that command to use a custom wrapper that you'll define. E.G:



passenger_ruby /usr/local/rvm/wrappers/ruby-1.9.2-p180/custom_ruby

Copy the script from the original wrapper into the new custom wrapper and add the source command where appropriate. My wrapper looks like this:



#!/usr/bin/env bash

if [[ -s "/usr/local/rvm/environments/ruby-1.9.2-p180" ]]
then
source "/opt/sqlanywhere10/bin64/sa_config.sh" # This is the custom line
source "/usr/local/rvm/environments/ruby-1.9.2-p180"
exec ruby "$@"
else
echo "ERROR: Missing RVM environment file: '/usr/local/rvm/environments/ruby-1.9.2-p180'" >&2
exit 1
fi


Restart nginx and that missing dbcapi error should be gone.

If you run into any issues, feel free to message me on Github and I'll try to assist.