Laravel Firebird Error "invalid request BLR at offset"-CodePudding

I'm trying to connect to .fdb using package in my web-app

settings:

'firebird' => [
        'driver'   => 'firebird',
        'host'     => 'localhost',
        'port'     => '3050',
        'database' => storage_path('db.fdb'),
        'username' => 'SYSDBA',
        'password' => 'masterkey',
        'charset'  => 'UTF8',
        'version'  => '2.5', // 1.5 - same
        'role'     => null,
        'UdfAccess' => 'Full', // try like DBeaver connection
        'isc_dpb_no_db_triggers' => true, // try like DBeaver connection
],

controller:

$fb = DB::connection('firebird')->table('table')->count();

result:

SQLSTATE[HY000] [335544343] invalid request BLR at offset 132 (SQL: select count(*) as "aggregate" from "TABLE").

Full error is

invalid request BLR at offset 132 function POS is not defined module name or entrypoint could not be found

What is my problem? How to ignore UDF or turn it on?

Win 10, x64, have Firebird 2.5 and 3.

Have the same problem in C# app.

CodePudding user response：

The problem is that your database has a UDF definition (POS), but cannot find or load the UDF library (or the library doesn't contain the function entrypoint). This can happen if you moved your database to a different server, but forgot to move/install the accompanying UDF libraries, or if you're using a different version of the UDF library (one that doesn't contain the function entrypoint).

There are basically two ways to recover from this:

Make sure your database server has the appropriate UDF library
Remove any usage of the UDF from your database.

Option 1

Your POS UDF uses the DMM_UDF library (which doesn't sounds familiar to me, so it is likely a custom UDF library) with the ibPos entry point. On windows, this library would generally be called DMM_UDF.dll or dmm_udf.dll.

Find the right UDF DLL file (e.g. on the old database server), and make sure it has the same bitness as your Firebird server. If you only have a 32-bit version, replace your 64-bit Firebird server with a 32-bit Firebird server.

You need to add this DLL to the udf directory of your Firebird installation, and make sure the UDF directory is enabled in firebird.conf (setting UdfAccess, default is UdfAccess = Restrict UDF in Firebird 2.5 and 3.0 which will allow use of the udf directory, note that in Firebird 4.0 the default is UdfAccess = None, which disables loading of UDFs).

After doing this, restart Firebird, and Firebird should then be able to load the UDF.

Option 2

Hunt down and remove or replace all usages of the UDF.

A query to find the dependencies of UDF POS is (at least for top-level dependencies of the UDF, and table/view columns):

select 
  dependent_type.RDB$TYPE_NAME dependent_type, 
  rd.RDB$DEPENDENT_NAME, 
  rrf.RDB$RELATION_NAME dependent_table,
  rrf.RDB$FIELD_NAME dependent_column,
  rf.RDB$COMPUTED_SOURCE dependent_expression,
  depended_on_type.RDB$TYPE_NAME depended_on_type, 
  rd.RDB$DEPENDED_ON_NAME 
from RDB$DEPENDENCIES rd
inner join RDB$TYPES dependent_type
  on rd.RDB$DEPENDENT_TYPE = dependent_type.RDB$TYPE and dependent_type.RDB$FIELD_NAME = 'RDB$OBJECT_TYPE'
inner join RDB$TYPES depended_on_type
  on rd.RDB$DEPENDED_ON_TYPE = depended_on_type.RDB$TYPE and depended_on_type.RDB$FIELD_NAME = 'RDB$OBJECT_TYPE' 
left join RDB$RELATION_FIELDS rrf 
  on dependent_type.RDB$TYPE_NAME IN ('COMPUTED_FIELD', 'FIELD') and rrf.RDB$FIELD_SOURCE = rd.RDB$DEPENDENT_NAME 
left join RDB$FIELDS rf 
  on dependent_type.RDB$TYPE_NAME IN ('COMPUTED_FIELD', 'FIELD') and rf.RDB$FIELD_NAME = rd.RDB$DEPENDENT_NAME 
where rd.RDB$DEPENDED_ON_TYPE = 15
and rd.RDB$DEPENDED_ON_NAME = 'POS'

For example, if the UDF is used for a calculated column in table X, column CALCULATED_POS with expression POS(A, B), then you can drop it with

alter table X drop CALCULATED_POS

However, if this column has dependencies, or you still need it, this might not be so easy (or not a good idea).

You can also try to alter it to remove the dependencies, by replacing the expression for an equivalent using built-in functions, or just a dummy expression to get things to work. For example, say POS is equivalent to the built-in POSITION, then you could do something like:

alter table X alter CALCULATED_POS generated always as (POSITION(A, B))

You will need to make similar modifications if the dependencies is used in a view, check constraint (listed as a trigger), trigger or stored procedure.