Caelum's Blog

It is available here: http://www.sybase.com/detail?id=1016644.

Open the package and double click the Install SQL Anywhere icon, select all components.

At the end click the Check for Updates button, it will take you to the EBF download. You will need to create a MySybase account to download the EBF package. Download the package, doubleclick the Install SQL Anywhere icon and select the update product option.

Add the following to your ~/.bashrc:

export SQLANYWHERE_HOME=/Applications/SQLAnywhere12/System
export DYLD_LIBRARY_PATH=$SQLANYWHERE_HOME/lib64:$SQLANYWHERE_HOME/lib32:$DYLD_LIBRARY_PATH
export DYLD_BIND_AT_LAUNCH=1
export PATH=$PATH:$SQLANYWHERE_HOME/bin64:$SQLANYWHERE_HOME/bin32

For the next step, open a new terminal session for the environment changes to take effect.

Execute the following to create a new database:

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

Create a file called ~/Library/LaunchAgents/com.ianywhere.sqlanywhere.plist, put the following in that file, changing /Users/rkitover to your actual home directory and the database file name to your actual database file:

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN"
"http://www.apple.com/DTDs/PropertyList-1.0.dtd" >
<plist version='1.0'>
<dict>
<key>Label</key><string>com.ianywhere.sqlanywhere</string>
<key>WorkingDirectory</key><string>/Users/rkitover/sqlanywhere</string>
<key>EnvironmentVariables</key>
    <dict>
        <key>DYLD_LIBRARY_PATH</key>
        <string>/Applications/SQLAnywhere12/System/lib64:/Applications/SQLAnywhere12/System/lib32</string>
        <key>DYLD_BIND_AT_LAUNCH</key>
        <string>1</string>
    </dict>
<key>ProgramArguments</key>
<array>
        <string>/Applications/SQLAnywhere12/System/bin32/dbsrv12</string>
        <string>-ud</string>
        <string>/Users/rkitover/sqlanywhere/hlaghdb.db</string>
</array>
<key>Debug</key><false/>
<key>Disabled</key><true/>
<key>KeepAlive</key><true/>
</dict>
</plist>

NOTE: we do not give the database server a name in this example. This is because for some reason when I tried to use -n option to dbsrv12 it responded with Communication error. Try running dbsrv12 from the command line with the -n option to see if it works for you, if it does add it to the ProgramArguments in the .plist file. E.g.:

dbsrv12 -ud -n SQLANYWHERE hlaghdb.db

If this works, add the following to the .plist file after dbsrv12:

<string>-n</string>
<string>SQLANYWHERE</string>

The following examples will all assume the database server has no name.

Now execute the following to load the service and start the database server:

launchctl load -w ~/Library/LaunchAgents/com.ianywhere.sqlanywhere.plist
launchctl start com.ianywhere.sqlanywhere

You should see the database server in ps:

$ ps aux | grep dbsrv
rkitover       51654   0.3  0.2  1627072  14000   ??  Ss    1:02PM   0:03.07 /Applications/SQLAnywhere12/System/bin32/dbsrv12 -ud /Users/rkitover/sqlanywhere/hlaghdb.db

It will be started across reboots.

launchctl stop doesn't work with this plist, so if you need to restart the server, just kill the process and do a launchctl stop followed by a launchctl start.

Open the SQLAnywhere12 folder under Applications in Finder.

The applications we're interested in are Sybase Central and Interactive SQL.

The Sybase Central application will allow you to configure all aspects of your database. To use it, open the application, double click on the SQL Anywhere 12 plugin, right click inside the blank window and choose Connect, choose Connect to a running database on this computer, enter the username and password you used with the dbinit command, which in this example is DBA and SQL respectively.

The Interactive SQL application is a handy GUI for testing SQL statements on your database. To login, open the application, choose Database authentication, choose Connect to a running database on this computer, enter your username and password and press Connect. Inside the application, enter your SQL statements in the SQL statements window and press F5 to execute them; you will see your results in the results window.

If you would prefer a commandline utility to Interactive SQL for testing SQL statements, you can use the sqsh utility from MacPorts (http://www.macports.org/.)

The sqsh utility is an interactive SQL utility for databases using the TDS protocol, such as Sybase ASE and Microsoft SQL Server. SQL Anywhere also uses the TDS protocol, so the utility works for these databases as well.

If you do not yet have an interfaces file, follow these steps to set one up.

In your .bashrc add the following:

export SYBASE=~/sybase

In this directory create a file called interfaces with the following:

SQLANYWHERE
  master tcp ether localhost 2638
  query tcp ether localhost 2638

Now you can launch sqsh like so:

sqsh -S SQLANYWHERE -U DBA -P SQL

To use it, type in a SQL statement, it can span multiple lines, then type go on a line by itself to execute it. This may take some getting used to if you're used to tools such as sqlplus or dbish, where SQL statements are terminated by a semicolon. You can use dbish with SQL Anywhere as well, I will describe how to do so further down.

If you have UnixODBC from MacPorts (http://www.macports.org/,) you can set up the included ODBC drivers with it.

In /opt/local/etc/odbcinst.ini add the following:

[SQLAnywhere]
Description = SQL Anywhere
Driver = /Applications/SQLAnywhere12/System/lib64/libdbodbc12.dylib
Setup = /Applications/SQLAnywhere12/System/lib64/libdbodbc12.dylib
FileUsage = 1

Now lets test it out (take out the -S flag to cpanm if you use local::lib):

curl -L http://cpanmin.us | perl - -S App::cpanminus
cpanm -n -S DBI
cpanm -n -S DBD::ODBC
perl -MDBI -le 'my $dbh = DBI->connect("dbi:ODBC:driver=SQLAnywhere", "DBA", "SQL"); print for $dbh->selectrow_array("select 42")'

First install DBI:

curl -L http://cpanmin.us | perl - -S App::cpanminus
cpanm -n -S DBI

Then build and install the Perl library:

cp -a /Applications/SQLAnywhere12/sdk/perl /tmp/sqlany
cd /tmp/sqlany
perl Makefile.PL
make
sudo make install
cd
rm -rf /tmp/sqlany

Test it out:

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

If you don't like Interactive SQL or sqsh, you can try the Perl module DBI::Shell for a commandline SQL utility.

First install it:

curl -L http://cpanmin.us | perl - -S App::cpanminus
cpanm -n -S DBI::Shell

Then launch it. I would recommend using the ODBC driver, especially if you use UUID columns:

dbish dbi:ODBC:driver=SQLAnywhere DBA SQL

To use the Perl binding:

dbish dbi:SQLAnywhere: DBA SQL

SQL statements are terminated by semicolons, type /quit to exit the shell and /help to see what commands are available.

If you would like to save the readline history of your sessions and use it for completion, you can wrap it with rlwrap. To do so add this alias to your ~/.bashrc:

alias sqlany="rlwrap -a -N -t dumb -i -f ~/.sqlanywhere_dbish_history -H ~/.sqlanywhere_dbish_history -s 30000 dbish dbi:ODBC:driver=SQLAnywhere DBA SQL"

and initialize the history file:

touch ~/.sqlanywhere_dbish_history

then you can type sqlany to launch the shell.

There is complete support for SQL Anywhere in DBIx::Class and DBIx::Class::Schema::Loader (the Catalyst create=static helper.)

Hope you found this guide helpful. Feel free to leave comments on the blog or email me if you have questions.