[Firebird-net-provider] [FB-Tracker] Created: (DNET-720) Dynamic SQL Statement generation limit err

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Dynamic SQL Statement generation limit error: eror code = -204 Implementation limit exceeded block size exceeds implementation restriction
------------------------------------------------------------------------------------------------------------------------------------------

                 Key: DNET-720
                 URL: http://tracker.firebirdsql.org/browse/DNET-720
             Project: .NET Data provider
          Issue Type: Bug
          Components: ADO.NET Provider, DDEX Provider, Entity Framework support
    Affects Versions: 5.6.0.0
         Environment: Windows 8, Visual Studio 2013 Update 5, Firebird-2.5.6.27020_0 (Win32), FirebirdSql.Data.FirebirdClient 5.6.0.0, DDEXProvider-3.0.2.0
            Reporter: Vladimir
            Assignee: Jiri Cincura

Hello, please help me with "Dynamic SQL Error: error code = -204 Implementation limit exceeded block size exceeds implementation restriction" error

Project details: .NET 4.5.2 WPF-app
nuget-packages:
Firebird Entity Framework Provider - version 5.6.0
Firebird ADO.NET Data Povider  - version 5.6.0
Entity Framework - version 6.1.3

I have a number of Stored Procedures in my DB, which i use a lot. But when i tried to use these procedures with EntityFramework, i faced with an unsolvable problem.  For this bug i chose two similar Stored Procedures: B_IMP_I_OGL and B_IMP_I_REESTR and created two projects to work with them. B_IMP_I_OGL - works perfectly, B_IMP_I_REESTR - returns following error during compilation:

"FirebirdSql.Data.FirebirdClient.FbException: Dynamic SQL Error
SQL error code = -204
Implementation limit exceeded
block size exceeds implementation restriction"

B_IMP_I_REESTR has 20 input parameters, while B_IMI_I_OGL has only 7 parameters. (Both procedures return only one parameter - integer ID)

This is my procedure:

create or alter procedure B_IMP_I_REESTR (
    IDUSER integer,
    IMPMODE integer,
    IDGEN integer,
    IDPARENT integer,
    IDTCH integer,
    NOMER varchar(15),
    SBORNIK integer,
    NAME varchar(250),
    VIX_DAN blob sub_type 0 segment size 80,
    ISSYSTEM integer,
    POKAZ varchar(8),
    GOD integer,
    RABMAT integer,
    IDGROUP integer,
    IDPODGROUP integer,
    IDTYPE integer,
    ORDERVIEW integer,
    CREATEDATE date,
    AUTHOR varchar(250),
    NORM_GUID varchar(100),
    INSTALL_GUID varchar(100),
    INDEX_YEAR integer,
    INDEX_MONTH integer,
    INDEX_ORGNAME varchar(250),
    INDTYPE varchar(50))
returns (
    ID integer)
