I use Database Workbench for all my Firebird Development and testing.
From: [email protected] [mailto:[email protected]] Sent: Monday, July 15, 2019 7:49 AM To: [email protected] Subject: RE: [firebird-support] Proper Case Function for Firebird 2.5.4 and above This is strange. When I run it in Firebird Maestro 17.1 it returns empty string. When I run it is Database Workbench 5.5 it works as expected. From: [email protected] <[email protected]> Sent: Monday, July 15, 2019 7:50 AM To: [email protected] Subject: [firebird-support] Proper Case Function for Firebird 2.5.4 and above Greetings All, We are currently on Firebird 2.5.4 and hope to move to Firebird 3.0 by the end of this year once I determine what all needs to take place in preparation to do so. One of the things we did when moving from Firebird 1.5 to 2.5 was to remove the dependencies on external UDF's from FreeAdhocUDF since Firebird at that point contained an internal replacement for most of those that we used. I have one UDF left to replace F_PROPERCASE(). It appears Firebird 2.5 nor Firebird 3.0 have a replacement internal UDF for this one unless I missed it. So we tried to come up with a way of doing this with a few stored procedures that my associate wrote. They are included below. They compiled and worked great. The next day when I tried to extract the metadata on my development database and the database on the office server to compare them to do an update script I received an error that I need guidance with. First off here is how to use the stored procedures: PROPER_CASE('firebird support group') which would return 1 value containing: Firebird Support Group PROPER_CASE calls SPLITTER: SPLITTER('firebird support group', ' ') which would return 3 values in the cursor containing: firebird support group Here are the two stored procedures: set term ^ ; create or alter procedure SPLITTER ( in_str varchar(8190), in_splitter char(1)) returns ( out_str varchar(8190) ) as declare variable iLast integer; declare variable iNext integer; declare variable iLen integer; begin iLast = 1; iNext = position(:in_splitter, in_str, iLast); iLen = char_length(in_str) + 1; if (:iNext = 0) then begin out_str = in_str; suspend; end else begin while (:iNext > 1) do begin out_str = substring(:in_str from :iLast for :iNext - :iLast); iLast = iNext + 1; iNext = position(:in_splitter, in_str, iLast); suspend; end if (iNext = 0 and iLast > 1) then begin out_str = substring(:in_str from :iLast for :iLen - :iLast); suspend; end end end^ set term ; ^ GRANT EXECUTE ON PROCEDURE SPLITTER TO SYSDBA; set term ^ ; create or alter procedure PROPER_CASE ( in_str varchar(8190) ) returns ( out_str varchar(8190) ) as declare variable tmp varchar(8190); declare variable tcase varchar(8190); begin out_str = ''; for select out_str from SPLITTER(:in_str, ' ') into :tmp do begin if (char_length(tmp) > 0) then tcase = upper(left(tmp, 1)) || lower(right(tmp, char_length(tmp) -1)); if (char_length(tcase) > 0 and char_length(out_str) > 0) then out_str = out_str || ' ' || tcase; else out_str = tcase; end suspend; end^ set term ; ^ GRANT EXECUTE ON PROCEDURE PROPER_CASE TO SYSDBA; **************************************************************** So what was the error? When extracting the metadata on the production database: Procedure SPLITTER: Invalid factor in expression (POSITION) Script: Line:11 Pos:20 Anyone have any idea what needs to be done to correct this problem? Or does anyone have another way to proper case a string without using F_PROPERCASE external function? Thanks for all who reply with advice on how to proceed. Mike [Non-text portions of this message have been removed]
