在ADO Query中使用参数(mysql/MyConnector) [英] Using parameters with ADO Query (mysql/MyConnector)

问题描述

今天我下载并安装了MyConnector，因此可以将Mysql与ADO一起使用，一切都安装好了，好！我可以与ODBC建立连接，并从我的delphi环境中建立连接.

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引发异常类EOleException，并显示消息参数类型错误，超出可接受范围或彼此冲突".进程已停止.使用步骤"或运行"继续.

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;

最奇怪的是，如果我关闭了在delphi中的调试功能，则此方法有效.

推荐答案

我会尝试在Adds周围添加SQL.BeginUpdate/SQL.EndUpdate，否则每次调用"Add"时都会解析SQL文本.

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

这通常是一个好主意，因为ADOQuery.SQL是一个TStringList，它具有一个设置CommandText的OnChange事件.然后，SetCommandText文本最终会调用TADOCommand.AssignCommandText，后者会进行大量的工作来解析参数，并设置CommandObject.CommandText.有时，驱动程序会因部分SQL语句而失败，但这看起来还不错.

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;

顺便说一句，嵌套的with真的很丑(让圣战开始)

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

我有时会使用with，但绝不会嵌套三个级别！如果是这样，请至少减小SQL的范围，以便它在使用Parameters之前结束.

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.

这篇关于在ADO Query中使用参数(mysql/MyConnector)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！