Mailing-List: contact empire-db-dev-help@incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: empire-db-dev@incubator.apache.org
Received-SPF: pass (nike.apache.org: domain of francisdb@gmail.com designates
 72.14.220.154 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=mime-version:in-reply-to:references:date:message-id:subject:from:to
         :content-type:content-transfer-encoding;
        b=ZcA75js0WSx9o8zLAD9OC5f9PGznO6oISDIivYbpFt8lfvjqw9EM8YP990QR/Gj8hq
         Epf8+/SeM4MEGrFWQFEPu+NHiPPfvGKn/6d0hNPf3/Dt+8gjvThY6lKOxFmTNAoJtyZn
         0+ON7fz8kcp0XeXjhy3LyvedtD0r78bhqZGaI=
MIME-Version: 1.0
In-Reply-To: <B297D4737108A0459B66C3A85BB7F901018AC8@kn.esteam.de>
References: <3ab983230902070929y4d982e58sb75054fc98cc990@mail.gmail.com>
	 <B297D4737108A0459B66C3A85BB7F901018AC8@kn.esteam.de>
Date: Sat, 7 Feb 2009 20:04:13 +0100
Message-ID: <3ab983230902071104q71c0966by280d8db0263cb502@mail.gmail.com>
Subject: Re: hsqldb driver
From: Francis De Brabandere <francisdb@gmail.com>
To: empire-db-dev@incubator.apache.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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=F6bele <doebele@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 an=
d it's a shame that there is nothing to replace or even work-around it in H=
SQLDB.
>
> The problem is, that the getSQLPhrase(...) function on the DBDatabaseDriv=
er isn't really designed to fail. As you might have seen already there is a=
n option to work with or without exceptions. When using exceptions you're f=
ine 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 driv=
ers.
> 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 co=
uldn't think of anything better.
> The code aims to extract the last part of a phone number assuming that th=
e phone number parts are separated by a dash. In Oracle this could be somet=
hing 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 avaiabl=
e for a particular driver. There already is the DBDatabaseDriver.isSupporte=
d method, but it takes a enum and not a phrase number.
>
> But instead of letting the getSQLPhrase function fail, there is another o=
ption:
> 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 c=
ould also consider to remove support for "reverse" completely. In this case=
 people who's database supports it and who needed it could still provide th=
e template themselves using a DBFuncExpression as follows:
>  REVERSE_COL =3D new DBFuncExpr(COL, "reverse(?)", null, null, false, Dat=
aType.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 an=
d it should run with postgree sql as well - even if it means changing the s=
ample 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.
>>
>
>


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