hsqldb driver

[Francis De Brabandere <fr...@gmail.com>]

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...

-- 
http://www.somatik.be
Microsoft gives you windows, Linux gives you the whole house.

Re: hsqldb driver

[Francis De Brabandere <fr...@gmail.com>]

my progress driver has this at the moment:

case SQL_FUNC_REVERSE:            return
"function_not_available_in_pgsql(?)";//"reverse(?)";
case SQL_FUNC_STRINDEX:           return "strpos(?, {0})";
case SQL_FUNC_STRINDEXFROM:       return
"function_not_available_in_pgsql({0}, ?, {1})";//"locate({0}, ?,
{1})";

so that's one more function that is missing for a driver, your
workaround in the demo doesn't work on postgresql :-) but this can be
fixed by some substring construction
we might also want to have an option to declare the missing functions...

and I have an other problem that the generation of the tables can not
be called inside a transaction for postgresql, but that's more a demo
app issue that can be fixed

I'm thinking of having a look at a driver for H2 database as well.
we might need some kind of integration test that tests all features on
all databases...

so is it ok if I submit my driver(s) to trunk when they're done?

On Sat, Feb 7, 2009 at 7:49 PM, Rainer Döbele <do...@esteam.de> wrote:
> 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.
>
> Regards
>
> Rainer
>
>
> 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...
>>
>> --
>> http://www.somatik.be
>> Microsoft gives you windows, Linux gives you the whole house.
>>
>
>



-- 
http://www.somatik.be
Microsoft gives you windows, Linux gives you the whole house.

Re: hsqldb driver

[Rainer Döbele <do...@esteam.de>]

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.

Regards

Rainer


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...
> 
> -- 
> http://www.somatik.be
> Microsoft gives you windows, Linux gives you the whole house.
>