Hi,

I don't know if it has been resolved yet - and I cannot look in CodeCentral
at this time -> it is down again.

But, there seems to be a bug in TIBQuery when using multiple parameters with
the same name. Let me explain:

I've got this statement:

"select relatieid, naam, defaultlocatie, vbanr, bvhnr, telefoon, fax
from LEVERANCIER_naw
where (BVHNR = :bvh and :bvh > 0)
or (VBANR = :vba and :vba > 0)"

Here, you can see 4 parameters that are actually 2 - they should get the
same values. When deleting the two extra parameters from the Params property
(designtime), everything is well. Note: probably IBX should already see they
are the same and remove them automagically.

Next, I will prepare the statement. This will trigger the InternalPrepare
method that triggers TIBSQL.PreprocessSQL - below is a small part of that
method:

(ParamState was set by a piece of code before this - it means we're inside a
parameter parse part):

---8<---
        ParamState:
        begin
          { collect the name of the parameter }
          if iCurParamState = ParamDefaultState then
          begin
            if cCurChar = '"' then
              iCurParamState := ParamQuoteState
            else if (cCurChar in ['A'..'Z', 'a'..'z', '0'..'9', '_', '$'])
then
                sParamName := sParamName + cCurChar
            else if FGenerateParamNames then
            begin
              sParamName := 'IBXParam' + IntToStr(iParamSuffix); {do not
localize}
              Inc(iParamSuffix);
              iCurState := DefaultState;

              slNames.Add(sParamName);

              sParamName := '';
            end
---8<---

Note the 'slNames.Add' line - it adds the parameter name to a stringlist
variable. Yes, this one accepts duplicates.

In turn, the TIBQuery.SetParams will get triggered from which the part below
is a small piece:

procedure TIBQuery.SetParams;
---8<---
begin
  for I := 0 to FParams.Count - 1 do
  begin
    if Params[i].IsNull then
      SQLParams[i].IsNull := True
    else begin
      SQLParams[i].IsNull := False;
      case Params[i].DataType of
      ---8<---
        ftString, ftFixedChar:
          SQLParams[i].AsString := Params[i].AsString;
        ftBoolean, ftSmallint, ftWord:
          SQLParams[i].AsShort := Params[i].AsSmallInt;
        ftInteger:
----8<----

Here, you can see that this loop expects the SQLParams property to have the
same number of entries as the Params property. But, because of the multiple
parameters with the same name, the SQLParams thingy holds twice the amount
of entries (in my case). Of course, the copying of the values will go
terribly wrong here and half the parameters I'm using doesn't get filled at
all.

I tried to modify the creation of the stringlist to this:

  slNames := TStringList.Create;
  slNames.Sorted := True;
  slNames.Duplicates := dupIgnore;

But that didn't work either (something about an unknown SQL type in
TIBXSQLDA.Initialize - don't know what I'm talking about here, so leaving it
be.

Anyway, it's easy to reproduce - hard to find - and there probably is a
better solution than my first try :)

Good luck!

--
Martijn Tonies
Upscene Productions

InterBase Workbench - The Developer Tool for InterBase
http://www.interbaseworkbench.com

        Don't do this.  That's not how TParams work.  Try it with the BDE --
it's exactly the same.  Keep all four Params in the Params list.  When
you do:

MyIBQuery.ParamByName('vba').AsInteger := 0;

        ...then *both* "vba" params will be set.

        This should also fix the "bug."

        -Craig

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
We're hiring: http://www.vertexsoftware.com/careerops.htm#sd
     Delphi/InterBase WebLog: http://delphi.weblogs.com

"Craig Stuntz (TeamB)" <cstuntz@no_spam.vertexsoftware.com> schreef in
bericht news:3BFA5FDB.3034EE75@no_spam.vertexsoftware.com...

I think I've done this once with TQuery and an Oracle database and it worked
fine, even when I deleted the 'extra' parameters... Anyway, I'll try this
too :)

--
Martijn Tonies
Upscene Productions

InterBase Workbench - The Developer Tool for InterBase
http://www.interbaseworkbench.com

"Craig Stuntz (TeamB)" <cstuntz@no_spam.vertexsoftware.com> schreef in
bericht news:3BFA5FDB.3034EE75@no_spam.vertexsoftware.com...

I think I've done this once with TQuery and an Oracle database and it worked
fine, even when I deleted the 'extra' parameters... Anyway, I'll try this
too :)

--
Martijn Tonies
Upscene Productions

InterBase Workbench - The Developer Tool for InterBase
http://www.interbaseworkbench.com

You can't do this.  IBX does parameter substitution when it preprocesses SQL.
When you write something like

select relatieid, naam, defaultlocatie, vbanr, bvhnr, telefoon, fax
from LEVERANCIER_naw
where (BVHNR = :bvh and :bvh > 0)
or (VBANR = :vba and :vba > 0)

it gets preprocessed and prepared as

select relatieid, naam, defaultlocatie, vbanr, bvhnr, telefoon, fax
from LEVERANCIER_naw
where (BVHNR = ? and ? > 0)
or (VBANR = ? and ? > 0)

with each param name being placed in the XSQLDA structure with its placeholder
index.  If you removed the duplicates the indexes will be off.

--
Jeff Overcash (TeamB)
      (Please do not email me directly unless  asked. Thank You)
The fool escaped from paradise will look over his shoulder and cry
Sit and chew on daffodils and struggle to answer why?
As you grow up and leave the playground
Where you kissed your Prince and found your frog
Remember the jester that showed you tears, the script for tears. (Fish)