At 09:22 AM 30/08/2005 +0000, you wrote:

>It seems like the order of the parameter is important even if I use
>named parameters like this:
>
>_cmdMove = DatabaseHelper.GetCommand("ADDREPERTOIREMOVE");
>_cmdMove.CommandType = CommandType.StoredProcedure;
>_cmdMove.Parameters.Add("@REPERTOIREMOVEGUID", FbDbType.Guid);
>_cmdMove.Parameters.Add("@REPERTOIREPOSITIONBEFOREGUID", FbDbType.
>Guid);
>
>...

Indeed. Naming parameters is a client-side thing. The structure that the
database engine sends to the client, containing the descriptions of the
input arguments, is totally order-dependent. The arguments themselves
don't have names.

>My stored procedure:
>
>CREATE PROCEDURE ADDREPERTOIREMOVE (
> REPERTOIREMOVEGUID CHAR(16) CHARACTER SET OCTETS,
> REPERTOIREPOSITIONBEFOREGUID CHAR(16) CHARACTER SET OCTETS,
> REPERTOIREPOSITIONAFTERGUID CHAR(16) CHARACTER SET OCTETS,
> REPERTOIREGUID CHAR(16) CHARACTER SET OCTETS,
>
>....

The names of the arguments are known inside the procedure, of course, and
get treated as local variables. The client interface must send values in
request calls in exactly the right order, with none missing, and they must
be of the defined type. For the server's part, it receives a Prepare
request, which includes this structure (named the XSQLDA) consisting of one
XSQLVAR structure for each parameter, in the correct order. The SQL
statement that your interface sends across in the Prepare request has a
comma-separated list of "?" symbols as placeholders for the XSQLVARs. The
server returns the attributes of the arguments in the XSDQLVARs. After
that, it is up to the interface to fill the structures with valid data.

>This surprised me. I would expect that I could add these parameters in
>any order as I'm using variable names, but when I change the order of
>adding the parameters the first parameter is always matched to
>REPERTOIREMOVEGUID, the 2nd to REPERTOIREPOSITIONBEFOREGUID etc. no
>matter what the name of the parameter is.

Parameters are not variables. It's tempting to think they are. It's
especially confusing for people using Borland tools, since Delphi borrows
the symbols used in PSQL *variables* for its symbolic representation of
parameters to SQL statements *and* for the input arguments for stored
procedures. But user variables cannot be passed as values for
parameters/arguments at all. Certain server context variables can: such
as CURRENT_USER, CURRENT_TIMESTAMP and the like.

>So basically the names have no real impact except that they give me
>some kind of oriantation and of course if I change the values for
>executing the same procedure more than one time I can easier match the
>values to the parameters.

That's it. It's a way for your application to put a rope around these values.

>Anyway, I think this is an easy way to go wrong and I wonder if a
>future Firebird version is going to change this?

No, it won't change You are certainly not the first person to have this
initial misunderstanding about arguments and replaceable
parameters. However, once you get used to the way your particular
interface surfaces the handling of replaceable parameters and SP arguments,
I'm sure any problems you perceive will disappear.

>Beside that I'm deeply impressed by Firebird. I have spent quite some
>time with it now and I can't believe how professional this database is
>in all meanings and yet for free.

Particularly powerful when you make use of parameterized statements. A
number of brain-dead bench tests running Firebird against the wham-bam
querying support of certain well-known "competitors" (an in-joke) throw
thousands of static SQL statements at the databases, usually
one-per-transaction, and claim firebird is "slow". The power of the
parameterized statement (prepare once, execute often) has not touched their
lives yet. :-)

./heLen