as
declare variable REORDERING integer;
declare variable AINSERTING integer;
declare variable IDIND integer;
declare variable ISSYSTEM_OLD integer;
declare variable IDPARENT_OLD integer;
BEGIN
  IF (:IDTCH = 0)
  THEN IDTCH = NULL;

  UPDATE B_TEMP_BLOB SET BLOB1 = :VIX_DAN WHERE ID = :IDGEN;
  SELECT BLOB1 FROM B_TEMP_BLOB WHERE ID = :IDGEN INTO :VIX_DAN;

  IF (:NORM_GUID <> '') THEN
  BEGIN
    SELECT ID, ISSYSTEM
    FROM B_IMP_F_REESTR(:IDUSER, :IMPMODE, :NORM_GUID)
    INTO ID, ISSYSTEM_OLD;

    /* Только поиск для определения наличия записи */
    IF (IMPMODE = 2) THEN BEGIN
      SUSPEND;
      EXIT;
    END

    IF (:ORDERVIEW IS NULL OR :ORDERVIEW <= 0)
    THEN
      SELECT MAX(ORDERVIEW) + 1
        FROM B_REESTR
        WHERE IDPARENT = :IDPARENT
          /*AND RABMAT = :RABMAT /* для подстраховки */
        INTO :ORDERVIEW;
    ELSE
      REORDERING = 1;

    IF (ORDERVIEW IS NULL) THEN
      ORDERVIEW = 1;
    ELSE
      REORDERING = 1;

    IF (:ID IS NULL) THEN
    BEGIN
      ID = GEN_ID (A_G_SMETA, 1);
      AINSERTING = 1;
    END

    IF (:IDGROUP = 0 OR :IDPODGROUP = 0) THEN
      SELECT IDGROUP, IDPODGROUP
        FROM B_READ_DEFAULT_REESTR_GROUPS (:IDUSER, :GOD)
        INTO :IDGROUP, :IDPODGROUP;

    IF (:REORDERING = 1) THEN
      EXECUTE PROCEDURE B_WRITE_NEWORDER_REESTR(
          :IDUSER, :ID, :IDPARENT, :RABMAT, :ORDERVIEW )
        RETURNING_VALUES (:REORDERING);

    SELECT INDEX_ID_MES
      FROM B_FIND_IND_PARAMS (:IDUSER, :INDEX_YEAR, :INDEX_MONTH, :INDEX_ORGNAME)
      INTO :IDIND;

    IF (:AINSERTING = 1) THEN
    BEGIN
      INSERT INTO B_REESTR (
          ID, IDPARENT, IDTCH, NOMER, SBORNIK, NAME, VIX_DAN, ISSYSTEM,
          POKAZ, GOD, RABMAT, IDGROUP, IDPODGROUP, IDTYPE, IDIND,
          ORDERVIEW, CREATEDATE, AUTHOR, NORM_GUID, INDTYPE, INSTALL_GUID)
        VALUES (
          :ID, :IDPARENT, :IDTCH, :NOMER, :SBORNIK, :NAME, :VIX_DAN, :ISSYSTEM,
          :POKAZ, :GOD, :RABMAT, :IDGROUP, :IDPODGROUP, :IDTYPE, :IDIND,
          :ORDERVIEW, :CREATEDATE, :AUTHOR, :NORM_GUID, :INDTYPE, :INSTALL_GUID);
    END
    ELSE
    BEGIN
      IF (:ISSYSTEM_OLD = 1)
      THEN ISSYSTEM = 1;

      /* <[9129]> - перенос нескрытых папок и сборников внутри скрываемого */
      if ((:SBORNIK=0) and (-10000/*FEB60E9A677D*/ - :RABMAT in (0, 2, 3)/*[9F5FFA1322A6]*/)) then
      for select :IDPARENT
        from B_REESTR
        where ID = :ID
        into :IDPARENT_OLD
      do
      update B_REESTR
        set IDPARENT = :IDPARENT_OLD
        where IDPARENT = :ID
          and RABMAT in (0, 2, 3);/*[9F5FFA1322A6]*/
      /* поиск нескрытых родителей по рекурсии вверх, равно как и перенос
         нескрытых дочерних узлов нижних уровней, выполнится в процессе Их
         (родительских или дочерних узлов) "сокрытия" при импорте
         </[9129]> */

      UPDATE B_REESTR
        SET IDPARENT     = :IDPARENT,
            IDTCH        = :IDTCH,
            NOMER        = :NOMER,
            SBORNIK      = :SBORNIK,
            NAME         = :NAME,
            VIX_DAN      = :VIX_DAN,
            ISSYSTEM     = :ISSYSTEM,
            POKAZ        = :POKAZ,
            GOD          = :GOD,
            RABMAT       = :RABMAT,
            IDGROUP      = :IDGROUP,
            IDPODGROUP   = :IDPODGROUP,
            IDTYPE       = :IDTYPE,
            ORDERVIEW    = :ORDERVIEW,
            IDIND        = :IDIND,
            CREATEDATE   = :CREATEDATE,
            AUTHOR       = :AUTHOR,
            NORM_GUID    = :NORM_GUID,
            INDTYPE      = :INDTYPE,
            INSTALL_GUID = :INSTALL_GUID
        WHERE ID = :ID;
    END
  END

  execute procedure B_IMP_U_REESTR_HIDE(:ID); /* [9129] - поддержка информации о скрытии/удалении */

  SUSPEND;
END

1) I added this Stored Procedure "B_IMP_I_REESTR" to my model.edmx file  (and checked in Model Browser\Storage Model, that it has been added)
2) Then i opened Model Browser again and created complex type with one integer field named ID (return type for import function)
3) Then i created import function for B_IMP_I_REESTR procedure, which returns complex type (2)
4) This is calling code:
var temp_B_IMP_I_REESTR = Context.B_IMP_I_REESTR(10, 1, 19105302, 1399455, null, "132456", 0, "some text", null, 0, "", null, 0,
               1, 1, 1399451, 6000000, null, "some other text", Guid.NewGuid().ToString(), null, null, null, "", "some random text");
 foreach (B_IMP_I_REESTR_ReturnType bImpReestReturnComplexType in temp_B_IMP_I_REESTR)
            {
                MessageBox.Show(bImpReestReturnComplexType.ID.ToString());
            }

