Caelum's Blog

I will briefly discuss setting up the development version of Sybase SQL Anywhere (ASA) on linux (or similar) and using it with Perl.

You can download it from:

http://www.sybase.com/detail?id=1016644

Go through the install process, and make a separate user for it (e.g. sybase-asa).

In the .bashrc for the user, add:

. /opt/sqlanywhere11/bin32/sa_config.sh 

Change to that user, and now lets create a database:

mkdir logs
dbinit -t ./logs/hlaghdb.log -p 4096 -dba DBA,SQL hlaghdb.db

This sets the username to DBA and the password to SQL.

Let's make an init script. Note that you should not install the init script to run automatically because of the obnoxious agree to license prompt on startup, use it as just a utility for starting the database. Edit the part at the top.

#!/bin/sh
#
# Startup script for Sybase SQL Anywhere
# 
# description: Sybase SQL Anywhere RDBMS System
# processname: dbsrv11

ASA_USER=sybase-asa
ASA_BIN=/opt/sqlanywhere11/bin32/
DB_FILE=hlaghdb.db
SRV_NAME=ASA

# Find the name of the script
NAME=`basename $0`

# For SELinux we need to use 'runuser' not 'su'
if [ -x /sbin/runuser ]
then
        SU=runuser
else
        SU=su
fi

# Find home
ASA_HOME=`$SU $ASA_USER -c 'echo \$HOME'`

start() {
    ASA_START=$"Starting ${NAME} service: "

    $SU $ASA_USER -c ". $ASA_BIN/sa_config.sh; $ASA_BIN/dbsrv11 -ud -n $SRV_NAME $ASA_HOME/$DB_FILE"

    ret=$? 
    if [ $ret -eq 0 ]
    then
            echo "$ASA_START Success."
    else
            echo "$ASA_START Failed!"
            exit 1
    fi
    echo
}

stop() {
    echo -n $"Stopping ${NAME} service: "

    pkill dbsrv11

    ret=$?
    if [ $ret -eq 0 ]
    then
            echo "Success."
    else
            echo "Failed!"
            exit 1
    fi
    echo
}

restart() {
    stop
    start
}

case "$1" in
    start)
        start
        ;;
    stop)
        stop
        ;;
    restart)
        restart
        ;;
    *)
        echo $"Usage: $0 {start|stop|restart}"
        exit 1
esac
exit 0

SQL Anywhere comes with a GUI and a Curses SQL shell, named dbisql and dbisqlc respectively.

Since it uses the TDS protocol, you can also use the ASE isql with rlwrap or the freely available sqsh utility.

To use sqsh, first set the SYBASE environment variable to some directory, for example $HOME/sybase. In that directory make a file called interfaces that contains the following:

ASA
        master tcp ether localhost 2638
        query tcp ether localhost 2638

Then to connect use:

sqsh -S ASA -D hlaghdb -U DBA -P SQL

statements are executed by typing go on a new line. To quit type quit on a new line.

To make a non-DBA user, use the following commands in a SQL shell:

grant connect to hlagh identified by dongs
grant resource to hlagh

Now install the DBD::SQLAnywhere perl driver.

Source the sa_config.sh and make a copy of the /opt/sqlanywhere11/sdk/perl directory. Change to the directory and:

perl Makefile.PL
make
sudo make install

Do not use the DBD::SQLAnywhere on CPAN, it is outdated and doesn't work.

Add /opt/sqlanywhere11/lib32 to your LD_LIBRARY_PATH, and try connecting from DBI:

perl -MDBI -le 'my $dbh = DBI->connect(
  "dbi:SQLAnywhere:ASA", "DBA", "SQL"); \
  print for $dbh->selectrow_array("select 42")'

DBIx::Class support for both the DBD and via DBD::ODBC is available in the current version.

DBIx::Class::Schema::Loader support is available as of version 0.05002.

To use with unixODBC, add the following to /etc/odbcinst.ini:

[SybASA]
Description = Sybase SQL Anywhere
Driver = /opt/sqlanywhere11/lib32/libdbodbc11.so
Setup = /opt/sqlanywhere11/lib32/libdbodbc11.so
FileUsage = 1

There are also _r versions of the drivers in that directory, these are the threaded libraries.

Then to connect from Perl:

perl -MDBI -le 'my $dbh = DBI->connect( \
  "dbi:ODBC:driver=SybASA;ENG=ASA", \
  "DBA", "SQL"); \
  print for $dbh->selectrow_array("select 42")'