TIB_Script run large script

Question

I encountered a problem that IB_Script returned an error while running a longer script:

SQL Error Code = -104 Unexpected end of command - line 18, column 52

As long as the number of characters in the script did not exceed 65,536 characters (current is 66.186), there was no such error message.

I upload IB_Script with the following code:

with dm.DDLScript do
    begin
        try
            SQL.Clear;
            SQL.Add('SET TERM ^;');
            SQL.Add(Format('CREATE OR ALTER PACKAGE %s', [ObjectName]));
            SQL.Add('AS');             
            SQL.AddStrings(hdr.Lines); // hdr > TSynEdit

            Execute;
            SQL.Clear;
            
            SQL.Add('SET TERM ^;');
            SQL.Add(Format('RECREATE PACKAGE BODY %s', [ObjectName]));
            SQL.Add('AS');
            SQL.AddStrings(bdy.Lines); // bdy > TSynEdit
            SQL.Add('^');
            Execute;

            ObjectMod := false;
        except
            on e:exception do
            begin
                messageDlg(e.Message, mtError,[mbOk], 0);
                abort;
            end;
        end;
    end;

Has anyone encountered a similar problem? It is not possible to run the script in a smaller number of characters because it would be a PACKAGE BODY as shown.

With such code you don't need TIBScript, simple query component is enough because you don't execute more than one statement at once. But in any case the problem must be in your script text which is most likely malformed and IB_Script fail to parse it correctly so trying to send to server as whole. Use debugger or Firebird trace service to find out the real text. — user13964273, Nov 29 '21 at 12:03

score 2 · Accepted Answer · answered Nov 29 '21 at 09:39

2

Firebird 2.5 and earlier only supported statements of 64KB (65535 bytes), and this limitation is also reflected in the fbclient.dll API where the size is a 16-bit unsigned integer. Firebird 3.0 increased the limitation to 10MB, but this requires the Firebird 3.0 or higher fbclient, and either using the new object-oriented API, or passing the statement with a length of zero, and the statement text as a NUL-terminated string.

Likely, the library you're using is explicitly passing the size and this size overflows, resulting in Firebird only receiving a small part of the statement being sent (or at least, I guess that line 18 column 52 is a lot less characters than 65535). As Firebird receives an incomplete statement, it then produces the error "Unexpected end of command".

I don't program Delphi myself, but the solution would need a modification in the library to pass statements to isc_dsql_prepare with size 0 and as a NUL-terminated string.

answered Nov 29 '21 at 09:39

Mark Rotteveel

100,966
191
140
197

I'm using firebird 3.0 – Himer Norbert Nov 29 '21 at 09:58
1

@HimerNorbert Yes, and I address that in my answer. The problem is that the library you're using is specifying the statement text size, and the Firebird API itself limits this to a 16-bit integer for historic reasons. The library you use must be changed to pass length zero and use a NUL-terminated string instead. – Mark Rotteveel Nov 29 '21 at 12:21
Thanks. Certainly this is a mistake! This part is already high for me, I didn't write this component and unfortunately I don't understand how to program the api part :/ – Himer Norbert Nov 29 '21 at 13:09
To get 64k overflow at line 18 these lines must be unusually long... – user13964273 Nov 29 '21 at 14:39
@user13964273 Not necessarily, if the entire statement is larger than 65535 and the value is assigned to a 16-bit integer, the resulting overflow could result in the size being interpreted as only a few dozen or hundred characters. For example, with the length in the question of 66.186, it would overflow to the value of 633 characters, which then leads to only 633 characters being sent to the server, which could very well be line 18. – Mark Rotteveel Nov 29 '21 at 14:42

TIB_Script run large script

1 Answers1