zmstone: July 2014

Friday, July 18, 2014

Erlang unixODBC to Oracle

Tested in CentOS 6.5, OTP-R15B03, Oracle 11.2
NOTE examples below assume that:
Oracle server/client are both installed on localhost as instructions here:
http://zmstone.blogspot.se/2014/07/oracle-11g-serverclient-in-cetos-65.html
Test database, username and passwords are created as instructions here:
http://zmstone.blogspot.se/2014/07/create-test-database-and-roles-in-fresh.html

Install some possibly missing clients/libraries [1]
oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm
oracle-instantclient11.2-odbc-11.2.0.4.0-1.x86_64.rpm
Install unixODBC

download unixODBC-2.3.2.tar.gz from http://www.unixodbc.org/

build unixODBC:

./configure --prefix=/usr --sysconfdir=/etc --enable-gui=no --enable-drivers=no --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE
# enable iconv library to make it work with unicode.
make
# if everything goes fine
sudo make install

Edit unixODBC config files

/etc/odbc.ini

[TEST]
Driver          = Oracle-11g
ServerName      = //localhost:1521
Database        = test

append to /etc/odbcinst.ini

[Oracle-11g]
Description     = Oracle ODBC driver for Oracle 11g
Driver          = /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
Driver64        = /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
Setup           = 
Setup64         = 
UsageCount      = 
CPTimeout       = 
CPReuse         = 
FileUsage       = 1 
Driver Logging  = 7

Test unixODBC oracle using isql

BASH> isql -v test tester1 SECRET
[01000][unixODBC][Driver Manager]Can't open lib '/usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1' : file not found
[ISQL]ERROR: Could not SQLConnect

This does not necessarily mean libsqora.so.11.1 is missing, as it's been investigated in [2], it's probably libodbcinst.so.1 missing.
To fix it, LD_LIBRARY_PATH should be updated:

export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib:/usr/lib64
# and make a soft link like below, since it's unixODBC-2.3.2 we installed,
# but libsqora.so is looking for libodbcinst.so.1
sudo ln -s /usr/lib64/libodbcinst.so.2.0.0 /usr/lib64/libodbcinst.so.1

