Firebird 3.0 query length is limited to 64KB instead of 10M

[papelhigienico3]

Hi!

I'm trying to execute large queries with ZeosLib + Firebird 3.0, and, after losing a lot of work, noted that the query arrives in the Firebird Server are incomplete. So I searched in about specs of Firebird 3.x and in the Firebird FAQ is reported that FB 3.0 supports queries with 10MB of size against FB 2.5 that supports only 64KB. So I found a hardcoded constant set to 64KB that cuts queries bigger than this and I don't have any way to change it.

I found too, that in the past, the code that verifies if the ZeosLib is handling a connection with a FB 3.0 server (and that sets maximum query size to 10MB) was removed (revision 4048). Now I'm working to at least, let this parameter accessible in the connection parameters.

[marsupilami]

Hello papelhigienico3,

welcome to the forums. I did a quick scan of the Firebird 3.0.4 release notes. The release notes tell me that the limit is only removed when using the new, interface based API:

If and only if the new API is being used.-

• The size of the body of a stored procedure or a trigger can exceed the traditional limit of 32 KB.[...] The same limit of 10MB also applies to any user-defined DSQL query.
• ...

Zeos still uses the Legacy API. So Zeos probably still has to honour the limit of 32KB. What I can agree on is that it should raise an exception rather than silently truncating the SQL.

I can only assume that this is about the SQLDA size. Currently TZInterbase6DatabaseInfo.GetMaxSQLDASize returns 10 MB for the maximum SQLDA size - which it really should not.

Best regards,

Jan

[marsupilami]

Hello papelhigienico3,

we had some discussion about this topic. According to the official documentation this is not supported for the legacy API. But there is a comment on the Firebird bugtracker by Karol Bieniaszewski that seems to suggest a workaround.

We will add this workaround to zeos eventually. As a quick solution you might want to set the length parameter in all calls to isc_dsql_prepare, isc_dsql_execute_immediate and isc_dsql_exec_immed2 to 0 (zero). This will make Firebird trat the strings as zero terminated C-strings. The limit of 10MB per statement which is hard coded in Firebird most probably still applies. Also you loose the ability to send the #0 character to Firebird.

Note that we didn't test this - so your mileage may vary.

Best regards,

Jan

[papelhigienico3]

Hi!

We are lucky, because I don't have reverted back my code to SQLite/MySQL (and I lost those versions hahaha).

I'll follow your suggestion of set 0 in the driver apis that had a length parameter. Probably I'll do this tomorrow. In any case I'll report the results here.

[marsupilami]

Hello I will be happy to know about your findings

[papelhigienico3]

Hi @marsupilami!

First of all, sorry for being so unpolished. I'm a little old (and a little inactive on last years) in this forum. I lost my password, so I have created a new one. Thanks by reception again!

Well, backing to FB. I did some tests, and setting the parameter query length to 0 (zero) did my queries to arrive entire/full in the FB server. But now I'm having issues related to query syntax and another FB internal limits. I'll try to fix that soon as.possible.

[marsupilami]

Hello,

Zeos 7.3 now has support for long Firebird statements. We will merge this into Zeos 7.2 eventually.

Best regards,

Jan