1

I'm trying to run the following query on a database to replace the leading characters of certain rows.

UPDATE table SET path = :newpath || SUBSTRING(path FROM :pathlen) 
  WHERE path STARTING WITH :oldpath

So for parameter :newpath = foo, :oldpath = bar and :pathlen = 4, I want this

bar\wibble

to be changed to ...

foo\wibble

However, I get the error "expression evaluation not supported" and I'm not sure why. Replacing :pathlen with a literal 4 works correctly, so it's definitely the SUBSTRING causing the problem.

Arioch 'The
  • 15,799
  • 35
  • 62
Roddy
  • 66,617
  • 42
  • 165
  • 277
  • 2
    Whats the version of Firebird? Per [documentation](http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-functions-scalarfuncs.html#fblangref25-functions-scalarfuncs-substring) "In Firebird 1.x, startpos and length must be integer literals. In 2.0 and above they can be any valid integer expression.". – ain Aug 20 '16 at 13:42
  • Sorry, it's Firebird 3.0 – Roddy Aug 20 '16 at 14:00
  • 1
    Are you sure, because I just tested it with Firebird 3.0, and it works as expected. Are you executing this from a stored procedure, and if not: from which language? – Mark Rotteveel Aug 20 '16 at 14:27
  • Maybe there is little reason two duplicate parameters by having both `:oldpath` and `:pathlen` - just because of DRY thing, to void risk of giving them two inconsistent values. I'd prefer to use `char_length( :oldpath ) + 1` instead – Arioch 'The Aug 22 '16 at 09:49
  • @Arioch'The That's actually what I started with - but that was giving me a -804 "data type unknown" error :) maybe something to do with this? http://tracker.firebirdsql.org/browse/CORE-1379 – Roddy Aug 22 '16 at 10:02
  • Correct. Then what about `char_length( cast( :oldpath as VARCHAR(100) ) ) + 1` ? – Arioch 'The Aug 22 '16 at 10:16
  • @MarkRotteveel - Ah. I'm using Devart's UniDAC library, with Delph (OK, actually C++Builder) as the language. Maybe that's where the problems coming from? – Roddy Aug 22 '16 at 10:16
  • Try the same code with other libs - DBX, UIB, Maybe FireDAC if your version has it – Arioch 'The Aug 22 '16 at 10:17
  • Also, may it depend upon fbclient.dll version, that the application loads ? – Arioch 'The Aug 22 '16 at 10:18
  • Also - can you use http://firebirdsql.su/doku.php?id=execute_block to make OldPath and explicitly-typed parameter, thus suited for CHAR_LENGTH ? – Arioch 'The Aug 22 '16 at 10:19
  • also see this answer - perhaps that way it might get shorter than EXECUTE BLOCK - http://stackoverflow.com/a/39046420/976391 – Arioch 'The Aug 22 '16 at 10:24
  • @Arioch'The FBClient 3.0.0.32483. Thanks for the help. – Roddy Aug 22 '16 at 10:28

0 Answers0