Is order of parameters for database Command object really important?

Question

I was debugging a database operation code and I found that proper UPDATE was never happening though the code never failed as such. This is the code:

        condb.Open();
        OleDbCommand dbcom = new OleDbCommand("UPDATE Word SET word=?,sentence=?,mp3=? WHERE id=? AND exercise_id=?", condb);
        dbcom.Parameters.AddWithValue("id", wd.ID);
        dbcom.Parameters.AddWithValue("exercise_id", wd.ExID);
        dbcom.Parameters.AddWithValue("word", wd.Name);
        dbcom.Parameters.AddWithValue("sentence", wd.Sentence);
        dbcom.Parameters.AddWithValue("mp3", wd.Mp3);

But after some tweaking this worked:

        condb.Open();
        OleDbCommand dbcom = new OleDbCommand("UPDATE Word SET word=?,sentence=?,mp3=? WHERE id=? AND exercise_id=?", condb);
        dbcom.Parameters.AddWithValue("word", wd.Name);
        dbcom.Parameters.AddWithValue("sentence", wd.Sentence);
        dbcom.Parameters.AddWithValue("mp3", wd.Mp3);                         
        dbcom.Parameters.AddWithValue("id", wd.ID);
        dbcom.Parameters.AddWithValue("exercise_id", wd.ExID);

1. Why is it so important that the parameters in WHERE clause has to be given the last in case of OleDb connection? Having worked with MySQL previously, I could (and usually do) write parameters of WHERE clause first because that's more logical to me.

2. Is parameter order important when querying database in general? Some performance concern or something?

3. Is there a specific order to be maintained in case of other databases like DB2, Sqlite etc?

Update: I got rid of ? and included proper names with and without @. The order is really important. In both cases only when WHERE clause parameters was mentioned last, actual update happened. To make matter worse, in complex queries, its hard to know ourselves which order is Access expecting, and in all situations where order is changed, the query doesnt do its intended duty with no warning/error!!

Seraph

Aren't your query template missing some syntax to give names to the parameter slots? In the absence of names the library will probably assume that you want to fill them in the order they appear. Do you expect the database engine to random select words that appear near the ? in the template and assume that this is the name you want to use for it? – Fishing

@Henning Makholm , oh may be that's the thing. Let me find it out if it is.. – Seraph

@Henning Makholm , No that's not the thing. I got rid of ? and included proper names with and without @. The order is really important. In both cases only when WHERE clause parameters was mentioned last, actual update happened. May be it got to do with OleDbCommand class. – Seraph

"May be it got to do with OleDbCommand class." - It has to do with OLEDB itself. Parameters are strictly positional. In most - if not all - cases we can assign names to the parameters (instead of calling them all "?"), but those names are ignored. – Balkan

Answer 1

Within Access, an ADODB.Command object ignores parameter names. In fact I can refer to a parameter using a bogus name (which doesn't even exist in the SQL statement) and ADO doesn't care. All it seems to care about is that you supply parameter values in the exact same order as those parameters appear in the SQL statement. BTW, that is also what happens if I build the SQL statement with ? place-holders instead of named parameters.

While I realize that your question is about c# and OleDbCommand, it looks to me like Dot.Net's OleDbCommand may be operating the same as Access' ADODB.Command. Unfortunately, I don't know Dot.Net ... but that is my hunch. :-)

Jaeger

Hans, if that is the case I find it so frustrating. With some complex queries how on earth shall I know the order for Access. It so easy with sqlite or mysql. And if u dont know .NET, you should learn it. Its awesome :) – Seraph

Hmmm. If you can find a way to use DAO from .NET (Interop assemblies maybe?), you may be happier because queries executed via DAO.QueryDef do seem to honor the parameter names (IIRC) as I think you wish. As far as the parameter order requirement with ADO, it's not much of a burden for me ... maybe because I've been doing it for so many years. RE .NET, it sounds tantalizing and I'll get there eventually ... but don't hold your breath. :-) – Jaeger

I do not understand the ADO DAO thing. I have used sqlite and mysql with ADO .NET connector. they dont require the order to be preserved. Only oledb requires. So doesnt that mean its not about ADO and its something specific to MS Access? – Seraph

Parameter order isn't an issue with DAO plus Access, but is for ADO/OleDb plus Access. So I would see this as something specific to the ADO/OleDb plus Access combination, not something specific to Access itself. – Jaeger

Hans, now I get it. The right kind of answer I was lookin for. Once again thanks for being informative – Seraph

