Friday, March 18, 2011

Connect to MySQL database from LibreOffice Base on Ubuntu

LibreOffice's Base program can provide a quick easy way to access a MySQL database and write some reports against data.  The following outlines how to easily setup LibreOffice Base to connect to MySQL.  It assumes that you already have LibreOffice Base installed.  It works just as easily for OpenOffice Base.

First we need to install the JDBC driver for MySQL
sudo apt-get install libmysql-java
Next open up any LibreOffice program and then click on Tools->Options.  Expand LibreOffice and then click on Java.  Click on the Class Path button on the right followed by Add Archive.  Browse to /usr/share/java/mysql-connector-java.jar  Click Ok and choose to restart LibreOffice.

Now open LibreOffice Base and choose to Connect to an existing database.  In the drop-down menu choose MySQL, and connect using JDBC.  Click on the Test class button to make sure the JDBC driver loads successfully.  If it doesn't, then check that you followed the previous steps properly.  If it does load then simply enter the settings as it relates to your MySQL database.

If you get the JDBC driver to load properly, but you cannot connect to your MySQL database, then I would recommend installing the mysql client and making sure you can connect from the MySQL client first.  If the JDBC driver loads properly, and you can connect to your database with the MySQL database, then you should not have any problem connecting with LibreOffice.

Sunday, March 6, 2011

Installing Oracle 11gR2 on Ubuntu 10.04 Lucid Lynx 32-bit

I did this install in a virtual machine, which I would strongly suggest you do first before doing it on a physical install that you intend to use.
Start off by logging in as the user you created during the install.  This user should have sudo powers.  First we need to install the libraries needed.
sudo apt-get install unzip build-essential x11-utils rpm ksh lsb-rpm libaio1
Ubuntu 10.04 comes with libstdc++6, but we need libstdc++5_3.3.6.  Browse to http://mirrors.kernel.org/ubuntu/pool/universe/g/gcc-3.3/ and find the latest version.  When I wrote this it was 5_3.3.6-21.  Let's download and install it.
wget http://mirrors.kernel.org/ubuntu/pool/universe/g/gcc-3.3/libstdc++5_3.3.6-21ubuntu1_i386.deb
dpkg-deb -x libstdc++5_3.3.6-21ubuntu1_i386.deb ia32-libs
sudo cp ia32-libs/usr/lib/libstdc++.so.5.0.7 /usr/lib/
sudo ln -s /usr/lib/libstdc++.so.5.0.7 /usr/lib/libstdc++.so.5
Next, we need to setup the oracle users and groups.
sudo addgroup oinstall
sudo addgroup dba
sudo useradd -g oinstall -G dba -p password -d /home/oracle -s /bin/bash oracle
sudo mkdir /home/oracle
sudo chown -R oracle:dba /home/oracle
sudo ln -s /usr/bin/awk /bin/awk
sudo ln -s /usr/bin/rpm /bin/rpm
sudo ln -s /usr/bin/basename /bin/basename
sudo mkdir /etc/rc.d
for i in 0 1 2 3 4 5 6 S ; do sudo ln -s /etc/rc$i.d /etc/rc.d/rc$i.d ; done
sudo mkdir -p /u01/app/oracle
sudo chown -R oracle:dba /u01
Various configuration files need to be modified.  Next we back up the original configuration file and add the configurations.

sudo cp /etc/sysctl.conf /etc/sysctl.conf.bak
echo 'fs.file-max = 6815744
fs.aio-max-nr = 1048576
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 1048576
net.core.wmem_max = 1048576
net.ipv4.ip_local_port_range = 9000 65535
' | sudo tee -a /etc/sysctl.conf > /dev/null
sudo cp /etc/security/limits.conf /etc/security/limits.conf.bak
echo 'oracle soft nproc 2047
oracle hard nproc 16383
oracle soft nofile 1023
oracle hard nofile 65535' | sudo tee -a /etc/security/limits.conf > /dev/null
sudo sysctl -p

Now it is time to log out, and then log back in as our new oracle user.  As the oracle user download the software from oracle.com  You should be able to find it at http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
Open a terminal and change into the directory where you downloaded both files.  And then run the following to unzip and run the installer.

unzip linux_11gR2_database_1of2.zip
unzip linux_11gR2_database_2of2.zip
cd database
./runInstaller
From here it is up to you which options you want to choose.  I used all the defaults and supplied 'password' for my passwords, since it is just a test install.
Last, we just need to setup our system paths.

