"; */ ?>


19
Nov 08

ZX-Spectrum Child

zx speccy logoMy first computer book was “Basic for Children with Arturo”. It was 1989, the book was in Russian, and I was 10…

Back then, in USSR, to own a 8086 compatible PC was somewhat mystical, and therefore people, who knew a thing or two about microelectronics, were putting together Speccy(s) (ZX Spectrums), which were based on Zilog Z80 8-bit microprocessor and ran at 3.5 MHz.

The official ZX-Spectrum that was sold by “Sinclair Research Ltd” at that time was a bit pricey for middle class families, and therefore many cities across Europe and USSR started to come up with their own “clones“. There were over 50(!) semi-official clones that came out by the end of 1980s, beginning of 1990s. But to cut cost even further, people who knew “how to solder” created hybrids of the clones at home, which came out to be very cheap.

The process to build ZX-Spectrum’s clone’s hybrid was “a bit” involved, and included many days/weeks of hacking poor, incomplete electrical circuit documentation and sleepless nights of manual calibration of the hardware. The one had to “get” a ZX-Spectrum full circuit schema:

zx spectrum electrical circuit

Study it, change it to fit the budget. Go to several flea markets to first find a CPU, which was a quite rare find:

zilog z80 cpu

plus many more components: an empty electrical board, all other chip, transistors, capacitors, resistors, etc…

After some time and a combination of new ideas, documentation, hardware and labour, the one ended up with something like this:

zx-spectrum circut board

ZX-Spectrum keyboard had an interesting feature – pretty much every key, depending on the combination of special keys used, could represent 3-4, sometimes even 5 different things (commands/letters/symbols/special characters/etc). This made it much smaller (“compacter”) than for example PC keyboards:

zx-spectrum keyboard

And due to its small size, it most of the time served as a ZX-Spectrum case, and fit it the Speccy’s “motherboard”:

zx-spectrum under the keyboard

There were also some alternatives, and some people used “an extended” keyboards, but “a single key multi-purpose” pattern was still there. Here is a snippet of the Russian ZX-Spectrum keyboard, for example:

zx-spectrum russian keyboard

which is “a bit extended” – has a separate cursor, and some other keys. But take a look at the individual keys – due to the additional Cyrillic letters, “an average key” is now used to represent even more (+1) values.

At this point, when most of the work with oscillograph was behind, the only true way to see if the creation works was to see it with your own eyes. The word “monitor” in a sense of a “computer monitor” was not a widely used due to its price mostly, therefore most of ZX-Spectrum people used old black and white (at first) TVs. Speccy (well it’s clone’s hybrid at least) was designed in a way that it could be pugged into a regular TV Antena’s jack:

zx-spectrum hookup to TV

And due to the fact that family is usually more than one person, the main TV often was not used, and people used old TVs as a Speccy “monitor” instead:

zx-spectrum TV monitor

The whole process above was about 50% of the job – yea, just 50%. The other half was split on two. First quarter was spent in calibrating hundreds of little circuits, in order to get a sacred “© 1982 Sinclair Research Ltd” message at the bottom of TV (monitor):

1982 Sinclair Research Ltd

This message brought lots and lots of joy into families once it appeared on the screen. It meant a lot – CPU was working (so that guy on the flea market who sold it to you was ok.. sigh…). ROM was working (so that guy on the flea mark…). CPU and ROM were calibrated to work together, along with RAM and some other parts.

Usually the one plugged in Speccy to TV with no RAM/ROM/CPU at first, and calibrated all the circuits to reach a documented pattern on TV (for example small squares). After that was achieved, RAM/ROM and CPU were inserted one after the other by the same technique – each had to produce a certain visual pattern. So if all the parts were inserted and the message “© 1982 Sinclair Research Ltd” appeared, job was 75% done.

The last quarter was usually spent to tune all the parts to work as designed. Most of the time Speccy would restart in 3-4 minutes of use, just because something was “out of it”. To find that “something” was an art on its own. There was no Internet, no books or literature on this – it was pure work of art!

