Re: Delphi/Dynamic Queries slow after Informix Upgrade

In article </00I10ElJ38f092yn@kcbbs.gen.nz>, kerry@kcbbs.gen.nz (Kerry Sainsbury) wrote: >In article <6fe3o4$o2b$1@picasso.op.net>, >root@candle.pha.pa.us (Bruce Momjian) wrote: >>I had posted several weeks ago stating that my Delphi application became >>slower after an upgrade to Informix-OnLine Dynamic Server Version >>7.24.UC3. Many thanks to those who offered suggestions, like >>OPTCOMPIND, but it was already set to zero. >> >>Here are more details on what is happening. The problem is that since >>the upgrade of Informix, prepared queries with parameters from Delphi >>are not using indexes, while they did before the upgrade. > >[snip] > >I'll post the patch tomorrow (it's at work, and I'm not) As promised, here's the original email from Borland US: 1) Though our official stance is that we don't support Informix SE, this is mainly because of QA and support issues. There is nothing that we do to prevent Informix SE from working, as you have discovered. 2) The problem is because the Native driver is telling Informix-Connect that the parameter is a VARCHAR, when in fact it is a CHAR (the customer describes the problem perfectly). In versions of BDE prior to 4.0, we treated CHAR and VARCHAR both as VARCHAR. But in BDE 4.0 (i.e. Delphi 3), we have fixed this problem at the BDE level. Unfortunately, the Delphi team was unable surface the BDE change in the Delphi 3.01 release because it would have required changes that would break backwards compatiblity with Delphi 3.0. They are planning to address the issue the 4.0 release. In other words, you could now characterize this as a bug in the Delphi VCL. 3) The good news is that because the bug is in VCL, there are a couple of ways to workaround the problem. Both involve changing the DBTABLES.PAS source file and recompiling the application to use the modified source. If the application is only working with Informix SE databases, and only with CHAR field types (that is, no VARCHAR fields are used), then the simple fix is to add the following line to the TQuery.SetParam method: if iFldType = fldZString then iSubType := fldstFIXED; This should be inserted after the line which assigns iFldType (line 8205 in the 3.01 source). Note that this fix will force all string query input parameters for to be treated as fixed length CHAR fields, so it should not be used if other types of servers are being used. The alternative fix is to add a "FixedChar" boolean property to TParam and then add a line of code to TQuery.SetParam as follows: if FixedChar then iSubType := fldstFIXED ; Again, this goes right after the line of code which assigns iFldType. Using this method, it is also necessary to add code after assigning the query parameter values to set the FixedChar property to true. Something similar to the following: Query1.ParamByName('FOO').AsString := 'BAR'; Query1.ParamByName('FOO').FixedChar := True; Note that because this workaround changes the interface seciton of the unit, any units which use DBTABLES will also have to be recompiled. The easiest way to do this is to add the SOURCE\\VCL directory to the search path in the project options. Finally, make sure the application is not using the VCLDB30 runtime package. Cheers, Kerry S ------------------------------------------------------------- Quanta Systems, Auckland, New Zealand. Ph. +64 9 302-3760 Fax. +64 9 302-3784 ------------------------------------------------------------- The Informix FAQ (v2.10 May 1997): http://www.iiug.org/techinfo/faq/informix.htm