Skip to content

Entries tagged "sql".

How to make SQLObject instances "picklable"?

I tried to store SQLObject instances in a session, but had problems with pickling them. Here is smart base class for model classes that allow to fast pickle / unpickle operation:
from sqlobject import *
import logging

class Base(SQLObject):

    def __getstate__(self):
        return self.id

    def __setstate__(self,id):
        obj = self.__class__.get(id)
        self.__dict__ = obj.__dict__
Just inherit from that class and that's all. Remember: restoring object from db is cheap: SQLObject caches instances in memory.

Automatically migrate SQL schema in SQLObject

When you're building an application you have to extends your model (and alter database schema that is placed under the model). I've found a method to automatically upgrade SQL schema (only ADD COLUMN operations for now):
from sqlobject import *
import logging

class Base(SQLObject):

    def migrate(self, *args, **kv):

        conn = self._connection
        schema = {}
        cols = self.sqlmeta.columns

        for a in conn.columnsFromSchema(self.sqlmeta.table, self):
            schema[a.name] = a
        logging.debug("existing schema: %s" % schema)

        for name, col in cols.iteritems():
            if name not in schema.keys():
                logging.info("add column: %s.%s" % (self.__class__.__name__, name))
                conn.addColumn(self.sqlmeta.table,col)

    migrate = classmethod(migrate)
migrate() class method only handles simplest case (adding a new column), but it may be sufficient in typical development process (more advanced transformations will still require SQL migration scripts written by hand). Enjoy!

Oracle on Debian Lenny: ORA-01034: ORACLE not available

Today I installed (again) Oracle XE on Debian (Lenny) and got the following error:

ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
SVR4 Error: 13: Permission denied

I suspected problem was caused by errant ORACLE_HOME / ORACLE_SID values bug got the answer:

chmod 6751 $ORACLE_HOME/bin/oracle

That gives "-rwsr-s--x". Why permissions were not set properly on installation - have no idea. Any clues?

Eclipse SQL Explorer

Eclipse SQL Explorer is simple but powerfull tool to inspect Your database state. You can:

  • exec SQL commands
  • browse database objects in Eclipse (including existing indices)
  • open multiple databases at once

Installation is pretty simple:

  1. Download preferred version (select any sqlexplorer_plugin-*.zip file)
  2. Unpack this ZIP to your Eclipse directory: unzip sqlexplorer_plugin-*.zip
  3. Restart Eclipse

Usage: switch to SQLExplorer perspective and define new connection:

1

If needed driver is not on the list you have to configure new driver by selecting JDBC jar file from Your disk.

2

By default the following views are present in SQL Explorer perspective:

  • Connection list
  • SQL commands history
  • SQL editor window
  • Database details (table structure, indices are here)
  • Database structure (list of tables, views etc.)

Enjoy!

Oracle SHOW ERRORS directive

By default Oracle is cryptic about details of an error. Directive SHOW ERRORS is very useful during debugging of stored procedures for instance.

SchemaSpy: inspect DB schema

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:

1

Installation is very simple. I'll describe integration process with Eclipse. First: download jar with application. Then create new Run configuration in Eclipse:

2

Add downloaded jar to classpath:

3Then fill execution program arguments. My preferred list is as follows:

-t orathin -db XE -u <db-user> -p <db-password> -o /opt/c2p/schema-docs -dp /opt/lib/ojdbc14.jar -host localhost -port 1521 -schema <db-schema> -noimplied

"orathin" option is database type and ojdbc14.jar contains JDBC driver for this connection type. "-noimplied" option will make documentation generation faster and saves some disk space for big projects.

The only missing feature for me is to disable rendering of attributes on diagrams (some association-rich tables gives very big images that could kill Firefox browser). It's an open source so I hope to fix this soon and of course release a path :-)

ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

I got the following error:

ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

on line:

<property name="customer" column="CustomerId" />

the fix for above error was to use many-to-many tag instead of plain <property>:

<many-to-one name="customer" column="CustomerId" />

Note that class is not required here - it's computed from return type of getCustomer() getter by reflection.

hibernate_logo_a

ORA-01722: invalid number

"ORA-01722: invalid number" is raised when a number was passed a query parameter, but another type (string) was expected.

The error message isn't very helpful, is it?

ORA-00600: internal error code

Interesting error found on Oracle XE installed for one of projects. After a run of test case that uses JDBC to perform some operations on database all tests started to fail with this exception:

Caused by: java.sql.SQLException: ORA-00600: internal error code, arguments:\
 [kdsgrp1], [], [], [], [], [], [], []
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
 at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
 at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
 at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1062)

Restart of Oracle database did not work. Seems table space was broken. I rebuit the database (DROP, then CREATE) and error dissapeared.

Interesting ...

How To Debug Hibernate SQL Queries With Parameters

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:

org.apache.commons.logging.simplelog.log.org.hibernate.SQL=trace
org.apache.commons.logging.simplelog.log.org.hibernate.engine.query=trace
org.apache.commons.logging.simplelog.log.org.hibernate.type=trace
org.apache.commons.logging.simplelog.log.org.hibernate.jdbc=trace

For your convenience here's log4j configuration:

log4j.logger.org.hibernate.SQL=trace
log4j.logger.org.hibernate.engine.query=trace
log4j.logger.org.hibernate.type=trace
log4j.logger.org.hibernate.jdbc=trace

Then you will see all passed parameters and results in logs:

[DEBUG] (generated SQL here with parameter placeholders)
[TRACE] preparing statement
[TRACE] binding '1002' to parameter: 1
[TRACE] binding '1002' to parameter: 2
[TRACE] binding '1002' to parameter: 3
[DEBUG] about to open ResultSet (open ResultSets: 0, globally: 0)
[DEBUG] about to close ResultSet (open ResultSets: 1, globally: 1)
[DEBUG] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
[TRACE] closing statement

Nothing hidden here. Happy debugging!

Recreate Derby Database Under WebSphere

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

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

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.