Mindpsill - Technology

This week was a hard programming week at work and I had to reface the hardships of database programming in Java with JDBC. On Monday morning I decided to solve the quotes problem when inserting and updating into the database. As in most programming languages, SQL gets quite upset when certain special characters are used incorrectly. In SQL's case the single quote (') is the trouble maker because it has a special use to enclose a character / string value. If you've got a field and a user types in a single quote the single quote must first be "escaped" by inserting another quote, before adding it to the insert statement. So for example insert into remarks value ('I don't want to drive'); must become insert into remarks value ('I don''t want to drive');.

My first attempt at solving this problem was to use the replaceAll() function in Java to replace all ' by '', for each field. This method is cumbersome and not very elegant, so I had to look for a better solution. The answer was to use the PreparedStatement object instead of the Statement object to update values in the database. Apart from solving the quotes problem the PreparedStatement pre-compiles the SQL statement passed as a parameter, thus making it faster than the normal statement. For more information of the usage of the PreparedStatement object see Sun's Java JDBC basic tutorial.

While I was learning how to use the PreparedStatement object I stumbled across a new connection leakage error. The actual error was: ORA-01000 maximum open cursors exceeded. To help me analyse the fault better I found two SQL statements to document the number of open cursors in the database:-

SQL Statement 1: List of al open cursors, users and the SQL executed.

select user_name, status, osuser, machine, a.sql_text
from v$session b, v$open_cursor a
where a.sid = b.sid;

SQL Statement 2: The number of truly open cursors.

select a.value, b.name
from v$mystat a, v$statname b
where a.statistic# = b.statistic# and
a.statistic# = 3;

The culprit for the error was that I was had an unclosed PreparedStatement well hidden inside a loop. Although it was not so difficult locating the error, the problem was not fixed immediately by changing the code, so I started going around in circles looking for problems that didn't exist. The reason for this was that Oracle frees the inactive connections every 15 minutes (this might be dependent on a database parameter [INITSID.ORA]), so the open cursors had to be freed before the code could work.

This week I had very little time to experiment with common applications but I found a quick way to load Adobe Acrobat faster i.e. without loading the plug-ins. To do this all you've got to do is press [Shift] while the program is loading. Apparently this feature also works with other programs that load plug-ins.

Some links:-

Calciomercato.com

This is the most complete source for summer football transfers, covering most of the European clubs. (Calciomercato means Soccer Transfers in Italian)

Perry-Castaneda Map Collection

Need a map? Go here.

Awstats

The best open source web log analyser around. I compared it to analog and webalizer but they are too featureless against awstats.

Ofofo

Photo storage and sharing by Kodak