hosodaamiya wrote:
SQL query aborts when SQL statement is too long or complex. Firebird seems to fail to handle the error of complecated SQL statement when an error occurs at fbembedde.dll. SQL query that includes IN operator with multiple columns and OR conditons have been aborted in our development environment. What is the cause of forced termination?
SQL query includes the condition like below ends with error:
where ID in ('000001','000002,'...'000800')
It uses around 800 constants which is much less than hard limit of 1500 stated in documentation.
The embedded DB is accessed via .NetFramework Web service in IIS.
Environment information:
OS:win2008R2Server IIS:7.5 fbembed.dll:2.5.1.26351 FirebirdSql.Data.FirebirdClient.dll:2.6.0 .NetFramework:3.5SP1
Dmitry Yemanov answers:
The 1500 items limit is not abstract. The IN list is transformed into a set of ORs and then processed recursively, thus requiering a large enough stack. Every thread has a limited stack size. On Windows, the default value is 1MB and it's predefined for exe-files at their linkage time. This is not enough for Firebird, so both fbserver.exe and fb_inet_server.exe override the limit to be 2MB on 32-bit platforms and 4MB on 64-bit ones. However, the embedded server is a library, so it depends on the stack size of the application that loads fbembed.dll. In your case this should be IIS and you surely cannot recompile it, but there are some tricks that you could try, see for example:
http://blogs.msdn.com/b/tom/archive/2008/03/31/stack-sizes-in-iis-affects-asp-net.aspx
http://stackoverflow.com/questions/2319711/increase-stack-size-iis-asp-net-3-5
Another option is to use a standalone Firebird server instead of the embedded one.