I just find out that the order of prepared statements in Access, is not the order left to right. Access use priority on subqueries !. In this example : SELECT * FROM bla WHERE type = ?1 AND age > (SELECT MIN(age) FROM bla WHERE type = ?2 AND date > ?3) parameter order has to be ?2, ?3, ?1. – Glazer

Absolutely position dependent. Names are irrelevant to the internals but certainly assist interpretation by the programmer. Suggest a diagnostic be used to dump the parm collection if problems arise.... – Expeditionary

Answer 2

The order is important because of the use of ? placeholders in the command string.

If you want to list the parameters in any order, it's best to use named parameters, such as @word, @sentence, etc.

condb.Open();
OleDbCommand dbcom = new OleDbCommand("UPDATE Word SET word=@word,sentence=@sentence,mp3=@mp3 WHERE id=@id AND exercise_id=@exercise_id", condb);
dbcom.Parameters.AddWithValue("@id", wd.ID);
dbcom.Parameters.AddWithValue("@exercise_id", wd.ExID);
dbcom.Parameters.AddWithValue("@word", wd.Name);
dbcom.Parameters.AddWithValue("@sentence", wd.Sentence);
dbcom.Parameters.AddWithValue("@mp3", wd.Mp3);                         

Josselyn

No that did not work. I mean this code works only in the order you mentioned, not in the order in my first post (even without ? placeholders) – Seraph

Interesting - maybe specific to the MS Access / OleDbCommand combination (I tried my previous solution with SQL Server and it worked fine). Perhaps try the updated answer? – Josselyn

I tried all that. It works only if the WHERE parameters are given lastly. I mean the order has to be maintained. Yes it is with MS Access. MySQL too doesnt have this issue. – Seraph

Answer 3

I have been doing some tests with using OleDbCommand and its parameters collection against an Access DB. The ordering of parameters is of course necessary, since this is a limitation of the OLE DB .NET provider. But there is a problem that you can encounter when using question marks as place holders.

Say you have a query ("stored procedure") in your Access DB that looks like this, very simplified here:

parameters
  prmFirstNumber Long,
  prmSecondNumber Long;
select
  fullName
from
  tblPersons
where 
  numberOfCars < prmFirstNumber And
  numberOfPets < prmSecondNumber And
  numberOfBooks beteween prmFirstNumber And prmSecondNumber

Here you see that simply changing to question marks would break the query.

I have found though, as a solution to this, that you can actually use names for parameters. So you can let the query above remain as it is. You just have to use the same order when you run the query. Like in this case, you first add the parameter prmFirstNumber and then prmSecondNumber, and then you run the query.

When reusing parameters, i.e. executing a query more than once and setting new values for the parameters each time, one must call the prepare method of the command object right after having defined the parameters. There are some details there that need to be fulfilled too, look at the documentation for "prepare". Not calling prepare causes strange behaviour without error messages which can corrupt your database or cause wrong information to be presented to users.

I can add also that when queries are stored in the Access DB with parameters specified, like in my example above, then the ordering of the parameters is unambiguously defined by the parameters-section.

I also made a routine, "retrieveDeclaredJetParametersInOrder", which automatically populates an OleDbCommand object with those named parameters, in the correct order. So my code can look like this:

Dim cmd As New OleDbCommand("qryInAccessDB", Conn)
cmd.CommandType = CommandType.StoredProcedure
Conn.Open()
retrieveDeclaredJetParametersInOrder(cmd)
cmd.Parameters("prmOneOfTheParametersPerhapsTheLastOneDeclared").Value = 1
cmd.Parameters("prmAnotherone").Value = 20
cmd.Parameters("prmYetAnotherPerhapsTheFirstOneDeclared").Value = 300
cmd.ExecuteNonQuery()
Conn.Close()

So, as you see, I can handle it as if parameters are named, and never have to bother with their ordering.

The retrieveDeclaredJetParametersInOrder of course adds extra time to execution, since it involves an extra call to the DB, where it retrieves the SQL-text and then parses out the parameter names and types.

Sultan

+1 for the first sentence. Thanks. Does the rest of the answer answer the specific question here? – Seraph

Thanks nawfal, yes I think it does, plus mentions some relevant warnings for strange and unexpected things that you might encounter, and how one can avoid those problems. – Sultan

Hmmm I like Jet/Ace engine, not bad for the job it does, but boy, the GUI MS provides under Office suite is horrible! – Seraph

GUI in 2003 is good, but 2007 I would agree is horrible. Never really looked at 2010. – Sultan

I don't agree, it only got better, but it can't match those clients that come along with say MySQL for instance. – Seraph