Linq2Db not all stored procedures scaffolding

[MaceWindu]

Discussed in #4442

^{Originally posted by Nevega March 6, 2024}
I've tried to scaffold an existing database using cli

dotnet linq2db scaffold -p Firebird -c "Server=;Database=;User Id=;Password=" --objects table,stored-procedure,aggregate-function --overwrite true
In result some stored procedures scaffold, but some do not. As I understand no scaffolding procedures which have out parameters.

[MaceWindu]

Provided database trigger issue with LINQPad driver we need to fix - procedures scaffold result in NRE exception.

Regarding original issue: many procedures in database failed to load it's schema. E.g. for GOOD$KITEXPQUANTS we have this error:

Error while parsing procedure GOOD$KITEXPQUANTS's BLR
invalid request BLR at offset 2
function IFI is not defined
module name or entrypoint could not be found

This is not a blocking issue for procedure, but because Firebird procedure could be used as Table function we mark them as Table function. And for table function lack of schema results in function skipping as we cannot define function mapping without known result schema.

How it could be workarounded:

• user could use interceptor to mark loaded function as procedure so we will generate mapping which doesn't return any tables
• user could fix it's database to not throw such errors

What we should fix on our side:

• as we can scaffold procedure as procedure or table function, we should fallback to procedure scaffold for firebird if we cannot load it's schema

[MaceWindu]

@Nevega you can see all errors using code like that:

var schema = db.DataProvider.GetSchemaProvider().GetSchema(db, new GetSchemaOptions()
{
	GetProcedures = true,
});

foreach (var proc in schema.Procedures)
{
	if (proc.ResultException != null)
	{
		Console.WriteLine($"Procedure: {proc.ProcedureName} : {proc.ResultException.Message}");
	}
}

[MaceWindu]

@Nevega,

As for interceptor approach workaround you need to look at those methods https://linq2db.github.io/articles/CLI.html#schema-load-interceptors, but I'm not sure it is not too late to fix it there