Feedback: TADO Parameter failures

[BrianGooch]

Delphi 2009: why some TADO Parameter operations fail.

TADOQuery: ParamByName not available - a little note in parentheses tucked away in the on-line help; nothing obvious about it.

As a result you cannot create a collection of ADOQuery parameters: thus Parameters.AddParameter, CreateParameter, Assign values do not work. An SQL statement with the standard aram1 format, eg. INSERT INTO <tablename> VALUES param1,..... ) fails because it is not possible to assign a value to aram1 - it does not have a name in the parameter collection since you cannot create that parameter on the fly.

It matters not whether the SQL statement is in an ADOQuery or in a TADOCommand.CommandText with CommandType cmdText: the effect is just the same - the SQL statement fails.

The on-line help suggests that you can use the ordinal index of the position of the aram1 in the statement in order to assign it a value, but if there is no collection of parameters then such an ordinal index does not exist.

Stored procs have Parameters, and they work: AddParameter, CreateParameter, Assign values, etc, ... work Ok provided you follow the rules. However, it is not possible to Copy a collection of TADOStoredProc parameters to a TADOQuery because TADOQuery does not support named parameters.

You can specify the TADOQuery parameters at design time and assign them a value but this is absolutely useless for doing things on the fly. Why do the SQL twice?: once to read and once to write when you have a computer to do it for you.

I wish to thank all of you who have had a go at getting to grips with this defect in D2009. I have developed a work around for this and have found in doing so it gives you, the programmer, total control over the data and the way it is manipulated and displayed. The digging was not really a waste of time since the future gains will be worth the effort, even if it was very, very frustrating at times.

I trust this post is of help.

Kind Regards,
Brian Gooch

 

[whosrdaddy]

I tried to explain to you earlier that TADOQuery is adding the parameters itself when you set the SQL property and ParamCheck is set to True. You just need to create the query on the fly, set the SQL property and set the parameter properties through ParamByName.

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[BrianGooch]

whosrdaddy

Thanks for the comment. The fatal error message from Delphi is always that it cannot find ClientQry parameter named 'xxxx'. It always fails to find it, or any of them, and the Parameters.Count = 0. As the on-line help stated: ParamByName for TADOQuery is not available; ParamByName is available for all other DB Access components, but not TADOQuery.

Maybe it's just the version of Delphi 2009 Pro that I have that's playing up.

As it happens, the workaround has come out fine, and, as luck would have it, it's free of those TADOQuery vicissitudes.

Kind Regards,
Brian Gooch.

 

[whosrdaddy]

once again, it works:

procedure Test;

var Qry : TADOQuery;

begin
 Qry := TADOQuery.Create(nil);
 try
  Qry.Connection := ADOConnection;
  //important!
  Qry.ParamCheck := True;
  Qry.SQL.Text := 'SELECT Something FROM DaTable WHERE Id=:Id';
  // this will work since TADOQuery has parsed the SQL and knows there is a parameter called 'Id'
  Qry.Parameters.ParamByName('Id').Value := 1;
  Qry.Open;
  ShowMessage(Qry.FieldByName('Id').AsString);
  Qry.Close;
 finally
  FreeAndNil(Qry);
 end;

end;

...

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!