Using parameters with ADO Query (mysql/MyConnector)

Question

Today I downloaded and installed MyConnector so I can use Mysql with ADO, everything installed, OK!, I can make connection with ODBC and do a connection from my delphi environment.

when I build my Query at runetime, I get an error saying :

Project Project1.exe raised exception class EOleException with message 'Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another'. Process stopped. Use Step or Run to continue.

function TForm1.CreateSQL : TADOQuery;
begin
  result := TADOQuery.create(self);
  with Result do
  begin
    Connection     := MainConnection;
    CursorLocation := clUseServer;
    CursorType     := ctStatic;
    CacheSize      := 50;
    AutoCalcFields := true;
    ParamCheck     := true;
    Prepared       := true;
  end;
end;

procedure TForm1.login();
begin
  with CreateSQL do
  try
    with SQL do
    begin
      add('SELECT                       ');
      add('  *                          ');
      add('FROM                         ');
      add('  LisenswebUsers             ');
      add('WHERE                        ');
      add('  UserName     = :MyUsername '); // debugger exception here
      add('AND                          ');
      add('  UserPassword = :MyPassword '); // debugger exception here
      with Parameters do
      begin
        ParamByName('MyUsername').value := txtLogin.text;
        ParamByName('MyPassword').value := strmd5(txtPassword.text);
      end;
      Open;

      if Recordcount <> 1 then
      begin
        lblLoggedinAs.Text := format('Du er logget inn som: %s (%s)',[FieldByName('Username').AsString,FieldByName('UserEmailaddress').AsString]);
        MainPageControl.ActivePageIndex := 1;
      end else
      begin
        txtPassword.Text := '';
        txtPassword.SetFocus;
      end;
    end;
  finally
   free;
  end;
end;

The strangest thing is that this works if I turn off debugging in delphi.

Answer 1

I would try adding SQL.BeginUpdate/SQL.EndUpdate around the Adds, otherwise the SQL text will be parsed every time you call "Add".

This is generally a good idea, as ADOQuery.SQL is a TStringList that has an OnChange event that sets the CommandText. SetCommandText text then end up calling TADOCommand.AssignCommandText which does a fair amount of work parsing params, and setting CommandObject.CommandText. Sometimes drivers will fail with partial SQL statements, but this stuff looks OK.

I had a similar problem many years ago - that's why I learnt about this stuff!

procedure TForm1.login();
var
  Qry : TADOQuery;
begin
  Qry := CreateSQL;
  try
    Qry.SQL.BeginUpdate;

    Qry.SQL.Add('SELECT');
    Qry.SQL.Add('  *');
    Qry.SQL.Add('FROM');
    Qry.SQL.Add('  LisenswebUsers');
    Qry.SQL.Add('WHERE UserName = :MyUsername '); // debugger exception here
    Qry.SQL.Add('  AND UserPassword = :MyPassword '); // debugger exception here

    Qry.SQL.EndUpdate;
    Qry.Parameters.ParamByName('MyUsername').value := txtLogin.text;
    Qry.Parameters.ParamByName('MyPassword').value := strmd5(txtPassword.text);
    Qry.Open;

    if Qry.Recordcount <> 1 then
    begin
      lblLoggedinAs.Text := format('Du er logget inn som: %s (%s)',[FieldByName('Username').AsString,FieldByName('UserEmailaddress').AsString]);
      MainPageControl.ActivePageIndex := 1;
    end
    else
    begin
      txtPassword.Text := '';
      txtPassword.SetFocus;
    end;
  finally
    Qry.Free;
  end;
end;

BTW, the nested withs are really ugly (let the holy war begin)

I will sometimes use with, but would never nest three levels! If you are, at least reduce the scope of with SQL so it ends before with Parameters.

Answer 2

Try setting an explicit datatype :

CreateSql.Parameters.ParamByName('MyUserName').DataType := ftString;

Answer 3

In my case, defining the parameters and assigning the query string before assigning the connection corrected the problem. The query executes successfully in both cases, but the TADOQuery component internally raises (and subsequently swallows) the EOleException noted in OP if the connection is assigned before the parameterized query.

