At 11:54 AM 3/02/2010, you wrote: >I am trying to execute a long Update sql statement but I am getting an Dynamic >SQL Error (error code -104).
That code is virtually useless, since it encompasses *all* DSQL errors. You need to capture the 9-digit ISC error code. >After that error I loose the connection with the database for some strange >reason. If you have a string overflow in the connection string, then the reason is not strange at all. Firebird will block any string overflow attempts that it detects in a connection request, since this is the most common way that bad guys have to instigate a denial-of-service attack on your server. >Steps to reproduce: > >1. Download the solution from this link: ><http://www.grbytes.com/downloads/Firebird_sql_error.rar>http://www.grbytes.com/downloads/Firebird_sql_error.rar > >2. Run the project and click on the "Create Connection" button to create a >connection with the database > >3. Click the "Execute SQL" button to execute the long update statement. You >will see the error. Please notice that the connection with the database is >also lost for some reason. If you click again the "Execute SQL" button you >will see an other error. > >It looks like a bug, but I am not sure. >My question is if there is a limitation for the length of the sql statement Yes, it is 65,535 bytes. That comprises every byte that is passed in the statement, including the value of all the parameters, white spaces, quote characters, etc. Note, BYTES, not characters. >and why the connection is lost after failing to execute it? The connection will be lost if the server thinks it is in danger of a DoS attack. However, that's only one (fairly unusual) example of what can break a connection. Applications should be checking what they pass across the interface, BEFORE they submit requests. If you are getting the same problem every time with this statement, and not otherwise, that tends to finger either that statement, either its content or the manner in which it is being submitted. The first clue will be that ISC code. Even if your application is not smart enough to pick it up, it will appear in firebird.log, chronologically near some network error reports, if the server process managed to survive the episode long enough. Bear in mind, too, that if your "long update statement" means "lots of records updated in one transaction", then maybe your batch size is too large for the amount of machine resources available to the server. Around 8000 records per transaction has long been regarded as a base "maximum" batch size to work backwards or forwards from to arrive at an optimal batch size for the resources available. Also, if your update is repeatedly updating the *same* records all within the same transaction, that is NOT recommended practice. It has a high chance of causing something to burst. Helen ------------------------------------------------------------------------------ The Planet: dedicated and managed hosting, cloud storage, colocation Stay online with enterprise data centers and the best network in the business Choose flexible plans and management services without long-term contracts Personal 24x7 support from experience hosting pros just a phone call away. http://p.sf.net/sfu/theplanet-com _______________________________________________ Firebird-net-provider mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
