what does positional and named parameter in a query mean?

Question

here we got a positional parameter:

SELECT 
u 
FROM ForumUser u 
WHERE u.id = ?1

and here a named parameter:

SELECT 
u 
FROM ForumUser u 
WHERE u.username = :name

this is DQL (doctrine query language) but i think the concept is the same.

could someone please explain what these mean and do?

Swenson

Answer 1

A positional parameter is set by its index in the clause.

A named parameter is set by its name.

When you are setting the values, you might have the values in an array, in which case the positional form could me more useful. Alternatively, you might have them in an associative array by name, in which case the named form is more useful.

---

Update - Although the documentation refers to positional parameters as for example ?1, the examples just use ?.

This example of positional parameters maps values by position in the array provided into the positional placeholders in the query.

$q = Doctrine_Query::create()
  ->from('User u')
  ->where('u.username = ? and u.age = ?', array('Arnold', 50));

$users = $q->fetchArray();

However this example maps values by name in an associative array to their named placeholders. See how they do not need tgo be in order.

$q = Doctrine_Query::create()
  ->from('User u')
  ->where('u.username = :username  and u.age = :age',
      array(':age' => 50, ':username' => 'Arnold'));

(Have to admit I'm not a PHP guy - above based on the examples here.)

Postdate

sorry for not understanding. what index in the clause? and what arrays? can you tell me what the above function will do? i still dont get the u.id = ?1 part. – Swenson

'?1' is a placeholder in the query definition. At runtime, it will be replaced by the first value in the list of parameter values. In the given example, '?1' will be replaced by 'Arnold' when the query executes. The second example uses named parameters for the same purpose, so ':username' will be replaced with the value linked to 'username' in the runtime parameter list, in this case also 'Arnold'. – Secant

@Secant so what is a ? on its own, like in the examples I found? Does supplying a number like ?1 allow you to declare them out of sequence in the query? – Postdate

'?' is still a positional placeholder. Different databases & interface libraries might have there own syntax conventiosn. IIRC, the '?n' syntax is from Oracle a while back (I use SQL Server exclusively now), but that's the last time I did htis kind of query building so it flowed for me. There's no substitute for knowing the stuff you're using. – Secant

Answer 2

Positional parameters are specified by their order in the query. Named parameters are specified by their names.

When using positional parameters you have to add them in the same order that they are used in the query, and if you want to use the same value more than once you have to add it multiple times as separate parameters.

When using names parameters you can add them in any order you want, and a parameter can be used more than once in the query.

For example if you have a query that searches in several fields, using positional parameters it could look like this:

select u.UserId, u.UserName
from FormumUser u
where u.UserName like ? or u.Email like ? or u.Address like ?

You would have to add the search string three times as separate parameters. Using names parameters it could look like:

select u.UserId, u.UserName
from FormumUser u
where u.UserName like @find or u.Email like @find or u.Address like @find

Then you would only add one parameter, as the query can use the same parameter in three places.

(The exact syntax for using the parameters in the query of course varies depending on what database solution you are using.)

Stentor

sorry but i dont get it. can u give me a very basic example of why we should use it, what it will return? – Swenson

@ajsie: I added an example above. – Stentor

Answer 3

I don't kown if I Understood right, so this is what I think:

Positional parameters should be indexed using an integer index, and named parameters should be accessed though their names.

Example (this is pseudocode):

query.SetParameter(0, 456); // here we set value 456 to the first parameter, which has index zero query.SetParameter("username", "John Smith"); // here we set value "John Smith" to the parameter named "username"

Courthouse