17 Dec 11 Layers in Visio

Sorry to say, but I love Microsoft Visio. Plse do not spread the word. But from time to time, I had to create a document that served two distinct groups: a group who was just interested in getting a highlight and a group who wanted to go into details. In earlier times, I created two documents: one for the highlights and one for the details. But I then had to maintain two documents. A change in the highlights has to be introduced in the details as well.
This can be shown in next figure:

The highlights are included in the first layer only; the details are composed of the first and second layer. If you maintain this in two documents, you will have to introduce a change in the first layer in two documents simultaneously.

However, this is not necessary as Viso knows the concepts of layers. One could add some objects to the first layer and some to the second. Subsequently, one may choose to print either the first or the second layers or both. This can be directly started when several layers are created.
Take view>Layer Properties

Once the layers are created, one could add the objects to the layer. Take format>Layer:

This could make life easier.
I owe thanks here to gert-Jan Kooren who pointed me at the Vision feature: it allows us to create complicate diagrams, that can also be shown at a lower level of detail. Nice!

07 Dec 11 Add blobs in your database

For some reason, I always overlooked the possibility to include pictures in a database. I decided to overcome this flaw and started experimenting with it.
The first step was to create a table in Access. In Access, we have the possibility to use the datatype “OLE Object”. A field with this datatype can be used to store pictures. The inclusion of a picture is then straightforward. Rightclick on the field brings you in a menu that allows you to include a picture:

That was really easy.
Let us move to Oracle.
In Oracle, it is really easy sailing. Let us first create a table.

CREATE TABLE "SCOTT"."BLOBJE"
   (	"NUMMER" NUMBER,
	"PLAATJE" BFILE
   )

Then, we create a storage where to put the images in.

CREATE OR REPLACE DIRECTORY
PLAATJE_DIR AS
'/home/tom/oracle';

This is then followed by storing a nice picture in the storagearea (=/home/tom/oracle). Let us say, we stored a file “thumbsup.gif” there.
The final step is to store a record in the table that was created first:

INSERT INTO blobje VALUES(4,bfilename('PLAATJE_DIR','thumbsup.gif'));

We can then see the picture from within an Oracle table:

Then with Teradata, it was not so simple. Like in Oracle, I created a table first:

CREATE SET TABLE financial.blobje
     (
      plaatje BLOB(2097088000),
      nummer INTEGER NOT NULL)
UNIQUE PRIMARY INDEX ( nummer );

I then changed the mode of Teradata SQL Assistant by listening to import. This was accomplished by File>Import Data. I then started a query, saying:

insert into  financial.blobje(nummer,plaatje) values (3,?B);

.
This started a dialogue where a reference was made to a control file that contained the name of the picture. The controlfile was stored in the same directory as the picture and it only contained the name of the picture.
This is not trivial. Nor could it be found in Teradata manual (at least I couldn’t). The information was retrieved from “http://developer.teradata.com/applications/articles/large-objects-part-1-loading”.

In this website, I found another solution that uses the BTEQWIN client. Let us first create a table:

create table financial.blobje(
    id varchar (80),
    binary_lob binary large object
)
unique primary index (id);

Then store several pictures in a directory (say in D:\tmaanen\Desktop\). Say we store “thumpsup.gif” and “thumbsdown.gif” in tht directory.
Also create an import.ctl file that contains:

D:\tmaanen\Desktop\thumbsup.gif,xxxx
D:\tmaanen\Desktop\thumbsdown.gif,yyyy

In BTEQWIN, we create a loop that reads the lines in this import.ctl file. This can be achieved by:

.import vartext ‘,’ LOBCOLS=1 file=’D:\tmaanen\Desktop\import.ctl’;

followed by:

.repeat *
using (lobFile blob as deferred,id varchar(40))
insert into financial.blobje (id, binary_lob)
values (:id, :lobFile);

It is not really easy, but it can be admittingly done.

Uoef:

04 Sep 11 Installing Oracle – the host is localhost!

Yesterday, I started installing Oracle. I decided to download the newest version: version 11g R. I used windows xp as a platform with DHCP switched off. It really looked simple: the screens look simple and the questions asked were straight forward. I let it go for about 2 hours and after this time I returned to the machine and I looked if everything was successfull. It certainly was: an encooraging “successfull” looked at me.
I checked the installation by a command “tnsping”. This command was issued at the same machine where Oracle was installed.
I then issued the command “sqlplus uid/psw” that also gave access to the Oracle database.
Finally, I installed TOAD on the same machine as Oracle installed and also TOAD gave access to the database.
Much to my surprise, I could not get access from another machine as the machine on which I had installed Oracle. I got an error saying “TNS-12541: TNS: geen listener”. I really did not understand as to why this happened. If you google this error, you get ideas on network problems, but this was not the case. I checked every possible typo but this did not happen either.
The clue as to why the error happened came as I compared the outcomes form the tnsping command. From the server, it looked like:

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (20 msec)

look at the host! It reads localhost where I expected to see the serveraddress.

The outcome from the remote computer was:

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.9)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
TNS-12541: TNS: geen listener

the host is 192.168.1.9 which in itsself is ok.

Apparently the listener on the server listens to the localhost whereas it had to listen to the server address 192.168.1.9.

The solution was twofold.
First I changed the hosts file on the server in \windows\system32\drivers\etc. I commented out the localhost and I introduced the serveraddress.
Secondly, I changed the tnsnames.ora, listener.ora that can be found in \network\Administrator on the server. Every “localhost” reference was replaced by the serveraddress 192.168.1.9.

This seemed to help: the listener started to listen to the network address and it was possible to access the database from outside.

Oops – took a few hours but Oracle was up and running.

19 Jun 11 Ranking the rows

I was asked a few days to write a SQL that would retrieve the one but latest row. Take as an example a few rows below: we have a several functions with their min salary. The question is: which function earns the one but highest minumum salary. In the rows below, we have minimum salary 20000, 15000, 3000 and 8200. The outcome would be the row where the minimum salary 15000 is found as we should disregard the highest number (here 20000).

To this problem, we have two solutions:
One solution is:

SELECT rownum, min_salary from
(select min_salary from jobs order by min_salary desc)A ;

Here, we order the table first, then return the rows with the rownumber. This could then be filtered on rownumber=2.
The second solution is:

SELECT min_salary,
       RANK() OVER (ORDER BY min_salary desc) "rank"
FROM   jobs;

This makes use of the so-called analytical functions. It leads to the same result: after a filter on RANK()=2, we have the desired row.

17 Jun 11 SQLite

SQLite is the smallest database server we know. The engine itsself is about 500 KB – considerably less than other engines. It can be downloaded from http://www.sqlite.org/ .
Once downloaded, it can be started by: sqlite3 test.db. Here, the sqlite3 is the database engine that is called and test.db the database.
I have downloaded a graphical client ( SQLite2009Pro ), that allows us to maintain the database. Moreover, the client also contains OBDC drivers which allows the exchange of data from different datasources to SQLlite.

I understand that SQLite is the database that is most widely used in the world. It is contained in MP3 players, cellphones, but also in programmes like Morzilla, PHP etc. Most likely, most users are not even aware that they have a SQLite database as it is embedded software.

I myself installed everything in about an hour: it is really straightforward.