Fortunately my father is such an artist, and by the time I was 10, I got one of those Speccy beasts that my dad and I put together. The only problem though was that although it was designed to have 64Kb of RAM, it only had 16Kb. Well, 7 months later we solder more capacitors around 4Kb RAM memory chips, and that fixed it, but for 7 months I could not really play any games, because most of them required 64Kb to be there. Hence, the “Basic for Children with Arturo” book did not get a chance to get too dusty…

Where ZX-Spectrum shined the most is of course boot time – there was none :) Well, everything was in a ROM chip, including Sinclair Basic.

So having spent about 4-5 months to build Speccy, I was out of luck, and could not use it. Well, that is how I saw it for the first one or two weeks. But then…. I pressed a “p” key, which by default was a Basic “print” command, and then…. I typed what was on my mind: “I want to play!”, and to my astonishment Speccy “said“: “I want to play!”.

It is probably hard to surprise anybody right now by the fact that computer prints something on the screen, when you ask him/her to, but for me, being 10, and haven’t seen anything cooler than that, it was the biggest technical discovery of all times. I picked up my Basic book, and started to type, and type, and type, and then…. type a little more. That is how I started. That, in programming sense, makes me a “Speccy Child”.

A program in Basic in Speccy looked something like this:

zx-spectrum Sinclair Basic

where every line had to have a number, and the usual increment was 10. Quickly my favourite commands became “peek”, “poke” and “randomize” which worked directly with the memory (absolute addresses). And although I felt like a memory King, something was missing. I figured out what it was after I bought a system program that exposed me to something much more powerful than Basic – it was 8-bit Assembly language, the language that Zilog Z80 talked natively. Therefore me together with Z80, could control pretty much everything in the Speccy kingdom. That was awesome!

Here is an example on how you’d print a line of text to the screen in Speccy’s native language:

zx-spectrum z80 assembly

Cool – huh!? Another thing that was very useful at the beginning of my Assembly journey, was a documented character table ( like an “ASCII” ):

zx-spectrum ascii table

The only problem was that in order to work in Assembly, or in any other language, or with any other system program – it needed to be loaded since it was not in RAM/ROM – and that took time. No, really took time. It needed to be loaded from a tape – a regular cassette that is used in a tape recorder. In fact these very tape recorders were used to load all these neat programs in:

zx-spectrum tape

But not only it did take time to load, it was a constant pain to tune the head of the tape recorder for each tape bought on the flea market, since nobody followed the recording standards. A lot of times, you would have to sit and hold a screwdriver against the recorder’s head for 3-5, even 10 minutes, depending on how big the program was.

Here is what the screen looked like when the program was loading from tape into RAM. You can see the zebra looking lines, they were the moving loading indicator:

zx-spectrum loading screen

But time went, flea markets grew along with demand, and we upgraded to the real “RGB deal”. It was done by soldering an external controller (kind of like modern video cards, but not really) that had an RGB jack:

zx-spectrum rgb jack

Although it had 9 pins, only 5 were used: Red / Green / Blue / Sync / Ground. But we did not stop on this. Soldered another external controller, but this time for a disk drive! Oh yea – that was a huuuge deal. Looking at programs to load for seconds (well in rear occasions minutes) from these beautiful 5 inch disks!

Since we had a disk drive, Speccy needed to catch up with us, and the disk controller brought another cool thing, which was TR-DOS, where, of course, DOS stands for Disk Operating System. That is how pretty it looked:

zx-spectrum tr-dos os

Did you notice some colour? Good catch :) So now it was possible to actually browse the disk, and start programs with a button click. There were tons of system utilities with different browsers that you could start at the start up time (sort of like in MBS, but not).

Right after TR-DOS, I felt like I need more. I need more.. I need more.. RAM! And we soldered another level of RAM on top of 64Kb chips. So now my Speccy was equipped with 128Kb of RAM, which could free up some ROM real estate, which enabled ROM to fit a little menu on startup:

zx-spectrum 128 menu

At this point not only I could swim in the Assembly language ocean, but also play games, and use various utilities!

First let’s look at Photoshop:

zx-spectrum artstudio

well, maybe not the Photoshop you’re used to see right now, but Art Studio rocked! I was drawing away for hours!

Now, how about CuBase/ProTools:

zx-spectrum wham the music box

well, maybe not exactly the latest CuBase, but I wrote lots of tunes in Wham. Some tunes I actually recorded along with playing a real guitar – it was cool!

