sql « Dariusz on Software Quality

25/06/2010

SQL Error: 17006, SQLState: null Invalid column name in Hibernate

Filed under: en — Tags: hibernate, oracle, sql — dariusz.cieslak @

Recently I’ve got the following error during SQL named query execution:

SQL Error: 17006, SQLState: null Invalid column name

In order to track the problem down I enabled SQL logging and collected SQL query issued, then run it under SQL monitor and … it was working without error! I was surprised.

In order to debug what’s going on I enabled detailed logging in Hibernate. What was happened then?

[INFO] could not read column value from result set: entityKey215_0_; Invalid column name

This message showed me that the problem was not related to generated SQL query but to column names expected by Hibernate. Generated recordset didn’t has entityKey215_0_ column. I added:

entityKey AS {list.entityKey}

to named SQL query and error dissapeared.

This error is not-very-obvious kind of error because Oracle hides column / table names in error messages returned. Why? I don’t get this cryptic error messages idea. In order to track an error under Oracle I had to enable verbose logging (error message standalone din’t give anything useful). Much simpler database MySQL has better error reporting than “fat” Oracle.

Comments (2)

22/04/2010

Recreate Derby Database Under WebSphere

Filed under: en — Tags: eclipse, j2ee, sql, websphere — dariusz.cieslak @

WebSphere uses SQL databases for internal managment of MQ queues (Derby database engine under the covers). Sometimes you need to reset their state. Here’s the script that erase and recreate BPEDB database state (tested under WS 6.1.2):

rm -rf $WID_HOME/pf/wps/databases/BPEDB
echo "CONNECT 'jdbc:derby:$WID_HOME/pf/wps/databases/BPEDB;create=true' AS BPEDB;"|\
    $WID_HOME/runtimes/bi_v61/derby/bin/embedded/ij.sh /dev/stdin

Comments (0)

14/04/2010

Second Level Cache For SQL Queries Under Hibernate

Filed under: en — Tags: HA, hibernate, j2ee, java, orm, sql — dariusz.cieslak @

Second level cache in Hibernate allows to greatly speed-up your application by minimizing number of SQL queries issued and serving some results from in-memory cache (with optional disk storage or distributed cache). You have option to plug in different cache libraries (or to Bring Your Own Cache – an approach popular among my colleagues from India ). There are caching limits you must be aware of when implementing 2nd level cache.

(more…)

Comments (0)

24/03/2010

How To Debug Hibernate SQL Queries With Parameters

Filed under: en — Tags: hibernate, logs, orm, sql — dariusz.cieslak @

I bet everyone knows how to enable SQL logging for Hibernate. If you add this parametr to Hibernate configuration:

<property name="hibernate.show_sql">true</property>

you will see queries like this in log file:

select roles0_.EntityKey as Entity9_1_, roles0_.ENTITYKEY as ENTITY9_168_0_
from USERROLE roles0_
where roles0_.EntityKey=?

but wait: what value is passed as parameter by Hibernate? (parameters are marked as “?” in JDBC) You have configure TRACE level for some log categories, here’s example for simplelog.properties:

(more…)

Comments (4)

16/09/2009

SchemaSpy: inspect DB schema

Filed under: en — Tags: eclipse, sql — dariusz.cieslak @

When you are joining to an existing project you don’t have good knowledge of internal project structures. Existing documentation may be inaccurate or just missing. In this case you can rely only on clean source code structure and source code comments. Based on them you can generate API docs using Javadoc or Pydoc for instance. But what with persistent data model stored in database? SchemaSpy comes with help here!

SchemaSpy is small (~200kB) Java application that is able to render schema retrieved from JDBC connection into set of “hyperlinked” HTML pages with Java Script and (if GraphViz installed) diagrams. Below you see one page of example output of this tool:

(more…)

Comments (0)