I use this code for a SP:

PROCEDURE "UpdateBehandlungsZeitraum" (IN "ID" INTEGER)
BEGIN
DECLARE Stmt STATEMENT;
PREPARE Stmt FROM
  'update Rechnungen set
  Behandlungsbeginn = (select min(datum) from Posten where RechnungenID
= ?),
  Behandlungsende =  (select max(datum) from Posten where RechnungenID = ?)
  where RechnungenID = ?';
EXECUTE Stmt using ID;
END

It executes without an error but the result is not correct.

Whereas this works fine:

....
EXECUTE Stmt using ID, ID, ID;

Have I really to use a parameter for each wildcard, till now I thought
EDB is using the same value for all wildcards, if there is only one
parameter.

Thanks Uli

Uli,

> Have I really to use a parameter for each wildcard, till now I thought EDB
> is using the same value for all wildcards, if there is only one parameter.

Yes, you must use one value for each parameter because parameter values are
passed by position.
The "?" character is not a parameter identifier but a placeholder for
parameters. Each "?" declares one unnamed parameter in the same position and
that's why you have 3 parameters in your statement, not one as you may
think.

--
Fernando Dias [Team Elevate]

Fernando,

> Yes, you must use one value for each parameter because parameter values are
> passed by position.
> The "?" character is not a parameter identifier but a placeholder for
> parameters. Each "?" declares one unnamed parameter in the same position and
> that's why you have 3 parameters in your statement, not one as you may
> think.

Thanks for the information.
I wondered why some of my statements didn't work as expected.

Dont' you think an error should be raised when the number of
placeholders and parameters doesn't match?

Uli


What happens if you use a named parameter (eg :ID) rather than ?

Roy Lambert

Uli, Roy

> Dont' you think an error should be raised when the number of
> placeholders and parameters doesn't match?

Yes, it could help. Or, if the number of arguments is less than the
number of parameters, it can be assumed to pass NULL values for the
missing arguments, but that can generate weird expressions, like
"=Null", ">null", "<null", etc.. Roy may have a different opinion here

About using named parameters, I think they are not allowed in dynamic
SQL, or at least they are not allowed in EDB dynamic SQL ...

--
Fernando Dias
[Team Elevate]

Fernando


>Roy may have a different opinion here

I'd go for an error message

>About using named parameters, I think they are not allowed in dynamic
>SQL, or at least they are not allowed in EDB dynamic SQL ...

I thought that might be the case but since I don't have any sp's defined yet I thought it would be easier for Uli to check.

Roy Lambert

Roy

> I'd go for an error message

I was talking about nulls, not the error message

--
Fernando Dias
[Team Elevate]

Fernando,

I know that named parameters are not allowed in sp's. (A man from [Team-Elevate] should
know it as well, Roy.

The problem is that without any exception raised you can run into really weird result
without even noticing that there is anything wrong. That was the case with the mentioned
procedure.

I only discovered it by chance.

Regards Uli

Uli,

<< Dont' you think an error should be raised when the number of placeholders
and parameters doesn't match? >>

It depends upon the needs of the application.  Right now it simply uses a
NULL value instead, so it gives you the option of having "optional"
parameters, so to speak.  This is equivalent to the behavior of client
applications in Delphi in terms of non-specified parameter values.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

> It depends upon the needs of the application.  Right now it simply uses a
> NULL value instead, so it gives you the option of having "optional"
> parameters, so to speak.  

What happens to the expressions containing the missing argument? Are
they evaluated taking NULL as the actual value for the parameter and in
that case, how does EDB deal with expressions like <ColName> = NULL ?

--
Fernando Dias
[Team Elevate]