As to games, my favourite control was my keyboard, but for many friends that came to play, I bought a joystick:

zx-spectrum joystick

Now we could play multiplayer games like:

zx-spectrum match point game

Match Point (Tennis) or:

zx-spectrum fist game

Fist (Karate) game. Or even:

zx-spectrum tetris2 game

Tetris2 – which was a bit different from the regular Tetris in a way it gave different patterns and challenges on different levels, and was also a multiplayer game, which was of course cool.

There were tons of other games, but two I still remember are Robocop:

zx-spectrum robocop game

and Bomb Jack:

zx-spectrum bomb jack game

I could play long long time in Bomb Jack, not longer than I spent writing my own little games in Assembly language of course, but still – it was good amount of Bomb Jack time.

All the above was of course some time ago… 25, or even soon 30 years. Everything is a bit different now. Speccy lives somewhere in museum behind the glass:

zx-spectrum in museum behind the glass

But I remember him/her – those were times of big discoveries for me!

Like probably right now for some other 10 year old kid who just accidentally pressed a “p” button, and discovered….. time traveling…

Good luck to you kid!


17
Nov 08

Configure iwl3945 Driver on Ubuntu

It is quite frustrating when you have a wireless card built in, but you cannot use it – don’t you agree? That seems to be the case for wifi cards that are served by “iwl3945” driver. One of such examples can be “Intel Corporation PRO/Wireless 3945ABG” card.

Before, ( e.g. Feisty and earlier ) “ipw3945” driver was used instead, and worked beautifully with Intel cards. However the active development of this driver has stopped a couple of years ago, which means that security risks that were identified in the last couple of years were not patched. Therefore Ubuntu community switched to a more recent and supported “iwl3945” driver.

However that created a problem with NetworkManager that is used as a default network user interface in (Gnome) Ubuntu. It appears that in order “to be compatible with NetworkManager, a wireless driver must support both hardware and software scanning. Currently, hardware scanning is faster and more reliable and so is recommended for use with NetworkManager“. But unfortunately “iwl3945” driver does not support hardware scanning very well, however it is a default behaviour that NetworkManager expects.

But usually, and that is what I love Ubuntu for, if there is a problem, there is 99.9999% a solution to this problem. So this how to will help you solve it, and enjoy your Intel Wireless card with Ubuntu, despite of inconsiderate (in this case) NetworkManager.

1. Remove the bad guy

sudo apt-get remove network-manager

2. Disable “iwl3945” hardware scan

sudo vi /etc/modrobe.d/iwl3945

enter:

	alias wlan0 iwl3945
	options iwl3945 disable_hw_scan=1   # <-- enables software scanning

and save it (:wq).

At this you might want to restart or (optinal) just re-insert the module like this:

sudo modprobe -r iwl3945
sudo modprobe iwl3945

3. Install the good guy

Here you’ll install “the good guy” – his/her name is WICD:

Add wicd’s repository to Ubuntu sources:

sudo vi /etc/apt/sources.list
	 deb http://apt.wicd.net intrepid extras

