Firebird SQL Server Internal Functions

2
votes

I have a Firebird 2.1.1 database deployed over a LAN. In a recent upgrade I used an FB internal function (COALESCE). When I was testing my Delphi app on my development machine there were no problems.

But when I tried to run a query on a production machine I received an error message telling me that the function (COALESCE) was unavailable.

Coalesce is a built in internal function in FB. It is not a UDF or a stored proc, it is built in.

Q: Why does a query using Coalesce work on my development machine but not a production machine?

Some more Info:

  • XP pro SP2 on both
  • My program is developed in Delphi 3.0 with BDE
  • BTW: I installed FB server on the workstation (production machine) and, low and behold, the query using Coaclesce works! I thought Coaclesce was an internal function?! I don't want to have to install FB Server on every machine. There are over seventy workstations in three different locations.

Normally I have FB (ver 2.1.1) installed on one machine running XP. This is my designated database server. All workstations running my app get their data from this server. I upgraded my app and changed the schema of the FB database that resides on my server that my client side app uses. One of the changes that I made was that I am using FB internal funcations for the first time. Specifically I am using function COALESCE. When I run my app on a workstation I get the following error message: -SQL error code = -804 -Function unknown -COALESCE The app running on the workstation is running a select statement against the database on the server that uses COALESCE.

Here's the interesting part: The query containing COALESCE ran fine on my development machine (which is another workstation on our network) but not my users' workstations (production machines). So I asked myself "What's the different about my development computer?" Well it has FB server on it. So I installed FB server on a user's workstation (FB is now on our server + on the workstation running my app) and: I don't get the error anymore! My app is still using the server's database (not the workstation's) but it's as if by having a full FB server on the client workstation my app can now find the internal FB functions.

I have been assuming that FB internal functions are part of the server install. They don't need to be copied to workstations and they don't need to be declared. They are like SUM, MIN, MAX or AVG.

Q: Why don't Firebird Internal functions run on Firebird client conmputers?

1
sqlfunctionfirebirdinternal
I don't get the 3rd point. You installed fresh FB server there? What is the version of FB on production machines? Isn't there some older version? And what is the error message exactly? - cincura.net
Check version on the development and production machine (COALESCE is available since Firebird 1.5). The exact error message including the Firebird error codes would be really useful. I also agree with cincura.net: I don't know what you are trying to say in your third point. - Mark Rotteveel
I have edited my original post to clarify. - Jonathan Elkins

1 Answers

2
votes

The Firebird's client library "fbclient.dll" (or perhaps renamed as "gds32.dll") parse the statement and validates the SQL keywords that are used.

It is not necessary to install the server on the client machines.

What happened is that the machine was running with an old version of the client library (maybe BDE has been distributed with an old "gds32.dll") that not recognize the "COALESCE" keyword. When you installed the server version 2.1.1 it also install the updated client modules, and possibly replaced the old "gds32.dll" in the system directory for a "fbclient.dll" (option checked by default in the server installer) compatible with Firebird 2.1, renamed as "gds32.dll".

You can attempt to reproduce the problem, search for all copies of "fbclient.dll" and "gds32.dll" in the workstation, and then notice if they are really old version, and try replace only this specific files, without install the server in the client machines.