incubator-empire-db-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rainer Döbele <>
Subject Re: hsqldb driver
Date Sat, 07 Feb 2009 18:49:54 GMT
Hi Francis,

yes, in principle you're absolutely right. This is clearly a "feature not supported" case
and we should fail.

This feature has originally been implemented for Oracle and SQL-Server and it's a shame that
there is nothing to replace or even work-around it in HSQLDB.

The problem is, that the getSQLPhrase(...) function on the DBDatabaseDriver isn't really designed
to fail. As you might have seen already there is an option to work with or without exceptions.
When using exceptions you're fine here. But when working without exceptions returing null
will not handle the error correctly. The non-exception mode is a relict of the past and we
could consider skipping in completely in a future release.

But the reverse function also causes trouble in the DBSample project.
It is used in SampleApp.queryRecords(...) which should work with all drivers.
In lines 356 to 363 you can see that I am explicitly checking for HSQLDB and that I am using
a workaround that I am really not happy with - but I couldn't think of anything better.
The code aims to extract the last part of a phone number assuming that the phone number parts
are separated by a dash. In Oracle this could be something like:
  substr(PHONE_NUMBER, length(PHONE_NUMBER)-instr(reverse(PHONE_NUMBER), '-')+2)

The questions is how do you get the desired result in HSql (or postgresql if you like)?
Can you think of a good solution except writing a stored procedure?

Another questions is how anyone could check whether a function is avaiable for a particular
driver. There already is the DBDatabaseDriver.isSupported method, but it takes a enum and
not a phrase number.

But instead of letting the getSQLPhrase function fail, there is another option:
At the moment the reverse function is the only one that is not available for all drivers.
So instead of letting the getSQLPhrase function fail, we could also consider to remove support
for "reverse" completely. In this case people who's database supports it and who needed it
could still provide the template themselves using a DBFuncExpression as follows:
 REVERSE_COL = new DBFuncExpr(COL, "reverse(?)", null, null, false, DataType.Text);
This would make their code specific to their type of database, but since it's not working
for another one it's not worse than getting an exception.

So as you can see, when I first came across that problem I was just a bit undecided what exactly
to do. But if you want to throw an exception there that’s fine with me. Just consider that
the DBSample project is using it and it should run with postgree sql as well - even if it
means changing the sample code in order to avoid using the reverse function completely.



Francis De Brabandere wrote:
> re: hsqldb driver
> Hi,
> the hsqldb driver has this definded:
>   case SQL_FUNC_REVERSE:      return "?"; // "reverse(?)";
> Is't this dangerous and shouldn't we fail when this method is
> requested instead of just continuing with the original value. (failing
> by keeping the reverse(?))
> The example application seems to to some logic to get rid of this
> issue. I see this solution as clearly wrong!
> I'm writing a postgresql driver and I'm having the same issue that
> that function is not available...
> -- 
> Microsoft gives you windows, Linux gives you the whole house.

  • Unnamed multipart/mixed (inline, None, 0 bytes)
View raw message