Java Trail: Stored Procedures

Why use SPs?

search on these terms: java call stored procedure
result

" ...Because stored procedures run in the DBMS itself, they can help to reduce latency in applications. Rather than executing four or five SQL statements in your Java code, you just execute one stored procedure that does the operations for you on the server side. Reducing the number of network trips alone can have a dramatic effect on performance... "

For performance

" ...Using stored procedures has a lot of the same advantages as using application servers based on EJBs or CORBA. The difference is that stored procedures come free with lots of popular DBMSs, while application servers are mostly expensive. This isn't just an issue of license cost. The time it takes to administer and write code for app servers, and the increased complexity of the client applications that rely on them, can be almost wholly replaced by a reliance on your DBMS... "

For simplicity

Are SPs a rival to application servers? I guess so.

What do application servers offer?

Are they just another layer to place shared logic and by doing this to improve performance and re-use?

Certainly the database is still a bottleneck - one whose capacity can be increased and perhaps distributed (with care) over additional resources.

Variations for SQL Server SPs

1. zero parameters (input or output), just an execution facility (no application data in or out)

2. an input parameter, execution can be influenced

3. an output parameter, can have one or more

" ...

CallableStatement toesUp = connection.prepareCall("{ ? = call list_early_deaths () }");

toesUp.registerOutParameter(1, Types.OTHER);

toesUp.execute();

ResultSet rs = (toesUp) getResults.getObject(1);

...Because returning cursors from procedures is not directly supported by JDBC, we use Types.OTHER to declare the return type of the procedure and then cast from the call to getObject()... "

Is it the case input parameters are typed based on the call made when they are set?

However output parameters need to be typed by a call?

Another search on java callablestatement

Result here

"...

The example below creates an instance of CallableStatement that contains a call to the stored procedure getTestData, which has two arguments and no result parameter:

CallableStatement cstmt = con.prepareCall("{call getTestData(?, ?)}");

Whether the ? placeholders are IN, OUT, or INOUT parameters depends on the stored procedure getTestData.

..."

1. Parameters are optional and can be in/out or both

2. Result parameter can only be out (I expect) but is optional

"...

If the stored procedure returns OUT parameters, the JDBC type of each OUT parameter must be registered before the CallableStatement object can be executed..."

3. OUT parameter types must be registered

Interesting conversation from this search... here

Problems reading output parameters and results set

"... I have it so that I can either read the resultset values or I can read the OUT parameters, but not both. Unfortunately, my requirement is to read both... "

I think this discussion is saying that you can get a result set that is separate to all output parameters.

I.e. you get your result set as a result of a call to executeQuery()

execute() does not return a result set

- - - -

I know from reading the Java docs on execute() that it can return multiple result sets, which are accessed by separate methods - perhaps output data from a callable statement is returned in two streams, i.e. result sets returned in one stream and output parameters (which could also be result sets) in another.

- - - -

Hmmm... in tests i can call a stored proc

String sql = "{call sp_EM_getWmiLoad}";

The stored proc does the following

select PartnerCountryCd, CountryName from countrycodes

And doing the following code gets access to the results set

ResultSet rs = proc.getResultSet();

I.e. there is NO USE made of parameters yet the output is still accessible

Don't think Chernobyl think Mayak

Advocates of nuclear energy spend a lot of time trying to convice others of its benefits, when they ought to be spending more time thinking how to explain to future generations how they intend to clean up the radioactive waste the industry generates.

Modern nuclear power stations no doubt offer improved reliability and safety compared to the pioneering facilities. Governments are considering building more facilities for reasons such as providing better energy security, also to help rebalance man's carbon output. Carbon receives a lot of attention.

If there is concern about the carbon waste there should be as much concern about the waste generated by the nuclear industry given the prospect of an increase to the world's capacity to generate energy from this source. Unlike radioactive waste, carbon can be handled, processed, stored, and even breathed with little adverse affect on human health. Radioactivity on the other hand leaves a stain that doesn't go away so easily. It cannot be vacuumed or scrubbed away with some detergent, and it lasts for thousands of years.

Mayak is the lesser known cousin of Chernobyl. It ought to provide food for thought to even the most ardent supporters of nuclear power:

http://news.bbc.co.uk/1/hi/world/europe/197295.stm
“ …According to some reports, radioactive waste equivalent to roughly 20 Chernobyls was pumped from Mayak into a lake that even today is capable of delivering a fatal dose of radiation within an hour… “