And then i get unresolved exception:
 "System.Data.Entity.Core.EntityCommandExecutionException" in EntityFramework.dll

Visual studio Output gives me the following statement:

FirebirdSql.Data.FirebirdClient Information: 0 : Command:
SELECT 
1 AS "C1", 
"A"."ID" AS "ID"
FROM "B_IMP_I_REESTR"(CAST(@IDUSER AS INT), CAST(@IMPMODE AS INT), CAST(@IDGEN AS INT), CAST(@IDPARENT AS INT), CAST(@IDTCH AS INT), CAST(@NOMER AS VARCHAR(8191)), CAST(@SBORNIK AS INT), CAST(@NAME AS VARCHAR(8191)), CAST(@VIX_DAN AS BLOB SUB_TYPE BINARY), CAST(@ISSYSTEM AS INT), CAST(@POKAZ AS VARCHAR(8191)), CAST(@GOD AS INT), CAST(@RABMAT AS INT), CAST(@IDGROUP AS INT), CAST(@IDPODGROUP AS INT), CAST(@IDTYPE AS INT), CAST(@ORDERVIEW AS INT), CAST(@CREATEDATE AS TIMESTAMP), CAST(@AUTHOR AS VARCHAR(8191)), CAST(@NORM_GUID AS VARCHAR(8191)), CAST(@INSTALL_GUID AS VARCHAR(8191)), CAST(@INDEX_YEAR AS INT), CAST(@INDEX_MONTH AS INT), CAST(@INDEX_ORGNAME AS VARCHAR(8191)), CAST(@INDTYPE AS VARCHAR(8191))) AS "A"
Parameters:
Name:IDUSER	Type:Integer	Used Value:10
Name:IMPMODE	Type:Integer	Used Value:1
Name:IDGEN	Type:Integer	Used Value:19105302
Name:IDPARENT	Type:Integer	Used Value:1399455
Name:IDTCH	Type:Integer	Used Value:<null>
Name:NOMER	Type:VarChar	Used Value:132456
Name:SBORNIK	Type:Integer	Used Value:0
Name:NAME	Type:VarChar	Used Value:some text
Name:VIX_DAN	Type:Binary	Used Value:<null>
Name:ISSYSTEM	Type:Integer	Used Value:0
Name:POKAZ	Type:VarChar	Used Value:
Name:GOD	Type:Integer	Used Value:<null>
Name:RABMAT	Type:Integer	Used Value:0
Name:IDGROUP	Type:Integer	Used Value:1
Name:IDPODGROUP	Type:Integer	Used Value:1
Name:IDTYPE	Type:Integer	Used Value:1399451
Name:ORDERVIEW	Type:Integer	Used Value:6000000
Name:CREATEDATE	Type:TimeStamp	Used Value:<null>
Name:AUTHOR	Type:VarChar	Used Value:some other text
Name:NORM_GUID	Type:VarChar	Used Value:da204a67-c82d-49ba-8475-945110f3c2f7
Name:INSTALL_GUID	Type:VarChar	Used Value:<null>
Name:INDEX_YEAR	Type:Integer	Used Value:<null>
Name:INDEX_MONTH	Type:Integer	Used Value:<null>
Name:INDEX_ORGNAME	Type:VarChar	Used Value:
Name:INDTYPE	Type:VarChar	Used Value:some random text

FirebirdSql.Data.FirebirdClient.FbException: Dynamic SQL Error
SQL error code = -204
Implementation limit exceeded
block size exceeds implementation restriction ---&amp;gt; FirebirdSql.Data.Common.IscException: Dynamic SQL Error
SQL error code = -204
Implementation limit exceeded
block size exceeds implementation restriction
   в FirebirdSql.Data.Client.Managed.Version10.GdsDatabase.ProcessResponse(IResponse response)
   в FirebirdSql.Data.Client.Managed.Version10.GdsDatabase.ReadResponse()
   в FirebirdSql.Data.Client.Managed.Version10.GdsDatabase.ReadGenericResponse()
   в FirebirdSql.Data.Client.Managed.Version11.GdsStatement.Prepare(String commandText)
   в FirebirdSql.Data.FirebirdClient.FbCommand.Prepare(Boolean returnsSet)
   в FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteCommand(CommandBehavior behavior, Boolean returnsSet)
   в FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior behavior)

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

[Firebird-net-provider] [FB-Tracker] Created: (DNET-720) Dynamic SQL Statement generation limit err

A powerful, cross platform, SQL database system

[Firebird-net-provider] [FB-Tracker] Created: (DNET-720) Dynamic SQL Statement generation limit error: eror code = -204 Implementation limit exceeded block size exceeds implementation restriction