Delphi: “Parameter object is improperly defined. Inconsistent or incomplete information was provided.”

Question

This is a Function that does the following:

• Create a random Token with 8 length
• Insert that Token into the Database

• If the User has already a token, update it.

• If the User has no token, insert it.

procedure createToken(BenuNr: integer);
var
  AQ_Query:       TADOQuery;
  strToken:       string;
  intZaehler:     integer;
  const cCharSet: string = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
begin
    //Random String as Token
    SetLength(strToken, 8);
  for intZaehler := 1 to 8 do
  begin
    strToken[intZaehler] := cCharSet[1+Random(Length(cCharSet))];
  end;
  //Inserts the Token into the Database
  with AQ_Query do
  begin
    try
      AQ_Query := TADOQuery.Create(nil);
      ConnectionString := strConnectionString;
      SQL.Text := 'if EXISTS(select * from TOKN where BENU_NR = :paramBenu_NR) begin update TOKN set TOKEN = :paramTOKEN where BENU_NR = :paramBenu_NR end else insert into TOKN (BENU_NR, TOKEN) values (:paramBENU_NR,:paramTOKEN)';
      Prepared := true;
      Parameters.ParamByName('paramBENU_NR').DataType := ftInteger;
      Parameters.ParamByName('paramTOKEN').DataType := ftString;
      Parameters.ParamByName('paramBENU_NR').Value := BenuNr;
      Parameters.ParamByName('paramTOKEN').Value := strToken;
      ExecSQL;    //<< Exception as stated in the title
    finally
      Free;
    end;
  end;
end;

Executing this throws me the exception as stated in the title. I cut the above example down and voila: no more exception. Unfortunately I don't get why?

procedure createToken();
var
  AQ_Query:     TADOQuery;
  strToken:     string;
  intZaehler:      integer;
  const cCharSet:  string = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
begin
    //Random String as Token
    SetLength(strToken, 8);
  for intZaehler := 1 to 8 do
  begin
    strToken[intZaehler] := cCharSet[1+Random(Length(cCharSet))];
  end;
  //Inserts the Token into the Database
  with AQ_Query do
  begin
    try
      AQ_Query := TADOQuery.Create(nil);
      ConnectionString := strConnectionString;
      SQL.Text := 'update TOKN set TOKEN = :paramTOKEN where BENU_NR = 1';
      Prepared := true;
      Parameters.ParamByName('paramTOKEN').DataType := ftString;
      Parameters.ParamByName('paramTOKEN').Value := strToken;
      ExecSQL;   //<< No more exception
    finally
      Free;
    end;
  end;
end;

It seems that there is only 1 Parameter allowed per SQL. I am using Delphi 7 and MSSQL Server 2005

Any idea how to fix the first code block to make it work?

Answer 1

Late for OP, but might be useful for someone else. A parameter may appear only once in sql statement - it's an ADO limitation.

select * from table where field1=:param or field2=:param

This doesn't work. You can either use two different params

select * from table where field1=:param1 or field2=:param2

or use an embedded variable, if your backend is mssql:

declare @param int=:param
select * from table where field1=@param or field2=@param

Answer 2

For me this error came without parameters, when the (otherwise completely valid) SQL statement contained a varchar literal value with a colon (:) inside a quoted sub-text. E.g.

UPDATE ... SET myfield = 'foo " :bar "'

The solution can be to switch to parameters from ugly embedded literals. I haven't found other proper workaround yet.

Answer 3

Turn off the prepare. Set prepared=False on the ADO component. It appears that the server is seeingn it before both parameters exist, and preparing (compiling) it. When you execute it with two parameters, the param list does not match the prepared statement.

Answer 4

To make this work you have to use every parameter only once in the SQL clause. To use same parameter more than once just declare it with new name. I don't know why this is so but I know it can be pretty annoying.

Answer 5

Until now, I did not fix the problem.

But I guess the issue lies somewhere inside the Parameters and how they are accessed. The compiler picks them by index, not by name as I was assuming.

Having a closer look at the TADOQuery Component in the OI (especially at the TParameters section) you can see that indexes.

In the end it it probaply neccecary to first, add a parameter, assign a name to it and then insert a value, or something like this.

Answer 6

I've had a chance to try this with a compiler :) I must install one at home sometime.

While I still find your use of WITH unusual, it does seem to work OK.

I have seen the error you are getting in several cases:

1. Trying to run more than one query against a connection at once (due to threading or a timer + processMessages)
2. With TADOStoredProc when the ProcedureName is incorrect
3. Sometimes if ADO cannot parse the query - can't test this without your DB schema.

Note that there is no need in SQL Server to explicitly define the parameter type. These are automatically assigned by a OnChanged event attached to the SQL TStringList object.

As a result, it is best to either assign the SQL.Text property (as you do), or if using .Add('SELECT ...'), use a SQL.BeginUpdate/SQL.EndUpdate pair.

Original reply:

  with AQ_Query do
  begin
    try
      AQ_Query := TADOQuery.Create(nil);
      ConnectionString := strConnectionString;

While this seems to work, it seems a bit strange to refer to an object before you instantiate it.

AQ_Query should be instantiated before the with statement:

  AQ_Query := TADOQuery.Create(nil);
  with AQ_Query do
  begin
    try
      ConnectionString := strConnectionString;

Better yet don't use WITH - It's asking for trouble.

Also note that object creation should be BEFORE a try..finally. As written you would have a compiler warning. Don't ignore these - they help you write better code.

Answer 7

While this error is challenging, you can diagnose it enough to see that your query is valid. The problem is in your parameters. The best way to find the true problem is to have SQL Server Profiler tracing your database when the query comes in. It will show you how the parameters were interpreted. Copy that query out into a text editor to see where your problem is.

If you are unable to use SQL Server Profiler you should just output the values "BenuNr" and "strToken" to the screen or console so that you can truly see what you are passing in as parameters.

Answer 8

You don't need to specify DataType. After a successful call to Prepare; the parameters should be configured correctly, based on the server table definition.

My guess is that by assigning DataType, the parameter is probably reset and some information is missing, for example, ParamType should be ptInput but is reset to ptUnknown or something like that.

Try removing those lines where you set DataType and see if it helps.

Answer 9

Ouch. You just hit what our system architect at work calls "the worst error ever". It's a pretty generic error and can mean all sorts of things. But I've only ever seen it on INSERT statements, so try looking there. It's caused by the definition that ADO gets not matching the database schema in some way.

Try cutting down your query to only do the insert and use the SQL profiler included with SQL Management Studio to watch what ADO is doing when it fires it. It'll most likely ask for the structure of your table, compare that against the structure of your statement, and end up not liking what it finds, and never actually sending the INSERT command to the database.

Make sure you've got the right data types on the fields, and that you can successfully run an INSERT into that table using just those two values. That might not work--this is the worst error ever, afterall--but it should give you a starting point.