Issue Details (XML | Word | Printable)

Key: CORE-1117
Type: Improvement Improvement
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Markus Woehling
Votes: 11
Watchers: 3

If you were logged in you would be able to see more operations.
Firebird Core

Remove or extend limit of command text length (64K)

Created: 30/Jan/07 03:11 PM   Updated: 24/Jun/16 05:56 AM
Component/s: Engine
Affects Version/s: 2.0.0
Fix Version/s: 3.0 Alpha 1

File Attachments: 1. File c1117kb.logs.7z (2 kB)
2. Text File CORE1117_script.txt (73 kB)

Environment: Windows
Issue Links:

QA Status: Done successfully
Test Details:
Test verifies that we can issue STATIC `SELECT` statement with length more than 64K.
Its SELECT statement has length = ~72900 bytes, but FB 3.0 can successfully run much longer (checked up to 5000 columns).
FB 2.5 fails with such commands with message: "Single isql command exceeded maximum buffer size"
See also: CORE-1609 ("PSQL output parameter size limited").

 Description  « Hide
A command text (I tried it with a dynamic SELECT statment) can't be longer then 65535 bytes. The parser seems to refill the first bytes of the buffer after this limit is reached (a command of length 65536 produces an error in line 1, column 1).

Sample SELECT statement: SELECT NULL, NULL, ...., NULL FROM <AnyTable>

It would be great if you can remove or extend this limit (maybe to 16M like MSSQL and Oracle)

Firebird 2.0 embedded was our first choice for an embedded solution besides MSSQL and Oracle, but this limitation prevents us from using firebird :-(
We are using an OR-mapping solution that produces quite long sql statements (if the query is complex ;-)

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Holger Klemt added a comment - 25/Oct/10 07:47 PM
i second this old requirement, especially also for procedures and triggers, it would be very useful to remove this limit, since i already often have problems with this limit


Marcelo Fortes added a comment - 26/Feb/13 05:27 AM
Its really, really very annoying limitation !

Tim Kelly added a comment - 14/Feb/14 02:05 AM
I just hit this limit in Firebird v2. This is a bit of a showstopper for me. Would backporting the fix be that difficult to version 2?

Dmitry Yemanov added a comment - 14/Feb/14 05:06 AM
Tim, this is absolutely impossible. Moreover, even with v3 it won't work automagically upon upgrade, it must be supported by the connectivity layers.

Karol Bieniaszewski added a comment - 30/Apr/15 09:56 AM
How big is this limit after the fix?

Dmitry Yemanov added a comment - 30/Apr/15 12:20 PM
There's a safety limit of 10MB at the moment, but it may increased any time without any API / protocol changes.

Karol Bieniaszewski added a comment - 23/Jun/16 10:51 AM
I have atteched script where this limit in current ( not work

Pavel Zotov added a comment - 24/Jun/16 04:55 AM

i've ran your script on 32539 & 32542 - no errors

Karol Bieniaszewski added a comment - 24/Jun/16 05:56 AM
Pavel - all is ok - no isssues here
this was on devel and privately with Dmitry
Now it works for me in v3 branch too. ISQL from shell works OK, but
still fails when running from the Eclipse console. Looks like the input
is somehow truncated there.

So it seems being the tool problem. If it always passes SQL string
length to the old API (instead of passing zero), then the issue is
perfectly expected. I can confirm that FlameRobin works exactly this
way, so it cannot process SQL statements longer than 64KB.


as hint to others who use Delphi and Firedac
to fix "tool" problem we need in Delphi modify unit FireDAC.Phys.IBWrapper

procedure TIBStatement.Prepare(const ASQL: String);
only one change is needed
Check(Lib.Fisc_dsql_prepare(@Error.FStatusVector, @FTransaction.FTRHandle,
0, PISC_SCHAR(PByte(sb)),
Dialect, OutVars.FXSQLDA));
and the same in
procedure TIBStatement.ExecuteImmediate(const ASQL: String);