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 ---&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 mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
