At 12:25 AM 7/07/2009, you wrote:

>Hi,
> I just upgraded to Firebird 2.1.2, and encountered a problem with casting strings to float which involve commas.
>
> e.g. select cast ("1,000" as float) from rdb$triggers
>
>This used to work in 2.0.3 (returning 1000) but now I'm getting

With a dialect 3 database and client it wouldn't. The exception you should get in v.2.0 with dialect 3 is ISC ERROR CODE:335544569,

Dynamic SQL Error
Column unknown
1,000
At line 1, column 22

That's because double-quote is not a legal delimiter for string literals in standards-compliant SQL (dialect 3 in Firebird terms). But in v.2.0, this returns what you expect:

select cast ('1,000' as float) from rdb$database

..so, if the idea was working for you in v.2.0 using double-quoted literals, then your database and client are dialect 1.

>Invalid modify request.
>conversion error from string " 1,000 "

SQL dialect issues aside, the behaviour you see in v.2.1 is the result of a fix to a long-standing bug in Firebird (and InterBase before it). Out there in the globe, different locales use a cornucopia of ways to decorate numbers. Where you live (and I, too) '1,000' means 1000 in arithmetical terms. In European and many other locales, '1,000' means 1.000. Because of such locale-specific ambiguities, the SQL standards don't allow automatic conversion of decorated numbers.

>Any ideas for fixes or workarounds?

Best practice is not to pass numbers as strings in the first place. The most efficient solution is to have your client code validate numeric input in any event, not just as a workaround for old bugs and subsequent fixes, but always. You're always living on borrowed time if you write client code that exploits the server's tolerance of risky input.

However, if you have non-human input that is being processed entirely at the server, e.g. generated output from another application to an external table, with alphanumeric numbers that can't be fixed for some reason, then you'll have to fall back on some server-based mechanism to convert the text.

An expression using the REPLACE (badstring, astring, anotherstring) is available in v.2.1., e.g.

select cast ((replace ('1,000', ',', '')) as float) from rdb$database

would hack your particular problem.

Of course, since it's less likely the operation in real life would be a select than an insert or update, you could get past *some* instances of the currently invalid input by intercepting the data type mismatch exception for the NEW value in Before Insert or Update triggers and applying REPLACE() in the PSQL exception handling. I see unacceptable problems with this hack. Besides restricting your database to handling only one style of number decoration, passing numbers as strings has too many other facets of potential inconsistency. It won't get past most software quality audits, either, in this day and age.

By the way, if you are planning to keep following Firebird's upgrade path you should consider upgrading your databases to dialect 3 as a must-do. The "split" into dialects was a hack in itself, more than a decade ago, intended to give those with legacy InterBase 5.x and older databases a period of grace. Support for dialect 1 is at "end of life" in Firebird and it will be formally deprecated at some point. This is *not* simply about running gfix and altering the attribute in the header, although it is actually possible to do this and wreck your database in the doing of it.

./heLen