//LADOQuery.Connection := LADOConnection;  // Exception @ LADOQuery.Text:=...

Param := LADOQuery.Parameters.AddParameter;
Param.Name := 'rid';
Param.DataType := ftFixedChar;
Param := LADOQuery.Parameters.AddParameter;
Param.Name := 'qd';
Param.DataType := ftLongWord;

LADOQuery.SQL.Clear;
LADOQuery.SQL.Text:='SELECT Val FROM table WHERE v1=:rid AND v2=:qd';

LADOQuery.Connection := LADOConnection;  // OK!

I'm open to explanations as to why this is the case - nothing in the documentation seems to suggest a need for this order of operations.

The exception is raised in ADODB.pas in TADOCommand.AssignCommandText here

 try
   // Retrieve additional parameter info from the server if supported 
   Parameters.InternalRefresh; 

where this branch is only followed if the TADOQuery is attached to a live connection. InternalRefresh performs :

if OLEDBParameters.GetParameterInfo(ParamCount, 
                                    PDBPARAMINFO(ParamInfo), 
                                    @NamesBuffer) = S_OK then
  for I := 0 to ParamCount - 1 do
    with ParamInfo[I] do
    begin
      // When no default name, fabricate one like ADO does 
      if pwszName = nil then
      Name := 'Param' + IntToStr(I+1) else // Do not localize 
      Name := pwszName;
      // ADO maps DBTYPE_BYTES to adVarBinary 
      if wType = DBTYPE_BYTES then wType := adVarBinary;
      // ADO maps DBTYPE_STR to adVarChar 
      if wType = DBTYPE_STR then wType := adVarChar;
      // ADO maps DBTYPE_WSTR to adVarWChar 
      if wType = DBTYPE_WSTR then wType := adVarWChar;
      Direction := dwFlags and $F;
      // Verify that the Direction is initialized 
      if Direction = adParamUnknown then Direction := adParamInput;
      Parameter := Command.CommandObject.CreateParameter(Name, wType, Direction, ulParamSize, EmptyParam);
      Parameter.Precision := bPrecision;
      Parameter.NumericScale := ParamInfo[I].bScale;
      // EOleException raised here  vvvvvvvvv
      Parameter.Attributes := dwFlags and $FFFFFFF0; //Mask out Input/Output flags 
      AddParameter.FParameter := Parameter;
    end;

The problem definitely seems to be at the OLE level, probably because the MySQL ODBC driver does not support returning this information (or returns invalid information). The exception is raised behind the _Parameter interface when setting

 Parameter.Attributes := dwFlags and $FFFFFFF0; 

using what seem to be invalid values (dwFlags = 320 -> bits set above DBPARAMFLAGSENUM defined length) returned from GetParameterInfo. Exception handling for flow control seems the only option given that the interface does not provide any mechanism to check values before setting them (and triggering exceptions).

---

Update :

It turns out there is an open QC about this : http://qc.embarcadero.com/wc/qcmain.aspx?d=107267

Answer 4

BeginUpdate/EndUpdate pair is not adequate. Use AddParameter to add parameters explicity before assigning sql command. Like:

var
  Qry : TADOQuery;
begin
  Qry := CreateSQL;
  try
    with Qry.Parameters.AddParameter do
    begin
      Name := 'MyUsername';
      DataType := ftString; 
    end;
    with Qry.Parameters.AddParameter do
    begin
      Name := 'MyPassword';
      DataType := ftString; 
    end;

    Qry.SQL.BeginUpdate;

    Qry.SQL.Add('SELECT');
    Qry.SQL.Add('  *');
    Qry.SQL.Add('FROM');
    Qry.SQL.Add('  LisenswebUsers');
    Qry.SQL.Add('WHERE UserName = :MyUsername '); // debugger exception here
    Qry.SQL.Add('  AND UserPassword = :MyPassword '); // debugger exception here

    Qry.SQL.EndUpdate;
...