(if you have Hardy Heron, then add “deb http://apt.wicd.net hardy extras” instead)

Update repositories

   sudo apt-get update

Install wicd “the normal Ubuntu way”:

   sudo apt-get install wicd

4. Run the good guy

   wicd-client

or restart, it should run automatically.

Happy wifying!


10
Nov 08

Configure Simp Server to Encrypt IM clients in Linux

Recently many companies started to inject their security policies with securing IM communication. It makes sense, since everything you type in that chat window to your buddy flies over the network in a clear text. That does not sound to bad, you think, but what it really means, is if anybody wanted to see what you are talking to your colleague, or even to your wife, about, s/he can see it easily by reading your network packets – which is a very easy thing to do now days.

Another example could be the client whose policy is to monitor all the incoming/outgoing network request, and you want to talk over IM to your colleague about something that you do not want your client to see.

Below I’ll show you how to secure you IM communication with SimpServer: http://www.secway.fr/us/products/simpserver/ on Unix client/server boxes. I am going to use Ubuntu here as an example, but it should work for most if not for all Unix flavours.

1. Get the SimpServer.

According to the liink above “SimpServer is currently beta software and is free for any use”, hence let’s get it for free from here:

wget http://download.secway.com/public/products/simpserver/simpserver-2.1.5c-linux-x86.tgz

2. Install the SimpServer.

Before untarring it, let’s make sure the standard C++ libraries that SimpServer uses are installed:

sudo apt-get install libstdc++5

Now let’s untar it and move to “/usr/local” – that is where it will search for its binary files, so make sure you do that:

tar -xvzf simpserver-2.1.5c-linux-x86.tgz
 
sudo mv simp/ /usr/local/

Go to “/usr/local/simp/bin/”, and run the SimpServer:

cd /usr/local/simp/bin/
 
./simpserver
 
----------------------------------------------------------------------------------
SimpServer Linux 2.2.1.5c - (c) Copyright Secway 2000-2005
All rights reserved
 
Visit http://www.secway.com/products/simpserver/ for updates.
Visit http://www.secway.com/support/ for support.
 
MSN Service on 0.0.0.0:11863, mode 1
MSN Service on 0.0.0.0:1863, mode 0
AIM Service on 0.0.0.0:15191, mode 1
ICQ Service on 0.0.0.0:15190, mode 1
YAHOO Service on 0.0.0.0:15050, mode 1
Admin Service on 127.0.0.1:10023, mode 0
 
----------------------------------------------------------------------------------

If there are any problems, grab the simpserver version that is offered here: http://www.secway.fr/us/products/simpserver/download.php

3. Configure the SimpServer.

While SimpServer is running, telnet to it (port 10023). Default “username/password” are “admin/admin”:

$ telnet localhost 10023
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
SimpServer Linux 2.2.1.5c - (c) Copyright Secway 2000-2005
All rights reserved
 
Visit http://www.secway.com/products/simpserver/ for updates.
Visit http://www.secway.com/support/ for support.
 
Login: admin
Password: admin

Let’s see what it is capable of by asking for “help”:

> help
SimpServer Linux 2.2.1.5c - (c) Copyright Secway 2000-2005
All rights reserved
 
Visit http://www.secway.com/products/simpserver/ for updates.
Visit http://www.secway.com/support/ for support.
 
 ?,        help                  print this help
 list,     list_keys             [all, public, private]
 generate, generate_private_key  [-e<account>] [-s<service>] [-c<cipher>] [-b<size>] [-n<name>] [-p
<password>]
 load,     load_private_key      -i<keyid> [-e<account>] [-s<service>] [-c<cipher>] [-p
<password>]
 unload,   unload_private_key    -i<keyid> [-e<account>] [-s<service>] [-c<cipher>]
 change,   change_password       -i<keyid> [-e<account>] [-s<service>] [-c<cipher>] [-o
<old_password>] [-p<new_password>]
 delete,   delete_key            -i<keyid> [-e<account>] [-s<service>] [-c<cipher>] [-force] [-pendinf]
 accept,   accept_pending_key    -i<keyid> [-e<account>] [-s<service>] [-c<cipher>]
 quit,     exit                  exit

4. Create private/public certificates.

Notice that the “list” command, from the above help, shows all/public/private keys, including your public/private key, and all your IM buddies that you are talking to.

Let’s try to see what keys we have right away (without doing anything yet):

> list
Prv  Loaded KeyId             SHA-1 fingerprint                                   Date        Type         Srv  Name
--------------------------------------------------------------------------------------------------------------------

As you can see, we have no keys – because for now we have neither “secure/encrypted” IM buddies, nor our public/private key pair.

Hence start by by running a “generate” command that would create your the key pair (link to certificates article) that will be used:

> generate
generating the new key pair, please wait... done!
new key: 278dc025d92cdbc4  b14e 7b16 6415 e88f 2a67 2fe6 2e31 579b 580e 1a89   2008-10-16  RSA-2048  [admin] KeyPair

Now let’s “list” the keys again:

> list
Prv  Loaded KeyId             SHA-1 fingerprint                                   Date        Type         Srv  Name
--------------------------------------------------------------------------------------------------------------------
Yes  Yes    278dc025d92cdbc4  b14e 7b16 6415 e88f 2a67 2fe6 2e31 579b 580e 1a89   2008-10-16     RSA-2048    *  [admin] KeyPair

Perfect – you have your own “KeyPair”, that will be used to authenticate and encrypt communication from your IM buddies.

5. Connect IM clients to the SimpServer.

Next, let’s connect to our SimpPro / SimpLight / etc.. IM buddies. Here is an example on how to configure “Pidgin” to use a SimpServer:

For example you’d like your work AIM account to “follow the company standards” and use encryption via simp server. Here is what you should do

Go to "Accounts" -> (Choose your AIM account) "Edit Account" -> "Advanced"
 
Choose "SOCKS4" in "Proxy Options"
 
type "127.0.0.1" in Host
type "15191" in Port

Pidgin SOCKS4 settings

Why “127.0.0.1”? Because you have started the SimpServer on your local box. By the way, you can start the server on one box, and use it from many other PCs by entering IP address of that box to the “host” field for the proxy settings of the account in your IM client.

Why “15191”? Let’s look at the server output once again:

MSN Service on 0.0.0.0:11863, mode 1
MSN Service on 0.0.0.0:1863, mode 0
AIM Service on 0.0.0.0:15191, mode 1
ICQ Service on 0.0.0.0:15190, mode 1
YAHOO Service on 0.0.0.0:15050, mode 1
Admin Service on 127.0.0.1:10023, mode 0

We see that “AIM Service” listens on the port “15191”.

6. Start using the SimpServer with your IM client(s).

Now when you send an IM to anybody who runs SimpPro / SimpLight / or just a Simp Server, they would get a pop up asking them to accept your message. After they do accept it, your public key ( one of the keys that was generated by the “generate” command ) will be added to their Simp database. Same thing will happen when they ping you or respond to your IM encrypted message – their public key will be added to the simpserver:

Here is an example of what happens initially, when there is a new “Simp-Oriented” buddy pings you:

(2008-10-16 15:16:09) BuddyName: *** (*) SimpServer Linux 2.2.1.5c - Encrypted and Authenticated (*) ***
(2008-10-16 15:16:09) BuddyName: Hey, that is cool - so now our messages are encrypted...!

And now you can go back to your “telnet” session and “list” the keys again:

> list
Prv  Loaded KeyId             SHA-1 fingerprint                                   Date        Type         Srv  Name
--------------------------------------------------------------------------------------------------------------------
Yes  Yes    278dc025d92cdbc4  b14e 7b16 6415 e88f 2a67 2fe6 2e31 579b 580e 1a89   2008-10-16     RSA-2048    *  [admin] KeyPair
 No  N/A    0f2292a9d40c2f90  ab1d 6f78 6bff a03e 892a 34df 2b3a 26e7 16ff cbb9   2008-10-16     RSA-2048  AIM   <buddy1Name>
 No  N/A    62f02d7e858e0139  c315 616f 8518 d9c8 9827 e4c4 d8b4 2448 06f7 4278   2008-10-16     RSA-2048  AIM   <buddy2Name>

Notice that now you have your own “KeyPair”, a public key for “Buddy1Name”, and a public key for “Buddy2Name”.

Happy Secure IMing, and don’t abuse your secrecy :)