By the way there are about 200 tonnes of highly radioactive waste under Chernobyl…. and the concrete ‘case’ that covers it has already started to leak. If the site is in this state after less than a few decades what is it going to take to keep this under control for the tens of thousands of years it will take for this material to decay to safe levels.

The world simply isn't ready for nuclear power with this in prospect – let alone the security argument. It’s not the facilities in rich nations that are to be feared most, it’s the raft of facilities that will start to spring up all over the world as each country exercises its 'right' to nuclear energy, in places where the operators may be tempted to pocket the dough than spend it on safety measures.

The facts about lake Mayak are staggering:

http://www.ecobridge.org/content/n_wst.htm#mayak
…The most radioactive place on the planet
Artificial lakes containing more than 14 billion cubic feet of waste from the Mayak nuclear processing plant are filled to capacity and within a few years may leak into the region's rivers, Gov. Pyotr Sumin of the Chelyabinsk region in the Ural Mountains wrote in a letter to Prime Minister Mikhail Kasyanov…Mayak, a major nuclear weapons plant during Soviet times, has been the site of several accidents, including a 1957 waste-facility explosion that contaminated 9,200 square miles. The region has been called the most radioactive place on the planet because of accidents and Soviet-era nuclear waste dumping into lakes and rivers. The vice governor of the Chelyabinsk region, Gennady Podtyosov, once said in an interview that the water level in the lakes was just 12 inches below the limit. If action is not taken, contaminated water could burst the dam within three to four years, he said. ``It would be a major catastrophe,'' Podtyosov said. ``Waste would pollute rivers and flow into the Arctic Ocean.''

As the world considers an expansion of its nuclear power generating capacity it ought to consider introducing the notion of a 'nuclear footprint', similar to the concept of a 'carbon footprint' that is in popular today. It needs to get a handle on how much nuclear waste each person generates, the toxicity, and the number of generations that will need to live alongside it before it renders itself safe.

Each time we switch on our TV or zoom down to maccy dees in our electric powered car. People need to be able to make a choice about the method by which their energy is generated and non-nuclear alternatives need to be given a level playing field equal to the subsidies received by the nuclear industry.

China

China often gets a bad rap.

In the process of holding together a country of over one billion individuals the rights of those individuals sometimes play second fiddle 'for the greater good'.

There's also less consultation by the state. If it believes something is a good idea then there's a good chance of it happening (and quickly).

Whilst on a visit there a city was in the process of banning small motorcycles from roads in a bid to cut pollution. There was no debate, people were expected to give up their motorcycles and the state would give them a new electric motorcycle. Whole communities are also told when and where they will be moved to make way for large dam projects.

Some argue China needs to attach more importance to the rights of the individual, then again some states find themselves under fire for championing the rights of the individuals to the other extreme (at the expense of wider society).

One positive outcome of the speed with which China can turn policy into action is their record on renewable energy. At one stage China was building one coal fired power station per week. China clearly sees a sustainable future needs to be built on something other than fossil fuels, and the evidence is - in this regard - that China's actions speak louder than the fine words used by many western governments.

http://news.bbc.co.uk/1/hi/sci/tech/7535839.stm

China is of course due to overtake the US as the worlds largest polluter. This could change in future if China stays the renewable course.

There's nothing more that makes western governments happier with themselves than to criticise the conduct of other countries. Communication is a two way street, and if China stays the renewable course then it is entirely right that China give the rest of the world a kick up the renewable backside. If China can raise their game they so can (and should) the rest of the world.

China could be setting an uncomfortable precedent in terms of its pace of change. Perhaps the west might be a touch more apprehensive about its criticism in future.

Logging and the Software Exception Main Success Scenario Use Case

Logging: not the wooden kind, the software kind.

When things go wrong they should not only go wrong gracefully but in a manner friendly to the software user.

Logging can help with this - a lot. The better the logging solution in place, the less important it becomes to flash up terse techical messages to the user of the software, burdening them with the job of conveying this to support teams.

Safe in the knowledge that the system has logged the problem:
- Which part of the software the problem occurred
- What it was doing at the time
- The nature of the problem
- Which application and user was involved

The software can focus on the practical action the user should take. For example an instruction to call the support desk giving their user and the application being used.

