It seems MS have not fixed the parameters.refresh bug yet.If I use this
code, it would throw out an exception of 'AV in module SQLOLEDB.dll'. By the
way, my SQL is 2000 + SP4 under Win2000 +SP4.

Jack Zhong

Jack Zhong (inva***@email.com) writes:
Which code?

(in Delphi 7)

sp.Parameters.Refresh;
.......
sp.ExecProc;

sp is a stored procedure component wrapped in datamodule. There would be an
AV exception if fire sp.Parameters.Refresh;  If this line is disabled, it
works perfectly well. I knew there was a Parameters.Refresh bug in previous
SQLOLEDB.dll before, but my SQL might be the latest, it is SP4 patched.

The following is snippet of my codes in Delphi 7.

function TBiz.TransData(FGuest: TGuest; FIndex: Byte): Boolean;
begin
  begin
    with dm.prTransGuest do
      begin
         try
              Parameters.Refresh;
              Parameters.ParamByName('@ref').Value := Ref ;
              ...
              Parameters.ParamByName('@position').Value := FIndex;

              Prepared:=True ;

              ExecProc;
              Result := True;
        except
              Result := False;
        end;
      end;
  end;
end;


Jack Zhong

Jack Zhong (inva***@email.com) writes:
Show quoteHide quoteI don't know Delphi or have any access to it, so I cannot repro. But I
know we use the .Refresh method in our code (ADO + VB6), and I have not
heard about any access violation.

Besides, the .Refresh method is not in SQLOLEDB proper, but is part of
ADO. True, though, that ADO uses SQLOLEDB (or whichever provider you
use).

Is Delphi 7 a recent version of Delphi?

Merry Christmas, Erland.

I am a betrayer to VB, ^_*. Yes, there are a great many AVs in Delphi than
in VB, VB is much friendly to programmers sometimes.

Actually, the AV is somewhat strange.  There are two function routines of
similar structure in the same loop to insert data into different tables, the
other function would be OK even though there is also a Parameters.Refresh.
The only diffrence is the underlying tables are slightly different. One has
primary key and the other one contains no primary keys. Inserting data into
the latter table, *without primary key*,  might throw out AV if execute
Parameters.Refresh before insertion. I am not sure whether this might be the
point in which the problem lies. However, if I put a TADOStoredProcedure
component on the form and set the values in the properties inspector, then
no matter how many times I fire it, there is no AV at all.

By the way, for there are some differences between Pascal data types and SQL
server, I modified some parameters data types, for instance from
ftWideString to ftString, I got an AV in module msado15.dll if I enable
Parameters.Refresh.

Delphi 7 is an Adult in the Delphi family, Delphi 2006 is the latest one. D7
might be the latest one for Win32 enviroment.

Following is the snippet of my codes, I have added some comments to it. BTW,
the TBiz is a class to implement business rules.

procedure TBiz.ButtonApplyClick(FMbr: tstrings; FContact: TContact);
//class routine respond to ButtonApply click event in host application
var
  i                 : Integer;
begin
  if Assigned(FMbr) then
    begin
      for i := 0 to FMbr.Count - 1 do  //repeatedly insert data into tables
with two stored procedure
        begin
          if TGuest(FMbr.Objects[i]).Rounds <> 0 then
            begin
              if ( AddNew( TGuest(FMbr.Objects[i]) ) <> 0) then
                TransData(TGuest(FMbr.Objects[i]), i + 1);
            end; //end if
        end; //end for

      case Operation of
        0: New;             //Operation create new.
        1: Append;        //Operation append
      end;

    end;
end;


function TBiz.TransData(FGuest: TGuest; FIndex: Byte): Boolean;
begin
  begin
    with dm.prTransGuest do    //prTransGuest is a stored procedure in dm
(TDataMudle);
      begin
         try

              Parameters.Refresh;

              Parameters.ParamByName('@ref').Value := Ref ;
              ...
              Parameters.ParamByName('@position').Value := FIndex;
              Prepared;
              ExecProc;   //throw out AV exception on the second try, if
Parameters.Refresh enabled
              Result := True;
        except
              Result := False;
        end;
      end;
  end;
end;


function TBiz.AddNew(FGuest: TGuest): LongWord;
begin
  with dm.prAddNew do
    begin
      with Parameters do
        begin
          try
            try

              Parameters.Refresh;

              ParamByName('@FirstName').Value := FGuest.FirstName;
              ...
              Prepared;
              ExecProc;   //No AV even enable the above Parameters.Refresh
              Result := ParamByName('@Return_Value').Value;
            except
              result := 0;
            end;
          finally
          end;
        end;
    end;
end;

Jack Zhong (inva***@email.com) writes:
There is one thing that I find strange in your code. You are refreshing
the parameters for each time in the loop, but you never set up any
command object. Or this is implicit in some way.

Anyway, calling .Refresh for every call to the procedure is hardly good
for performance, since that is a roundtrip to the server. Ideally, you
could call .Refresh once, and the reuse the parameter collection. Un-
fortunately, when I have tried to reuse command objects in ADO, it has
started doing things I don't like at all.

This might be the difference between Delphi and VB, I am afraid. Delphi
could add parameter list in the property inspector, so it is not needed to
add parameters.refresh in the routines again. But If there were some
parameters added to the procedure and the programmer forgot to add them to
the inspector box, Delphi would throw out 'parameter not found' error.

I would move Refresh command to the form.create routine, this might avoid
refreshing SP for many times.

Thanks.

Show quoteHide quote

Popuate field with value from last row
Why query optimizer does not select the index?
Finding the right record
CHARINDEX in CASE Within SELECT Statement
Default Value Calculation