Now it should work (tab0 in the example below is created here: http://zmstone.blogspot.se/2014/07/create-test-database-and-roles-in-fresh.html)

BASH> isql -v test tester1 SECRET
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from tab0;
+-------------+---------------------------------+
| K           | V                               |
+-------------+---------------------------------+
| 1           | test value                      |
+-------------+---------------------------------+
SQLRowCount returns -1
1 rows fetched

Try connect to oracle from Erlang shell [3]
NOTE: If Erlang OTP was built from source code, and lib/erlang/lib/odbc-* can not be found in OTP install directory, you'll have to install libodbc first, then redo configure and make install.

BASH> erl
Erlang R15B03 ......

Eshell V5.9.3.1  (abort with ^G)
1> odbc:start().
ok
2> {ok, Pid} = odbc:connect("DSN=test;UID=tester1;PWD=SECRET", [{scrollable_cursors, off}]).
{ok,<0 data-blogger-escaped-.39.0="">}
3> odbc:sql_query(Pid, "select * from tab0").    
{selected,["K","V"],[{1.0,"test value"}]}

References
[1] https://community.oracle.com/thread/1115824
[2] http://en.it-usenet.org/thread/876/22511/
[3] http://www.databaseskill.com/331542/

create test database and roles in a fresh oracle install

See here to install oracle and sqlplus.

Connect to oracle as system or sysdba

BASH> rlwrap $ORACLE_HOME/bin/sqlplus -L system/INIT_PASSWORD@//localhost:1521/

Create a test database:

SQL> create bigfile tablespace test datafile 'test.dat' size 32m autoextend on nologging;

Tablespace created.

Create a test user:

SQL> create role tester; 

Role created.

SQL> grant connect, resource, dba to tester;

Grant succeeded.

SQL> create user tester1 identified by SECRET default tablespace test;

User created.

SQL> grant tester to tester1;

Grant succeeded.

SQL> alter user tester1 quota unlimited on test;

User altered.

SQL> quit

...

Now try to connect to oracle using the newly created user

BASH> rlwrap $ORACLE_HOME/bin/sqlplus -L tester1/SECRET@//localhost:1521

Create a test table and insert some random value

SQL> create table tab0(k number(10) not null, v varchar2(32) not null) tablespace test;

Table created

SQL> insert into tab0 (k,v) values (1, 'test value');
1 row created.

SQL> select * from tab0;
         K V
---------- --------------------------------
         1 test value

Wednesday, July 16, 2014

oracle-11g server/client in cetos 6.5

SERVER

1. download
oracle-xe-11.2.0-1.0.x86_64.rpm

2. install

BASH> sudo rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm

3. initial configure the database

BASH> sudo /etc/init.d/oracle-xe configure

Oracle Database 11g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 11g Express Edition.
The following questions will determine whether the database should be starting upon system boot, the ports it will use, and the passwords that will be used for database accounts.
Press  to accept the defaults.
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:
Specify a port that will be used for the database listener [1521]:
Specify a password to be used for database accounts.
Note that the same password will be used for SYS and SYSTEM. Oracle recommends the use of different passwords for each database account. This can be done after
initial configuration: INIT_PASSWORD
Confirm the password: INIT_PASSWORD
Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:y

Starting Oracle Net Listener...
DoneConfiguring database...
DoneStarting Oracle Database 11g Express Edition instance...
DoneInstallation completed successfully.

CLIENT
(although sqlplus comes together with SERVER installation, e.g. /u01/app/oracle/product/11.2.0/xe/sqlplus)

1. download

oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm

oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm

2. install

BASH> sudo rpm -ivh oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm
...
BASH> sudo rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm

sqlplus executable is to be found here: /usr/lib/oracle/11.2/client64/bin/

3. set environment variables
ORACLE_HOME should be set to /usr/lib/oracle/11.2/client64/ --- one level above the bin directory where sqlplus executable is found etc...

export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export ORACLE_HOME_LISTNER=/usr/lib/oracle/11.2/client64/bin
export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
export SQLPATH=/usr/lib/oracle/11.2/client64/lib
export TNS_ADMIN=/usr/lib/oracle/11.2/client64/bin

4. verify if sqlplus works

BASH> $ORACLE_HOME/bin/sqlplus -V

SQL*Plus: Release 11.2.0.4.0 Production

5. try connect to server

BASH> $ORACLE_HOME/bin/sqlplus -L system/INIT_PASSWORD@//localhost:1521/

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 16 15:20:54 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved

Connected to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> quit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

TIP
install rlwrap to get a more user-friendly SQL> shell, e.g.

BASH> rlwrap $ORACLE_HOME/bin/sqlplus -L system/INIT_PASSWORD@//localhost:1521/

Friday, July 11, 2014

bash print eth0 ip to erlang term format

eth0 ip address to erlang term:

printf "{%s}" $(ip addr show eth0 | grep -o "inet\s[0-9]*.*.*.*/" | cut -d " " -f2 | cut -d "/" -f1 | sed "s/\./,/g")

default gateway for eth0 to erlang term:

printf "{%s}" $(route | grep eth0 | grep default | sed "s/\s/\n/g" | grep "[[1-9][0-9]*\.]*3" | sed "s/\./,/g")

Thursday, July 10, 2014

build rabbtmq-server and rabbitmq-management plugin

1. clone the code

$ hg clone http://hg.rabbitmq.com/rabbitmq-public-umbrella

2. checkout all dependencies:

$ cd rabbitmq-public-umbrella && make checkout

3. build rabbitmq server:

$ cd rabbitmq-server && make

4. start rabbitmq server:

$ make run

NB:

a) by default, it requires write access to files in /etc and /var

to change it, edit SYS_PREFIX in rabbtimq-server/scripts/rabbitmq-defaults

b) by default, rabbitmq-server write logs mnesia db to /tmp, to change it, edit TMPDIR in Makefile

5. build rabbitmq-management plugin (in rabbitmq-server directory)

$ mkdir plugins
$ cd ../rabbitmq-management
$ make run
# plugin and its dependencies are build in directory 'dist'
$ cp dist/*.ez ../rabbitmq-server/plugins

6. start rabbitmq-management plugin

$ cd ../rabbitmq-server
$ ./scripts/rabbitmq-plugins enable rabbitmq_management

7. check plugin status:

./scripts/rabbitmq-plugins list
 Configured: E = explicitly enabled; e = implicitly enabled
 | Status:   * = running on rabbit@zmc0
 |/
[e*] amqp_client               0.0.0
[e*] mochiweb                  2.7.0-rmq0.0.0-git680dba8
[  ] rabbit_common             0.0.0
[E*] rabbitmq_management       0.0.0
[e*] rabbitmq_management_agent 0.0.0
[  ] rabbitmq_test             0.0.0
[e*] rabbitmq_web_dispatch     0.0.0
[e*] webmachine                1.10.3-rmq0.0.0-gite9359c7