Search code examples
sql-serverdelphioledbado

ADO not passing first parameter value

I'm using an ADO Command object to generate parameterized queries against SQL Server.

If i generate and supply multiple parameters, all the parameter values are passed - except the first one.

If you imagine a query like:

SELECT ?, ?, ?, ?, ?

And use the ADO Command object to supply parameter values:

command.Parameters.Append(command.CreateParameter('', adInteger, adParamInput, 0, 1);
command.Parameters.Append(command.CreateParameter('', adInteger, adParamInput, 0, 2);
command.Parameters.Append(command.CreateParameter('', adInteger, adParamInput, 0, 3);
command.Parameters.Append(command.CreateParameter('', adInteger, adParamInput, 0, 4);
command.Parameters.Append(command.CreateParameter('', adInteger, adParamInput, 0, 5);

You can use Profiler to see the first parameter value is null:

enter image description here

exec sp_executesql N'SELECT @P1, @P2, @P3, @P4, @P5',N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int',NULL,2,3,4,5

I've tried it with different types, in different queries, in different orders. It's always the first parameter that refuses to be supplied to the database server.

And i can confirm that the parameter does have a value before calling Execute:

command.Parameters[0].Value

What am i doing wrong?

  • Client: Windows Vista, Windows 7, Windows 10
  • OLEDB Provider: SQLOLEDB, SQLNCLI11, MSOLEDBSQL
  • Server: SQL Server 2000, SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, SQL Sever 2017
  • Compiler: Delphi 5, Delphi XE6, Delphi 10.3 Electric Boogaloo

CMRE

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  ActiveX,
  ComObj,
  ADOint,
  Variants;

procedure Main;
var
    cs: string;
    cn: Connection;
    cmd: Command;
    p: _Parameter;
    recordsAffected: OleVariant;
begin
    cs := 'Provider=SQLOLEDB;Data Source=screwdriver;User ID=frog;Password=hunter2';
    cn := CoConnection.Create;
    WriteLn('Connecting to database...');
    cn.Open(cs, '', '', Integer(adConnectUnspecified));

    cmd := CoCommand.Create;
    cmd.CommandType := adCmdText;
    cmd.CommandText := 'IF ? IS NULL RAISERROR(''It was null'', 16, 1);';

    cmd.Parameters.Append(cmd.CreateParameter('', adinteger, adParamInput, 0, 1));

    cmd.Set_ActiveConnection(cn);

    WriteLn('Executing command');
    cmd.Execute({out}recordsAffected, Null, adExecuteNoRecords);
end;

begin
  try
    CoInitialize(nil);
     Main;
        WriteLn('Success');
  except
     on E: Exception do
        begin
            Writeln(E.ClassName, ': ', E.Message);
        end;
  end;
    WriteLn('Press enter to close...');
    ReadLn;

end.

Bonus Reading

Solution

  • You should use EmptyParam instead of Null in your cmd.Execute statement (which is the Parameters argument). 

    cmd.Execute({out}recordsAffected, EmptyParam, adExecuteNoRecords);

    See: Execute Method (ADO Command)

    The EmptyParam should be compatible with an Optional ole parameter. You could also use OleVariant(cmd).Execute in your example.