Remove or extend limit of command text length (64K) [CORE1117] #1538

firebird-automations · 2007-01-30T17:11:01Z

Submitted by: Markus Woehling (woehling)

Is duplicated by CORE3713
Is related to QA625

Attachments:
CORE1117_script.txt
c1117kb.logs.7z

Votes: 11

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 ;-)

====== 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: CORE1609 ("PSQL output parameter size limited").

firebird-automations · 2007-01-31T10:50:46Z

Modified by: Markus Woehling (woehling)

description: 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 after this limit (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 ths limitations prevents us from using firebird :-(
We are using an OR-mapping solution that produces quite long sql statements (if the query is complex ;-)

=>

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 ths limitation prevents us from using firebird :-(
We are using an OR-mapping solution that produces quite long sql statements (if the query is complex ;-)

firebird-automations · 2007-01-31T10:52:05Z

Modified by: Markus Woehling (woehling)

description: 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 ths limitation prevents us from using firebird :-(
We are using an OR-mapping solution that produces quite long sql statements (if the query is complex ;-)

=>

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 ;-)

firebird-automations · 2008-01-28T15:23:13Z

Modified by: @pcisar

Workflow: jira [ 11550 ] => Firebird [ 15509 ]

firebird-automations · 2010-10-25T21:47:44Z

Commented by: Holger Klemt (klemmo)

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

Holger
http://www.ibexpert.com

firebird-automations · 2011-12-31T09:02:15Z

Modified by: @dyemanov

Fix Version: 3.0 Alpha 1 [ 10331 ]

firebird-automations · 2011-12-31T09:03:45Z

Modified by: @dyemanov

Link: This issue is duplicated by CORE3713 [ CORE3713 ]

firebird-automations · 2013-02-26T08:27:41Z

Commented by: Marcelo Fortes (ultrix)

Its really, really very annoying limitation !

firebird-automations · 2013-04-11T20:41:35Z

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

assignee: Dmitry Yemanov [ dimitr ]

firebird-automations · 2014-02-14T04:05:58Z

Commented by: Tim Kelly (m00bh000)

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?

firebird-automations · 2014-02-14T07:06:58Z

Commented by: @dyemanov

Tim, this is absolutely impossible. Moreover, even with v3 it won't work automagically upon upgrade, it must be supported by the connectivity layers.

firebird-automations · 2015-04-30T12:56:08Z

Commented by: @livius2

How big is this limit after the fix?

firebird-automations · 2015-04-30T15:20:19Z

Commented by: @dyemanov

There's a safety limit of 10MB at the moment, but it may increased any time without any API / protocol changes.

firebird-automations · 2015-05-28T18:45:13Z

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

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: CORE1609 ("PSQL output parameter size limited").

firebird-automations · 2015-09-28T16:06:44Z

Modified by: @pcisar

Link: This issue is related to QA625 [ QA625 ]

firebird-automations · 2016-06-23T13:51:27Z

Commented by: @livius2

I have atteched script where this limit in current (3.0.1.32541) not work

firebird-automations · 2016-06-23T13:51:27Z

Modified by: @livius2

Attachment: CORE1117_script.txt [ 12976 ]

firebird-automations · 2016-06-24T07:55:15Z

Commented by: @pavel-zotov

Karol,

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

firebird-automations · 2016-06-24T07:55:28Z

Modified by: @pavel-zotov

Attachment: c1117kb.logs.7z [ 12977 ]

firebird-automations · 2016-06-24T08:56:19Z

Commented by: @livius2

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.

Dmitry"

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,
@FSTHandle,
{Database.Encoder.EncodedLength(sb)}
0, PISC_SCHAR(PByte(sb)),
Dialect, OutVars.FXSQLDA));
and the same in
procedure TIBStatement.ExecuteImmediate(const ASQL: String);

firebird-automations closed this as completed May 28, 2015

firebird-automations added affect-version: 2.0.0 fix-version: 3.0 Alpha 1 resolution: fixed priority: major component: engine type: improvement qa: done successfully labels Apr 25, 2021

firebird-automations assigned dyemanov Apr 25, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Remove or extend limit of command text length (64K) [CORE1117] #1538

Remove or extend limit of command text length (64K) [CORE1117] #1538

firebird-automations commented Jan 30, 2007

firebird-automations commented Jan 31, 2007

firebird-automations commented Jan 31, 2007

firebird-automations commented Jan 28, 2008

firebird-automations commented Oct 25, 2010

firebird-automations commented Dec 31, 2011

firebird-automations commented Dec 31, 2011

firebird-automations commented Feb 26, 2013

firebird-automations commented Apr 11, 2013

firebird-automations commented Feb 14, 2014

firebird-automations commented Feb 14, 2014

firebird-automations commented Apr 30, 2015

firebird-automations commented Apr 30, 2015

firebird-automations commented May 28, 2015

firebird-automations commented Sep 28, 2015

firebird-automations commented Jun 23, 2016

firebird-automations commented Jun 23, 2016

firebird-automations commented Jun 24, 2016

firebird-automations commented Jun 24, 2016

firebird-automations commented Jun 24, 2016

Remove or extend limit of command text length (64K) [CORE1117] #1538

Remove or extend limit of command text length (64K) [CORE1117] #1538

Comments

firebird-automations commented Jan 30, 2007

firebird-automations commented Jan 31, 2007

firebird-automations commented Jan 31, 2007

firebird-automations commented Jan 28, 2008

firebird-automations commented Oct 25, 2010

firebird-automations commented Dec 31, 2011

firebird-automations commented Dec 31, 2011

firebird-automations commented Feb 26, 2013

firebird-automations commented Apr 11, 2013

firebird-automations commented Feb 14, 2014

firebird-automations commented Feb 14, 2014

firebird-automations commented Apr 30, 2015

firebird-automations commented Apr 30, 2015

firebird-automations commented May 28, 2015

firebird-automations commented Sep 28, 2015

firebird-automations commented Jun 23, 2016

firebird-automations commented Jun 23, 2016

firebird-automations commented Jun 24, 2016

firebird-automations commented Jun 24, 2016

firebird-automations commented Jun 24, 2016