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