24
Oct 08

Getting Return Code from ANT in SHELL

This short tutorial shows how to get an exit code from Ant and act upon it. This can be useful when building a massive application that requires several Ant scripts/targets and a collection of shell scripts.

The rule of thumb when building the system is “when it should fail -> it should fail”. Sounds weird, but it is really true.

If the whole build of the application takes 15-20 minutes, and one of the libraries failed to build (for example), the whole build should fail. Otherwise, if the build master inspects the logs, and sees something went wrong (in case it is logged), s/he would have to spend another 20 minutes to rebuild it. Or what is even worse, the application could end up in an inconsistent state, without anybody raising a flag, and that would be very dangerous, especially if the current release goes beyond integration builds, for example to quality assurance or even worse – to production.

So, above are the reasons, below is an ultra simple example with a solution:

Let’s say we have an Ant script (build.xml) that checks for duplicate jars in a directory. The target name that does that is “check-duplicate-jars”:

build.xml:

    <target name="check-duplicate-jars" depends="some-other-task">
        <!-- does its magic here -->
    </target>

Let’s say we also have a simple shell script that calls “ant “. If this shell script is in the same directory as the “build.xml”, we can call it like this “./callant.sh check-duplicate-jars”, and it should check if there are any duplicate jars. Let’s run the shell script:

