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 <[email protected]> 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.
