TIBQUERY Error Message

[Adalberto Baldini]

I get this error message :
Error at line 1
Dynamic SQL Error
SQL error code = -804
Incorrect values within SQLDA structure
SQL - select distinct g.abinp, g.ambname,g.a_abiqt,
CAST(g.au_acimur as Integer) as au_acimur,
g.fil, g.filname, g.f_filiqt, CAST(g.au_fcimur as Integer) as au_fcimur,
CAST(g.aut_input as Integer) as aut_input, CAST(g.aut_celur as Integer) as
aut_celur
from ope_doc_view a1
left join AB_DOC (:utente, a1.csoc, a1.nreserc) a on (a.coddoc =
a1.coddoc_2)
left join FIL_DOC (:utente, a1.csoc, a1.nreserc) b on (b.coddoc =
a.coddoc and a.coddoc is not null)
left join FITIPDOC c on (c.coddoc = b.tipdoc and c.coddoc =a1.tipdoc_2 and
b.tipdoc is not null)
left join AB_GIORN(:utente, a1.csoc, a1.nreserc) aa on (aa.codgio =
a1.codgio_2)
left join FIL_GIORN(:utente, a1.csoc, a1.nreserc) ab on (ab.codgio =
aa.codgio and aa.codgio is not null)
left join FITIPGIO ac on (ac.codgio = ab.tipgio and ac.codgio =
a1.tipgio_2 and ab.tipgio is not null)
left join abfl_usrsocinp (:utente, :csoc, :nreserc ) g on ( a.abinp =
g.a_abiqt and b.fil = g.f_filiqt )
where a1.csoc = :csoc and
a1.nreserc = :nreserc and
a1.codope = :ope and
a1.coddoc_2 = :doc_2 and
c.des60 is not null and
ac.des60 is not null and
g.abinp is not null and
g.fil is not null
order by
g.abinp, g.fil

At design time if I set the query Active = True I don't get error
if I run the query with a 3° part SQL Tool it works fine

with IBConsole and running program I get above error.

Where I have to search for error ?
Thanks for help.

Adalberto Baldini

[Wayne Niddery (TeamB)]

Post by Adalberto Baldini
from ope_doc_view a1
left join AB_DOC (:utente, a1.csoc, a1.nreserc) a on (a.coddoc =

[...]

Post by Adalberto Baldini
left join abfl_usrsocinp (:utente, :csoc, :nreserc ) g on ( a.abinp =
g.a_abiqt and b.fil = g.f_filiqt )
where a1.csoc = :csoc and
a1.nreserc = :nreserc and
a1.codope = :ope and
a1.coddoc_2 = :doc_2 and
Where I have to search for error ?

How are you assigning the parameters? Are you using ParamByName? That should
work (it should assign all parameters with the same name), whereas I've had
trouble using indexed parameter positions, e.g. MyQuery.Params[0].AsString.

If you continue to have trouble, you'll probably need to give all the
parameters unique names.

[Adalberto Baldini]

Post by Wayne Niddery (TeamB)
How are you assigning the parameters? Are you using ParamByName? That
should work (it should assign all parameters with the same name),
whereas I've had trouble using indexed parameter positions, e.g.
MyQuery.Params[0].AsString.

YES, I use ParamByName.

Post by Wayne Niddery (TeamB)
If you continue to have trouble, you'll probably need to give all the
parameters unique names.

What do you means ? It seems to me I use unique param name except for
:utente which is repeated many times .

Post by Wayne Niddery (TeamB)

Post by Adalberto Baldini
from ope_doc_view a1
left join AB_DOC (:utente, a1.csoc, a1.nreserc) a on (a.coddoc =

[...]

Post by Adalberto Baldini
left join abfl_usrsocinp (:utente, :csoc, :nreserc ) g on

left join abfl_usrsocinp (:utente, a1.csoc, a1.nreserc ) g on

THIS MODIFIED IT WORKS.
(I think there is something to review on parameters substitution; as I
said a 3° part tool works fine in same condition)

Thanks.
Adalberto

[Wayne Niddery (TeamB)]

Post by Adalberto Baldini
What do you means ? It seems to me I use unique param name except for
:utente which is repeated many times .

Exactly. This seems to be the problem. Try naming them as :utente1,
:utente2, etc. It's a bit of a pain, more assignments are needed but it
seems to workaround the problem.

[Adalberto Baldini]

Post by Wayne Niddery (TeamB)
Exactly. This seems to be the problem. Try naming them as :utente1,
:utente2, etc. It's a bit of a pain, more assignments are needed but it
seems to workaround the problem.

Param :utente is not the problem, even if it is repeated many times.
Param :csoc and :nreserc are.

Naming it :csoc, :csoc1, :nreserc, :nreserc1 it works,
like changing
left join abfl_usrsocinp (:utente, :csoc, :nreserc ) g on with
left join abfl_usrsocinp (:utente, a1.csoc, a1.nreserc ) g on

Conflict is arised by params that are ALSO used in WHERE clause
if I leave params as I defined and remove
a1.csoc = :csoc and
a1.nreserc = :nreserc and
from WHERE it works.

Adalberto

[Jeff Overcash (TeamB)]

Post by Adalberto Baldini

Post by Wayne Niddery (TeamB)
How are you assigning the parameters? Are you using ParamByName? That
should work (it should assign all parameters with the same name),
whereas I've had trouble using indexed parameter positions, e.g.
MyQuery.Params[0].AsString.

YES, I use ParamByName.

TParam does not allow for reusing a parameter name it will only assign the first
instance. TIBDataset does not use TParams and it will assign all params the value.