[user@host]$ ./callant.sh check-duplicate-jars
ANT: Running check-duplicate-jars task...
Buildfile: build.xml
 
check-duplicate-jars:
     [echo] Checking for duplicate jars......
     [exec] ***********    NO DUPLICATE JARS FOUND IN LIB!    ***********
 
BUILD SUCCESSFUL
Total time: 3 seconds
ANT: Return code is: "0"
GREAT SUCCESS: Niiice - I liiike!

This is an expected behavior. However what would be nice is to have this shell script “fail hard”, in case the Ant script (build.xml) fails.

Let’s try to misspell the target name “check-duplicate-jar” (should be plural: “check-duplicate-jars”) and run it:

[user@host]$ ./call-ant.sh check-duplicate-jar
ANT: Running check-duplicate-jar task...
Buildfile: build.xml
 
BUILD FAILED
Target `check-duplicate-jar' does not exist in this project.
 
Total time: 1 second
ANT: Return code is: "1"
BUILD ERROR: I am failing hard...

Wonderful – it failed! That is an expected behavior. And here is a sneak peek to the magic box – the “callant.sh” shell script:

#!/bin/bash
 
tname=$1
 
echo "ANT: Running $tname task..."
ant $tname
 
antReturnCode=$?
 
echo "ANT: Return code is: \""$antReturnCode"\""
 
if [ $antReturnCode -ne 0 ];then
 
    echo "BUILD ERROR: I am failing hard..."
    exit 1;
else
 
    echo "GREAT SUCCESS: Niiice - I liiike!"
    exit 0;
fi

Hence the whole magic is here:

antReturnCode=$?

In general, “$?” will return an exist code of any executable run within the shell script.
Well, now you ready for the massive build process. Get to work, and may the build force be with you! :)


22
Oct 08

Use HSQLDB Functions with Hibernate Mapping Files

Let’s say you need to do a “local integration” test, where you code does not depend on external systems (queues/external servers/DB/etc..). Creating unit tests, mocking/stubbing everything out is all good, but sometimes you need to be able to run tests that are as close as possible to the “real world” deal, while you are in “local mode” – e.g. plane, subway, basically somewhere without access to the real external systems.

One of ways you can approach it with databases is to load schemas you need in memory, and work (test) against those schemas. This is relatively easy to do with HSQLDB, and there are many “googlable” guides on how to do it. However in this little howto, I want to show you how you can define your own HSQLDB functions, and use them as either “stubs” (or even “real deal”).

Here is an example on why you may need it. Consider this Hibernate mapping file that is used in your application:

 
<?xml version="1.0"?>
 
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
 
<hibernate-mapping
	package="org.project.customer.dto.search">
	<class name="CustomerCreditCardSearchResultDto" table="CR_CREDIT_CARD">
 
		<id name="id" column="CR_CREDIT_CARD_ID">
			<generator class="native">
                                 <param name="sequence">SQ_CR_CREDIT_CARD</param>
			</generator>
		</id>
 
                <property name="accountNumber" formula="some_pkg.decode( ACCOUNT_NUMBER )" />
                <property name="secretNumber" formula="some_pkg.decode( SECRET_NUMBER )" />
                <property name="expirationDate" column="EXPIRATION_DATE" type="date" />
                <property name="zipCode" column="ZIP_CODE" />
 
	</class>
</hibernate-mapping>

Let’s say you created a CR_CREDIT_CARD table in in-memory DB, populated it, started your test. Now when you try to read a “CustomerCreditCardSearchResultDto” object somewhere in your test, Hibernate will construct an SQL query from the mapping file above, and execute it on the in-memory DB.

However there is a problem – it is going to fail with a similar Exception:

Caused by: java.sql.SQLException: Unexpected token: DECODE in statement [select <query here> .... ]
	at org.hsqldb.jdbc.Util.throwError(Unknown Source)
	at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
	at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
	at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:248)
	at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:302)
	at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:442)
	at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:368)
	at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:105)
	at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1561)
	at org.hibernate.loader.Loader.doQuery(Loader.java:661)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
	at org.hibernate.loader.Loader.loadEntity(Loader.java:1785)
	... 60 more

If there is an Exception – there is a Reason for it :) Notice these two mappings in the Hibernate mapping file above:

<property name="accountNumber" formula="some_pkg.decode( ACCOUNT_NUMBER )" />
<property name="secretNumber" formula="some_pkg.decode( SECRET_NUMBER )" />

That says Hibernate to construct SQL that uses this “some_pkg.decode()” custom function/store procedure after (in case of SELECT) reading these values from a database. However, in you local testing environment, you do not have this function defined. It is probably defined in the DB (like Oracle/DB2/etc) itself.

But for most, if not for all, technical problems there is a solution, so don’t worry – you can define this function(s) yourself. Since it is a test, the most logical thing, since you want to abstract the code out from the external systems (and their functions) would be to define stubs to these functions.

Let’s create a utility “HsqlFunctions” Java class with static methods to be used by HSQLDB (it can only use static Java methods in its SQL, btw):

package org.project.test.util;
 
/**
 * HSQL functions to be aliased.
 *
 *    DDL example:
 *
 *    CREATE ALIAS DECODE FOR "org.project.test.util.HsqlFunctions.decodeString"
 *
 *
 * @author blog.dotkam.com
 *
 */
 
public final class HsqlFunctions
{
   private HsqlFunctions()
   {
     // static utility class - does not need to be constructed.
   }
 
    /**
     * Stub for the decode function.
     * Usually used to "please" Hibernate Mapping Files.
     *
     * @param value - String value for the column
     * @return - returns the same String value
     */
    public static String decodeString( String value )
    {
        return value;
    }
}

Now, when creating a test schema, you can ALIAS this static method as HSQLDB function like this:

     CREATE ALIAS DECODE FOR "org.project.test.util.HsqlFunctions.decodeString"

This will tell HSQLDB to call “decodeString” static Java method on the column value, every time it sees “decode( COLUMN )” in SQL.

One thing to notice, though – make sure the type that the Java methods take are exactly the same as defined by DDL (Database Schema). For example, if you pass in column value as an Object:

    public static String decodeString( Object value )
    {
        return String.valueOf (value );
    }

And the column is defined as VARCHAR in schema, HSQLDB will try to pack that String into an Object, and it will fail with a similar exception:

Caused by: java.sql.SQLException: Wrong data type: hexadecimal string with odd number of characters in statement [select ... <query here>]
	at org.hsqldb.jdbc.Util.throwError(Unknown Source)
	at org.hsqldb.jdbc.jdbcPreparedStatement.executeQuery(Unknown Source)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
	at org.hibernate.loader.Loader.doQuery(Loader.java:662)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
	at org.hibernate.loader.Loader.loadEntity(Loader.java:1785)
	... 60 more

Below is just an excerpt on how you would set up the schema to use it with HSQLDB in your test:

    ....
 
    private static final String CREATE_ALIASES =
        "CREATE ALIAS DECODE " +
        "FOR \"org.project.test.util.HsqlFunctions.decodeString\"";
 
    private static final String CREATE_SEQUENCES =
        "DROP SEQUENCE SQ_CR_CREDIT_CARD IF EXISTS;" +
 
        "CREATE SEQUENCE SQ_CR_CREDIT_CARD " +
        "START WITH 1 INCREMENT BY 1";
 
    private static final String CREATE_TABLES =
        "DROP TABLE cr_credit_card IF EXISTS;" +
 
        "CREATE TABLE cr_credit_card (" +
            "cr_credit_id NUMERIC(15), " +
            "account_number VARCHAR(48)," +
             ....
             ....
   ....
 
        //  Creating the schema
 
        m_jdbcTemplate.execute( CREATE_ALIASES );
        m_jdbcTemplate.execute( CREATE_SEQUENCES );
        m_jdbcTemplate.execute( CREATE_TABLES );
 
        //  HSQLDB is ready to be populated with data at this point.
   ....

You can use DBUnit to create the schema above and populate it with the data. The above is just a straight forward hardcoded example.
Notice how it also creates a sequence “SQ_CR_CREDIT_CARD” to please the Hibernate, and others who might use it in the application.

Happy “local integration” testing!

Feel free to post questions/comments/suggestions, I’ll try to respond when have a free second or two :)