The support desk should have ready access to the application data logs, they should be able to filter these logs by application / user / time, they are then in a better position to advise action / delegate the problem to a specialist team.

Asteroid Dead Ahead

Or is it a meteorite? Asteroids are large and one hitting Earth could be a big deal. Meteorites are small chunks of asteroids so are less of a big deal.

We can all sleep a little easier knowing somebody has not only pondered the consequences of a large rock hitting Earth, but also devised a way they believe will avoid such an event.

If the truth be told we would all be sleeping easier had we remained in blissful ignorance of the possibility. Hollywood put paid to that.

In terms of Earth years large rocks regularly hit Earth (once every 100000 years on average). The folks over at the b612 foundation believe we have the techology to lick the problem:

http://www.b612foundation.org/info/solution.html

What differentiates b612 is they believe it can be solved by non-nuclear means. In some circles there is a belief both the threat of a collision and the necessity of a nuclear option to combat a rogue asteroid has been over-played, a ruse for putting nukes in space.

The line dividing civil and military applications is a thin one.

Space is already colonised by the military, but beyond a few experiments routine weaponisation has not yet taken place. It looks increasingly likely as more and more military capability is lofted into orbit. The Chinese recently fired a space-bound missle from an Earth launch site to take out an ageing satellite.

There is little that man can do to stop himself crossing over this line, in fact if there isn't a line then man feels obliged to draw one, then compelled to cross it! For example the carving up the rain forests continues apace, and noises are begining to be heard about a race to exploit natural resources held under the poles.

What drives this? It's not so much that one man (or country) needs these things, but more the thought that things will be worse for him if he fails to act and loses the initiate to another. Ultimately a manifestation of our competitive nature.

Space presents a new set of problems, hardware is not as easy to conceil and sovereign boundaries are less straightforward than on Earth. Then again a satellite orbits the Earth every 45 minutes, giving space-based missles a distinct time advantage.

Neil Armstrong claimed mankind made a great leap when man set foot on the moon - in terms of that particular event yes. But in the wider context the evidence is mankind always fall from the same tree and never falls too far from it. And in 100 years from now when the moon is full of flags and missile silos we may wonder when and where it all went wrong.

Perhaps some comfort can be drawn from the possibility that a war fought in space could be far less destructive in terms of human suffering than one fought on Earth.

Astronauts have spoken of a sense of spiritual well being after journeying into space. Perhaps Virgin Galactic can offer discounted tickets to the great leaders of the world - perhaps space trips should be mandatory before taking office.

Give Renewables a Chance

The 'spiralling cost' of energy seems to be a very popular way to describe the fluctuations in the price of oil these days. As news pundits try to dramatise the issue for maximum effect, the world is whipped into a frenzy and advocates of nuclear power are rubbing their hands with glea.

For the price of oil, as well as concerns over the environment, do their corner no harm at all it seems. Governments are (in the West) talking up the prospect of nuclear power as some sort of win-win fix to the world's present 'energy crisis'.

Don't you just wish they'd hold up a minute? Whilst it's good news that the world gets used to paying more for its energy (for all sorts of reasons), the recent increases in the price of oil are not sustainable. Higher costs will at some point dampen demand, which will see prices fall.

Setting aside all debate about the rights and wrongs of nuclear power, lets hope that this frenzy does not give way to any sudden leaps in terms of policy. The world is waking up to the need to find alternative sources of energy, but let it make a reasoned choice and give plenty of time for alternatives to bubble to the surface.

We're an ingeneous lot and it would be a shame to reach for the first alternative to hand / the shortest path (i.e. the atom). Subsidies for nuclear power could quickly kill off the search for better alternatives. For example such as more efficient solar technologies like that developed by MIT (link below) which could yield massively more energy compared to conventional solar panels:

http://web.mit.edu/newsoffice/2008/solarcells-0710.html

Just why are so many taking a very rose-tinted view of nuclear power these days? Surely there's no truth in conspiracy theories that suggest the richer countries want nuclear power to win through because of the leverage it offers them in terms of controlling access to it by other less well off countries (on grounds of security for example).

Word Origins: Vandal

The vandals were members of a Germanic tribe which in the 4th and 5th centuries invaded western Europe and settled in France and Spain before moving on to north Africa. Their reputation for violence comes especially from their sacking of Rome in 455, when they destroyed its artistic and literary treasures.