sudo echo '
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=orcl
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH
export EDITOR=/usr/bin/vi' >> /etc/profile
Your install is ready to go.  You should be able to log in with sqlplus, or any other IDE you want to use, based on the setup you chose during the install.
Now, we need Oracle to start up on each reboot.  To do this we need to create an init script.  As root, create a file at /etc/init.d/oracledb with the following contents.

#!/bin/bash
#
# /etc/init.d/oracledb
#
# Run-level Startup script for the Oracle Listener and Instances
# It relies on the information on /etc/oratab
# these are the paths for our base installation
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/dbhome_1
export ORACLE_OWNR=oracle
export PATH=$PATH:$ORACLE_HOME/bin
if [ ! -f $ORACLE_HOME/bin/dbstart -o ! -d $ORACLE_HOME ]
then
    echo "Oracle startup: cannot start"
    exit 1
fi
case "$1" in
    start)
        # Oracle listener and instance startup
        echo -n "Starting Oracle: "
        su $ORACLE_OWNR -c "$ORACLE_HOME/bin/lsnrctl start"
        su $ORACLE_OWNR -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
        touch /var/lock/oracle
        echo "OK"
        ;;
    stop)
        # Oracle listener and instance shutdown
        echo -n "Shutdown Oracle: "
        su $ORACLE_OWNR -c "$ORACLE_HOME/bin/lsnrctl stop"
        su $ORACLE_OWNR -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
        rm -f /var/lock/oracle
        echo "OK"
        ;;
    reload|restart)
        $0 stop
        $0 start
        ;;
    *)
        echo "Usage: `basename $0` start|stop|restart|reload"
        exit 1
esac
exit 0

Now we need to make it bootable and tell Ubuntu to run it on bootup.

sudo chmod a+x /etc/init.d/oracledbsudo update-rc.d oracledb default
Use dbca to create and manage databases, and use netca to create and manage listeners.

Saturday, March 5, 2011

Aurora's first manga

Today we went to Borders so Aurora could spend a gift card she got for there.  I picked up a Python book and thumbed through it for a while.  Then Kim needed me to watch Aurora because she wanted to go look at books in places other than the kids section, where Athena and Arianna wanted to hang out.
Aurora had already picked out a book, but was still looking around.  We went over to the history section for a while a checked out what was in store there, and then started heading back to the kids section.
On our way back we ran into the manga section, and I made a comment to Aurora about how I'd like for her to try manga to see if she liked it.  She started talking about putting her book back and I told her that she needs the book she wants to buy, not the one I want her to buy.
She asked me what manga was about, so I randomly picked up a book that looked like it may be something in her area and showed her the first page.  I showed her how the first page is what we normally think of as the last page.  I then put the book back and started looking for something that I thought was closer to what she may like when she asked me for that same book back.  I explained it was just a book I randomly selected, not necessarily anything I thought she would like.
While I looked around she thumbed through the book and declared she wanted to put her other book back and get the one I had shown her.  It is called Neko Ramen.  It is about a cat named Taisho who owns a Ramen shop and comes up with ridiculous ideas about how he can sell Ramen, such as using spaghetti noodles and calling it Italian Ramen.
I double checked she really wanted to get this book I just randomly grabbed off the shelf instead of the one she had already picked out.  She assured me she was, so she ended up buying it.
On the way home she read through the book and thought it was really funny.  She thought it was really weird how the excited faces look.  I explained that it is part of the manga art style.  It took us both a while to realise that you ride from top-right, down, then move to the top of the next panel to the left.  We started off thinking you started at the top-right, moved left, and then down, until we realised why the panels sometimes made no sense.
I hope Aurora takes to this manga thing.  Aurora and I have didn't interests in books.  She likes non-fiction and historical fiction type books.  I prefer science fiction and fantasy.
I have been able to get her into the Narnia series.  We have finished The Magician's Nephew and The Lion, the Witch, and the Wardrobe.  At first she did not like The Magician's Nephew, but she gave it a second shot and started to get into it.  We are presently taking a break from the Narnia series and reading James and the Giant Peach.
So, if this trend continues, Aurora and I will have more things to do together.  Plus, it has been nice reading good children's books that I would otherwise not put any interest into.
As for me, I'm reading a chapter a day of an eleven-hundred plus page book on programming in Python, which is followed by another Python book that covers more advanced topics on the language and is equally as large.  Fun times.